Oracle Performance and Engineering

Learned something new today. It never stops, does it?

I was looking into one DB to find out most expensive SQL statements. That's my principal "tuning arsenal". i.e., the query -

SELECT sql_text, buffer_gets, executions, disk_reads, cpu_time, elapsed_time
  FROM (SELECT   *
            FROM v$sqlarea
        ORDER BY elapsed_time desc)
 WHERE ROWNUM < 25

Many a time, you won't get full SQL_text from this query. But then you can easily modify this one and join it with V$SQL (or V$SQL_WITH_NEWLINES). Many of my collegues here - wanting to start their career in performance tuning - ask me for opinion on 'performance tools'. I always tell them about the query above first. This, along with Statspack or AWRSTATS report gathered at calculated interval, can give you pretty much all the data to pinpoint the problem.

However, as I was scanning the V$SQLAREA - I found queries like following taking about 20% response time of the "Top 100" queries. (Another tactic I use is build a histogram with Top 10-25-100 queries. Typically 80 to 90% of database "work" is done by these. I then generate pie chart for external reports to show how much each single query - sometimes bottomed up to application transaction level - consume in the DB.).

SELECT /*+NESTED_TABLE_GET_REFS+*/
       "SCH"."KWFL_STEP_TRANSACTIONS".*
  FROM "SCH"."KWFL_STEP_TRANSACTIONS"

It looks so much like those Oracle recursive queries, doesn't it? I found out that Oracle "exp" (export) issues the query with the above hint for every table! This is an undocumented hint, supposed to work with "Nested Tables" (though the table above is not a Nested one). More info about the hint can be found here and here. Apparently, the hint will just be ignored if the table is not a nested table.