A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.
This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 18.104.22.168).
As an extra however, this version now differentiates between general CPU usage and in-memory CPU usage (similar to 22.214.171.124 Real-Time SQL Monitoring). This is not done in all possible sections of the output yet, but the most important ones are already covered.
In an earlier (not very serious) post about count(*) I pointed out how the optimizer sometimes does a redundant “bitmap conversion to rowid” when counting. In the basic count(*) example I showed this wasn’t a realistic issue unless you had set cursor_sharing to “force” (or the now-deprecated “similar”). There are, however, some cases where the optimizer can do this in more realistic circumstances and this posting models a scenario I came across a few years ago. The exact execution path has changed over time (i.e. version) but the anomaly persists, even in 126.96.36.199.
First we create a “fact” table and a dimension table, with a bitmap index on the fact table and a corresponding primary key on the dimension table:
One thing you (ought to) learn very early on in an Oracle career is that there are always cases you haven’t previously considered. It’s a feature that is frequently the downfall of “I found it on the internet” SQL. Here’s one (heavily paraphrased) example that appeared on the OTN database forum a few days ago:
select table_name,round((blocks*8),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';
select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';
The result from the first query is 704 kb, the result from the second is 25.4 kb … fragmentation, rebuild, CTAS etc. etc.
A couple of days ago I highlighted an optimizer anomaly caused by the presence of an index with a descending column. This was a minor (unrelated) detail that appeared in a problem on OTN where the optimizer was using an index FULL scan when someone was expecting to see an index RANGE scan. My earlier posting supplies the SQL to create the table and indexes I used to model the problem – and in this posting I’ll explain the problem and answer the central question.
I’ve just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 188.8.131.52 wasfixed in 10.2.0.3 – and this is true for the simple example in the posting; but a recent question on the OTN database forum has shown that the bug still appears in more complex cases. Here’s some code to create a table and two indexes:
Because you can never have enough of a good thing.
Here’s a thought – The optimizer doesn’t treat all constants equally. No explanations, just read the code – execution plans at the end:
A new version 4.2 of the XPLAN_ASH utility is available for download.
There were no too significant changes in this release, mainly some new sections related to I/O figures were added.
One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings for CMD.EXE under Windows for example you might have to adjust accordingly to prevent ugly line wrapping.
Here are the notes from the change log:
- New sections "Concurrent activity I/O Summary based on ASH" and "Concurrent activity I/O Summary per Instance based on ASH" to see the I/O activity summary for concurrent activity
Here’s a query (with a few hints to control how I want Oracle to run it) that demonstrates the difficulty of trying to solve problems by hinting (and the need to make sure you know where all your hinted code is):
Here’s a little query I wrote some time ago to see where my time went while running a query. It’s nothing sophisticated, just one of those simple things you can do with v$active_session_history (or dba_hist_active_sess_history, if you don’t get to the crime scene in time).
One of the presentations I went to at the DOAG conference earlier on this month was called “PL/SQL Tuning, finding the perf. bottleneck with hierarchical profiler” by Radu Parvu from Finland. If you do a lot of PL/SQL programming and haven’t noticed the dbms_hprof package yet make sure you take a good look at it.
A peripheral question that came up at the end of the session asked about problems with line numbers in pl/sql procedures; why, when you get a run-time error, does the reported line number sometimes look wrong, and how do you find the right line. I can answer (or give at least one reason for) the first part, but not the second part; Julian Dontcheff had an answer for the second bit, but unfortunately I failed to take a note of it.