Oracle database features wish list – 2 (V$SQL_UNSHARED_CURSOR)

It’s been too long from a previous wish for Oracle. While reading a MOS updated articles (in a new HTML interface which looks nice, much better than flash-based introduction) I’ve seen this document that made me write this blog post.
As you know, Oracle has V$SQL_SHARED_CURSOR view that helps to understand why a particular child cursor has been built due inability to share existing child cursor. Since 11.2.0.2 there’s also a REASON column that “helps” by storing more data in an XML. The thing is: in my opinion this view usability is far from perfect. Why? Because 1) usually application could suffer from different issues related to cursor sharing 2) child cursors come and go as garbage without traces left in the dynamic views. So, if you are interested in just one particular child cursor, then V$SQL_SHARED_CURSOR will work just fine for you (don’t forget to use nonshared.sql from TPT). But it’s not too useful for a statistical analysis on SQL or instance level. I want to know Top 10 causes why a given SQL had unshared cursors during its lifetime. Or the same information on the instance level. These questions are impossible to answer easy way & correctly right know. That’s why it would be nice to have V$SQL_UNSHARED_CURSOR with columns SQL_ID, REASON, VALUE. For each SQL_ID it would report how many times a particular REASON of non-shared cursor was encountered. Right now there’re 64 reasons in V$SQL_SHARED_CURSOR, so to support V$SQL_UNSHARED_CURCOR it would be necessary to add at least 64 bytes to each parent cursor plus some infrastructure overhead. I don’t know too much details but compared to a normal child cursor size of 15K, 100-ish bytes doesn’t look much to me. And the profit would be huge.
To support similar counters on the system level it’s logical to add them as statistics to V$SYSSTAT. It’s easy and fits perfectly well to AWR reports. Let’s say, you see substantial hard parse (sharing criteria) elapsed time or “hard parse (bind mismatch) elapsed time” reported in AWR report. Then you just scroll down to statistics section of the report and see Top reasons for these issues.
What do you think?

RuOUG in Saint Petersburg

On February 10th I was in Saint Petersburg on a seminar organized by Russian Oracle User Group. Actually, it was mostly prepared by company called Devexperts. Seminar took place in their office, with most presentations done by their people.

FBI trouble

In our application we extensively use a function-based index on an important table. Couple of days ago I’ve seen an interesting issue associated with this FBI, view and a GROUP BY query. I have to say I don’t have an explanation what exactly it is and how I should call it properly, hence just “trouble” in the subject line.

Oracle Core

As Martin I had a pleasure to “unofficially” review Jonathan’s latest book, Oracle Core. It was my first experience in the area of printed content. If you want to know little bit more how the process went, my opinion on the book and what to expect from it – I’ll try to cover these points here.

Recycle bin

Recent Charles Hooper’s post on the topic of Recycle bin (which is, BTW, documented behavior) reminded me of an issue with that functionality I’ve seen recently. The problem was a single-row INSERT INTO table VALUES () statement was hanging for more than an hour burning CPU.

EXPLAIN PLAN shared memory

Recently I did an investigation of an ORA-04031 which happens almost regularly on a 10.2.0.4 Oracle database server with 9G of memory allocated to shared pool and disabled Automatic Shared Memory Management (sga_target=0). The problem query is a report that is a very big SQL query – more than 200K of plain text. The exact error message is “ORA-04031: unable to allocate 235704 bytes of shared memory (“shared pool”,”select * from (…”,”Typecheck”,”qry_text : qcpisqt”)”. Here I’ll briefly describe what I did to identify root cause of the error and will talk about one interesting detail of the EXPLAIN PLAN as it was used to reproduce the issue.

CBO isn’t perfect

And you should remember that. Here is a nice example how Cost Based Optimizer can miss an obvious option (which is available to human eye and Oracle run-time with a hint) while searching for the best plan. CBO simply doesn’t consider Index Skip Scan with constant ‘in list’ predicates in the query, although it costs skip scan for a join. Such bits are always popping up here and there, so you just can’t say “The Cost Based Optimizer examines all of the possible plans for a SQL statement …”, even if Optimizer Team tells you CBO should do so. There will always be places where CBO will do less than possible to come to the best plan and will need a help from your side, such as re-written SQL or a hint.

16K CR gets for UQ index scan? Easy. Kind of.

I planned to write on this for quite some time, but failed to do so. Sorry about that. Today I finally got time and desire to describe a situation from the title. It was observed on an Oracle 9.2.0.8 running Solaris SPARC; manifested itself as a severe CPU burning at 100% utilization with ‘latch free’ on the first place of the Top 5 wait events in Statspack report.

Scalar subquery unnesting

Here is a nice example of what Oracle 11.2.0.2 is able to do with a subquery inside an expression. It can unnest it – that is a new 11.2.0.2 feature of the transformation part of the CBO.

JIT

Starting with Oracle 11gR1 Oracle JVM includes Just-in-Time compiler. Its goal is to convert JVM bytecode into platform-specific native code based on the performance data gathered in run-time. It is stated in the documentation that JIT “enables much faster execution” – and this is, in general, true. There are ways to control the behavior of JIT, one way is described in the MOS Doc ID 1309858.1, and another one here.

ORDER BY

This is a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. The mantra is very simple:

Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

Repeat it as necessary many times everywhere, because it’s true (Update: but see the first comment from Sokrates for an exception to the rule). I want to say it again because yesterday I’ve seen it in action. A query that has been working correctly for several years without an issue has started to randomly return rows without an expected order. The query looks like this:

select ...
  from (select ...
          from t
          where ...
         order by col1
       ) t1
     , t2
 where t1.id = t2.id;

i.e. it joins an inline view with another table on the primary key column. It worked well before 11g due to the fact that t2 was always joined using nested loops. However, after an upgrade the CBO has decided to use nested loops join batching which causes the order of rows to change when the data is on disk. That’s it – the query starts to return “unordered” result set sometimes, which in fact doesn’t have to be ordered. I also recommend to read Doc 378254.1 Order of the resultset changes when using a different version/patchset.

sunoraclefaster

I saw the press release on the stunning 27 node RAC, but somehow I missed how Oracle uses this achievement in advertising. The press release talks about throughput whereas the picture:

hides the main message behind images of children’s bicycle (Superdome) and what looks like an F1 car from 70s (for an IBM P7), whereas Oracle’s result is associated with a fighter. I think this is wrong to make such an analogy and makes one to believe that one system has an incredibly fast response times – which is obviously not true. They all have similar numbers (except for some strange figures for IBM) – I mean, they are not far away from each other (OK, Oracle’s result looks better to me because it is more consistent – or maybe I misinterpreted FDRs), and the result just shows the transactions throughput. I really can’t imagine who needs this 27 heads monster or why Oracle did that benchmark after all when there are smaller yet powerful alternatives.

Distinct placement

As a follow-up to a recent post on different names Oracle can use for the intermediate views, here is a quick example of the technique called distinct placement.

Database Machine 3D tours

Somehow I missed a post in the Oracle Database Insider blog with the links to 3D tours of the latest Database Machines – X2-2 and X2-8. I must say they’ve impressed me. Not by the amount of technical details these simple tours have, but rather the way they present data. It’s easy. It’s understandable. It’s absolutely not Oracle style. If you are following Oracle’s press releases you know what I mean. They suck. I spend time reading press releases to get WTF marketing people want to say. Go here for example and tell me what is the configuration of new servers in s/c/t. And after you get that info try to understand WTF the word “clustered” is doing there. Now imagine all that crap is thrown away and the data is presented visually in a simple way… I hope some day Oracle will do that regularly.

Enabling constraint in parallel

Recently I did some tuning of data generation scripts, which purpose is to build large amount of representative data for application testing. Direct-path inserts are in use and as a prerequisite all constraints and indexes on target tables are disabled before the load and are enabled after it. Since I wanted to utilize available resources on the machine for that task, almost each step uses parallel execution. Well, kind of almost, because enabling constraints didn’t run in parallel, although I’ve politely asked Oracle to do so. I’ll explain here why it didn’t work.

Automatic shared memory resize with disabled ASMM/AMM

Thanks to Nigel Antell – he has posted a comment on one of Tanel Poder’s blog notes with a link to MOS Doc ID 1269139.1. In this note a parameter called _MEMORY_IMM_MODE_WITHOUT_AUTOSGA is described. It allows Oracle 11gR2 to adjust sizes of shared memory structures even if Automatic (Shared) Memory Management have been explicitly disabled; with the main purpose of avoiding ORA-04031 error. This is nice.
PS. I wonder if it was done by Oracle development specifically due to many customers don’t like ASMM (for ex. because of negative impact on application) but encounter ORA-04031 from time to time, which results in many opened SRs. Who knows.

PQ_DISTRIBUTE enhancement – Part 1

Oracle 11g has introduced a hell lot of new features related to Parallel Execution – like statement queuing, in-memory PX and auto-DOP, to name a few. There are also small bits of enhancements here and there – like default value of the PARALLEL_EXECUTION_MESSAGE_SIZE and an introduction of an easy way to stick PX slaves to a current RAC node with PARALLEL_FORCE_LOCAL. As I’ve recently discovered, PQ_DISTRIBUTE hint used only for joins prior to 11gR2, can now be used for load operations.

_connect_by_use_union_all

This is just a short note on the parameter introduced in the 11gR2 called _connect_by_use_union_all. I’ve noticed it for the first time in Doc ID 7210630.8, which gives a brief overview of the changes made to the way CBO generates plans for hierarchical queries. As usually happens, the change helps to one problem, but produces a bunch of unexpected side effects, such as wrong or incorrect results, and even ORA-00904 in a simple case. All these bugs have been fixed in the 11.2.0.2 patch set, but who knows how many issues related to the change are still there? If you see something unusual with a standard Oracle hierarchical query in the 11gR2, I think it’s good to try turning this parameter off and see if it helps.

More History

In a recent Oracle Magazine there’s a question from Marco Pinzuti about why Oracle Magazine articles are available for the past 5 years only. The editors respond they do not host old articles because technology moves quite fast and there’s no reason to keep old content which is most likely not relevant to current users. Although I agree with this fact – Oracle RDBMS indeed move pretty fast – I do not agree with Oracle’s approach of removing oldish content from Oracle website, because
1) I don’t like broken links
2) from time to time here and there pops up a question “where can I get the article, it’s not available anymore”
3) there’s a lot more “suspect” content out there which sometimes is completely wrong, so everyone should remember about this
Good thing is they say

We do not currently plan to maintain a historical archive for most content that is more than five years old, but if other readers are interested in an Oracle Magazine archive, please send an e-mail to opubedit_us@oracle.com

If you, like me, think that old articles should not be deleted from Oracle website, you simply have to write an e-mail to them. You can help this “History Campaign” by spreading the word about such option too.

Is this really a patch set?

The title of the post is a question taken from a MOS Doc ID 1189783.1 named “Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2″. Yes, the first patch set for 11gR2 is out for Linux x86 and x86-64 (patch number is 10098816). It’s 5G in size, BTW. There’s no patch set release notes, though. I’ll update the post when they become available.

Update 11-SEP: the patch is no longer available for downloading.
Update 13-SEP: the patch is now available.
Update 16-SEP: patch set release notes are now available