Flashback query allows to get data as of required point in time. It’s a nice feature. It’s strange in the implementation though. If you try to use AS OF SCN query with bind variables, it won’t help you to keep number of child cursors low: each execution (independently of the incoming SCN value) will cause a new child cursor to appear with FLASHBACK_CURSOR as the cause. I don’t understand why Oracle is doing it this way.
I’ve just noticed that the new HTML MOS introduced a nice addition to the old ORA-600/ORA-7445 Error Look-up Tool: now there is a new ORA-600 Troubleshooting Tool (if the link is not opened automatically for you try refreshing the page with F5 – works for me in Chrome).
Within a few minutes it allows you to upload a generated trace file and then automatically redirects you to the search results based on the error reported in the trace. The tool should be useful to those who don’t know what to do with the trace. It also allows you to save the results in a “report”. It’s definitely a very nice addition to the MOS functionality.
Thanks to everyone for spreading the link and participating in the poll on statistics. As it was pointed out in the comments, the poll isn’t very specific, I know. My point was to get an idea how many people use the defaults (I considered Oracle version 11g) for stats collection, how many do a very simple and reasonable tweak to get more stable execution plans, and how many people prefer custom statistics management. Well, I think the results will not change much and here are as following (326 votes at the time of writing):
42% use the defaults (if you truncate the value. I can’t put something other than 42 here, sorry guys :-))
16% don’t like unexpected histograms 😀 and prefer to take situation under control
OMG another 42% (since it’s not Russian elections it has to be 100% in total, you know) use custom statistics management
Nice! Now we know that the defaults are suited to less than a half of Oracle installations. And my opinion is there would be more than a half using the defaults, should Oracle decided to switch the default METHOD_OPT value to ‘FOR ALL COLUMNS SIZE REPEAT’. Consider Tim Hall’s reply “On low priority systems the defaults are fine in many cases”, and think about why defaults are fine for such systems? My humble opinion is: nobody cares about low priority systems. So it doesn’t really matter if it’s reliable or not, whether there are histograms present or not, whether a system is stable or not – it’s low priority anyway. It will survive somehow. For me it means it’s not too much of a problem to go back to the future and modify default METHOD_OPT so that no histograms are gathered initially by default – as all should do manually now /me thinks.
PS. I wouldn’t mind switching GATHER_TEMP back to the FALSE and returning it to the documentation too, as it was in 9i.
This is a short note on the topic of information exchange. I use OraNA.info to get up-to-date technology news. This is a blog aggregator I’ve tried first and have been using it since then, which is 3-4 years I guess. It’s great 🙂 and I’d like to use this opportunity to say Thank You to Eddie Awad for a great blogs collection!
If you still don’t use blog aggregators and rely on your own blog collection in Google Reader or something else, then consider using one entry point instead of multiple, it’s much easier. Also I’d like to suggest using contact form on the OraNA to suggest new blogs if you know some which are not aggregated. I use it regularly when I find something interesting; I have contributed about a dozen of new blogs to OraNA so far. You can [should] do the same.
Here is a poll prompted by today twitter talks on the default METHOD_OPT value (which is ‘for all columns size auto’ since 10g) and automatic statistics gathering job (auto-task since 11g). CBO development team suggests to use the default job to gather statistics with default options. Do you use the job exactly this way with real production databases? I’m very interested to see some numbers. Please share the poll link http://poll.fm/3n7pn so that more people vote. Thanks.
I’ve a level 5 Statspack report from a real production 11.2.0.2 database with the following Top 5 Timed events section:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
log buffer space 28 1,396 49870 41.0
buffer busy waits 51 1,049 20567 30.8
CPU time 320 9.4
log file parallel write 4,011 159 40 4.7
log file sync 3,275 153 47 4.5
The complete report (without SQL and several other sections) is here. Can you explain what & why is wrong in this Top?
I must admit I don’t have the definitive answer (probably yet), but I have partial explanation of the issue as well as several ideas.
If you you want to get an idea how some feature may be implemented by Oracle, then you ought to have a look for patents. Here are several examples I find quite interesting and containing valuable information:
METHOD AND SYSTEM FOR IMPLEMENTING EFFICIENT CURSOR PRUNING – a very good-looking patent that describes how Oracle handles sharing of the child cursors nicely. Actually after reading this I realized that V$SQL_SHARED_CURSOR isn’t just an informational data supplied for diagnostic purpose, but rather it’s used and supported by Oracle to minimize number of comparisons while searching for a child with the required properties.
EXTENDED CURSOR SHARING – this one is also on the topic of cursor sharing features related to adaptive optimization in run-time
The list can go on. Just use “assignee:oracle” in the search box if you want to see Oracle’s patents only. What I like the most in these documents is they are unique. You will rarely find such a precisely described algorithms with block diagrams describing inner workings of a potential database system. Of course the information in patents is not the exact features description, nevertheless I find it quite useful.
In the previous post I wrote about strangely behaving V$SQL. For some reason there were duplicate rows leading to wrong results issue when running DBMS_XPLAN.DISPLAY_CURSOR for a particular child cursor. I tried to reproduce the issue using simple test case – and it was reproduced.
The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on 11.2.0.3 instance.
I like Load profile section of Statspack or AWR reports (who doesn’t). It’s short and gives a brief understanding of what kind of work a database does. But what if you don’t have an access to Statspack or AWR but still want to see something similar? It’s possible to use V$SYSMETRIC to get this numbers for last 60 or 15 seconds. I wanted to write a script to do this for a long time. Here it is.
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?
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.
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.
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.
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.
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.
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.
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.
