Kerry Osborne's Oracle Blog

Well I’ve been holed up playing with an Exadata V2 machine for the past several weeks. Wow. Very interesting technology.

I must say that I believe the concept of offloading SQL processing to the storage layer is a game changer and I wouldn’t be at all surprised to see this as a standard feature a few years from now. What that means for other storage vendors is unclear at this point. So for this first post on the topic let me just describe the configuration (and some potential upgrades).

The basic architecture consists of a set of database severs and a set of storage servers.

Database Servers:

• Sun x4170 (1RU 64x server)
• 2 – Quad-core Intel Xeon E5540 2.53GHz processors
• 72G Ram (18x4G Dimms – max of 144G using 8G DIMMs)
• Dual-Port QDR InfiniBand Host Channel Adapter
• HBA with 512MB Battery Backed Write Cache (only for internal disks???)
• 4 – 146G internal drives (SAS 10,000 RPM)
• dual hot swappable power supplies
• no spare/empty slots!

Here’s what the Database Servers look like:

Storage Servers:

• Sun x4275 (2RU 64x server)
• 2 – Quad-core Intel Xeon E5540 (2.53GHz) processors
• 24G Ram
• Dual-Port QDR InfiniBand Host Channel Adapter
• HBA with 512MB Battery Backed Write Cache (only for internal disks???)
• dual hot swappable power supplies
• 4 – 96G Sun Flash PCIe Cards (total of 384 GB)
• 12 – 600 GB 15,000 RPM SAS or 2 TB 7,200 RPM SATA

Continue reading ‘What is Exadata?’ »

I ran across a funny SQL statement recently (funny strange, not funny ha ha – well actually funny ha ha too I guess). It had a first_rows hint like so:

select /*+ FIRST_ROWS (999999999)  */ 
"MANDT" , "OPBEL" , "OPUPW" , "OPUPK" , "OPUPZ" , "BUKRS" , "GSBER" , "BUPLA" , "SEGMENT" , 
"AUGST" , "GPART" , "VTREF" , "VTRE2" , "VKONT" , "ABWBL" , "ABWTP" , "ABWKT" , "APPLK" , 
"HVORG" , "TVORG" , "KOFIZ" , "SPART" , "HKONT", "MWSKZ" , "MWSZKZ" , "XANZA" , "STAKZ" , 
"BLDAT" , "BUDAT" , "OPTXT" , "WAERS" , "FAEDN", "FAEDS" , "VERKZ" , "STUDT" , "SKTPZ" , 
"XMANL" , "KURSF" , "BETRH" , "BETRW" , "BETR2" , "BETR3" , "SKFBT" , "SBETH" , "SBETW" , 
"SBET2" , "SBET3" , "MWSKO" , "MWVKO" , "TXRUL" , "SPZAH" , "PYMET" , "PYBUK" , "PERNR" , 
"GRKEY" , "PERSL" , "XAESP" , "AUGDT" , "AUGBL" , "AUGBD" , "AUGRD" , "AUGWA" , "AUGBT" , 
"AUGBS" , "AUGSK" , "AUGVD" , "AUGOB" , "WHANG" , "WHGRP" , "XEIPH" , "MAHNV" , "MANSP" , 
"XAUGP" , "ABRZU" , "ABRZO" , "FDGRP" , "FDLEV" , "FDZTG", "FDWBT" , "XTAUS" , "AUGRS" , 
"PYGRP" , "PDTYP" , "SPERZ" , "INFOZ" , "TXJCD" , "TXDAT" ,"VBUND" , "KONTT" , "KONTL" , 
"OPSTA" , "BLART" , "EMGPA" , "EMBVT" , "EMADR" , "IKEY" , "EUROU" , "XRAGL" , "ASTKZ" , 
"ASBLG" , "XBLNR" , "INKPS" , "RNDPS" , "QSSKZ" , "QSSEW" , "QSPTP" , "QSSHB" , "QBSHB" , 
"QSZNR" , "RFUPK" , "STRKZ" , "FITPR" , "XPYOR" , "LANDL" , "INTBU", "EMCRD" , "C4EYE" , 
"C4EYP" , "SCTAX" , "STTAX" , "STZAL" , "ORUPZ" , "NEGBU" , "SUBAP" , "PSWSL" , "PSWBT" , 
"PSWTX" , "PSGRP" , "FINRE" , "RDSTA" , "RDSTB" , "DEAKTIV" , "SGRKEY", "SOLLDAT" , "RECPT" , 
"TOCOLLECT" , "EINMALANF" , "VORAUSZAHL" , "APERIODIC" , "ABRABS" , "GRBBP" , "ASMETH" , 
"INT_CROSSREFNO" , "ETHPPM" , "PAYFREQID" , "INVOICING_PARTY" , "PPMST" , "LOGNO" , "APERIODICT" , 
"ADD_REFOBJ" , "ADD_REFOBJID" , "ADD_SERVICE" , "ZZAGENCY" , "ZZ_EXT_REF" , "ZZ_PAY_AGENT" , 
"ZZFUNDSOURCE" , "ZZINSTALLMENT" , "Z_PROD_ID" , "ZZUSERNAME" , "ZZWF_STAT" , "ZZPAYCHANNEL" 
FROM "DFKKOP" 
WHERE "MANDT" = :A0 -- NDV=1
AND "BUKRS" = :A1 -- NDV=1 
AND "AUGST" = :A2 -- NDV=2 
AND "FAEDN" < :A3 -- less than today probably all records
AND ( "PYMET" = :A4 OR "PYMET" = :A5 ) -- NDV=8
AND ROWNUM <= :A6; -- less than 1B

Yes - that's a first rows hint with about a billion as the number of rows to optimizer for.

The reason I noticed it is that it runs for 15 hours before getting a Snapshot Too Old error. The attempted solution was to restart it the next day (thinking maybe it will run better the second time I guess). The table has roughly 100M rows. There was no index on PYMET which is unfortunate as the two values requested account for only about 0.15% (not 15%, 0.15%). The optimizer chooses an index on MANDT, BURKRS, AUGST, FAEDN and as you might expect, it doesn't work very well (see the NDV comments I added to the statement).

Funny things:

The First_Rows hint is requesting the Oracle optimizer to return the first billion records as fast as possible (even though there are only 100M rows).

The documentation for the First_Rows hint in 11g looks like this:

The FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently. For integer, specify the number of rows to return.

For example, the optimizer uses the query optimization approach to optimize the following statement for best response time:

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;

In this example each department contains many employees. The user wants the first 10 employees of department 20 to be displayed as quickly as possible.

So I can see where the developers might have interpreted this as the ever elusive "Go Fast" hint.

The developers also added "and rownum < 999999999" to the where clause which limits the amount of rows that can be returned. I'm not sure whether they knew it or not, but this clause also has the same affect as the hint. That is to say that the clause causes the optimizer to modify it's calculations as if the first_rows_N hint had been applied. Maybe the developers weren't getting the "go fast" behavior they expected from the hint and after doing some research found that the "rownum <" syntax would basically do the same thing. I'm guessing that's the case because I can't see why they would really want to limit the number of rows coming back, but I'm not sure. It's a very odd statement because the First_Rows hint tends to push the optimizer towards index usage, and this statement was behaving badly precisely because it was using an index (a full table scan only took about 1 hour). Regardless of what the developers were trying to do, the fact that they used such a big number caused the optimizer to ignore the hint anyway. Since the table only had 100M rows and the parameter was 1B, the hint was ignored (well at least the "First K Rows" modifications to the optimizer calculations were not used). This happens to the "rownum <" induced behavior as well by the way. Here's a bit of a couple of 10053 trace file showing some details: Continue reading ‘Funny Developer Tricks – first_rows(999999999)’ »

I saw a funny one today. I’ll paraphrase:

select * from table_x
where upper(acct_number) = '876876'
or upper(acct_number) = '826531';

Nice huh?

1. Obviously turns off any indexes on acct_number (unless they had a function based index).
2. Looks like they are probably storing numeric data in a character data type (or implicitly converting a number to character string).
3. Not using bind variables so they are not helping themselves from a parsing standpoint.
4. Finally, they’re making darn sure they take care of any mixed case numbers!

Some fun.

(by the way, those lower case numbers cause me problems all the time)

I was scanning a manual for a software product that runs on top of Oracle the other day and I ran across some “best practices” for Oracle. I was surprised to see a new mount option that I hadn’t run across before. Apparently it makes sequential reads 10-15% faster. See below for the section of the SERVER PREPARATION GUIDE for IBM Prospect® for Alcatel-Lucent Release 33.

Using forcedirection to Increase Performance

Sun Microsystems best practices for Solaris and Oracle installations recommends using the forcedirection mount option for file systems that store Oracle data files. The forcedirection option forces input/output (I/O) operations to bypass Solaris file buffering and caching. The bypass lets you combine the performance benefits of raw file systems with the manageability and flexibility of traditional file systems. The forcedirection option typically results in 10 percent to 15 percent faster sequential reads.

———————————————————
Caution! Use the forcedirection option on Solaris UNIX file systems. Other third-party file
systems provide similar functionality. If you are using a third-party file system, such as Veritas,
consult the company documentation for instructions on turning off file system buffering.
———————————————————

To set up the forcedirection option on existing IBM Prospect servers

1.Edit the

/etc/vfstab

file to include the forcedirection option using the root account.
$ su root
2.The seventh column in /etc/vfstab is the mount option column. In this column, change the value from – to forcedirection for the file systems containing Oracle data files. For standard installations, these are file systems /u02 through /u06. For example:
/dev/dsk/c2t0d0s6 /dev/rdsk/c2t0d0s6 /u02 ufs 3 yes –
becomes
SERVER PREPARATION GUIDE
IBM Prospect® for Alcatel-Lucent Release 33
Installing and Configuring Solaris
© Copyright IBM Corp 1999, 2009. All rights reserved.
SPG – Page 28
/dev/dsk/c2t0d0s6 /dev/rdsk/c2t0d0s6 /u02 ufs 3 yes forcedirection
Caution! For standard installations, enable forcedirection on file systems /u02 through
/u06 only. Do not enable forcedirection on file system /u01 — the /u01 file system
does not contain Oracle data files.
3.The forcedirection setup changes occur during the next system reboot. These changes are only a performance improvement; if they are the only changes requiring a reboot, schedule the reboot for the next available maintenance period. For information about scheduled maintenance, see the Administration Guide.
4.For a setup using forcedirection, use mount table entries in /etc/vfstab as follows:
/dev/dsk/c0t0d0s7 /dev/rdsk/c0t0d0s7 /u01 ufs 2 yes –
/dev/dsk/c2t0d0s6 /dev/rdsk/c2t0d0s6 /u02 ufs 3 yes forcedirection
/dev/dsk/c2t1d0s6 /dev/rdsk/c2t1d0s6 /u03 ufs 3 yes forcedirection
/dev/dsk/c2t2d0s6 /dev/rdsk/c2t2d0s6 /u04 ufs 3 yes forcedirection
/dev/dsk/c2t3d0s6 /dev/rdsk/c2t3d0s6 /u05 ufs 3 yes forcedirection
/dev/dsk/c2t4d0s6 /dev/rdsk/c2t4d0s6 /u06 ufs 3 yes forcedirection

If you’ve made it this far I have to go ahead and let you know that there is no forcedirection mount option (as far as I know). I think the editor of the manual (or maybe an overactive spell checker) got a little carried away – it should have been forcedirectio. Happy April Fools Day!

At the Hotsos Symposium last week, Tanel Poder pointed out an interesting hidden parameter (_high_priority_processes). We were discussing the occasional need to increase priority on the LGWR process when dealing with “log file sync” issues. The aforementioned parameter is used to tell Oracle to automatically set background processes to a higher than normal priority when the database is started. On linux this means putting them in the RR class, in Solaris, the RT class is used. The parameter defaults to LMS* in 10.2 and to LMS*||VKTM in 11gR1. (LMS* are the Lock Manager Server processes in a RAC instance and VKTM is the new Time Keeper process in 11g). So it makes sense that these processes would run at a higher priority.

Recently I have been working on a system that was spending a significant amount of time waiting on “log file sync”. Increasing the priority of the LGWR process made a significant improvement. It’s not a silver bullet though. When the system becomes really busy, the “log file sync” times still suck, just not as much. But I digress, this post is just to point out that Oracle has a built in mechanism to increase priority on background processes, not to tell you how to reduce long “log file sync” times. There are numerous posts on that subject already. Here a couple of pretty good ones:

At the Hotsos symposium last week, Tanel Poder pointed to an interesting hidden parameter of the beneficial property of the viagradrug. It is very effective in helping men who have erection problems.

Manly-Men Only Use Solid State Disk for Redo Logging – Kevin Closson
Tuning Log File Sync Wait Events – Riyaj Shamsudeen
Hotsos 2010 – Day 4 – Doug Burns (see the comments section)

Back to my story. Unfortunately, every time the database is bounced, LGWR goes back to his original priority and class and we have to get the Unix guys to reset it. So I got to wondering if the _high_priority_processes parameter could be used to do this automatically. And sure enough it looks like it can. First here’s a look at the class and priority info of the standard high priority processes on a Solaris system and a Linux system.

Continue reading ‘Increasing Priority of lgwr Process using _high_priority_processes’ »

I posted earlier on Explain Plan’s tendency to spread rumors (Explain Plan Lies). Autotrace is another commonly used tool that suffers from the same basic character flaw (i.e. it lies too). The reason is simple. It uses the Explain Plan command under the covers.

Here’s a quick demonstration:

Continue reading ‘Autotrace Lies Too!’ »

Hey, I just thought of something. One of the things I do fairly regularly when struggling with a SQL statement that is not behaving goes like this:

1. create a copy of the statement in a test script
2. add the GATHER_PLAN_STATISTICS hint
3. duplicate all the bind variables (with values used at parse if possible)
4. run it
5. run xplan on it

This provides the ability to see how the optimizer’s estimates line up with reality using the DBMS_XPLAN.DISPLAY_CURSOR function with the ALLSTATS format option. It’s very easy to see where the optimizer is having problems by comparing the A-Rows column (actual rows) with the E-Rows column (estimated rows). Anywhere there is a big difference (think orders of magnitude), there is a potential problem. Well actually it’s a little more complicated than that, because the A-Rows are cumulative while the E-Rows are not. You have to multiply the E-Row by Starts (or divide A-Rows by the number of executions) in order to compare apples to apples. If you need a little more info see this post by Jonathan Lewis or this one by Greg Rahn. Here’s how the output looks in case you haven’t seen it before:

SYS@LAB1024> @fs
Enter value for sql_text: %gather_plan%
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
dvfumy503zfnw      0 1660344652          3           .00           25 select /*+ gather_plan_statistics */ t1.x, t2.y from kso.t1
                                                                      t1, kso.t2 t2 where t1.y = t2.x and rownum < 10


SYS@LAB1024> !cat dplan_allstats.sql
set lines 180
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats  +peeked_binds'))
/

SYS@LAB1024> @dplan_allstats
Enter value for sql_id: dvfumy503zfnw
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dvfumy503zfnw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ t1.x, t2.y from kso.t1 t1, kso.t2 t2 where t1.y = t2.x and
rownum < 10

Plan hash value: 1660344652

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                |              |      3 |        |     27 |00:00:00.01 |      75 |
|   2 |   NESTED LOOPS                |              |      3 |      9 |     27 |00:00:00.01 |      75 |
|   3 |    TABLE ACCESS FULL          | T1           |      3 |  53629 |     27 |00:00:00.01 |      15 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2           |     27 |      1 |     27 |00:00:00.01 |      60 |
|*  5 |     INDEX UNIQUE SCAN         | SYS_C0014104 |     27 |      1 |     27 |00:00:00.01 |      33 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   5 - access("T1"."Y"="T2"."X")


23 rows selected.

Just a quick note on the correlation between STARTS, E-ROWS, and A-ROWS. I picked a bit of a bad example due to the "rownum < 10" predicate. It causes processing to stop on the full table scan after 9 rows are retrieved. But it does show a mismatch in the number of estimated rows and the number of actual rows. Keep in mind that the actual rows are cumulative, so it's the total rows this step has handled regardless of the number of executions. This statement has been executed 3 times (see the 3 under Starts on line 1). And the full table scan was executed (Started) 3 times (once per execution). So the optimizer was expecting 53629 rows for each execution, but got on average 9 rows per execution. Note also that the Starts column is the actual number of times the operation was executed, not the number of times that the optimizer thinks it will be executed. Here's another little bit of output to show that Starts is actual, not estimated:

SYS@LAB1024> -- here's a query without the "rownum<10" predicate
SYS@LAB1024> @fs
Enter value for sql_text: %t1_nl_join_agg.sql%
Enter value for sql_id: 

SQL_ID         CHILD    PLAN_HASH        EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ------------ ------------ ------------- ------------ ------------------------------------------------------------
a36w6pfkgp2sy      0    174109304            1          3.77       53,745 select /*+ gather_plan_statistics */ avg(x*2) from ( select
                                                                          /* t1_nl_join_agg.sql */ t1.x, t2.y from kso.t1 t1, kso.t2
                                                                          t2 where t1.y = t2.x )


1 row selected.

SYS@LAB1024> @dplan_allstats
Enter value for sql_id: a36w6pfkgp2sy
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a36w6pfkgp2sy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(x*2) from ( select /* t1_nl_join_agg.sql */ t1.x,
t2.y from kso.t1 t1, kso.t2 t2 where t1.y = t2.x )

Plan hash value: 174109304

-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:03.77 |   53745 |    169 |
|   2 |   NESTED LOOPS      |              |      1 |  53629 |  25994 |00:00:02.94 |   53745 |    169 |
|   3 |    TABLE ACCESS FULL| T1           |      1 |  53629 |  53629 |00:00:00.80 |     114 |    105 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014104 |  53629 |      1 |  25994 |00:00:02.41 |   53631 |     64 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."Y"="T2"."X")


21 rows selected.

SYS@LAB1024> -- the stats are 100% correct on the full table scan (line 3), 
SYS@LAB1024> -- but off by 2X on the NL join (25994 vs. 53629 on line 2)
SYS@LAB1024> -- so let's mess up the stats on T1 to see if Starts is optimizer guess or actual
SYS@LAB1024> -- set rows in T1 to 1234
SYS@LAB1024> 
SYS@LAB1024> exec dbms_stats.set_table_stats(ownname => 'KSO', tabname => 'T1', numrows => 1234);

PL/SQL procedure successfully completed.

SYS@LAB1024> @flush_sql
Enter value for sql_id: a36w6pfkgp2sy

PL/SQL procedure successfully completed.

SYS@LAB1024> @t1_nl_join_agg.sql

    AVG(X*2)
------------
108794.25383

1 row selected.

SYS@LAB1024> @dplan_allstats
Enter value for sql_id: a36w6pfkgp2sy     
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a36w6pfkgp2sy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(x*2) from ( select /* t1_nl_join_agg.sql */
t1.x, t2.y from kso.t1 t1, kso.t2 t2 where t1.y = t2.x )

Plan hash value: 174109304

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |              |      1 |      1 |      1 |00:00:03.63 |   53745 |
|   2 |   NESTED LOOPS      |              |      1 |   1234 |  25994 |00:00:03.20 |   53745 |
|   3 |    TABLE ACCESS FULL| T1           |      1 |   1234 |  53629 |00:00:00.86 |     114 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0014104 |  53629 |      1 |  25994 |00:00:02.41 |   53631 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."Y"="T2"."X")


21 rows selected.

SYS@LAB1024> -- note that Starts on the Index Lookup inside the NL Join is 53629 (line 4)
SYS@LAB1024> -- this is the number of times that operation was actually executed,
SYS@LAB1024> -- not 1234, which is the number of times the optimizer thought is would be executed
SYS@LAB1024> -- so Starts and A-rows are actual values, E-Rows is the optimizer estimate
SYS@LAB1024> -- (at least for NL Join)

Back to the topic at hand, my normal steps (i.e. generate test script, add GATHER_PLAN_STATISTICS hint, etc…)

I have a script that helps generate the test script (see this post: Creating Test Scripts with Bind Variables). But there are definite drawbacks to this approach. It can be difficult to duplicate the exact environment that the optimizer sees when running the production code, even if you are testing on the production instance. The bottom line is that you have to be pretty careful to make sure that your test script is really doing the same thing that the production code is doing.

Which brings me to my idea (finally)! Here’s a quick way to get extended plan statistics for any statement without modifying the code or creating a test version. Drum roll please ….

Just create a one line SQL Profile (using create_1_hint_sql_profile.sql) with the GATHER_PLAN_STATISTICS hint. You can use dplan_allstats.sql to see the extended statistics. Here’s an example:

> !sql
sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 28 17:49:56 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@LAB112> -- note: SQL_ID 84q0zxfzn5u6s is the statement I want to look at
SYS@LAB112>
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): PROFILE_84q0zxfzn5u6s_GPS
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: GATHER_PLAN_STATISTICS
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.

PL/SQL procedure successfully completed.

SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: 

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
PROFILE_84q0zxfzn5u6s_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO

7 rows selected.

SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_GPS

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
GATHER_PLAN_STATISTICS

1 rows selected.

SYS@LAB112> @avgskewi

AVG(PK_COL)
-----------
   15636133

1 row selected.

SYS@LAB112> @dplan_allstats
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133

Plan hash value: 3723858078

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      1 |00:00:00.01 |      35 |
|   1 |  SORT AGGREGATE              |           |      1 |      1 |      1 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |      1 |     35 |     32 |00:00:00.01 |      35 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |      1 |     35 |     32 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("COL1"=136133)

Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_GPS used for this statement
   - SQL plan baseline SQL_84Q0ZXFZN5U6S_3723858078 used for this statement


25 rows selected.

SYS@LAB112> @sql_hints
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0

OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))

6 rows selected.

Works great! Now you don’t have to do all that work just to see the rowsource execution stats.

Note that the Outline Hints stored in the OTHER_XML column of V$sql_plan have no record of the GATHER_PLAN_STATISTICS hint, even though it was clearly executed (otherwise we’d have gotten a warning message in the XPLAN output and no A-Rows column).

Note also that this example was done on an 11gR2 database and that the statement was found in the SQL Management Base (i.e. there was a Baseline in place for this statement already). The Notes section of the plan output shows that both the Profile and the Baseline were used. This got me wondering if it really merged the hints from the Profile and the Baseline. The short answer is YES, it appears that it does. I’ve got an example, but since that’s a different topic and this post is already pretty long, I’ll just save it for another post.

As always, your comments are welcomed.

UPDATE: This has proved pretty useful, so I wrote a little script that just prompts for a sql_id and creates a SQL Profile with the GATHER_PLAN_STATISTICS hint. I have just uploaded it to the site here: gps.sql

UPDATE 2 (4/5/12): I have recently re-written the gps.sql script to use the SQL Patch facility which allows it to be used with statements that already have a SQL Profile attached to them.

Warning! While the details of the other points in this post remain valid, the main conclusion of the post (i.e. that the Tuning Advisor can create Profiles with hints other than the “statistics modification” type, opt_estimate for example) is not valid. At this point I have no proof that the Tuning Advisor creates Profiles with the more directive type hints. The conclusion was based on a faulty script (sql_profile_hints) which in certain situations combined hints from multiple Profiles. (The script has been modified now)

Be sure and see the comments from Jonathan and my response for further information. I will leave the remainder of the post unchanged for reference.

============================================================

This is the 2nd post in response to a dialog with Jonathan Lewis regarding SQL Profiles. Here’s a link to the 1st post and a link to the original dialog.

Just a quick recap: Jonathan had pointed out that I was using SQL Profiles in a manner that was not intended by the developers (with which I had to agree). SQL Profiles are created by the SQL Tuning Advisor (STA) primarily to address inaccurate calculations by the optimizer. The first post dealt with the question of whether SQL Profiles were capable of dealing with any/all hints or just the ones most commonly used by STA generated Profiles. This post focuses on a second question, namely whether STA generated Profiles ever had any thing other than the “statistics modification” type hints (opt_estimate, column_stats, table_stats, index_stats, etc…).

So in an effort to answer that question, I did a little looking around on some systems that had implemented a fair number of STA generated Profiles. In order to speed my “looking around”, I wrote a script to do a simple grouping of hints (sql_profile_distinct_hints.sql). <- got to use a little regex in that one 😉 You'll also need my sql_profile_hints.sql script if you want to follow along in your hymnal.

Also note that STA creates Profiles that are named with this format: SYS_SQLPROF_xxxxxxx, so we’ll only look at Profiles that are named in that fashion.

===============
System 1
===============

SQL> set echo on
SQL> @sql_profiles
SQL> col category for a15
SQL> col sql_text for a70 trunc
SQL> select name, category, status, sql_text
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text',sql_text)
  4  and name like nvl('&name',name)
  5  order by last_modified
  6  /
Enter value for sql_text: 
Enter value for name: SYS_SQLPROF%

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               
------------------------------ --------------- -------- ----------------------------------------------------------------------
SYS_SQLPROF_014715f1c62ec002   DEFAULT         ENABLED  SELECT /*+ OPAQUE_TRANSFORM */ ...
SYS_SQLPROF_0148f3819bdb0003   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3819bd1c002   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3819be38004   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3b92f920008   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f3b92f5c4005   DEFAULT         ENABLED  DELETE ...
SYS_SQLPROF_0148f3b92f848007   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014914d83003400b   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014914d8682e800c   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491c90b1dd0010   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491dfb1c85c012   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014913e28eec0006   DEFAULT         DISABLED SELECT ... 
SYS_SQLPROF_0148f3b92fa04009   DEFAULT         DISABLED SELECT ...
SYS_SQLPROF_01491ccd37348011   DEFAULT         DISABLED SELECT ...

14 rows selected.

SQL> 
SQL> @sql_profile_distinct_hints
SQL> set lines 155
SQL> col hint for a50 trunc
SQL> select hint, count(*) from (
  2  select regexp_replace(attr_val,'\(.*$') hint -- eliminate from ( to end of line
  3  from dba_sql_profiles p, sqlprof$attr h
  4  where p.signature = h.signature
  5  and name like ('&profile_name')
  6  )
  7  group by hint
  8  order by hint
  9  /
Enter value for profile_name: SYS_SQLPROF%

HINT                                                 COUNT(*)
-------------------------------------------------- ----------
COLUMN_STATS                                               13
FIRST_ROWS                                                  1
IGNORE_OPTIM_EMBEDDED_HINTS                                 1
INDEX_STATS                                                 1
OPTIMIZER_FEATURES_ENABLE                                  14
OPT_ESTIMATE                                              178
TABLE_STATS                                                 2

7 rows selected.

===============
System 2
===============

SQL> @sql_profiles
Enter value for sql_text: 
Enter value for name: 

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               
------------------------------ --------------- -------- ---------------------------------------------------------------------- 
SYS_SQLPROF_0149380562710001   DEFAULT         ENABLED  SELECT DISTINCT ...
SYS_SQLPROF_01494247f1d7c007   DEFAULT         ENABLED        INSERT INTO ...
SYS_SQLPROF_01494247f254c008   DEFAULT         ENABLED        INSERT INTO ...
SYS_SQLPROF_01494db7d72f0000   DEFAULT         ENABLED        INSERT INTO ...
PROFILE_1zywf8dtd9a6m          DEFAULT         ENABLED  select ...
PROFILE_as2u1cbquf6t3_moved    DEFAULT         ENABLED  SELECT ...
PROFILE_ay0yn9xczhqk2          DEFAULT         ENABLED  select ...
PROFILE_402tfpm6851du_moved    DEFAULT         ENABLED  SELECT /*+ORDERED ...
PROFILE_0bbnbjt6r17zg          DEFAULT         ENABLED  select /* test 1qkytgsyhfrn6_11.sql */ ...
PROFILE_7kj5cc04njm64          DEFAULT         ENABLED  select /* test 1qkytgsyhfrn6_4a.sql */ ...
PROFILE_1qkytgsyhfrn6_moved    DEFAULT         ENABLED  SELECT /*+ORDERED ...
PROFILE_8xva1napnu034_moved    DEFAULT         ENABLED  SELECT /*+ORDERED USE_NL ...
PROFILE_6njq8vtawcq1m          DEFAULT         ENABLED  DELETE FROM ...

13 rows selected.

SQL> 
SQL> @sql_profile_distinct_hints
Enter value for profile_name: SYS_SQLPROF%

HINT                                                 COUNT(*)
-------------------------------------------------- ----------
COLUMN_STATS                                                3
IGNORE_OPTIM_EMBEDDED_HINTS                                 3
INDEX_STATS                                                 2
OPTIMIZER_FEATURES_ENABLE                                   4
OPT_ESTIMATE                                              416
TABLE_STATS                                                 1

6 rows selected.

===============
System 3
===============


SQL> @sql_profiles
Enter value for sql_text:  
Enter value for name: SYS%

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
SYS_SQLPROF_0146d1cc64d5c001   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146d1ce58150002   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146d1cfd2488003   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146d1e45f1f0005   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0146f4d7bee40007   DEFAULT         ENABLED  select ...
SYS_SQLPROF_014738be1cb64000   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0148f25055a28004   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148f2528b754005   DEFAULT         ENABLED  select ...
SYS_SQLPROF_0148f252e7e84007   DEFAULT         ENABLED  SELECT DISTINCT ...
SYS_SQLPROF_0148f2553429800a   DEFAULT         ENABLED  SELECT /*+ FULL (...
SYS_SQLPROF_0248f26958210000   DEFAULT         ENABLED  select * from ...
SYS_SQLPROF_0148f37413ef000c   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_0148fcc1fa89c001   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_014909a2d7bc4002   DEFAULT         ENABLED  select ...
SYS_SQLPROF_01491c971e1e4003   DEFAULT         ENABLED  select ...
SYS_SQLPROF_01491ce0f3c1800b   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491ce50f9e800c   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_01491f66feb0400d   DEFAULT         ENABLED  SELECT ...
SYS_SQLPROF_02492723d3778000   SAVED           DISABLED select ...
SYS_SQLPROF_01491ce04c38c008   DEFAULT         DISABLED SELECT ...

20 rows selected.

SQL> @sql_profile_distinct_hints
Enter value for profile_name: SYS_SQLPROF%

HINT                                                 COUNT(*)
-------------------------------------------------- ----------
ALL_ROWS                                                    3
COLUMN_STATS                                               12
FIRST_ROWS                                                  1
FULL                                                        3
IGNORE_OPTIM_EMBEDDED_HINTS                                 2
INDEX                                                       2
INDEX_RS_ASC                                                2
INDEX_STATS                                                12
LEADING                                                     1
OPTIMIZER_FEATURES_ENABLE                                   6
OPT_ESTIMATE                                              212
OPT_PARAM                                                   2
OUTLINE                                                     2
OUTLINE_LEAF                                                1
SWAP_JOIN_INPUTS                                            1
TABLE_STATS                                                 4
UNNEST                                                      1
USE_HASH                                                    2
USE_NL                                                      4

19 rows selected.

SQL> -- different
SQL> -- After a little looking I found that one Profile had all the non standard hints
SQL>
SQL> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_02492723d3778000

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
ALL_ROWS
ALL_ROWS
FULL(@"SEL$5DA710D3" "A"@"SEL$1")
FULL(@"SEL$5DA710D3" "B"@"SEL$1")
FULL(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
IGNORE_OPTIM_EMBEDDED_HINTS
INDEX(@"SEL$5DA710D3" "D"@"SEL$1" ("TAB_XXX3"."STRATEGY_ID" "TAB_XXX3"."FF_STATUS"))
INDEX(@"SEL$5DA710D3" "F"@"SEL$1" ("TAB_XXX2"."COL1" "TAB_XXX2"."COL2" "TAB_XXX2"."COL3" "TAB_XXX2"."COL4"))
INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$1" ("TAB_XXX3"."COL1" "TAB_XXX3"."COL2" "TAB_XXX3"."COL3"))
INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("TAB_XXX4"."COL1" "TAB_XXX4"."COL2" "TAB_XXX4"."COL3"))
LEADING(@"SEL$5DA710D3" "A"@"SEL$1" "B"@"SEL$1" "TAB_XXX1"@"SEL$2" "F"@"SEL$1" "C"@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=4.446153275)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=7.884506683)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=1.308307653)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=25.60960842)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=135.982493)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=190.2944942)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=26.52093258)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=259.4309108)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=862.935946
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1"), SCALE_ROWS=26.34181566)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=839.9683673)
OPT_ESTIMATE(@"SEL$5DA710D3", TABLE, "D"@"SEL$1", SCALE_ROWS=11.39782103)
OPT_PARAM('optimizer_index_caching' 60)
OPT_PARAM('optimizer_index_cost_adj' 80)
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
UNNEST(@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "B"@"SEL$1")
USE_HASH(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "C"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "D"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "E"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "F"@"SEL$1")

39 rows selected.

SQL> -- So did someone create a manual Profile and name it SYS_SQLPROF_xxxxx ?
SQL> -- Seems unlikely
SQL> -- Let's try to reproduce this behavior (it's on sql_id: 425p8m2qdxdtm)
SQL>
SQL> @create_tuning_task
Task_Name: KSO TEST 425p8m2qdxdtm
Enter value for sql_id: 425p8m2qdxdtm
Enter value for time_limit: 3000

PL/SQL procedure successfully completed.


RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : KSO TEST 425p8m2qdxdtm
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 3000
Completion Status                 : COMPLETED
Started at                        : 01/26/2010 15:25:08
Completed at                      : 01/26/2010 15:35:05
Number of SQL Profile Findings    : 1

-------------------------------------------------------------------------------
Schema Name: SCHEMA_XXX
SQL ID     : 425p8m2qdxdtm
SQL Text   : select ...

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 73.58%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'KSO TEST
            425p8m2qdxdtm', replace => TRUE);

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "PROFILE_425p8m2qdxdtm" exists for this statement and was
  ignored during the tuning process.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 3884938694

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |     1 |   207 |       |   171K  (2)| 00:34:18 |
|   1 |  HASH UNIQUE                    |                          |     1 |   207 |       |   171K  (2)| 00:34:18 |
|   2 |   NESTED LOOPS                  |                          |     1 |   207 |       |   171K  (2)| 00:34:18 |
|   3 |    NESTED LOOPS                 |                          |     1 |   198 |       |   171K  (2)| 00:34:18 |
|   4 |     NESTED LOOPS                |                          |     7 |  1092 |       |   171K  (2)| 00:34:18 |
|   5 |      NESTED LOOPS               |                          |    69 |  9039 |       |   171K  (2)| 00:34:16 |
|*  6 |       HASH JOIN RIGHT ANTI      |                          | 11209 |   996K|  5912K|   171K  (2)| 00:34:16 |
|...

--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(TAB_XXX1.COL1)
   ...

2- Using SQL Profile
--------------------
Plan hash value: 3101771407

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |     1 |   207 |       | 45293   (2)| 00:09:04 |
|   1 |  HASH UNIQUE                      |                          |     1 |   207 |       | 45293   (2)| 00:09:04 |
|   2 |   NESTED LOOPS ANTI               |                          |     1 |   207 |       | 45292   (2)| 00:09:04 |
|   3 |    NESTED LOOPS                   |                          |     1 |   170 |       | 45290   (2)| 00:09:04 |
|   4 |     NESTED LOOPS                  |                          |   122 | 15860 |       | 45289   (2)| 00:09:04 |
|*  5 |      HASH JOIN                    |                          |  2868 |   277K|  4480K| 40696   (2)| 00:08:09 |
|*  6 |       HASH JOIN                   |                          | 66442 |  3698K|    22M| 36124   (2)| 00:07:14 |
| ...

----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(TAB_XXX1.COL1)
   ...

-------------------------------------------------------------------------------


SQL> set echo on
SQL> set echo off
SQL> !cat accept_sql_profile.sql
exec dbms_sqltune.accept_sql_profile(task_name => '&task_name',category => '&category');

SQL> @accept_sql_profile
Enter value for task_name: KSO TEST 425p8m2qdxdtm
Enter value for category: TEST

PL/SQL procedure successfully completed.

SQL>  select name, category, status, sql_text
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text',sql_text)
  4  and name like nvl('&name',name)
  5  and categeory like 'TEST'
  6* order by last_modified;
Enter value for sql_text: 
Enter value for name: 

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               
------------------------------ --------------- -------- ---------------------------------------------------------------------- 
SYS_SQLPROF_01499b27a4ea4000   TEST            ENABLED  select ...


SQL> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_01499b27a4ea4000

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
ALL_ROWS
ALL_ROWS
ALL_ROWS
FULL(@"SEL$5DA710D3" "A"@"SEL$1")
FULL(@"SEL$5DA710D3" "B"@"SEL$1")
FULL(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
IGNORE_OPTIM_EMBEDDED_HINTS
INDEX(@"SEL$5DA710D3" "D"@"SEL$1" ("TAB_XXX2"."COL1" "TAB_XXX2"."COL2"))
INDEX(@"SEL$5DA710D3" "F"@"SEL$1" ("TAB_XXX3"."COL1" "TAB_XXX3"."COL2" "TAB_XXX3"."COL3"))
INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$1" ("TAB_XXX2"."COL1" "TAB_XXX2"."COL2" "TAB_XXX2"."COL3"))
INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("TAB_XXX3"."COL1" "TAB_XXX3"."COL2" "TAB_XXX3"."COL3"))
LEADING(@"SEL$5DA710D3" "A"@"SEL$1" "B"@"SEL$1" "TAB_XXX1"@"SEL$2" "F"@"SEL$1" "C"@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", TAB_XXX3_IX1, SCALE_ROWS=6.531081416e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "A"@"SEL$1", TAB_XXX2_IX3, SCALE_ROWS=1.481282096e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "A"@"SEL$1", TAB_XXX2_IX7, SCALE_ROWS=1.481282096e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "F"@"SEL$1", TAB_XXX_IX1, SCALE_ROWS=6.531081416e-06)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=4.446153275)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=7.884506683)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=8.321869785)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=1.308307653)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=22.98505769)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=25.60960842)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=4.872324946)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=135.982493)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=188.6083111)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=190.2944942)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=26.52093258)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=40.52912454)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=259.4309108)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=41.60863798)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=1102.68181
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "D"@"SEL$1", "C"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1", "TAB_XXX1"@"SEL$2"), SCALE_ROWS=862.935946
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1"), SCALE_ROWS=26.34181566)
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=839.9683673)
OPT_ESTIMATE(@"SEL$5DA710D3", TABLE, "D"@"SEL$1", SCALE_ROWS=11.39782103)
OPT_ESTIMATE(@"SEL$5DA710D3", TABLE, "D"@"SEL$1", SCALE_ROWS=11.95241429)
OPT_PARAM('optimizer_index_caching' 61)
OPT_PARAM('optimizer_index_cost_adj' 79)
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
UNNEST(@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "B"@"SEL$1")
USE_HASH(@"SEL$5DA710D3" "TAB_XXX1"@"SEL$2")
USE_NL(@"SEL$5DA710D3" "C"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "D"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "E"@"SEL$1")
USE_NL(@"SEL$5DA710D3" "F"@"SEL$1")

52 rows selected.

SQL> -- so STA really did create a Profile with all these hints, interesting...

A couple of things worth noting:

OPT_ESTIMATE was far and away the most popular hint. This is followed by the occasional XXX_STATS hints. The OPTIMIZER_FEATURES_ENABLE hint shows up in almost all the STA Profiles. Also, the IGNORE_OPTIM_EMBEDDED_HINTS shows up fairly regularly, but not always (not sure why it’s there sometimes and not others – I thought it might show up only when there are actually embedded hints in the SQL text, but that is not the case). Occasionally, an ALL_ROWS or FIRST_ROWS hint will show up. And finally, in rare cases, a full blown Profile with many directives will show up as in the one case I was able to reproduce. I really have no idea when/why this kicks in. But it doesn’t seem to happen very often. However, it does give a strong clue that most, if not all, valid hints can be applied by SQL Profiles.

And a couple of editorial points:

1. I don’t really like STA generated Profiles as a long term solution. Since they primarily apply fudge factors (via the opt_estimate hint) to the optimizer calculations (which are based in large part on statistics), they tend to go bad fairly quickly as the statistics change.

2. I think they are very useful though for finding where the optimizer has gone wrong. Anywhere there is an opt_estimate hint with a really large (or small) scaling factor, the optimizer had some kind of problem. This could be due to bad stats, or just a short coming in the optimizer itself. But it can be a very good pointer to why/where the optimizer is not doing a good job.

3. There is no reason you can’t use an STA Profile to get a decent plan and then do something else to make it more permanent – i.e. create an Outline or Baseline or even another Profile with all the hints necessary to reproduce the plan. This is of course if your intent is to “lock” the plan.

4. If you have a short coming in the optimizer (such as 10g’s lack of knowledge about correlated columns) then the opt_estimate hint in the STA Profiles may be a viable long term solution. Of course, this implies that you know enough about how the calculations are done to be sure that a certain calculation will always be off by a calculatable (did I just make that word up?) factor.

As always, your comments are welcomed.

Seems like all I ever write about these days is SQL Profiles. I do other stuff, honest! It just seems like getting Oracle to do what you want when you can’t touch the code is the closest thing to “Magic” that DBAs get to do. By the way, software developers get to create “Magic” all the time. They have the ability to create something out of nothing. I think it’s one of the coolest jobs ever. Of course a lot of art is like that too. But painters and sculptors start with some sort of raw material – programmers don’t. Musicians don’t really use raw material either, but music is much less tangible than software. Maybe one of these days I’ll write a little on the similarities between musicians and software developers, but right now I’m way off in the weeds. Back to the subject at hand.

A few weeks ago Jonathan Lewis called me to task on a couple of posts regarding SQL Profiles (in a very nice collegial sort of way). You can see the original dialog here. One of his main points was that SQL Profiles were not meant to be a generic mechanism for forcing a particular execution plan the way Outlines are. There is after all, no documented way (that I’m aware of) to directly create a SQL Profile on a statement. I had to agree with him that I was using them in a way that was not necessarily intended. Outlines were designed to lock execution plans, SQL Profiles were designed to overcome shortcomings in the optimizer with regards to statistics. Tom Kyte described SQL Profiles like this:

So, a SQL profile is sort of like gathering statistics on A QUERY – which involves many
tables, columns and the like….

In fact – it is just like gathering statistics for a query, it stores additional
information in the dictionary which the optimizer uses at optimization time to determine
the correct plan. The SQL Profile is not “locking a plan in place”, but rather giving
the optimizer yet more bits of information it can use to get the right plan.

Tom is referring to the documented way of creating a SQL Profile which is to use the SQL Tuning Advisor. The Tuning Advisor verifies the optimizer’s calculations and can create a SQL Profile that corrects the calculations, if they are found to be in error. The corrections most often come in the form of OPT_ESTIMATE hints which apply a scaling factor at various places in the optimizer’s calculations. But take note that the underlying mechanism of applying these corrections is hints. So a SQL Profile is ultimately a mechanism for applying a set of stored hints to a SQL statement (or set of statements) behind the scenes. And whether it was intended by the developers or not, this gives us a tremendously powerful tool. With this tool we can influence (and often times control) execution plans for statements coming from application code that is difficult or impossible to change.

By the way, all this discussion of SQL Profiles and whether they should be used as a generic mechanism for affecting plans without touching a SQL statement is probably going to be fairly short lived. SQL Baselines (introduced in 11g) are the latest revision of the “behind the scenes hint application” idea. They will most likely make SQL Profiles a less attractive option in the future. Fortunately, it is a simple matter to convert a SQL Profile into a Baseline. And SQL Profiles still work fine in 11g as well (so do Outlines for that matter), but on the off chance that Oracle decides to do away with SQL Profiles (or alter their behavior) in some future release, it’s comforting to know that we are not headed down a dead end street.

Back to the subject at hand. Jonathan expressed concern and doubt on a couple of points:

1. That Profiles really were a generic mechanism to apply any hint behind the scenes. (i.e. that they could be used to apply any arbitrary hint, not just the hints created by the Tuning Advisor – like opt_estimate).
2. Whether Profiles created by the Tuning Advisor ever had hints other than “Change the Optimizer Calculations” type hints (i.e. opt_estimate, index_stats, table_stats, etc…).

I think we finally agreed that they can be used to apply hints in a generic fashion, even if that was not the intention of the developers. Nevertheless, here is a simple test case to demonstrate that they can be used for that purpose. I created a little script to create a SQL Profile with a single manually typed hint called create_1_hint_sql_profile.sql. Please note that the syntax can be quite finicky with regards to query block names and aliases.

> !sql
sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 25 15:23:02 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@LAB112> @flush_pool

System altered.

SYS@LAB112> @avgskewi

AVG(PK_COL)
-----------
   15636133

SYS@LAB112> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          1           .05          190 select avg(pk_col) from kso.skew where col1 = 136133

SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133

Plan hash value: 3723858078

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    35 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   840 |    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    35 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("COL1"=136133)


20 rows selected.

SYS@LAB112> -- let's create a 1 hint profile to force a full table scan
SYS@LAB112> 
SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full(skew)
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.

PL/SQL procedure successfully completed.

SYS@LAB112> @sql_profiles   
Enter value for sql_text: 
Enter value for name: 

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
PROFILE_84q0zxfzn5u6s_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO

7 rows selected.

SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full(skew)

1 rows selected.

SYS@LAB112> @avgskewi

AVG(PK_COL)
-----------
   15636133

1 row selected.

SYS@LAB112> @find_sql
Enter value for sql_text: 
Enter value for sql_id: 84q0zxfzn5u6s

SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
84q0zxfzn5u6s      0 3723858078          1           .01           86 select avg(pk_col) from kso.skew where col1 = 136133

1 row selected.

SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 0

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133

Plan hash value: 3723858078

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    35 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   840 |    35   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    35 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("COL1"=136133)

Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_MANUAL used for this statement


24 rows selected.

SYS@LAB112> -- didn't work - i.e. Profile got created and used, but the hint was ignored
SYS@LAB112> -- oh yeah, Query Block needed
SYS@LAB112> 
SYS@LAB112> @drop_sql_profile
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL

PL/SQL procedure successfully completed.

SYS@LAB112> -- must reload SQL statement for create_1_hint_sql_profile to work
SYS@LAB112> @avgskewi

AVG(PK_COL)
-----------
   15636133

1 row selected.

SYS@LAB112> @create_1_hint_sql_profile
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for profile_name (PROFILE_sqlid_MANUAL): 
Enter value for category (DEFAULT): 
Enter value for force_matching (false): 
Enter value for hint: full( SKEW@SEL$1 )
Profile PROFILE_84q0zxfzn5u6s_MANUAL created.

PL/SQL procedure successfully completed.

SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROFILE_84q0zxfzn5u6s_MANUAL

HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
full( SKEW@SEL$1 )

1 rows selected.

SYS@LAB112> @avgskewi


AVG(PK_COL)
-----------
   15636133

1 row selected.

SYS@LAB112> @dplan
Enter value for sql_id: 84q0zxfzn5u6s
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133

Plan hash value: 568322376

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 28360 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    24 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |    35 |   840 | 28360   (1)| 00:05:41 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1"=136133)

Note
-----
   - SQL profile PROFILE_84q0zxfzn5u6s_MANUAL used for this statement


23 rows selected.

SYS@LAB112> -- so that worked

Note that the syntax can be tricky requiring correct Query Block name (and aliases if there are any). SEL$1 is the default name for the query block of a simple select. (DEL$1 for deletes, UPD$1 for updates) As you can see from the example, the FULL hint was silently ignored without the QB name.

Since this post has gotten a little long, I’ll save my response to Johnathan’s second concern for the next post. Please let me know if you have any questions.

There was a post on the oracle-l list the other day regarding disk space usage in the temp tablespace. Rich Jesse posted a neat little script for capturing large consumers of temp space along with the user and SQL statement responsible. I started playing around with the script and noticed that it wasn’t reporting the correct SQL Statement. Turns out there is a bug having to do with the definition of v$sort_usage. It’s apparently been around since 10gR1.

There is a listing on Metalink (sorry,… I mean My Oracle Support). I’ve pasted in the interesting bits here:

Bug 7210183: SQL_ID VALUE IS NOT SAME IN V$TEMPSEG_USAGE AND OTHER VIEWS.
~~~~~~~~~~~
The actual problem here looks to be in the definition of
GV$SORT_USAGE thus:

In RDBMS_MAIN_LINUX_080825:
GV$TEMPSEG_USAGE is a synonym resolving to GV_$SORT_USAGE.

GV_$SORT_USAGE is a VIEW which selects various columns from
GV$SORT_USAGE.

GV$SORT_USAGE has a definition like this:
select x$ktsso.inst_id, username, username, ktssoses, ktssosno,
prev_sql_addr, prev_hash_value, prev_sql_id, <<<<< NOTICE HERE ktssotsn, .... from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial# So GV$SORT_USAGE SQL_ID etc.. are just taken from the sessions current PREV_* columns. This is not correct. The sort usage in a session could be tied to any of the open cursors for that session. The above definition just looks plain wrong. *** 09/09/08 05:45 pm *** *** 09/10/08 12:06 am *** (DEL: Impact/Symptom->WRONG RESULTS )
*** 09/10/08 12:06 am *** (ADD: Impact/Symptom->WRONG RESULTS )
*** 09/10/08 12:06 am ***
I am not sure that in all cases using V$SESSION.SQL_ADDRESS
etc.. would be correct.
eg: Consider that the session does something like the
following:
Open cursor 1
Parse , execute and fetch one row from a SQL that needs
temp space in part of the plan
Open cursor 2
select * from dual (no sorting)
At this point V$SESSION.SQL_ID would be for the
select from dual

I do not believe that we actually have the information
available to return the correct SQL_ID etc.. this view.

eg: a. Consider this simple test:
create global temporary table foo ( a number );
insert into foo values(10);
b. In a second session do:
select sql_id from v$sort_usage;
c. Now in the first session issue any old select:
select 99 from dual;
d. And check V$SORT_USAGE:
select sql_id from v$sort_usage;
e. Repeat c and d over using a different SQL each time
in c . eg: select 77 from dual A;

In this example V$SORT_USAGE will show us which session
has the temp space (SESSION_ADDR) but the SQL which
created the temp space is not even available as an open
cursor against that session (the insert created the temp
space).

It looks like this really needs a larger change – something
like capturing the SQL_ID etc.. at the time that the temp seg
gets created and then exposing that information through some
new X$ colums in x$ktsso ?

So I modified the script to report the current sql_id instead.
Here’s a quick replay of the investigation starting with the original query:

SYS@LAB1024> -- original query
SYS@LAB1024> l      
  1  SELECT sysdate "TIME_STAMP", vsu.username, vsu.sql_id, vsu.tablespace,
  2  vsu.usage_mb, vst.sql_text, vp.spid
  3          FROM
  4          (
  5                  SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
  6  sum(blocks)*8192/1024/1024 "USAGE_MB"
  7                  FROM v$sort_usage
  8                  HAVING SUM(blocks)> 10000 -- 80MB
  9                  GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
 10          ) "VSU",
 11          v$sqltext vst,
 12          v$session vs,
 13          v$process vp
 14  WHERE vsu.sql_id = vst.sql_id
 15          AND vsu.sqladdr = vst.address
 16          AND vsu.sqlhash = vst.hash_value
 17          AND vsu.session_addr = vs.saddr
 18          AND vs.paddr = vp.addr
 19          AND vst.piece = 0;

no rows selected

SYS@LAB1024> -- note the filter to only get sorts using more than 10000 blocks of temp space
SYS@LAB1024> -- (that's why no records were returned)
SYS@LAB1024> -- go to another session and start a big sort, then check again

SYS@LAB1024> /

TIME_STAM USERNAME        SQL_ID        TABLESPACE        USAGE_MB SQL_TEXT                                                         SPID
--------- --------------- ------------- --------------- ---------- ---------------------------------------------------------------- ------------
19-JAN-10 SYS             9babjv8yq8ru3 TEMP                   145 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;             31514

SYS@LAB1024> -- so now we have a big one
SYS@LAB1024> -- but obviously the get_lines procedure is not causing the big sort
SYS@LAB1024> -- hmmm, what's actually active right now?
SYS@LAB1024> @as

no rows selected

SYS@LAB1024> l
  1  select sid, substr(program,1,19) prog, address, hash_value, b.sql_id, sql_child_number child, plan_hash_value, executions execs,
  2  (elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime,
  3  sql_text
  4  from v$session a, v$sqlarea b
  5  where status = 'ACTIVE'
  6  and username is not null
  7  and a.sql_id = b.sql_id
  8* and audsid != SYS_CONTEXT('userenv','sessionid')
SYS@LAB1024> del
SYS@LAB1024> /

 SID PROG       ADDRESS  HASH_VALUE SQL_ID         CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME SQL_TEXT
---- ---------- -------- ---------- ------------- ------ --------------- ---------- ----------- -----------------------------------------
 410 sqlplus@ho 74AAF3D0 1572343862 1mkjj1tfvh41q      0      1304592819          5       67.42 SELECT COL1,COL2 FROM KSO.SKEW ORDER BY C
 433 sqlplus@ho 74BB6C20 1310159811 bn8b1wp71fwy3      0       187045271          4         .01 select sid, substr(program,1,19) prog, ad

SYS@LAB1024> -- yep, SID 410 is running the query I started in the other session
SYS@LAB1024> -- let' check the prev_sql_id as indicated by the Metalink note
SYS@LAB1024>
SYS@LAB1024> select sid, sql_id, prev_sql_id from v$session where sid=410;

 SID SQL_ID        PREV_SQL_ID
---- ------------- -------------
 410 1mkjj1tfvh41q 9babjv8yq8ru3

SYS@LAB1024> -- aha, it is prev_sql_id that is exposed in v$sort_usage
SYS@LAB1024> -- just to prove it let's look at the fixed view definition
SYS@LAB1024> get fixed_view_def
  1  select * from v$fixed_view_definition
  2* where view_name like upper('&view_name')
SYS@LAB1024> /
Enter value for view_name: GV$SORT_USAGE

VIEW_NAME
------------------------------
VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------------------
GV$SORT_USAGE
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1,
'TEMPORARY'), decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, k
tssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#

SYS@LAB1024> -- notice the prev_sql_id ...
SYS@LAB1024>
SYS@LAB1024> -- so now let's look at a fixed version (only modified slightly)
SYS@LAB1024> -- since we're already joining to v$session, we can just get the sql_id from there
SYS@LAB1024> -- I rearranged the columns a bit as well 
SYS@LAB1024> -- and joined to dba_tablespaces to allow for various block sizes
SYS@LAB1024> 
SYS@LAB1024> @temp_usage

no rows selected

SYS@LAB1024> l
  1  SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text, vsu.tablespace,
  2         sum_blocks*dt.block_size/1024/1024 usage_mb
  3     FROM
  4     (
  5             SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
  6  -- sum(blocks)*8192/1024/1024 "USAGE_MB",
  7                  sum(blocks) sum_blocks
  8             FROM v$sort_usage
  9             HAVING SUM(blocks)> 1000
 10             GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
 11     ) "VSU",
 12     v$sqltext vst,
 13     v$session vs,
 14     v$process vp,
 15     dba_tablespaces dt
 16  WHERE vs.sql_id = vst.sql_id
 17  -- AND vsu.sqladdr = vst.address
 18  -- AND vsu.sqlhash = vst.hash_value
 19     AND vsu.session_addr = vs.saddr
 20     AND vs.paddr = vp.addr
 21     AND vst.piece = 0
 22     AND dt.tablespace_name = vsu.tablespace
 23* order by usage_mb

SYS@LAB1024> -- start the sort again, and recheck
SYS@LAB1024> /

TIME_STAM USERNAME         SID SPID         SQL_ID        SQL_TEXT                                           TABLESPACE        USAGE_MB
--------- --------------- ---- ------------ ------------- -------------------------------------------------- --------------- ----------
19-JAN-10 SYS              410 31514        1mkjj1tfvh41q SELECT COL1,COL2 FROM KSO.SKEW ORDER BY COL3, COL2 TEMP                   191

SYS@LAB1024> -- that looks better, that's the statement that is causing the big sort and temp usage

As pointed out in the Metalink note, it’s possible that the current sql_id may not have the correct value in some situations. One specific case that was mentioned was when a statement has completed and the session has issued another statement, but the temp space has not yet been cleaned up. It seems to work pretty well in most situations though.

One other note, it appears that this issue (bug) has not been addressed as of 11.2.0.1.