Search

OakieTags

Who's online

There are currently 0 users and 24 guests online.

Recent comments

Oakies Blog Aggregator

What the heck is the INTERNAL_FUNCTION in execution plan predicate section?

Sometimes you see something like this in an execution plan:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    22 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("B"=INTERNAL_FUNCTION("A"))

There’s quite a little information available about what the INTERNAL_FUNCTION really is and why does it show up, thus this blog entry.

There’s actually no function called INTERNAL_FUNCTION in Oracle and this is (partially) confirmed also by querying the V$SQLFN_METADATA view:

SQL> @sqlfn %internal%

no rows selected

The common understanding, coming from Oracle documentation is that the INTERNAL_FUNCTION is some sort of a special function doing (implicit) datatype conversion. This is only partially true and not the whole truth – but let’s examine the datatype conversion first and proceed to the explanation and other examples later on.

Implicit datatype conversion

I’m creating a table which stores a date in VARCHAR2 datatype in column A and a date in DATE datatype in column B:

SQL> CREATE TABLE t(a VARCHAR2(20), b DATE);

Table created.

SQL> @desc t
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        VARCHAR2(20)
    2      B                                        DATE

SQL> INSERT INTO t VALUES( TO_CHAR(sysdate), sysdate) ;

1 row created.

Now let’s run a simple select query and see its execution plan:

SQL> SELECT * FROM t WHERE a = b;

A                    B
-------------------- -----------------
20130116 17:41:49    20130116 17:41:49

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    21 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"=INTERNAL_FUNCTION("A"))

What happens here is that Oracle is forced to (implicitly) add a datatype conversion function around column A, to be able to physically compare two different datatypes. Internally Oracle is not running a comparison "WHERE a = b" anymore, but rather something like "WHERE TO_DATE(a) = b". This is one of the reasons why the INTERNAL_FUNCTION shows up – the code generating the human-readable execution plan from the actual “binary” execution plan is not able to convert the internal opcode to a corresponding human-readable function name, thus shows a default “INTERNAL_FUNCTION” string there instead.

This is Oracle 10g+ behavior, in 9i and before, Oracle just printed nothing as that function name, example output from 9i is below:

1 - filter(("DUAL"."DUMMY")=:TEST_VAR)

Do you see that there are seemingly unnecessary brackets around “DUAL”.”DUMMY” above? Why not just "DUAL.DUMMY"=:TEST_VAR, is this ("DUAL"."DUMMY"):=TEST_VAR usage some typo? It’s actually not a typo, before Oracle 10g you had to be careful to spot any such “unneccesary” brackets as they really indicated there was some function called, something like F(“DUAL”.”DUMMY”), only that the “F” was never printed. In Oracle 10g onwards, a generic “INTERNAL_FUNCTION” is printed instead of nothing in such cases – at least we know there’s some function applied to the column/variable. You need to look into the code (and possibly session-level NLS_ settings) to figure out what function it could logically be (like a function casting TIMESTAMP into a DATE when comparing these datatypes).

Un-unparseable Complex Expressions

Ok, the datatype conversion reason is already covered in Oracle docs and blogosphere. But there’s more.

Check out this example with an OR clause:

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;

Table created.

SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM';

  COUNT(*)
----------
     32272

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 36652 |   608K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter(("OWNER"='SYS' OR "OWNER"='SYSTEM'))

All is fine so far – the DBMS_XPLAN.DISPLAY_CURSOR function, which reads the plans directly from library cache is able to explain the predicate correctly.

Now let’s make the predicate a little more complex, I will add another OR to this predicate, but it’s against a different column:

SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' OR #ff0000;">object_id = 123;
...
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 36652 |  1073K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter((INTERNAL_FUNCTION("OWNER") OR "OBJECT_ID"=123))

Now, suddenly the two conditions on the OWNER table are gone and replaced by an INTERNAL_FUNCTION?

Let’s try an IN operator instead of the OR, but wait, we are checking for values in two different columns (so we can’t put them all into the same IN clause):

SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';

  COUNT(*)
----------
      1178

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   374 | 10472 |   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter((INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE'))

Still no luck.
Let’s try a logically simpler operation, with just searching for 3 values in the same column:

SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT');

  COUNT(*)
----------
     32278

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 31960 |   530K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM'))

Works! Oracle has transformed (or at least shows it in the explanation) this IN predicate to a bunch of OR-ed conditions (against the same column).

You might already see what’s going on with the earlier examples – DBMS_XPLAN.DISPLAY_CURSOR is not able to explain “complex” composite predicates applied in a single execution plan step, which include multiple different columns AND at least one of the columns has multiple values to check for (like an in-list or OR-ed predicates).

Where does DISPLAY_CURSOR get its data from and an explanation

DBMS_XPLAN.DISPLAY_CURSOR gets its plan data from V$SQL_PLAN, the predicate section comes from ACCESS_PREDICATES and FILTER_PREDICATES columns. But when I query the V$SQL_PLAN directly, I still see the same problem:

SQL> SELECT id, filter_predicates FROM v$sql_plan WHERE sql_id = 'gcqgrmtna9g1u';

        ID FILTER_PREDICATES
---------- ------------------------------------------------------------
         0
         1
         2 (INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE')

And you may have noticed that there are brackets() around the raw ORed conditions above too, which in 9i meant that there was an “unexplained” internal function in the “binary” execution plan around the reported predicates, but in this case (as 10g+ supports the internal_function naming), blank function names shouldn’t occur… Not really sure why there about this, but that’s too low level little detail for this post.

The V$SQL_PLAN view itself accesses the actual “binary” child cursor in library cache (after taking appropriate latches/pins/mutexes) and UNPARSES it. Why such term – well isn’t parsing something that takes a human readable input and translates it into computer-understandable “binary” format. Thus unparsing is the opposite – V$SQL_PLAN accesses the cursor’s “binary” execution plan memory structure and translates it to human-readable execution plan output. There’s even a parameter controlling this V$SQL_PLAN behavior, if it’s set to false, the ACCESS_PREDICATES and FILTER_PREDICATES columns will be empty there:

SQL> @pd unparse
Show all parameters and session values from x$ksppi/x$ksppcv...

NAME                             VALUE                                      DESCRIPTION
----------------------------- --------- -----------------------------------------------
_cursor_plan_unparse_enabled      TRUE          enables/disables using unparse to build
                                                                  projection/predicates

By the way, why do I keep saying “binary” execution plan and in double quotes? It’s because I want to emphasize that the real execution plan that Oracle executes is not in the text form like we see on the screen, the text is just generated for humans, for troubleshooting reasons. The execution plan is not a real executable binary (as in oracle.exe) either, it’s not directly fed to the CPUs for execution. The physical execution plan in the library cache child cursor is a bunch of opcodes, object_ids and pointers for defining the hierarchy and order of rowsource execution. It’s the SQL execution engine, which then loops through these opcodes, decodes them and knows what to do (which rowsource function to call) next.

So, as we’ve seen above, some predicates with complex AND/OR conditions chained together are displayed as INTERNAL_FUNCTION() by DBMS_XPLAN.DISPLAY_CURSOR and V$SQL_PLAN as they are unable to decode (unparse) the execution plan info fully.

Using the good old EXPLAIN PLAN

There’s some good news though! The good old EXPLAIN PLAN command can unparse (some of) these complex predicates properly! As the EXPLAIN PLAN operation parses the given SQL again, in a special, more instrumented way, it has more information at hand apparently (and it uses more memory too). Or it could just be that whoever wrote V$SQL_PLAN, didn’t write the piece of code for unparsing more complex predicates :)

Check this output, where I’m using the explain plan command instead of just running the SQL:

SQL> EXPLAIN PLAN FOR 
     SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    28 |   293   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   374 | 10472 |   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')
              AND "OBJECT_TYPE"='TABLE')

It’s a miracle! The INTERNAL_FUNCTION is gone and all the predicate values are shown correctly. EXPLAIN PLAN command was very useful here.

So, while I usually don’t use the EXPLAIN PLAN command as explain plan can lie to you, then whenever I see an INTERNAL_FUNCTION in the DISPLAY_CURSOR/V$SQL_PLAN/SQL Monitor output, I run an explain plan command for the same query in hope of quickly finding out what the predicates in there really are.

Ok, it’s 2:30am here yet again – off to sleep! :)


Delphix Live webcast: Database Virtualization

Live webcast: Realize Massive ROI with Database Virtualization
Date: Wednesday Jan 30, 2013 @12pm ET/9am PT
Click Here to Register

If you could create as many copies as you wanted of production databases for development, reporting and QA, how many would you create? 10,20, a 100? It is possible in the space of a single copy of the production database using thin provision cloning. Thin provision cloning give enormous disk savings by sharing the majority of source database data blocks. Thin provision cloning is one technology of database virtualization. Database virtualization goes beyond thin provision cloning technology to provide agile corporate data management.

Virtual databases can be created, refreshed, rolled back, rolled forward and deleted in seconds. Virtual databases can be provisioned from any second within the source databases retention window which is typically several weeks. Every developer can have their own full copy of production databases, production databases can have 50 days of backup live online in the space of one backup. Backups can be brought online in seconds, data reviewed or extracted and the copy removed in seconds. QA teams can go from one test environment to instead having multiple full copies of production database allowing running QA tests in parallel.

Delphix eliminates widespread IT inefficiencies caused by dragging behind enormous amounts of infrastructure, process and bureaucracy  required to provide database copies.  Delphix eliminates the drag and provides power through agile data management software and database virtualization. Join this session to learn how organizations like Deutsche Bank, Proctor and Gamble, Facebook, EA, Stubhub and many more are realizing significant returns with Delphix, including:

 

  • Greater agility: 500% greater application project output
  • Lower risk: 50% higher error detection in development
  • Reduced costs: 90% reduction in storage cost for copies and backups 

Join us on Jan 30 @12PM ET to learn more. Click Here to Register

 

Oracle 11gR2 RAC Installation on Oracle Linux 5

As promised in a recent post, I’ve updated the Oracle 11gR2 RAC on Oracle Linux 5 article. It now uses VirtualBox 4.2.6, rather than 3.2.8 as it was before, and Oracle Linux 5.8.

I’ve purposely left it as an 11.2.0.1 installation as you can get this from OTN without needing access to My Oracle Support (MOS). The process works just as well for 11.2.0.3 and I would recommend you use that if you do have access to MOS. Remember, if you are doing the RAC installation on Oracle Linux 6 you are going to need 11.2.0.3, so OL5 might be the right option if you are playing around with this at home with no access to MOS.

Cheers

Tim…


Oracle 11gR2 RAC Installation on Oracle Linux 5 was first posted on January 16, 2013 at 12:41 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Oracle Linux and ESX hot add cpu and memory

I'm still quite new in Vmware ESX environment and features which I'm using now as my lab (curiosity of VMWare Guru Program).
This time I decided to test hot add CPU and memory functionality. It looks very interesting and in past years hot cpu / memory games where restricted to sophisticated hardware only - now everybody can test is at home.

My lab is basesd on Intel 4 cores and VM are running Oracle Linux 6.3 with or without Oracle Enterprise Kernel. Here are my findings:

1. Hot add CPU / memory is disabled by default and has to be enabled when VM is down. To do so you have to edit your VM configuration and go to options.

2. When feature is enabled you can ADD CPU or memory by editing your VM configuration. Keep in mind that there is no possibility to decrease number of CPU or memory.

3. Here is a table with supported options for ESX 5i and Oracle Linux with or without UEK

I tested adding a CPU to system for RedHat and UEK kernel. As you can find in table linked above adding memory is supported for RedHat kernels only.

RedHat kernel

OEL 6.3 with RedHat kernel running with 1 CPU

[root@oraclese ~]# uname -a
Linux oraclese.localdomain 2.6.32-279.5.2.el6.x86_64 #1 SMP Thu Aug 23 12:05:59 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc up arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat epb xsaveopt pln pts dts
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

New CPU has been added in vSphere client. Below is output from messages log.

Jan 13 15:17:25 oraclese kernel: SMP alternatives: switching to SMP code
Jan 13 15:17:25 oraclese kernel: Booting Node 0 Processor 1 APIC 0x1
Jan 13 15:17:25 oraclese kernel: Disabled fast string operations
Jan 13 15:17:25 oraclese kernel: mce: CPU supports 0 MCE banks
Jan 13 15:17:25 oraclese kernel: x86 PAT enabled: cpu 1, old 0x0, new 0x7010600070106
Jan 13 15:17:25 oraclese kernel: Skipped synchronization checks as TSC is reliable.
Jan 13 15:17:25 oraclese kernel: microcode: CPU1 sig=0x206a7, pf=0x1, revision=0x14
Jan 13 15:17:25 oraclese kernel: platform microcode: firmware: requesting intel-ucode/06-2a-07
Jan 13 15:17:25 oraclese firmware.sh[2070]: Cannot find firmware file 'intel-ucode/06-2a-07'
Jan 13 15:17:25 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:17:25 oraclese kdump: failed to stop
Jan 13 15:17:25 oraclese kdump: No crashkernel parameter specified for running kernel

Now system is running with two CPU's

[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat epb xsaveopt pln pts dts
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat epb xsaveopt pln pts dts
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

Adding memory - OEL 6.3 wtih RedHat kernel - system running with 1 GB of RAM

[root@oraclese ~]# uname -a
Linux oraclese.localdomain 2.6.32-279.5.2.el6.x86_64 #1 SMP Thu Aug 23 12:05:59 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
[root@oraclese ~]# free
total used free shared buffers cached
Mem: 1016680 190384 826296 0 4736 77112
-/+ buffers/cache: 108536 908144
Swap: 2097144 0 2097144

Additional 1 GB has been added via vSphere Client. Below is output from messages log.

Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000040000000-0000000048000000
Jan 13 15:40:54 oraclese kernel: Built 1 zonelists in Node order, mobility grouping on. Total pages: 274190
Jan 13 15:40:54 oraclese kernel: Policy zone: Normal
Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000048000000-0000000050000000
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000050000000-0000000058000000
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000058000000-0000000080000000
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel

Now Linux reconized 2 GB of RAM

[root@oraclese ~]# free
total used free shared buffers cached
Mem: 2065256 211840 1853416 0 4764 77328
-/+ buffers/cache: 129748 1935508
Swap: 2097144 0 2097144

Oracle UEK release 2 kernel

OEL 6.3 running with Oracle UEK release 2

[root@oraclese ~]# uname -a
Linux oraclese.localdomain 2.6.32-300.3.1.el6uek.x86_64 #1 SMP Fri Dec 9 18:57:35 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc up arch_perfmon pebs bts rep_good xtopology tsc_reliable nonstop_tsc aperfmperf pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

New CPU has been added in vSphere client. Below is output from messages log.

Jan 13 15:38:12 oraclese kernel: processor LNXCPU:01: registered as cooling_device1
Jan 13 15:38:12 oraclese kernel: SMP alternatives: switching to SMP code
Jan 13 15:38:12 oraclese kernel: Booting processor 1 APIC 0x1 ip 0x6000
Jan 13 15:38:12 oraclese kernel: Initializing CPU#1
Jan 13 15:38:12 oraclese kernel: CPU: Physical Processor ID: 2
Jan 13 15:38:12 oraclese kernel: CPU: L1 I cache: 32K, L1 D cache: 32K
Jan 13 15:38:12 oraclese kernel: CPU: L2 cache: 256K
Jan 13 15:38:12 oraclese kernel: CPU: L3 cache: 6144K
Jan 13 15:38:12 oraclese kernel: CPU 1/0x1 -> Node 0
Jan 13 15:38:12 oraclese kernel: mce: CPU supports 0 MCE banks
Jan 13 15:38:12 oraclese kernel: x86 PAT enabled: cpu 1, old 0x0, new 0x7010600070106
Jan 13 15:38:12 oraclese kernel: CPU1: Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz stepping 07
Jan 13 15:38:12 oraclese kernel: Skipping synchronization checks as TSC is reliable.
Jan 13 15:38:12 oraclese kernel: microcode: CPU1 sig=0x206a7, pf=0x1, revision=0x14
Jan 13 15:38:12 oraclese kernel: platform microcode: firmware: requesting intel-ucode/06-2a-07
Jan 13 15:38:12 oraclese firmware.sh[2074]: Cannot find firmware file 'intel-ucode/06-2a-07'
Jan 13 15:38:12 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:38:12 oraclese kdump: failed to stop
Jan 13 15:38:12 oraclese kdump: No crashkernel parameter specified for running kernel

Now system is running with two CPU's

[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology tsc_reliable nonstop_tsc aperfmperf pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology tsc_reliable nonstop_tsc aperfmperf pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

regards, Marcin

Oracle Linux and ESX hot add cpu and memory

I'm still quite new in Vmware ESX environment and features which I'm using now as my lab (curiosity of VMWare Guru Program).
This time I decided to test hot add CPU and memory functionality. It looks very interesting and in past years hot cpu / memory games where restricted to sophisticated hardware only - now everybody can test is at home.

My lab is basesd on Intel 4 cores and VM are running Oracle Linux 6.3 with or without Oracle Enterprise Kernel. Here are my findings:

1. Hot add CPU / memory is disabled by default and has to be enabled when VM is down. To do so you have to edit your VM configuration and go to options.

2. When feature is enabled you can ADD CPU or memory by editing your VM configuration. Keep in mind that there is no possibility to decrease number of CPU or memory.

3. Here is a table with supported options for ESX 5i and Oracle Linux with or without UEK

I tested adding a CPU to system for RedHat and UEK kernel. As you can find in table linked above adding memory is supported for RedHat kernels only.

RedHat kernel

OEL 6.3 with RedHat kernel running with 1 CPU

[root@oraclese ~]# uname -a
Linux oraclese.localdomain 2.6.32-279.5.2.el6.x86_64 #1 SMP Thu Aug 23 12:05:59 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc up arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat epb xsaveopt pln pts dts
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

New CPU has been added in vSphere client. Below is output from messages log.

Jan 13 15:17:25 oraclese kernel: SMP alternatives: switching to SMP code
Jan 13 15:17:25 oraclese kernel: Booting Node 0 Processor 1 APIC 0x1
Jan 13 15:17:25 oraclese kernel: Disabled fast string operations
Jan 13 15:17:25 oraclese kernel: mce: CPU supports 0 MCE banks
Jan 13 15:17:25 oraclese kernel: x86 PAT enabled: cpu 1, old 0x0, new 0x7010600070106
Jan 13 15:17:25 oraclese kernel: Skipped synchronization checks as TSC is reliable.
Jan 13 15:17:25 oraclese kernel: microcode: CPU1 sig=0x206a7, pf=0x1, revision=0x14
Jan 13 15:17:25 oraclese kernel: platform microcode: firmware: requesting intel-ucode/06-2a-07
Jan 13 15:17:25 oraclese firmware.sh[2070]: Cannot find firmware file 'intel-ucode/06-2a-07'
Jan 13 15:17:25 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:17:25 oraclese kdump: failed to stop
Jan 13 15:17:25 oraclese kdump: No crashkernel parameter specified for running kernel

Now system is running with two CPU's

[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat epb xsaveopt pln pts dts
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat epb xsaveopt pln pts dts
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

Adding memory - OEL 6.3 wtih RedHat kernel - system running with 1 GB of RAM

[root@oraclese ~]# uname -a
Linux oraclese.localdomain 2.6.32-279.5.2.el6.x86_64 #1 SMP Thu Aug 23 12:05:59 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
[root@oraclese ~]# free
total used free shared buffers cached
Mem: 1016680 190384 826296 0 4736 77112
-/+ buffers/cache: 108536 908144
Swap: 2097144 0 2097144

Additional 1 GB has been added via vSphere Client. Below is output from messages log.

Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000040000000-0000000048000000
Jan 13 15:40:54 oraclese kernel: Built 1 zonelists in Node order, mobility grouping on. Total pages: 274190
Jan 13 15:40:54 oraclese kernel: Policy zone: Normal
Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000048000000-0000000050000000
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000050000000-0000000058000000
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kernel: init_memory_mapping: 0000000058000000-0000000080000000
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: failed to stop
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel
Jan 13 15:40:54 oraclese kdump: No crashkernel parameter specified for running kernel

Now Linux reconized 2 GB of RAM

[root@oraclese ~]# free
total used free shared buffers cached
Mem: 2065256 211840 1853416 0 4764 77328
-/+ buffers/cache: 129748 1935508
Swap: 2097144 0 2097144

Oracle UEK release 2 kernel

OEL 6.3 running with Oracle UEK release 2

[root@oraclese ~]# uname -a
Linux oraclese.localdomain 2.6.32-300.3.1.el6uek.x86_64 #1 SMP Fri Dec 9 18:57:35 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc up arch_perfmon pebs bts rep_good xtopology tsc_reliable nonstop_tsc aperfmperf pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

New CPU has been added in vSphere client. Below is output from messages log.

Jan 13 15:38:12 oraclese kernel: processor LNXCPU:01: registered as cooling_device1
Jan 13 15:38:12 oraclese kernel: SMP alternatives: switching to SMP code
Jan 13 15:38:12 oraclese kernel: Booting processor 1 APIC 0x1 ip 0x6000
Jan 13 15:38:12 oraclese kernel: Initializing CPU#1
Jan 13 15:38:12 oraclese kernel: CPU: Physical Processor ID: 2
Jan 13 15:38:12 oraclese kernel: CPU: L1 I cache: 32K, L1 D cache: 32K
Jan 13 15:38:12 oraclese kernel: CPU: L2 cache: 256K
Jan 13 15:38:12 oraclese kernel: CPU: L3 cache: 6144K
Jan 13 15:38:12 oraclese kernel: CPU 1/0x1 -> Node 0
Jan 13 15:38:12 oraclese kernel: mce: CPU supports 0 MCE banks
Jan 13 15:38:12 oraclese kernel: x86 PAT enabled: cpu 1, old 0x0, new 0x7010600070106
Jan 13 15:38:12 oraclese kernel: CPU1: Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz stepping 07
Jan 13 15:38:12 oraclese kernel: Skipping synchronization checks as TSC is reliable.
Jan 13 15:38:12 oraclese kernel: microcode: CPU1 sig=0x206a7, pf=0x1, revision=0x14
Jan 13 15:38:12 oraclese kernel: platform microcode: firmware: requesting intel-ucode/06-2a-07
Jan 13 15:38:12 oraclese firmware.sh[2074]: Cannot find firmware file 'intel-ucode/06-2a-07'
Jan 13 15:38:12 oraclese kdump: kexec: failed to unload kdump kernel
Jan 13 15:38:12 oraclese kdump: failed to stop
Jan 13 15:38:12 oraclese kdump: No crashkernel parameter specified for running kernel

Now system is running with two CPU's

[root@oraclese ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology tsc_reliable nonstop_tsc aperfmperf pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz
stepping : 7
cpu MHz : 3110.469
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology tsc_reliable nonstop_tsc aperfmperf pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt aes xsave avx hypervisor lahf_lm ida arat
bogomips : 6220.93
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:

regards, Marcin

Tuning paramon.sql

I know no one really likes the term “tuning” these days, but it’s a short catchy word that gets the idea across. So I’ll just stick with it for the title of this post.

Note that this is one of those posts that’s not really supposed to be about how to solve a particular problem. It’s really just a story about a distraction that I ran into and I how I thought about getting around the issue and then ultimately resolving the root cause. Maybe you will find it instructive to see the process.

So I have this script that I use occasionally (paramon.sql) to see what parallel query slaves are doing. Unfortunately the script doesn’t have a header in it, but I’m pretty sure I lifted it from Randolf Geist. I can’t find it on his blog anywhere, but it looks like his style of writing SQL, and PX Query is something he’s written a lot about, so I’m pretty sure that’s where I got it. (Update: see Jonathan Lewis’s comment below attributing the script to Andy Brooker) Anyway, the script has worked great for me in the past but I recently noticed that it was really sluggish on a couple of 11gR2 DB’s running on Exadata. Here’s an example:

-bash-3.2$ !sql
sqlp
 
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 14 12:23:15 2013
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME               CURRENT_TIME                  DAYS    SECONDS
---------------- -------------------------- -------------------------- ------- ----------
dbm1             09-JAN-2013 03:25          14-JAN-2013 12:23             5.37     464246
 
SYS@dbm1> set SQLPROMPT "11.2.0.3> "
11.2.0.3>
11.2.0.3> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     35
      P001 AVAILABLE     36
      P002 AVAILABLE     37
      P003 AVAILABLE     38
      P004 AVAILABLE     39
      P005 AVAILABLE     40
      P006 AVAILABLE     41
      P007 AVAILABLE     42
      P008 AVAILABLE     43
      P009 AVAILABLE     44
      P010 AVAILABLE     45
      P011 AVAILABLE     46
      P012 AVAILABLE     47
      P013 AVAILABLE     48
      P014 AVAILABLE     49
      P015 AVAILABLE     50
      P016 AVAILABLE     51
      P017 AVAILABLE     52
      P018 AVAILABLE     53
      P019 AVAILABLE     54
      P020 AVAILABLE     55
      P021 AVAILABLE     56
      P022 AVAILABLE     57
      P023 AVAILABLE     58
      P024 AVAILABLE     59
      P025 AVAILABLE     60
      P026 AVAILABLE     61
      P027 AVAILABLE     62
      P028 AVAILABLE     63
      P029 AVAILABLE     64
      P030 AVAILABLE     65
      P031 AVAILABLE     66
 
 
32 rows selected.
 
Elapsed: 00:00:23.11

So on this 11g DB it took 23 seconds to run the query. On one of my 10g DB’s though the performance was stellar.

[osborne@homer scripts]$ rlwrap sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 14 11:20:28 2013
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1024          19-DEC-2012 14:44 14-JAN-2013 11:20   25.86    2234138
 
SYS@LAB1024> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     19
      P001 AVAILABLE     20
      P002 AVAILABLE     21
      P003 AVAILABLE     22
      P004 AVAILABLE     23
      P005 AVAILABLE     24
      P006 AVAILABLE     25
      P007 AVAILABLE     26
      P008 AVAILABLE     27
      P009 AVAILABLE     28
      P010 AVAILABLE     29
      P011 AVAILABLE     30
      P012 AVAILABLE     31
      P013 AVAILABLE     32
      P014 AVAILABLE     33
      P015 AVAILABLE     36
      P016 AVAILABLE     37
      P017 AVAILABLE     38
      P018 AVAILABLE     39
      P019 AVAILABLE     40
      P020 AVAILABLE     41
      P021 AVAILABLE     42
      P022 AVAILABLE     43
      P023 AVAILABLE     44
      P024 AVAILABLE     45
      P025 AVAILABLE     46
      P026 AVAILABLE     47
      P027 AVAILABLE     48
      P028 AVAILABLE     49
      P029 AVAILABLE     50
      P030 AVAILABLE     51
      P031 AVAILABLE     52
 
 
32 rows selected.
 
Elapsed: 00:00:00.03

So it’s sub-second on the 10g DB and the relatively long 25-30 second execution time on 11g became irritating very quickly. Some day I’ll do a post about the different thought processes involved in solving problems, but for now just let me say that since I am a fairly experienced Oracle Guy, I usually indulge myself in a few quick checks on known suspects before jumping into any kind of methodical thinking. And in my experience, the most common cause of performance degradation is a plan change. That takes about 5 seconds to check, which I did, and sure enough the plan was different.

11.2.0.3> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 1
-------------------------------------
 select distinct v.inst_id              , x.server_name       ,
x.status as x_status       , x.pid as x_pid       , x.sid as x_sid
 , w2.sid as p_sid       , v.osuser       , v.schemaname       ,
w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      ,
v$session_wait w1      , v$session_wait w2  where x.sid <> l.sid(+)
and   to_number (substr(x.server_name,2)) = l.id2(+)  and   x.sid =
w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)
     and substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 2444082350
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |     6 (100)|          |        |      |            |
|   1 |  SORT UNIQUE                       |                 |     1 |   219 |     5 (100)| 00:00:01 |        |      |            |
|*  2 |   HASH JOIN OUTER                  |                 |     1 |   219 |     4 (100)| 00:00:01 |        |      |            |
|*  3 |    HASH JOIN OUTER                 |                 |     1 |   172 |     3 (100)| 00:00:01 |        |      |            |
|*  4 |     HASH JOIN OUTER                |                 |     1 |   125 |     3 (100)| 00:00:01 |        |      |            |
|*  5 |      FILTER                        |                 |       |       |            |          |        |      |            |
|*  6 |       HASH JOIN OUTER              |                 |     1 |    65 |     2 (100)| 00:00:01 |        |      |            |
|   7 |        VIEW                        | V$PX_PROCESS    |     1 |    36 |     1 (100)| 00:00:01 |        |      |            |
|*  8 |         HASH JOIN OUTER            |                 |     1 |   124 |     1 (100)| 00:00:01 |        |      |            |
|*  9 |          HASH JOIN                 |                 |     1 |    97 |     1 (100)| 00:00:01 |        |      |            |
|* 10 |           FIXED TABLE FULL         | X$KXFPDP        |     1 |    44 |     0   (0)|          |        |      |            |
|* 11 |           FIXED TABLE FULL         | X$KSUPR         |     1 |    53 |     0   (0)|          |        |      |            |
|  12 |          VIEW                      | V$SESSION       |     1 |    27 |     0   (0)|          |        |      |            |
|  13 |           NESTED LOOPS             |                 |     1 |   105 |     0   (0)|          |        |      |            |
|  14 |            NESTED LOOPS            |                 |     1 |    92 |     0   (0)|          |        |      |            |
|* 15 |             FIXED TABLE FULL       | X$KSUSE         |     1 |    66 |     0   (0)|          |        |      |            |
|* 16 |             FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |        |      |            |
|* 17 |            FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |        |      |            |
|  18 |        VIEW                        | V$LOCK          |     1 |    29 |     1 (100)| 00:00:01 |        |      |            |
|* 19 |         HASH JOIN                  |                 |     1 |    79 |     1 (100)| 00:00:01 |        |      |            |
|  20 |          MERGE JOIN CARTESIAN      |                 |   100 |  6700 |     0   (0)|          |        |      |            |
|* 21 |           FIXED TABLE FULL         | X$KSUSE         |     1 |    32 |     0   (0)|          |        |      |            |
|  22 |           BUFFER SORT              |                 |   100 |  3500 |     0   (0)|          |        |      |            |
|  23 |            FIXED TABLE FULL        | X$KSQRS         |   100 |  3500 |     0   (0)|          |        |      |            |
|  24 |          VIEW                      | GV$_LOCK        |    10 |   120 |     0   (0)|          |        |      |            |
|  25 |           UNION-ALL                |                 |       |       |            |          |        |      |            |
|* 26 |            FILTER                  |                 |       |       |            |          |        |      |            |
|  27 |             VIEW                   | GV$_LOCK1       |     2 |    24 |     0   (0)|          |        |      |            |
|  28 |              UNION-ALL             |                 |       |       |            |          |        |      |            |
|* 29 |               FIXED TABLE FULL     | X$KDNSSF        |     1 |    64 |     0   (0)|          |        |      |            |
|* 30 |               FIXED TABLE FULL     | X$KSQEQ         |     1 |    64 |     0   (0)|          |        |      |            |
|* 31 |            FIXED TABLE FULL        | X$KTADM         |     1 |    64 |     0   (0)|          |        |      |            |
|* 32 |            FIXED TABLE FULL        | X$KTATRFIL      |     1 |    64 |     0   (0)|          |        |      |            |
|* 33 |            FIXED TABLE FULL        | X$KTATRFSL      |     1 |    64 |     0   (0)|          |        |      |            |
|* 34 |            FIXED TABLE FULL        | X$KTATL         |     1 |    64 |     0   (0)|          |        |      |            |
|* 35 |            FIXED TABLE FULL        | X$KTSTUSC       |     1 |    64 |     0   (0)|          |        |      |            |
|* 36 |            FIXED TABLE FULL        | X$KTSTUSS       |     1 |    64 |     0   (0)|          |        |      |            |
|* 37 |            FIXED TABLE FULL        | X$KTSTUSG       |     1 |    64 |     0   (0)|          |        |      |            |
|* 38 |            FIXED TABLE FULL        | X$KTCXB         |     1 |    64 |     0   (0)|          |        |      |            |
|  39 |      PX COORDINATOR                |                 |     1 |    60 |     0   (0)|          |        |      |            |
|  40 |       PX SEND QC (RANDOM)          | :TQ10000        |     1 |   125 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|  41 |        VIEW                        | GV$SESSION      |       |       |            |          |  Q1,00 | PCWP |            |
|  42 |         NESTED LOOPS               |                 |     1 |   125 |     0   (0)|          |  Q1,00 | PCWP |            |
|  43 |          NESTED LOOPS              |                 |     1 |   112 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 44 |           FIXED TABLE FULL         | X$KSUSE         |     1 |    86 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 45 |           FIXED TABLE FIXED INDEX  | X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 46 |          FIXED TABLE FIXED INDEX   | X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |  Q1,00 | PCWP |            |
|  47 |     VIEW                           | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  48 |      NESTED LOOPS                  |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 49 |       FIXED TABLE FULL             | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 50 |       FIXED TABLE FIXED INDEX      | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
|  51 |    VIEW                            | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  52 |     NESTED LOOPS                   |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 53 |      FIXED TABLE FULL              | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 54 |      FIXED TABLE FIXED INDEX       | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("L"."SID"="W2"."SID")
   3 - access("X"."SID"="W1"."SID")
   4 - access("X"."SID"="V"."SID")
   5 - filter(NVL("L"."TYPE",'PS')='PS')
   6 - access("L"."ID2"=TO_NUMBER(SUBSTR("X"."SERVER_NAME",2)))
       filter("X"."SID"<>"L"."SID")
   8 - access("A"."KXFPDPSPID"="C"."PROCESS")
   9 - access("A"."KXFPDPSPID"="KSUPRPID")
  10 - filter(("A"."INST_ID"=USERENV('INSTANCE') AND BITAND("KXFPDPFLG",8)<>0 AND SUBSTR("A"."KXFPDPNAM",2,1)<>'Z' AND
              DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE') LIKE DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE')))
  11 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  15 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  16 - filter("S"."INDX"="W"."KSLWTSID")
  17 - filter("W"."KSLWTEVT"="E"."INDX")
  19 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."
              ADDR"))
  21 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  26 - filter(USERENV('INSTANCE') IS NOT NULL)
  29 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  30 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  31 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  32 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  33 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  34 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  35 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  36 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  37 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0))
  38 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  44 - filter((BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  45 - filter("S"."INDX"="W"."KSLWTSID")
  46 - filter("W"."KSLWTEVT"="E"."INDX")
  49 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  50 - filter("S"."KSLWTEVT"="E"."INDX")
  53 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  54 - filter("S"."KSLWTEVT"="E"."INDX")
 
Note
-----
   - statement not queuable: gv$ statement
 
 
118 rows selected.
 
Elapsed: 00:00:00.06
 
=================================================
switching to 10g
=================================================
 
SYS@LAB1024> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 0
-------------------------------------
 select distinct v.inst_id              , x.server_name       , x.status as x_status       ,
x.pid as x_pid       , x.sid as x_sid       , w2.sid as p_sid       , v.osuser       ,
v.schemaname       , w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      , v$session_wait w1      ,
v$session_wait w2  where x.sid <> l.sid(+)  and   to_number (substr(x.server_name,2)) =
l.id2(+)  and   x.sid = w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)         and
substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 2160357371
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |       |       |     6 (100)|          |
|   1 |  SORT UNIQUE                      |                 |     1 |   219 |     5 (100)| 00:00:01 |
|*  2 |   HASH JOIN OUTER                 |                 |     1 |   219 |     4 (100)| 00:00:01 |
|*  3 |    HASH JOIN OUTER                |                 |     1 |   172 |     3 (100)| 00:00:01 |
|*  4 |     HASH JOIN OUTER               |                 |     1 |   125 |     3 (100)| 00:00:01 |
|*  5 |      FILTER                       |                 |       |       |            |          |
|*  6 |       HASH JOIN OUTER             |                 |     1 |    65 |     2 (100)| 00:00:01 |
|   7 |        VIEW                       | V$PX_PROCESS    |     1 |    36 |     1 (100)| 00:00:01 |
|*  8 |         HASH JOIN OUTER           |                 |     1 |   106 |     1 (100)| 00:00:01 |
|*  9 |          HASH JOIN                |                 |     1 |    85 |     1 (100)| 00:00:01 |
|* 10 |           FIXED TABLE FULL        | X$KXFPDP        |     1 |    38 |     0   (0)|          |
|* 11 |           FIXED TABLE FULL        | X$KSUPR         |     1 |    47 |     0   (0)|          |
|  12 |          VIEW                     | V$SESSION       |     1 |    21 |     0   (0)|          |
|  13 |           NESTED LOOPS            |                 |     1 |    86 |     0   (0)|          |
|* 14 |            FIXED TABLE FULL       | X$KSUSE         |     1 |    73 |     0   (0)|          |
|* 15 |            FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |
|  16 |        VIEW                       | V$LOCK          |     1 |    29 |     1 (100)| 00:00:01 |
|  17 |         NESTED LOOPS              |                 |     1 |    79 |     1 (100)| 00:00:01 |
|* 18 |          HASH JOIN                |                 |     1 |    57 |     1 (100)| 00:00:01 |
|* 19 |           FIXED TABLE FULL        | X$KSUSE         |     1 |    32 |     0   (0)|          |
|* 20 |           VIEW                    | GV$_LOCK        |    10 |   250 |     0   (0)|          |
|  21 |            UNION-ALL              |                 |       |       |            |          |
|* 22 |             VIEW                  | GV$_LOCK1       |     2 |   178 |     0   (0)|          |
|  23 |              UNION-ALL            |                 |       |       |            |          |
|* 24 |               FIXED TABLE FULL    | X$KDNSSF        |     1 |   102 |     0   (0)|          |
|* 25 |               FIXED TABLE FULL    | X$KSQEQ         |     1 |   102 |     0   (0)|          |
|* 26 |             FIXED TABLE FULL      | X$KTADM         |     1 |   102 |     0   (0)|          |
|* 27 |             FIXED TABLE FULL      | X$KTATRFIL      |     1 |   102 |     0   (0)|          |
|* 28 |             FIXED TABLE FULL      | X$KTATRFSL      |     1 |   102 |     0   (0)|          |
|* 29 |             FIXED TABLE FULL      | X$KTATL         |     1 |   102 |     0   (0)|          |
|* 30 |             FIXED TABLE FULL      | X$KTSTUSC       |     1 |   102 |     0   (0)|          |
|* 31 |             FIXED TABLE FULL      | X$KTSTUSS       |     1 |   102 |     0   (0)|          |
|* 32 |             FIXED TABLE FULL      | X$KTSTUSG       |     1 |   102 |     0   (0)|          |
|* 33 |             FIXED TABLE FULL      | X$KTCXB         |     1 |   102 |     0   (0)|          |
|* 34 |          FIXED TABLE FIXED INDEX  | X$KSQRS (ind:1) |     1 |    22 |     0   (0)|          |
|  35 |      VIEW                         | GV$SESSION      |     1 |    60 |     0   (0)|          |
|  36 |       NESTED LOOPS                |                 |     1 |   112 |     0   (0)|          |
|* 37 |        FIXED TABLE FULL           | X$KSUSE         |     1 |    99 |     0   (0)|          |
|* 38 |        FIXED TABLE FIXED INDEX    | X$KSLED (ind:2) |     1 |    13 |     0   (0)|          |
|  39 |     VIEW                          | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |
|  40 |      NESTED LOOPS                 |                 |     1 |   125 |     0   (0)|          |
|* 41 |       FIXED TABLE FULL            | X$KSUSECST      |     1 |    78 |     0   (0)|          |
|* 42 |       FIXED TABLE FIXED INDEX     | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |
|  43 |    VIEW                           | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |
|  44 |     NESTED LOOPS                  |                 |     1 |   125 |     0   (0)|          |
|* 45 |      FIXED TABLE FULL             | X$KSUSECST      |     1 |    78 |     0   (0)|          |
|* 46 |      FIXED TABLE FIXED INDEX      | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("L"."SID"="W2"."SID")
   3 - access("X"."SID"="W1"."SID")
   4 - access("X"."SID"="V"."SID")
   5 - filter(NVL("L"."TYPE",'PS')='PS')
   6 - access("L"."ID2"=TO_NUMBER(SUBSTR("X"."SERVER_NAME",2)))
       filter("X"."SID"<>"L"."SID")
   8 - access("A"."KXFPDPSPID"="C"."PROCESS")
   9 - access("A"."KXFPDPSPID"="KSUPRPID")
  10 - filter((SUBSTR("A"."KXFPDPNAM",2,1)<>'Z' AND "A"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("KXFPDPFLG",8)<>0 AND DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE') LIKE
              NVL('',DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE'))))
  11 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  14 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0))
  15 - filter("S"."KSUSEOPC"="E"."INDX")
  18 - access("SADDR"="S"."ADDR")
  19 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  20 - filter("INST_ID"=USERENV('INSTANCE'))
  22 - filter("INST_ID"=USERENV('INSTANCE'))
  24 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  25 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  26 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  27 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  28 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  29 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  30 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  31 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  32 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  33 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0))
  34 - filter("RADDR"="R"."ADDR")
  37 - filter((BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  38 - filter("S"."KSUSEOPC"="E"."INDX")
  41 - filter(("S"."KSUSSSEQ"<>0 AND "S"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  42 - filter("S"."KSUSSOPC"="E"."INDX")
  45 - filter(("S"."KSUSSSEQ"<>0 AND "S"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  46 - filter("S"."KSUSSOPC"="E"."INDX")
 
 
107 rows selected.

Clearly the plans are different because the plan_hash_values are different, but the plans are a little long and on the ugly side. And since I had wanted to use the script, I decided to see if I could just get the old plan back. An easy way to do this is to use Carlos Seirra’s coe_xfr_sql_profile.sql script which is distributed by Oracle on MOS as part of SQLT. (note that I renamed the script coe.sql because the full name was too long for me to type) To use the script, all you have to do is run it and give it a sql_id and a plan_hash_value and it will create another script which you can use to create a SQL Profile with all the necessary hints to recreate the plan. So I did that.

SYS@LAB1024> @coe
 
Parameter 1:
SQL_ID (required)
 
Enter value for 1: 9b2hgfwj6bnu1
 
 
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2160357371        .018
 
Parameter 2:
PLAN_HASH_VALUE (required)
 
Enter value for 2: 2160357371
 
Values passed:
~~~~~~~~~~~~~
SQL_ID         : "9b2hgfwj6bnu1"
PLAN_HASH_VALUE: "2160357371"
 
 
Execute coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql
on TARGET system in order to create a custom SQL Profile
with plan 2160357371 linked to adjusted sql_text.
 
 
COE_XFR_SQL_PROFILE completed.

Now all I need to do is run the script (coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql) on the 11g DB to create the SQL Profile and then test to make sure the plan could be reproduced and that it fixed the performance issue. Note that the output of the specific coe script is a bit verbose, but it gets the job done. :)

11.2.0.3> !scp homer:/home/osborne/scripts/coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql .
osborne@homer's password: 
coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql                                                                                                                     100% 6862     6.7KB/s   00:00    
 
11.2.0.3> @coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql
11.2.0.3> REM
11.2.0.3> REM $Header: 215187.1 coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql 11.4.1.4 2013/01/14 csierra $
11.2.0.3> REM
11.2.0.3> REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
11.2.0.3> REM
11.2.0.3> REM AUTHOR
11.2.0.3> REM   carlos.sierra@oracle.com
11.2.0.3> REM
11.2.0.3> REM SCRIPT
11.2.0.3> REM   coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql
11.2.0.3> REM
11.2.0.3> REM DESCRIPTION
11.2.0.3> REM   This script is generated by coe_xfr_sql_profile.sql
11.2.0.3> REM   It contains the SQL*Plus commands to create a custom
11.2.0.3> REM   SQL Profile for SQL_ID 9b2hgfwj6bnu1 based on plan hash
11.2.0.3> REM   value 2160357371.
11.2.0.3> REM   The custom SQL Profile to be created by this script
11.2.0.3> REM   will affect plans for SQL commands with signature
11.2.0.3> REM   matching the one for SQL Text below.
11.2.0.3> REM   Review SQL Text and adjust accordingly.
11.2.0.3> REM
11.2.0.3> REM PARAMETERS
11.2.0.3> REM   None.
11.2.0.3> REM
11.2.0.3> REM EXAMPLE
11.2.0.3> REM   SQL> START coe_xfr_sql_profile_9b2hgfwj6bnu1_2160357371.sql;
11.2.0.3> REM
11.2.0.3> REM NOTES
11.2.0.3> REM   1. Should be run as SYSTEM or SYSDBA.
11.2.0.3> REM   2. User must have CREATE ANY SQL PROFILE privilege.
11.2.0.3> REM   3. SOURCE and TARGET systems can be the same or similar.
11.2.0.3> REM   4. To drop this custom SQL Profile after it has been created:
11.2.0.3> REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9b2hgfwj6bnu1_2160357371');
11.2.0.3> REM   5. Be aware that using DBMS_SQLTUNE requires a license
11.2.0.3> REM      for the Oracle Tuning Pack.
11.2.0.3> REM
11.2.0.3> WHENEVER SQLERROR EXIT SQL.SQLCODE;
11.2.0.3> REM
11.2.0.3> VAR signature NUMBER;
11.2.0.3> REM
11.2.0.3> DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6          select distinct v.inst_id
  7  , x.server_name
  8               , x.status as x_status
  9               , x.pid as x_pid
 10               , x.sid as x_sid
 11               , w2.sid as p_sid
 12               , v.osuser
 13               , v.schemaname
 14               , w1.event as child_wait
 15               , w2.event as parent_wait
 16          from  v$px_process x
 17              , v$lock l
 18              , gv$session v
 19              , v$session_wait w1
 20              , v$session_wait w2
 21          where x.sid <> l.sid(+)
 22          and   to_number (substr(x.server_name,2)) = l.id2(+)
 23          and   x.sid = w1.sid(+)
 24          and   l.sid = w2.sid(+)
 25          and   x.sid = v.sid(+)
 26          and   nvl(l.type,'PS') = 'PS'
 27  and   x.status like nvl('',x.status)
 28  and substr(x.server_name,2,1) != 'Z'
 29          order by p_sid, 1,2
 30  ]';
 31  h := SYS.SQLPROF_ATTR(
 32  q'[BEGIN_OUTLINE_DATA]',
 33  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 34  q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
 35  q'[ALL_ROWS]',
 36  q'[OUTLINE_LEAF(@"SEL$68B588A0")]',
 37  q'[MERGE(@"SEL$7")]',
 38  q'[OUTLINE_LEAF(@"SEL$71D7A081")]',
 39  q'[MERGE(@"SEL$C8360722")]',
 40  q'[OUTLINE_LEAF(@"SEL$13")]',
 41  q'[OUTLINE_LEAF(@"SEL$14")]',
 42  q'[OUTLINE_LEAF(@"SET$2")]',
 43  q'[OUTLINE_LEAF(@"SEL$42DFC41A")]',
 44  q'[MERGE(@"SEL$12")]',
 45  q'[OUTLINE_LEAF(@"SEL$15")]',
 46  q'[OUTLINE_LEAF(@"SEL$16")]',
 47  q'[OUTLINE_LEAF(@"SEL$17")]',
 48  q'[OUTLINE_LEAF(@"SEL$18")]',
 49  q'[OUTLINE_LEAF(@"SEL$19")]',
 50  q'[OUTLINE_LEAF(@"SEL$20")]',
 51  q'[OUTLINE_LEAF(@"SEL$21")]',
 52  q'[OUTLINE_LEAF(@"SEL$22")]',
 53  q'[OUTLINE_LEAF(@"SET$1")]',
 54  q'[OUTLINE_LEAF(@"SEL$2F35337B")]',
 55  q'[MERGE(@"SEL$0EE6DB63")]',
 56  q'[OUTLINE_LEAF(@"SEL$23")]',
 57  q'[OUTLINE_LEAF(@"SEL$DC3B0B0A")]',
 58  q'[MERGE(@"SEL$25")]',
 59  q'[OUTLINE_LEAF(@"SEL$FFAA604D")]',
 60  q'[MERGE(@"SEL$27")]',
 61  q'[OUTLINE_LEAF(@"SEL$1")]',
 62  q'[OUTLINE(@"SEL$6")]',
 63  q'[OUTLINE(@"SEL$7")]',
 64  q'[OUTLINE(@"SEL$2")]',
 65  q'[OUTLINE(@"SEL$C8360722")]',
 66  q'[MERGE(@"SEL$7286615E")]',
 67  q'[OUTLINE(@"SEL$13")]',
 68  q'[OUTLINE(@"SEL$14")]',
 69  q'[OUTLINE(@"SET$2")]',
 70  q'[OUTLINE(@"SEL$11")]',
 71  q'[OUTLINE(@"SEL$12")]',
 72  q'[OUTLINE(@"SEL$15")]',
 73  q'[OUTLINE(@"SEL$16")]',
 74  q'[OUTLINE(@"SEL$17")]',
 75  q'[OUTLINE(@"SEL$18")]',
 76  q'[OUTLINE(@"SEL$19")]',
 77  q'[OUTLINE(@"SEL$20")]',
 78  q'[OUTLINE(@"SEL$21")]',
 79  q'[OUTLINE(@"SEL$22")]',
 80  q'[OUTLINE(@"SET$1")]',
 81  q'[OUTLINE(@"SEL$8")]',
 82  q'[OUTLINE(@"SEL$0EE6DB63")]',
 83  q'[MERGE(@"SEL$10")]',
 84  q'[OUTLINE(@"SEL$23")]',
 85  q'[OUTLINE(@"SEL$24")]',
 86  q'[OUTLINE(@"SEL$25")]',
 87  q'[OUTLINE(@"SEL$26")]',
 88  q'[OUTLINE(@"SEL$27")]',
 89  q'[OUTLINE(@"SEL$1")]',
 90  q'[OUTLINE(@"SEL$3")]',
 91  q'[OUTLINE(@"SEL$7286615E")]',
 92  q'[MERGE(@"SEL$5")]',
 93  q'[OUTLINE(@"SEL$9")]',
 94  q'[OUTLINE(@"SEL$10")]',
 95  q'[OUTLINE(@"SEL$4")]',
 96  q'[OUTLINE(@"SEL$5")]',
 97  q'[NO_ACCESS(@"SEL$1" "X"@"SEL$1")]',
 98  q'[NO_ACCESS(@"SEL$1" "L"@"SEL$1")]',
 99  q'[NO_ACCESS(@"SEL$1" "V"@"SEL$1")]',
100  q'[NO_ACCESS(@"SEL$1" "W1"@"SEL$1")]',
101  q'[NO_ACCESS(@"SEL$1" "W2"@"SEL$1")]',
102  q'[LEADING(@"SEL$1" "X"@"SEL$1" "L"@"SEL$1" "V"@"SEL$1" "W1"@"SEL$1" "W2"@"SEL$1")]',
103  q'[USE_HASH(@"SEL$1" "L"@"SEL$1")]',
104  q'[USE_HASH(@"SEL$1" "V"@"SEL$1")]',
105  q'[USE_HASH(@"SEL$1" "W1"@"SEL$1")]',
106  q'[USE_HASH(@"SEL$1" "W2"@"SEL$1")]',
107  q'[FULL(@"SEL$71D7A081" "A"@"SEL$3")]',
108  q'[FULL(@"SEL$71D7A081" "X$KSUPR"@"SEL$5")]',
109  q'[NO_ACCESS(@"SEL$71D7A081" "C"@"SEL$3")]',
110  q'[LEADING(@"SEL$71D7A081" "A"@"SEL$3" "X$KSUPR"@"SEL$5" "C"@"SEL$3")]',
111  q'[USE_HASH(@"SEL$71D7A081" "X$KSUPR"@"SEL$5")]',
112  q'[USE_HASH(@"SEL$71D7A081" "C"@"SEL$3")]',
113  q'[FULL(@"SEL$2F35337B" "S"@"SEL$9")]',
114  q'[NO_ACCESS(@"SEL$2F35337B" "GV$_LOCK"@"SEL$10")]',
115  q'[FULL(@"SEL$2F35337B" "R"@"SEL$9")]',
116  q'[LEADING(@"SEL$2F35337B" "S"@"SEL$9" "GV$_LOCK"@"SEL$10" "R"@"SEL$9")]',
117  q'[USE_HASH(@"SEL$2F35337B" "GV$_LOCK"@"SEL$10")]',
118  q'[USE_NL(@"SEL$2F35337B" "R"@"SEL$9")]',
119  q'[FULL(@"SEL$23" "S"@"SEL$23")]',
120  q'[FULL(@"SEL$23" "E"@"SEL$23")]',
121  q'[LEADING(@"SEL$23" "S"@"SEL$23" "E"@"SEL$23")]',
122  q'[USE_NL(@"SEL$23" "E"@"SEL$23")]',
123  q'[FULL(@"SEL$DC3B0B0A" "S"@"SEL$25")]',
124  q'[FULL(@"SEL$DC3B0B0A" "E"@"SEL$25")]',
125  q'[LEADING(@"SEL$DC3B0B0A" "S"@"SEL$25" "E"@"SEL$25")]',
126  q'[USE_NL(@"SEL$DC3B0B0A" "E"@"SEL$25")]',
127  q'[FULL(@"SEL$FFAA604D" "S"@"SEL$27")]',
128  q'[FULL(@"SEL$FFAA604D" "E"@"SEL$27")]',
129  q'[LEADING(@"SEL$FFAA604D" "S"@"SEL$27" "E"@"SEL$27")]',
130  q'[USE_NL(@"SEL$FFAA604D" "E"@"SEL$27")]',
131  q'[FULL(@"SEL$22" "X$KTCXB"@"SEL$22")]',
132  q'[FULL(@"SEL$21" "X$KTSTUSG"@"SEL$21")]',
133  q'[FULL(@"SEL$20" "X$KTSTUSS"@"SEL$20")]',
134  q'[FULL(@"SEL$19" "X$KTSTUSC"@"SEL$19")]',
135  q'[FULL(@"SEL$18" "X$KTATL"@"SEL$18")]',
136  q'[FULL(@"SEL$17" "X$KTATRFSL"@"SEL$17")]',
137  q'[FULL(@"SEL$16" "X$KTATRFIL"@"SEL$16")]',
138  q'[FULL(@"SEL$15" "X$KTADM"@"SEL$15")]',
139  q'[NO_ACCESS(@"SEL$42DFC41A" "GV$_LOCK1"@"SEL$12")]',
140  q'[FULL(@"SEL$14" "X$KSQEQ"@"SEL$14")]',
141  q'[FULL(@"SEL$13" "X$KDNSSF"@"SEL$13")]',
142  q'[FULL(@"SEL$68B588A0" "S"@"SEL$7")]',
143  q'[FULL(@"SEL$68B588A0" "E"@"SEL$7")]',
144  q'[LEADING(@"SEL$68B588A0" "S"@"SEL$7" "E"@"SEL$7")]',
145  q'[USE_NL(@"SEL$68B588A0" "E"@"SEL$7")]',
146  q'[END_OUTLINE_DATA]');
147  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
148  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
149  sql_text    => sql_txt,
150  profile     => h,
151  name        => 'coe_9b2hgfwj6bnu1_2160357371',
152  description => 'coe 9b2hgfwj6bnu1 2160357371 '||:signature||'',
153  category    => 'DEFAULT',
154  validate    => TRUE,
155  replace     => TRUE,
156  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
157  END;
158  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.04
11.2.0.3> WHENEVER SQLERROR CONTINUE
11.2.0.3> SET ECHO OFF;
 
            SIGNATURE
---------------------
  6396643439679565602
 
 
... manual custom SQL Profile has been created
 
 
COE_XFR_SQL_PROFILE_9b2hgfwj6bnu1_2160357371 completed
11.2.0.3> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     35
      P001 AVAILABLE     36
      P002 AVAILABLE     37
      P003 AVAILABLE     38
      P004 AVAILABLE     39
      P005 AVAILABLE     40
      P006 AVAILABLE     41
      P007 AVAILABLE     42
      P008 AVAILABLE     43
      P009 AVAILABLE     44
      P010 AVAILABLE     45
      P011 AVAILABLE     46
      P012 AVAILABLE     47
      P013 AVAILABLE     48
      P014 AVAILABLE     49
      P015 AVAILABLE     50
      P016 AVAILABLE     51
      P017 AVAILABLE     52
      P018 AVAILABLE     53
      P019 AVAILABLE     54
      P020 AVAILABLE     55
      P021 AVAILABLE     56
      P022 AVAILABLE     57
      P023 AVAILABLE     58
      P024 AVAILABLE     59
      P025 AVAILABLE     60
      P026 AVAILABLE     61
      P027 AVAILABLE     62
      P028 AVAILABLE     63
      P029 AVAILABLE     64
      P030 AVAILABLE     65
      P031 AVAILABLE     66
 
 
32 rows selected.
 
Elapsed: 00:00:00.65
11.2.0.3> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 1
-------------------------------------
 select distinct v.inst_id              , x.server_name       ,
x.status as x_status       , x.pid as x_pid       , x.sid as x_sid
 , w2.sid as p_sid       , v.osuser       , v.schemaname       ,
w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      ,
v$session_wait w1      , v$session_wait w2  where x.sid <> l.sid(+)
and   to_number (substr(x.server_name,2)) = l.id2(+)  and   x.sid =
w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)
     and substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 1754705978
 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |       |       |     6 (100)|          |        |      |            |
|   1 |  SORT UNIQUE                      |                 |     1 |   219 |     5 (100)| 00:00:01 |        |      |            |
|*  2 |   HASH JOIN OUTER                 |                 |     1 |   219 |     4 (100)| 00:00:01 |        |      |            |
|*  3 |    HASH JOIN OUTER                |                 |     1 |   172 |     3 (100)| 00:00:01 |        |      |            |
|*  4 |     HASH JOIN OUTER               |                 |     1 |   125 |     3 (100)| 00:00:01 |        |      |            |
|*  5 |      FILTER                       |                 |       |       |            |          |        |      |            |
|*  6 |       HASH JOIN OUTER             |                 |     1 |    65 |     2 (100)| 00:00:01 |        |      |            |
|   7 |        VIEW                       | V$PX_PROCESS    |     1 |    36 |     1 (100)| 00:00:01 |        |      |            |
|*  8 |         HASH JOIN OUTER           |                 |     1 |   124 |     1 (100)| 00:00:01 |        |      |            |
|*  9 |          HASH JOIN                |                 |     1 |    97 |     1 (100)| 00:00:01 |        |      |            |
|* 10 |           FIXED TABLE FULL        | X$KXFPDP        |     1 |    44 |     0   (0)|          |        |      |            |
|* 11 |           FIXED TABLE FULL        | X$KSUPR         |     1 |    53 |     0   (0)|          |        |      |            |
|  12 |          VIEW                     | V$SESSION       |     1 |    27 |     0   (0)|          |        |      |            |
|  13 |           NESTED LOOPS            |                 |     1 |   105 |     0   (0)|          |        |      |            |
|  14 |            NESTED LOOPS           |                 |     1 |    79 |     0   (0)|          |        |      |            |
|* 15 |             FIXED TABLE FULL      | X$KSUSE         |     1 |    66 |     0   (0)|          |        |      |            |
|  16 |             FIXED TABLE FULL      | X$KSLED         |   100 |  1300 |     0   (0)|          |        |      |            |
|* 17 |            FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |        |      |            |
|  18 |        VIEW                       | V$LOCK          |     1 |    29 |     1 (100)| 00:00:01 |        |      |            |
|  19 |         NESTED LOOPS              |                 |     1 |    92 |     1 (100)| 00:00:01 |        |      |            |
|* 20 |          HASH JOIN                |                 |     1 |    57 |     1 (100)| 00:00:01 |        |      |            |
|* 21 |           FIXED TABLE FULL        | X$KSUSE         |     1 |    32 |     0   (0)|          |        |      |            |
|* 22 |           VIEW                    | GV$_LOCK        |    10 |   250 |     0   (0)|          |        |      |            |
|  23 |            UNION-ALL              |                 |       |       |            |          |        |      |            |
|* 24 |             VIEW                  | GV$_LOCK1       |     2 |   178 |     0   (0)|          |        |      |            |
|  25 |              UNION-ALL            |                 |       |       |            |          |        |      |            |
|* 26 |               FIXED TABLE FULL    | X$KDNSSF        |     1 |   102 |     0   (0)|          |        |      |            |
|* 27 |               FIXED TABLE FULL    | X$KSQEQ         |     1 |   102 |     0   (0)|          |        |      |            |
|* 28 |             FIXED TABLE FULL      | X$KTADM         |     1 |   102 |     0   (0)|          |        |      |            |
|* 29 |             FIXED TABLE FULL      | X$KTATRFIL      |     1 |   102 |     0   (0)|          |        |      |            |
|* 30 |             FIXED TABLE FULL      | X$KTATRFSL      |     1 |   102 |     0   (0)|          |        |      |            |
|* 31 |             FIXED TABLE FULL      | X$KTATL         |     1 |   102 |     0   (0)|          |        |      |            |
|* 32 |             FIXED TABLE FULL      | X$KTSTUSC       |     1 |   102 |     0   (0)|          |        |      |            |
|* 33 |             FIXED TABLE FULL      | X$KTSTUSS       |     1 |   102 |     0   (0)|          |        |      |            |
|* 34 |             FIXED TABLE FULL      | X$KTSTUSG       |     1 |   102 |     0   (0)|          |        |      |            |
|* 35 |             FIXED TABLE FULL      | X$KTCXB         |     1 |   102 |     0   (0)|          |        |      |            |
|* 36 |          FIXED TABLE FULL         | X$KSQRS         |     1 |    35 |     0   (0)|          |        |      |            |
|  37 |      PX COORDINATOR               |                 |     1 |    60 |     0   (0)|          |        |      |            |
|  38 |       PX SEND QC (RANDOM)         | :TQ10000        |     1 |   125 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|  39 |        VIEW                       | GV$SESSION      |       |       |            |          |  Q1,00 | PCWP |            |
|  40 |         NESTED LOOPS              |                 |     1 |   125 |     0   (0)|          |  Q1,00 | PCWP |            |
|  41 |          NESTED LOOPS             |                 |    25 |  2475 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 42 |           FIXED TABLE FULL        | X$KSUSE         |     1 |    86 |     0   (0)|          |  Q1,00 | PCWP |            |
|  43 |           FIXED TABLE FULL        | X$KSLED         |   100 |  1300 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 44 |          FIXED TABLE FIXED INDEX  | X$KSLWT (ind:1) |     1 |    26 |     0   (0)|          |  Q1,00 | PCWP |            |
|  45 |     VIEW                          | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  46 |      NESTED LOOPS                 |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 47 |       FIXED TABLE FULL            | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 48 |       FIXED TABLE FIXED INDEX     | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
|  49 |    VIEW                           | V$SESSION_WAIT  |     1 |    47 |     0   (0)|          |        |      |            |
|  50 |     NESTED LOOPS                  |                 |     1 |    86 |     0   (0)|          |        |      |            |
|* 51 |      FIXED TABLE FULL             | X$KSLWT         |     1 |    39 |     0   (0)|          |        |      |            |
|* 52 |      FIXED TABLE FIXED INDEX      | X$KSLED (ind:2) |     1 |    47 |     0   (0)|          |        |      |            |
----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("L"."SID"="W2"."SID")
   3 - access("X"."SID"="W1"."SID")
   4 - access("X"."SID"="V"."SID")
   5 - filter(NVL("L"."TYPE",'PS')='PS')
   6 - access("L"."ID2"=TO_NUMBER(SUBSTR("X"."SERVER_NAME",2)))
       filter("X"."SID"<>"L"."SID")
   8 - access("A"."KXFPDPSPID"="C"."PROCESS")
   9 - access("A"."KXFPDPSPID"="KSUPRPID")
  10 - filter(("A"."INST_ID"=USERENV('INSTANCE') AND BITAND("KXFPDPFLG",8)<>0 AND SUBSTR("A"."KXFPDPNAM",2,1)<>'Z' AND
              DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN USE','AVAILABLE') LIKE NVL('',DECODE(BITAND("A"."KXFPDPFLG",16),0,'IN
              USE','AVAILABLE'))))
  11 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
  15 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  17 - filter(("S"."INDX"="W"."KSLWTSID" AND "W"."KSLWTEVT"="E"."INDX"))
  20 - access("SADDR"="S"."ADDR")
  21 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  22 - filter("INST_ID"=USERENV('INSTANCE'))
  24 - filter("INST_ID"=USERENV('INSTANCE'))
  26 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  27 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  28 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  29 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  30 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  31 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  32 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  33 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  34 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0))
  35 - filter((("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0))
  36 - filter(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR"))
  42 - filter((BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
  44 - filter(("S"."INDX"="W"."KSLWTSID" AND "W"."KSLWTEVT"="E"."INDX"))
  47 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  48 - filter("S"."KSLWTEVT"="E"."INDX")
  51 - filter("S"."INST_ID"=USERENV('INSTANCE'))
  52 - filter("S"."KSLWTEVT"="E"."INDX")
 
Note
-----
   - statement not queuable: gv$ statement
   - SQL profile coe_9b2hgfwj6bnu1_2160357371 used for this statement
 
 
117 rows selected.
 
Elapsed: 00:00:00.04

So as you can see, the statement now executes in under a second (0.65) and uses the original plan as encouraged by the newly created SQL Profile. You can also see in the note section of xplan output that the SQL Profile was used. This all took only a couple of minutes by the way and allowed me to continue my investigation on the parallel query issue I was interested in without too much distraction due to the sidetrack.

But that’s not the end of the story. After I was finished messing with the PX stuff, I wanted to know how and why the plan had changed in the first place and so I looked into it further. The first thing I did was to look at the Report SQL Monitor output to see where the bad plan was spending all it’s time.

11.2.0.3> @sm
Enter value for sid: 
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for sql_exec_id: 
 
       SID    SERIAL# SQL_ID        SQL_EXEC_ID SQL_EXEC_START       PLAN_HASH_VALUE      ETIME BUFFER_GETS DISK_READS
---------- ---------- ------------- ----------- -------------------- --------------- ---------- ----------- ----------
       397        955                  16777230 14-Jan-13 13:22:42        2444082350  22.895054           0       70579
 
11.2.0.3> @rsm
Enter value for sid: 397
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for sql_exec_id: 16777230
 
REPORT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
select distinct v.inst_id , x.server_name , x.status as x_status , x.pid as x_pid , x.sid as x_sid , w2.sid as p_sid , v.osuser , v.schemaname , w1.event as child_wait , w2.event as parent_wait from v$px_process x , v$lock l , gv$session v , v$session_wait w1 , v$session_wait w2 where x.sid <> l.sid(+) and to_number (substr(x.server_name,2)) = l.id2(+) and x.sid = w1.sid(+) an
d l.sid = w2.sid(+) and x.sid = v.sid(+) and nvl(l.type,'PS') = 'PS' and x.status like nvl('',x.status) and
substr(x.server_name,2,1) != 'Z' order by p_sid, 1,2
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (397:955)
 SQL ID              :  9b2hgfwj6bnu1
 SQL Execution ID    :  16777230
 Execution Started   :  01/14/2013 13:22:42
 First Refresh Time  :  01/14/2013 13:22:48
 Last Refresh Time   :  01/14/2013 13:23:05
 Duration            :  23s
 Module/Action       :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@enkdb01.enkitec.com (TNS V1-V3)
 Fetch Calls         :  4
 
Global Stats
=================================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Read | Read  | Write | Write |  Cell   |
| Time(s) | Time(s) | Waits(s) | Calls | Reqs | Bytes | Reqs  | Bytes | Offload |
=================================================================================
|      23 |      15 |     8.39 |     4 | 7057 | 827MB |  7056 | 827MB | -49.25% |
=================================================================================
 
Parallel Execution Details (DOP=2 , Servers Allocated=2)
=======================================================================================================================================
|      Name      | Type | Server# | Elapsed |   Cpu   |    IO    | Read | Read  | Write | Write |  Cell   |        Wait Events        |
|                |      |         | Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Reqs  | Bytes | Offload |        (sample #)         |
=======================================================================================================================================
| PX Coordinator | QC   |         |      23 |      15 |     8.39 | 7057 | 827MB |  7056 | 827MB | -49.25% | direct path read temp (7) |
=======================================================================================================================================
 
Instance Drill-Down
================================================================================================================================
| Instance | Process Names | Elapsed |   Cpu   |    IO    | Read | Read  | Write | Write |  Cell   | Wait Events               |
|          |               | Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Reqs  | Bytes | Offload |                           |
================================================================================================================================
|    1     | QC            |      23 |      15 |     8.39 | 7057 | 827MB |  7056 | 827MB | -49.25% | direct path read temp (7) |
================================================================================================================================
 
SQL Plan Monitoring Details (Plan Hash Value=2444082350)
==============================================================================================================================================================================================================
| Id |              Operation               |      Name       |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write |  Mem  | Temp  | Activity |      Activity Detail      |
|    |                                      |                 | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes | (Max) | (Max) |   (%)    |        (# samples)      |
==============================================================================================================================================================================================================
|  0 | SELECT STATEMENT                     |                 |         |      |         1 |    +23 |     1 |       32 |      |       |       |       |       |       |          |                         |
|  1 |   SORT UNIQUE                        |                 |       1 |    5 |         1 |    +23 |     1 |       32 |      |       |       |       |  4096 |       |          |                         |
|  2 |    HASH JOIN OUTER                   |                 |       1 |    4 |         1 |    +23 |     1 |       32 |      |       |       |       |  409K |       |          |                         |
|  3 |     HASH JOIN OUTER                  |                 |       1 |    3 |         1 |    +23 |     1 |       32 |      |       |       |       |  422K |       |          |                         |
|  4 |      HASH JOIN OUTER                 |                 |       1 |    3 |         1 |    +23 |     1 |       32 |      |       |       |       |  443K |       |          |                         |
|  5 |       FILTER                         |                 |         |      |         1 |    +23 |     1 |       32 |      |       |       |       |       |       |          |                         |
|  6 |        HASH JOIN OUTER               |                 |       1 |    2 |        18 |     +6 |     1 |       32 |      |       |       |       |    1M |       |          |                         |
|  7 |         VIEW                         | V$PX_PROCESS    |       1 |    1 |         1 |     +6 |     1 |       32 |      |       |       |       |       |       |          |                         |
|  8 |          HASH JOIN OUTER             |                 |       1 |    1 |         1 |     +6 |     1 |       32 |      |       |       |       |    1M |       |          |                         |
|  9 |           HASH JOIN                  |                 |       1 |    1 |         1 |     +6 |     1 |       32 |      |       |       |       |    1M |       |          |                         |
| 10 |            FIXED TABLE FULL          | X$KXFPDP        |       1 |      |         1 |     +6 |     1 |       32 |      |       |       |       |       |       |          |                         |
| 11 |            FIXED TABLE FULL          | X$KSUPR         |       1 |      |         1 |     +6 |     1 |       89 |      |       |       |       |       |       |          |                         |
| 12 |           VIEW                       | V$SESSION       |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 13 |            NESTED LOOPS              |                 |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 14 |             NESTED LOOPS             |                 |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 15 |              FIXED TABLE FULL        | X$KSUSE         |       1 |      |         1 |     +6 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 16 |              FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) |       1 |      |         1 |     +6 |    55 |       55 |      |       |       |       |       |       |          |                         |
| 17 |             FIXED TABLE FIXED INDEX  | X$KSLED (ind:2) |       1 |      |         1 |     +6 |    55 |       55 |      |       |       |       |       |       |          |                         |
| 18 |         VIEW                         | V$LOCK          |       1 |    1 |         4 |    +20 |     1 |       34 |      |       |       |       |       |       |          |                         |
| 19 |          HASH JOIN                   |                 |       1 |    1 |        24 |     +0 |     1 |       34 | 7057 | 827MB |  7056 | 827MB |  922M |  925M |   100.00 | Cpu (16)                |
|    |                                      |                 |         |      |           |        |       |          |      |       |       |       |       |       |          | direct path read temp (7) |
| 20 |           MERGE JOIN CARTESIAN       |                 |     100 |      |         9 |     +6 |     1 |      14M |      |       |       |       |       |       |          |                         |
| 21 |            FIXED TABLE FULL          | X$KSUSE         |       1 |      |         9 |     +6 |     1 |     1568 |      |       |       |       |       |       |          |                         |
| 22 |            BUFFER SORT               |                 |     100 |      |         9 |     +6 |  1568 |      14M |      |       |       |       |  434K |       |          |                         |
| 23 |             FIXED TABLE FULL         | X$KSQRS         |     100 |      |         1 |     +6 |     1 |     8928 |      |       |       |       |       |       |          |                         |
| 24 |           VIEW                       | GV$_LOCK        |      10 |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 25 |            UNION-ALL                 |                 |         |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 26 |             FILTER                   |                 |         |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 27 |              VIEW                    | GV$_LOCK1       |       2 |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 28 |               UNION-ALL              |                 |         |      |         1 |    +20 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 29 |                FIXED TABLE FULL      | X$KDNSSF        |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 30 |                FIXED TABLE FULL      | X$KSQEQ         |       1 |      |         7 |    +14 |     1 |       35 |      |       |       |       |       |       |          |                         |
| 31 |             FIXED TABLE FULL         | X$KTADM         |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 32 |             FIXED TABLE FULL         | X$KTATRFIL      |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 33 |             FIXED TABLE FULL         | X$KTATRFSL      |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 34 |             FIXED TABLE FULL         | X$KTATL         |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 35 |             FIXED TABLE FULL         | X$KTSTUSC       |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 36 |             FIXED TABLE FULL         | X$KTSTUSS       |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 37 |             FIXED TABLE FULL         | X$KTSTUSG       |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 38 |             FIXED TABLE FULL         | X$KTCXB         |       1 |      |           |        |     1 |          |      |       |       |       |       |       |          |                         |
| 39 |       PX COORDINATOR                 |                 |       1 |      |         1 |    +23 |     1 |      109 |      |       |       |       |       |       |          |                         |
| 40 |        PX SEND QC (RANDOM)           | :TQ10000        |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 41 |         VIEW                         | GV$SESSION      |         |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 42 |          NESTED LOOPS                |                 |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 43 |           NESTED LOOPS               |                 |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 44 |            FIXED TABLE FULL          | X$KSUSE         |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 45 |            FIXED TABLE FIXED INDEX   | X$KSLWT (ind:1) |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 46 |           FIXED TABLE FIXED INDEX    | X$KSLED (ind:2) |       1 |      |           |        |       |          |      |       |       |       |       |       |          |                         |
| 47 |      VIEW                            | V$SESSION_WAIT  |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 48 |       NESTED LOOPS                   |                 |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 49 |        FIXED TABLE FULL              | X$KSLWT         |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 50 |        FIXED TABLE FIXED INDEX       | X$KSLED (ind:2) |       1 |      |         1 |    +23 |    55 |       55 |      |       |       |       |       |       |          |                         |
| 51 |     VIEW                             | V$SESSION_WAIT  |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 52 |      NESTED LOOPS                    |                 |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 53 |       FIXED TABLE FULL               | X$KSLWT         |       1 |      |         1 |    +23 |     1 |       55 |      |       |       |       |       |       |          |                         |
| 54 |       FIXED TABLE FIXED INDEX        | X$KSLED (ind:2) |       1 |      |         1 |    +23 |    55 |       55 |      |       |       |       |       |       |          |                         |
==============================================================================================================================================================================================================

If you scroll to the right and look at the Activity % column you’ll see that 100% of the time was spent on step 19 (a Hash Join) and if you look at lines 20-23 you’ll see the following:

| 19 |          HASH JOIN                   |                 |       1 |    1 |        24 |     +0 |     1 |       34 | 7057 | 827MB |  7056 | 827MB |  922M |  925M |   100.00 | Cpu (16)                |
|    |                                      |                 |         |      |           |        |       |          |      |       |       |       |       |       |          | direct path read temp (7) |
| 20 |           MERGE JOIN CARTESIAN       |                 |     100 |      |         9 |     +6 |     1 |      14M |      |       |       |       |       |       |          |                         |
| 21 |            FIXED TABLE FULL          | X$KSUSE         |       1 |      |         9 |     +6 |     1 |     1568 |      |       |       |       |       |       |          |                         |
| 22 |            BUFFER SORT               |                 |     100 |      |         9 |     +6 |  1568 |      14M |      |       |       |       |  434K |       |          |                         |
| 23 |             FIXED TABLE FULL         | X$KSQRS         |     100 |      |         1 |     +6 |     1 |     8928

Note that the cardinality estimates are way off for X$KSQRS (100 vs. 8928) and X$KSUSE (1 vs. 1568). This is what tricks the optimizer into doing a MERGE JOIN CARTESIAN which basically joins every row from one table with every row from the other – resulting in 14M rows instead of the 100 the optimizer thought it would get.

So what did the 10g version do instead? If you go back and look closely at the plan you will see that it did a Nested Loop instead of the Merge Join Cartesian. Another trick that can be used to help locate the differences in plans is to do a diff on the set of hints that Oracle thinks are necessary to reproduce each plan. This could be done by spooling a file with the hints as generated by my sql_hints.sql script or by using a new script I just wrote (sql_hints_diff.sql) to take one set of outline hints and do a MINUS on the other set. The script produces easier to read output but I’ll show the output of both techniques here.

11.2.0.3> !diff 11_hints.dat 10_hints.dat
5c5
< OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
---
> OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
64a65
> USE_HASH(@"SEL$1" "V"@"SEL$1")
72d72
< FULL(@"SEL$2F35337B" "R"@"SEL$9")
74,75c74,75
< LEADING(@"SEL$2F35337B" "S"@"SEL$9" "R"@"SEL$9" "GV$_LOCK"@"SEL$10")
< USE_MERGE_CARTESIAN(@"SEL$2F35337B" "R"@"SEL$9")
---
> FULL(@"SEL$2F35337B" "R"@"SEL$9")
> LEADING(@"SEL$2F35337B" "S"@"SEL$9" "GV$_LOCK"@"SEL$10" "R"@"SEL$9")
76a77
> USE_NL(@"SEL$2F35337B" "R"@"SEL$9")
78d78
< FULL(@"SEL$23" "W"@"SEL$23")
80,81c80,81
< LEADING(@"SEL$23" "S"@"SEL$23" "W"@"SEL$23" "E"@"SEL$23")
< USE_NL(@"SEL$23" "W"@"SEL$23")
---
> FULL(@"SEL$23" "W"@"SEL$23")
> LEADING(@"SEL$23" "S"@"SEL$23" "E"@"SEL$23" "W"@"SEL$23")
82a83
> USE_NL(@"SEL$23" "W"@"SEL$23")
103d103
< FULL(@"SEL$68B588A0" "W"@"SEL$7")
105,106c105,106
< LEADING(@"SEL$68B588A0" "S"@"SEL$7" "W"@"SEL$7" "E"@"SEL$7")
< USE_NL(@"SEL$68B588A0" "W"@"SEL$7")
---
> FULL(@"SEL$68B588A0" "W"@"SEL$7")
> LEADING(@"SEL$68B588A0" "S"@"SEL$7" "E"@"SEL$7" "W"@"SEL$7")
107a108
> USE_NL(@"SEL$68B588A0" "W"@"SEL$7")
 
11.2.0.3> @sql_hints_diff.sql
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for child_no: 0
Enter value for sql_id_2: 9b2hgfwj6bnu1
Enter value for child_no_2: 1
 
OUTLINE_HINTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LEADING(@"SEL$23" "S"@"SEL$23" "W"@"SEL$23" "E"@"SEL$23")
LEADING(@"SEL$2F35337B" "S"@"SEL$9" "R"@"SEL$9" "GV$_LOCK"@"SEL$10")
LEADING(@"SEL$68B588A0" "S"@"SEL$7" "W"@"SEL$7" "E"@"SEL$7")
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
USE_MERGE_CARTESIAN(@"SEL$2F35337B" "R"@"SEL$9")
 
5 rows selected.
 
Elapsed: 00:00:01.18
11.2.0.3> /
Enter value for sql_id: 9b2hgfwj6bnu1
Enter value for child_no: 1
Enter value for sql_id_2: 9b2hgfwj6bnu1
Enter value for child_no_2: 0
 
OUTLINE_HINTS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LEADING(@"SEL$23" "S"@"SEL$23" "E"@"SEL$23" "W"@"SEL$23")
LEADING(@"SEL$2F35337B" "S"@"SEL$9" "GV$_LOCK"@"SEL$10" "R"@"SEL$9")
LEADING(@"SEL$68B588A0" "S"@"SEL$7" "E"@"SEL$7" "W"@"SEL$7")
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
USE_NL(@"SEL$2F35337B" "R"@"SEL$9")
 
5 rows selected.
 
Elapsed: 00:00:00.96

In the script output it’s pretty easy to see that the ordering of the tables was slightly different (the LEADING hints) but the major difference is that the 10g version chose a Nested Loop, USE_NL(@”SEL$2F35337B” “R”@”SEL$9″), on R which is X$KSQRS (note you can use XPLAN with the aliases option to determine what R is). While the 11g version did the Merge Join Cartesian, USE_MERGE_CARTESIAN(@”SEL$2F35337B” “R”@”SEL$9″). So you should be able to “fix” the query by using the USE_NL hint either directly embedded in the statement or via a SQL Profile or SQL Patch. I’ve tried all three approaches and they work, but I will leave that as an exercise for the reader since this post is getting so long.

But I’m still not satisfied because I haven’t solved the underlying problem. I’d like for Oracle to get it right without my intervention if possible. So why was the cardinality estimate off in the first place? Bad stats? All the objects in the query are V$ views which sit on X$ tables. Are there bad stats on the X$ tables? The answer is no, actually there are no stats on the X$ tables. This situation is common and can be fixed by gathering fixed object stats. If you’re not familiar with them you may want to check out Maria Colgan’s article about gathering fixed object stats.

11.2.0.3> @check_fixed_object_stats
Enter value for name: 
 
no rows selected
 
11.2.0.3> !cat gather_fixed_obj_stats.sql
-- EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
 
11.2.0.3> @gather_fixed_obj_stats.sql
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:02:50.47
11.2.0.3> @check_fixed_object_stats
Enter value for name: X$KS%
 
NAME                              OBJECT_ID ANALYZETI  LOGICALREAD       ROWCNT       BLKCNT       EMPCNT       AVGSPC       CHNCNT       AVGRLN   AVGSPC_FLB       FLBCNT   SAMPLESIZE        FLAGS
------------------------------ ------------ --------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
X$KSDAF                          4294952775 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSDAFT                         4294952776 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSLLTR                         4294950993 14-JAN-13                       553            0            0            0            0          138            0            0          553            0
X$KSLHOT                         4294952169 14-JAN-13                        10            0            0            0            0           19            0            0           10            0
X$KSLLCLASS                      4294951813 14-JAN-13                         8            0            0            0            0           47            0            0            8            0
X$KSLECLASS                      4294951830 14-JAN-13                        97            0            0            0            0           25            0            0           97            0
X$KSLEMAP                        4294951831 14-JAN-13                      1152            0            0            0            0           20            0            0         1152            0
X$KSLLD                          4294950994 14-JAN-13                       553            0            0            0            0           58            0            0          553            0
X$KSLED                          4294951094 14-JAN-13                      1152            0            0            0            0           79            0            0         1152            0
X$KSLCS                          4294952078 14-JAN-13                     20384            0            0            0            0           46            0            0        20384            0
X$KSLSCS                         4294952079 14-JAN-13                        13            0            0            0            0           61            0            0           13            0
X$KSLES                          4294951095 14-JAN-13                    641312            0            0            0            0           33            0            0       641312            0
X$KSLSESHIST                     4294951973 14-JAN-13                      1419            0            0            0            0           42            0            0         1419            0
X$KSLEI                          4294951102 14-JAN-13                      1152            0            0            0            0           41            0            0         1152            0
X$KSLLW                          4294951183 14-JAN-13                      5540            0            0            0            0           36            0            0         5540            0
X$KSLPO                          4294951184 14-JAN-13                       389            0            0            0            0           40            0            0          389            0
X$KSLWSC                         4294951185 14-JAN-13                      5540            0            0            0            0           45            0            0         5540            0
X$KSLWH                          4294952866 14-JAN-13                       550            0            0            0            0          113            0            0          550            0
X$KSLWT                          4294952867 14-JAN-13                        56            0            0            0            0          108            0            0           56            0
X$KSQEQTYP                       4294951983 14-JAN-13                       206            0            0            0            0           96            0            0          206            0
X$KSQRS                          4294950999 14-JAN-13                      8928            0            0            0            0           27            0            0         8928            0
X$KSQDN                          4294951001 14-JAN-13                         1            0            0            0            0           33            0            0            1            0
X$KSQST                          4294951085 14-JAN-13                       305            0            0            0            0          101            0            0          305            0
X$KSUINSTSTAT                    4294953015 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSUSE                          4294951004 14-JAN-13                      1568            0            0            0            0          218            0            0         1568            0
X$KSUSEX                         4294951428 14-JAN-13                      1568            0            0            0            0           28            0            0         1568            0
X$KSUPR                          4294951005 14-JAN-13                      1024            0            0            0            0          162            0            0         1024            0
X$KSUPRLAT                       4294951006 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSURLMT                        4294951396 14-JAN-13                        27            0            0            0            0           50            0            0           27            0
X$KSUSD                          4294951007 14-JAN-13                       638            0            0            0            0           55            0            0          638            0
X$KSUSGSTA                       4294951008 14-JAN-13                       638            0            0            0            0           62            0            0          638            0
X$KSUTM                          4294951067 14-JAN-13                         1            0            0            0            0           21            0            0            1            0
X$KSUSGIF                        4294951930 14-JAN-13                         1            0            0            0            0           18            0            0            1            0
X$KSUSESTA                       4294951009 14-JAN-13                   1019200            0            0            0            0           32            0            0      1019200            0
X$KSUMYSTA                       4294951106 14-JAN-13                       638            0            0            0            0           37            0            0          638            0
X$KSUSIO                         4294951079 14-JAN-13                      1568            0            0            0            0           37            0            0         1568            0
X$KSUSECST                       4294951096 14-JAN-13                      1568            0            0            0            0           52            0            0         1568            0
X$KSULOP                         4294951244 14-JAN-13                       121            0            0            0            0          294            0            0          121            0
X$KSUPGP                         4294951577 14-JAN-13                        89            0            0            0            0           26            0            0           89            0
X$KSUPGS                         4294951578 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSURU                          4294951010 14-JAN-13                     15680            0            0            0            0           23            0            0        15680            0
X$KSUPL                          4294951011 14-JAN-13                        10            0            0            0            0           24            0            0           10            0
X$KSUCF                          4294951012 14-JAN-13                        10            0            0            0            0           19            0            0           10            0
X$KSULL                          4294951082 14-JAN-13                         1            0            0            0            0           44            0            0            1            0
X$KSUCPUSTAT                     4294952112 14-JAN-13                        11            0            0            0            0           81            0            0           11            0
X$KSUVMSTAT                      4294952113 14-JAN-13                         3            0            0            0            0           83            0            0            3            0
X$KSUNETSTAT                     4294953033 14-JAN-13                         8            0            0            0            0           79            0            0            8            0
X$KSUSM                          4294952182 14-JAN-13                      1568            0            0            0            0           67            0            0         1568            0
X$KSIRESTYP                      4294951982 14-JAN-13                       206            0            0            0            0          104            0            0          206            0
X$KSIMSI                         4294951178 14-JAN-13                         2            0            0            0            0           47            0            0            2            0
X$KSIMAV                         4294951179 14-JAN-13                         6            0            0            0            0           38            0            0            6            0
X$KSIMAT                         4294951180 14-JAN-13                         5            0            0            0            0           39            0            0            5            0
X$KSMSD                          4294950995 14-JAN-13                         4            0            0            0            0           36            0            0            4            0
X$KSMSS                          4294950997 14-JAN-13                      1782            0            0            0            0           41            0            0         1782            0
X$KSMNS                          4294951493 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMNIM                         4294951494 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMLS                          4294951383 14-JAN-13                        18            0            0            0            0           36            0            0           18            0
X$KSMFS                          4294951384 14-JAN-13                         4            0            0            0            0           33            0            0            4            0
X$KSMJS                          4294951595 14-JAN-13                        10            0            0            0            0           33            0            0           10            0
X$KSMSTRS                        4294952000 14-JAN-13                         4            0            0            0            0           33            0            0            4            0
X$KSMFSV                         4294951146 14-JAN-13                     13340            0            0            0            0           45            0            0        13340            0
X$KSMLRU                         4294951099 14-JAN-13                        40            0            0            0            0           44            0            0           40            0
X$KSMSPR                         4294951170 14-JAN-13                       149            0            0            0            0           55            0            0          149            0
X$KSMPP                          4294951191 14-JAN-13                      6556            0            0            0            0           50            0            0         6556            0
X$KSMUP                          4294951192 14-JAN-13                      2501            0            0            0            0           54            0            0         2501            0
X$KSMHP                          4294951193 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMJCH                         4294951609 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMSST                         4294952688 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMSP_DSNEW                    4294951768 14-JAN-13                         1            0            0            0            0           26            0            0            1            0
X$KSMSP_NWEX                     4294951769 14-JAN-13                        65            0            0            0            0           63            0            0           65            0
X$KSMSGMEM                       4294952475 14-JAN-13                        11            0            0            0            0           45            0            0           11            0
X$KSMPGST                        4294952646 14-JAN-13                      6144            0            0            0            0           41            0            0         6144            0
X$KSMPGDST                       4294952673 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSMPGDP                        4294952674 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSPPI                          4294950998 14-JAN-13                      2752            0            0            0            0           98            0            0         2752            0
X$KSPPCV                         4294951198 14-JAN-13                      2752            0            0            0            0           32            0            0         2752            0
X$KSPPSV                         4294951199 14-JAN-13                      2752            0            0            0            0           32            0            0         2752            0
X$KSPPCV2                        4294951587 14-JAN-13                      2756            0            0            0            0           45            0            0         2756            0
X$KSPPSV2                        4294951588 14-JAN-13                      2756            0            0            0            0           50            0            0         2756            0
X$KSPSPFILE                      4294951746 14-JAN-13                      2766            0            0            0            0           60            0            0         2766            0
X$KSPSPFH                        4294951933 14-JAN-13                         1            0            0            0            0           36            0            0            1            0
X$KSPVLD_VALUES                  4294952694 14-JAN-13                       597            0            0            0            0           58            0            0          597            0
X$KSPPO                          4294951486 14-JAN-13                       131            0            0            0            0           42            0            0          131            0
X$KSBDP                          4294950990 14-JAN-13                       296            0            0            0            0           35            0            0          296            0
X$KSBDD                          4294950991 14-JAN-13                       296            0            0            0            0           45            0            0          296            0
X$KSBFT                          4294952690 14-JAN-13                        74            0            0            0            0           32            0            0           74            0
X$KSBSRVDT                       4294953196 14-JAN-13                        38            0            0            0            0           54            0            0           38            0
X$KSBTABACT                      4294952582 14-JAN-13                      2119            0            0            0            0           52            0            0         2119            0
X$KSQEQ                          4294951000 14-JAN-13                     20016            0            0            0            0           44            0            0        20016            0
X$KSTEX                          4294951145 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSULV                          4294951130 14-JAN-13                       499            0            0            0            0           39            0            0          499            0
X$KSUXSINST                      4294951046 14-JAN-13                         1            0            0            0            0           95            0            0            1            0
X$KSUSECON                       4294951158 14-JAN-13                      1618            0            0            0            0           45            0            0         1618            0
X$KSRPCIOS                       4294952737 14-JAN-13                         2            0            0            0            0           33            0            0            2            0
X$KSFDFTYP                       4294952493 14-JAN-13                        29            0            0            0            0           29            0            0           29            0
X$KSFDSTCG                       4294952842 14-JAN-13                        96            0            0            0            0           69            0            0           96            0
X$KSFDSTCMP                      4294952843 14-JAN-13                      1386            0            0            0            0           65            0            0         1386            0
X$KSFDSTFILE                     4294952844 14-JAN-13                        45            0            0            0            0           68            0            0           45            0
X$KSFDSTBLK                      4294953234 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFDSTHIST                     4294952845 14-JAN-13                        11            0            0            0            0           26            0            0           11            0
X$KSFDSTTHIST                    4294952846 14-JAN-13                         1            0            0            0            0           25            0            0            1            0
X$KSFQP                          4294951563 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFQDVNT                       4294951380 14-JAN-13                         1            0            0            0            0           23            0            0            1            0
X$KSFVQST                        4294951441 14-JAN-13                       192            0            0            0            0           26            0            0          192            0
X$KSFVSTA                        4294951427 14-JAN-13                        32            0            0            0            0           25            0            0           32            0
X$KSFVSL                         4294951426 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSKPLW                         4294952188 14-JAN-13                        13            0            0            0            0           70            0            0           13            0
X$KSRCDES                        4294951579 14-JAN-13                       240            0            0            0            0           44            0            0          240            0
X$KSRCCTX                        4294951580 14-JAN-13                       240            0            0            0            0           38            0            0          240            0
X$KSRMSGDES                      4294951581 14-JAN-13                        80            0            0            0            0           55            0            0           80            0
X$KSRMPCTX                       4294951582 14-JAN-13                        80            0            0            0            0           25            0            0           80            0
X$KSRCHDL                        4294951583 14-JAN-13                        94            0            0            0            0           72            0            0           94            0
X$KSRMSGO                        4294951584 14-JAN-13                        10            0            0            0            0           57            0            0           10            0
X$KSXPPING                       4294952378 14-JAN-13                         2            0            0            0            0           77            0            0            2            0
X$KSXPCLIENT                     4294952380 14-JAN-13                        11            0            0            0            0           31            0            0           11            0
X$KSXPIF                         4294952379 14-JAN-13                         8            0            0            0            0          101            0            0            8            0
X$KSXPIA                         4294952165 14-JAN-13                         1            0            0            0            0           38            0            0            1            0
X$KSMDD                          4294951622 14-JAN-13                       407            0            0            0            0           74            0            0          407            0
X$KSMDUT1                        4294952607 14-JAN-13                       320            0            0            0            0           22            0            0          320            0
X$KSMGE                          4294951733 14-JAN-13                       505            0            0            0            0           64            0            0          505            0
X$KSXRMSG                        4294951728 14-JAN-13                       100            0            0            0            0           74            0            0          100            0
X$KSXRREPQ                       4294951729 14-JAN-13                       200            0            0            0            0           69            0            0          200            0
X$KSXRCONQ                       4294951730 14-JAN-13                       112            0            0            0            0           43            0            0          112            0
X$KSXRCH                         4294951731 14-JAN-13                        54            0            0            0            0           37            0            0           54            0
X$KSXRSG                         4294951732 14-JAN-13                         1            0            0            0            0           74            0            0            1            0
X$KSFMLIB                        4294951910 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMIOST                       4294951817 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMFILE                       4294951818 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMFILEEXT                    4294951819 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMSUBELEM                    4294951820 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMELEM                       4294951821 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMEXTELEM                    4294951822 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSFMCOMPL                      4294951823 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSOLSSTAT                      4294951842 14-JAN-13                        21            0            0            0            0           38            0            0           21            0
X$KSOLSFTS                       4294951841 14-JAN-13                     75516            0            0            0            0           65            0            0        75516            0
X$KSWSCLSTAB                     4294952155 14-JAN-13                        52            0            0            0            0           58            0            0           52            0
X$KSWSEVTAB                      4294952154 14-JAN-13                      1636            0            0            0            0           47            0            0         1636            0
X$KSWSASTAB                      4294952091 14-JAN-13                         4            0            0            0            0           76            0            0            4            0
X$KSWSAFTAB                      4294953052 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSIRGD                         4294952708 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSDHNG_CHAINS                  4294952927 14-JAN-13                        12            0            0            0            0          145            0            0           12            0
X$KSDHNG_SESSION_BLOCKERS        4294953211 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSDHNG_CACHE_HISTORY           4294953212 14-JAN-13                        20            0            0            0            0           43            0            0           20            0
X$KSXM_DFT                       4294953215 14-JAN-13                         0            0            0            0            0            0            0            0            0            0
X$KSXAFA                         4294951204 14-JAN-13                        12            0            0            0            0           35            0            0           12            0
 
145 rows selected.
 
Elapsed: 00:00:00.01
11.2.0.3> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_c7q8y75rh36sc_3723858078  DEFAULT         ENABLED  select /* test */ avg(pk_col) from kso.skew where col1 = 23489         NO
PROF_gm0w4as5hhr4m_3867171677  DEFAULT         ENABLED  select count(*) from kso.skew                                          NO
PROFILE_399m90n8jzpu6_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
PROFILE_93356j5juvwpz_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew a                                     NO
PROFILE_7s0b9ygcrj77u_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
coe_gs35v5t21d9yf_2684249835   DEFAULT         ENABLED                                                                         NO
coe_9b2hgfwj6bnu1_2160357371   DEFAULT         ENABLED                                                                         NO
 
7 rows selected.
 
Elapsed: 00:00:00.00
11.2.0.3> @disable_sql_profile
Enter value for profile_name: coe_9b2hgfwj6bnu1_2160357371
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01
11.2.0.3> @sql_profiles
Enter value for sql_text: 
Enter value for name: 
 
NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_c7q8y75rh36sc_3723858078  DEFAULT         ENABLED  select /* test */ avg(pk_col) from kso.skew where col1 = 23489         NO
PROF_gm0w4as5hhr4m_3867171677  DEFAULT         ENABLED  select count(*) from kso.skew                                          NO
PROFILE_399m90n8jzpu6_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
PROFILE_93356j5juvwpz_MANUAL   DEFAULT         ENABLED  select avg(pk_col) from kso.skew a                                     NO
PROFILE_7s0b9ygcrj77u_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
coe_gs35v5t21d9yf_2684249835   DEFAULT         ENABLED                                                                         NO
coe_9b2hgfwj6bnu1_2160357371   DEFAULT         DISABLED                                                                        NO
 
7 rows selected.
 
Elapsed: 00:00:00.01
11.2.0.3> @paramon
Enter value for status: 
 
 Node Name Status       Pid   Sid Parent OSUSER                         Schema     CHILD_WAIT                     PARENT_WAIT
----- ---- ---------- ----- ----- ------ ------------------------------ ---------- ------------------------------ ------------------------------
      P000 AVAILABLE     35
      P001 AVAILABLE     36
      P002 AVAILABLE     37
      P003 AVAILABLE     38
      P004 AVAILABLE     39
      P005 AVAILABLE     40
      P006 AVAILABLE     41
      P007 AVAILABLE     42
      P008 AVAILABLE     43
      P009 AVAILABLE     44
      P010 AVAILABLE     45
      P011 AVAILABLE     46
      P012 AVAILABLE     47
      P013 AVAILABLE     48
      P014 AVAILABLE     49
      P015 AVAILABLE     50
      P016 AVAILABLE     51
      P017 AVAILABLE     52
      P018 AVAILABLE     53
      P019 AVAILABLE     54
      P020 AVAILABLE     55
      P021 AVAILABLE     56
      P022 AVAILABLE     57
      P023 AVAILABLE     58
      P024 AVAILABLE     59
      P025 AVAILABLE     60
      P026 AVAILABLE     61
      P027 AVAILABLE     62
      P028 AVAILABLE     63
      P029 AVAILABLE     64
      P030 AVAILABLE     65
      P031 AVAILABLE     66
 
 
32 rows selected.
 
Elapsed: 00:00:00.09
11.2.0.3> @x
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9b2hgfwj6bnu1, child number 0
-------------------------------------
 select distinct v.inst_id              , x.server_name       ,
x.status as x_status       , x.pid as x_pid       , x.sid as x_sid
 , w2.sid as p_sid       , v.osuser       , v.schemaname       ,
w1.event as child_wait       , w2.event as parent_wait  from
v$px_process x      , v$lock l      , gv$session v      ,
v$session_wait w1      , v$session_wait w2  where x.sid <> l.sid(+)
and   to_number (substr(x.server_name,2)) = l.id2(+)  and   x.sid =
w1.sid(+)  and   l.sid = w2.sid(+)  and   x.sid = v.sid(+)  and
nvl(l.type,'PS') = 'PS'         and   x.status like nvl('',x.status)
     and substr(x.server_name,2,1) != 'Z'  order by p_sid, 1,2
 
Plan hash value: 1445225033
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |    14 (100)|          |        |      |            |
|   1 |  SORT UNIQUE                       |                 |     1 |   110 |    13 (100)| 00:00:01 |        |      |            |
|   2 |   HASH JOIN OUTER                  |                 |     1 |   110 |    12 (100)| 00:00:01 |        |      |            |
|   3 |    FILTER                          |                 |       |       |            |          |        |      |            |
|   4 |     HASH JOIN OUTER                |                 |     1 |    80 |    12 (100)| 00:00:01 |        |      |            |
|   5 |      HASH JOIN OUTER               |                 |     1 |    60 |     2 (100)| 00:00:01 |        |      |            |
|   6 |       HASH JOIN OUTER              |                 |     1 |    30 |     2 (100)| 00:00:01 |        |      |            |
|   7 |        VIEW                        | V$PX_PROCESS    |     1 |    19 |     1 (100)| 00:00:01 |        |      |            |
|   8 |         HASH JOIN OUTER            |                 |     1 |    56 |     1 (100)| 00:00:01 |        |      |            |
|   9 |          HASH JOIN                 |                 |     1 |    29 |     1 (100)| 00:00:01 |        |      |            |
|  10 |           FIXED TABLE FULL         | X$KXFPDP        |     1 |    17 |     0   (0)|          |        |      |            |
|  11 |           FIXED TABLE FULL         | X$KSUPR         |     9 |   108 |     0   (0)|          |        |      |            |
|  12 |          VIEW                      | V$SESSION       |    57 |  1539 |     0   (0)|          |        |      |            |
|  13 |           NESTED LOOPS             |                 |    57 |  1539 |     0   (0)|          |        |      |            |
|  14 |            NESTED LOOPS            |                 |    57 |   684 |     0   (0)|          |        |      |            |
|  15 |             FIXED TABLE FULL       | X$KSLWT         |    57 |   456 |     0   (0)|          |        |      |            |
|  16 |             FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)|          |        |      |            |
|  17 |            FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) |     1 |    15 |     0   (0)|          |        |      |            |
|  18 |        PX COORDINATOR              |                 |    57 |   627 |     0   (0)|          |        |      |            |
|  19 |         PX SEND QC (RANDOM)        | :TQ10000        |    57 |  1653 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|  20 |          VIEW                      | GV$SESSION      |       |       |            |          |  Q1,00 | PCWP |            |
|  21 |           NESTED LOOPS             |                 |    57 |  1653 |     0   (0)|          |  Q1,00 | PCWP |            |
|  22 |            NESTED LOOPS            |                 |    57 |   684 |     0   (0)|          |  Q1,00 | PCWP |            |
|  23 |             FIXED TABLE FULL       | X$KSLWT         |    57 |   456 |     0   (0)|          |  Q1,00 | PCWP |            |
|  24 |             FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)|          |  Q1,00 | PCWP |            |
|  25 |            FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) |     1 |    17 |     0   (0)|          |  Q1,00 | PCWP |            |
|  26 |       VIEW                         | V$SESSION_WAIT  |    57 |  1710 |     0   (0)|          |        |      |            |
|  27 |        NESTED LOOPS                |                 |    57 |  2337 |     0   (0)|          |        |      |            |
|  28 |         FIXED TABLE FULL           | X$KSLWT         |    57 |   627 |     0   (0)|          |        |      |            |
|  29 |         FIXED TABLE FIXED INDEX    | X$KSLED (ind:2) |     1 |    30 |     0   (0)|          |        |      |            |
|  30 |      VIEW                          | V$LOCK          |  4018 | 80360 |     9 (100)| 00:00:01 |        |      |            |
|  31 |       HASH JOIN                    |                 |  4018 |   180K|     9 (100)| 00:00:01 |        |      |            |
|  32 |        HASH JOIN                   |                 |    45 |  1260 |     7 (100)| 00:00:01 |        |      |            |
|  33 |         VIEW                       | GV$_LOCK        |    45 |   540 |     6 (100)| 00:00:01 |        |      |            |
|  34 |          UNION-ALL                 |                 |       |       |            |          |        |      |            |
|  35 |           FILTER                   |                 |       |       |            |          |        |      |            |
|  36 |            VIEW                    | GV$_LOCK1       |    37 |   444 |     4 (100)| 00:00:01 |        |      |            |
|  37 |             UNION-ALL              |                 |       |       |            |          |        |      |            |
|  38 |              FIXED TABLE FULL      | X$KDNSSF        |     1 |    16 |     0   (0)|          |        |      |            |
|  39 |              FIXED TABLE FULL      | X$KSQEQ         |    36 |   612 |     4 (100)| 00:00:01 |        |      |            |
|  40 |           FIXED TABLE FULL         | X$KTADM         |     1 |    18 |     1 (100)| 00:00:01 |        |      |            |
|  41 |           FIXED TABLE FULL         | X$KTATRFIL      |     1 |    14 |     0   (0)|          |        |      |            |
|  42 |           FIXED TABLE FULL         | X$KTATRFSL      |     1 |    14 |     0   (0)|          |        |      |            |
|  43 |           FIXED TABLE FULL         | X$KTATL         |     1 |    14 |     0   (0)|          |        |      |            |
|  44 |           FIXED TABLE FULL         | X$KTSTUSC       |     1 |    14 |     0   (0)|          |        |      |            |
|  45 |           FIXED TABLE FULL         | X$KTSTUSS       |     1 |    16 |     0   (0)|          |        |      |            |
|  46 |           FIXED TABLE FULL         | X$KTSTUSG       |     1 |    14 |     0   (0)|          |        |      |            |
|  47 |           FIXED TABLE FULL         | X$KTCXB         |     1 |    16 |     0   (0)|          |        |      |            |
|  48 |         FIXED TABLE FULL           | X$KSUSE         |  1568 | 25088 |     0   (0)|          |        |      |            |
|  49 |        FIXED TABLE FULL            | X$KSQRS         |  8928 |   156K|     2 (100)| 00:00:01 |        |      |            |
|  50 |    VIEW                            | V$SESSION_WAIT  |    57 |  1710 |     0   (0)|          |        |      |            |
|  51 |     NESTED LOOPS                   |                 |    57 |  2337 |     0   (0)|          |        |      |            |
|  52 |      FIXED TABLE FULL              | X$KSLWT         |    57 |   627 |     0   (0)|          |        |      |            |
|  53 |      FIXED TABLE FIXED INDEX       | X$KSLED (ind:2) |     1 |    30 |     0   (0)|          |        |      |            |
-----------------------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - statement not queuable: gv$ statement
 
 
78 rows selected.
 
Elapsed: 00:00:00.04

So gathering the fixed object stats enabled the optimizer to get the right cardinality on the X$KSUSE and the X$KSQRS objects and come up with a good plan. It’s not the same as the one from 10g by the way, but performs well and doesn’t require any intervention. This is the correct way to resolve the root problem.

It’s import to note that the original 11g system I was working on was a production system that I was not comfortable gathering fixed object stats on without discussion and testing, while the bandaid of applying a SQL Profile or adding a hint to my query was a quick work around that allowed me to continue my work with little risk to the rest of the system. So try to use these techniques for what they are good for. Remember that bandaids only help with specific issues and do not resolve underlying problems, but they can provide very quick relief and provide you with a chance to resolve the underlying problem under less stressful conditions.

Secure Coding PL/SQL

I wrote a new presentation last year on secure coding with PL/SQL and presented it twice; once at a SIG in London and once in Oracles office in Edinburgh. This is a really interesting subject for me as i have....[Read More]

Posted by Pete On 14/01/13 At 07:43 PM

Blog formatting

I think I’ve finally gotten the blog formatting to be like I want … The syntax highlighter plugins were not good enough as I want very flexible output formatting (not just code snippets, but sqlplus script output) as below:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SID, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    189, SYS       , STAT, opened cursors cumulative                                 ,            44,        8.8,         ,             ,          ,           , 44,383,STAT,-2147483643
    189, SYS       , STAT, recursive calls                                           ,            45,          9,         ,             ,          ,           , 45,1139,STAT,-2147483638
    189, SYS       , STAT, recursive cpu usage                                       ,             7,        1.4,         ,             ,          ,           , 7,41,STAT,-2147483637
#ff0000;">    189, SYS       , STAT, CPU used by this session                                  ,             8,        1.6,         ,             ,          ,           , 8,80,STAT,-2147483630
    189, SYS       , STAT, in call idle wait time                                    ,           423,       84.6,         ,             ,          ,           , 423,423,STAT,-2147483621
    189, SYS       , STAT, #008000;">session uga memory                                        ,         65512,      13.1k,         ,             ,          ,           , 65512,635700,STAT,-2147483618
    189, SYS       , STAT, session pga memory                                        ,        720896,    144.18k,         ,             ,          ,           , 720896,14525104,STAT,-2147483612
    189, SYS       , STAT, session pga memory max                                    ,        589824,    117.96k,         ,             ,          ,           , 589824,14525104,STAT,-2147483611
    189, SYS       , STAT, calls to get snapshot scn: kcmgss                         ,            44,        8.8,         ,             ,          ,           , 44,367,STAT,-2147483474
    189, SYS       , STAT, session cursor cache hits                                 ,            44,        8.8,         ,             ,          ,           , 44,288,STAT,-2147483115
    189, SYS       , STAT, session cursor cache count                                ,             1,         .2,         ,             ,          ,           , 1,24,STAT,-2147483114
    189, SYS       , STAT, workarea executions - optimal                             ,            49,        9.8,         ,             ,          ,           , 49,78,STAT,-2147483070
    189, SYS       , STAT, execute count                                             ,            44,        8.8,         ,             ,          ,           , 44,353,STAT,-2147483059
    189, SYS       , STAT, sorts (memory)                                            ,            46,        9.2,         ,             ,          ,           , 46,62,STAT,-2147483048
    189, SYS       , STAT, sorts (rows)                                              ,          1868,      373.6,         ,             ,          ,           , 1868,3699,STAT,-2147483046
    189, SYS       , TIME, PL/SQL execution elapsed time                             ,         11842,     2.37ms,      .2%, [          ],          ,           , 11842,17125,TIME,496422347
    189, SYS       , TIME, DB CPU                                                    ,        376943,    75.39ms,     7.5%, [@         ],          ,           , 376943,803877,TIME,600798790
    189, SYS       , TIME, sql execute elapsed time                                  ,        116895,    23.38ms,     2.3%, [#         ],          ,           , 116895,282639,TIME,674214537
    189, SYS       , TIME, DB time                                                   ,        390034,    78.01ms,     7.8%, [#         ],          ,           , 390034,819575,TIME,1501598727
    189, SYS       , WAIT, PL/SQL lock timer                                         ,       4232858,   846.57ms,    84.7%, [WWWWWWWWW ],        42,        8.4,   100.78ms

--  End of Stats snap 1, end=2013-01-14 14:29:09, seconds=5

--  End of ASH snap 1, end=2013-01-14 14:29:09, seconds=5, samples_taken=42

Try to hover your mouse over the blue “session cursor cache hits” item above. It should show some “help text” too.

I think I have the mobile templates fixed now and even old posts like this should allow scrolling wide output left & right.

I still plan to change a few little things (like adding padding to the code output in the code boxes) etc.

Feel free to test the new layout out and give feedback if it doesn’t work on some device. Thanks!


HAVING Cardinality

When performing aggregate GROUP BY operations an additional filter on the aggregates can be applied using the HAVING clause.Usually aggregates are one of the last steps executed before the final result set is returned to the client.However there are various reasons, why a GROUP BY operation might be somewhere in the middle of the execution plan operation, for example it might be part of a view that cannot be merged (or was hinted not to be merged using the NO_MERGE hint), or in the more recent releases (11g+) the optimizer decided to use the GROUP BY PLACEMENT transformation that deliberately can move the GROUP BY operation to a different execution step of the plan.In such cases, when the GROUP BY operation will be input to some other operation, it becomes essential for the overall efficiency of the execution plan preferred by the optimizer that the cardinality estimates are in the right ballpark, as it will influence the choice of other related execution steps like join orders and methods or simply the decision between an index-based access or a full table scan.While the optimizer based on the statistics can come up with a reasonable estimate regarding the cardinality of the GROUP BY expression (the emphasis here is on *can*, it might also be wrong), it is important to understand that an additional filter on the aggregates using the HAVING clause is in principle treated like an "unknown" expression and therefore the estimates are based on built-in defaults that might not have much to do with actual filter selectivities of that HAVING expression.Here is a simple example to demonstrate the point:


create table t
as
select
rownum as id
, date '2000-01-01' + mod(rownum - 1, 100) as a_date1
, date '2000-01-01' + mod(rownum - 1, 100) as a_date2
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e5
;

exec dbms_stats.gather_table_stats(null, 't')

create unique index t_idx on t (id);

There is a table of 100K rows with two dates in it that have each 100 distinct values (we can ignore in this case here that the values generated are actually correlated) - the ID column is unique.If I ask for an estimate for queries similar to the following on this table:


set echo on linesize 200 pagesize 0

explain plan for
select
id
, max(a_date1) as max_a_date1
, min(a_date2) as min_a_date2
from
t
-- 50% of data
where
id > 50000
group by
id
;

select * from table(dbms_xplan.display);

explain plan for
select
id
, max(a_date1) as max_a_date1
, min(a_date2) as min_a_date2
from
t
-- 50% of data
where
id > 50000
group by
id
-- No actual filtering
having
max(a_date1) >= date '2000-01-01'
and min(a_date2) <= date '2000-01-01' + 100
;

select * from table(dbms_xplan.display);

I'll get these estimates:


-- Simple GROUP BY without HAVING
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 50001 |
| 1 | HASH GROUP BY | | 50001 |
|* 2 | TABLE ACCESS FULL| T | 50001 |
-------------------------------------------

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

2 - filter("ID">50000)

-- GROUP BY plus HAVING
--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 126 |
|* 1 | FILTER | | |
| 2 | HASH GROUP BY | | 126 |
|* 3 | TABLE ACCESS FULL| T | 50001 |
--------------------------------------------

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

1 - filter(MAX("A_DATE1")>=TO_DATE(' 2000-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND MIN("A_DATE2")<=TO_DATE(' 2000-04-10
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - filter("ID">50000)

Note how the optimizer gets the cardinality right for the first statement. The aggregation doesn't reduce the cardinality due to the uniqueness of the ID column.Notice how the filter predicates on the aggregates in the second case actually do not filter at all as they select the whole date range available. But the optimizer simply assumes 5% selectivity per unknown range predicate resulting in 0.05 times 0.05 = 0.0025 selectivity.I don't think that it is a bug - it simply cannot assume anything regarding the possible MIN and MAX values given any potential arbitrary other filters.If I now wrap the aggregate query as a view into a more complex statement, it becomes obvious that the HAVING clause can also implicitly be derived by applying corresponding filters to the view:


select /*+ no_merge(x) */ * from (
select /*+ gather_plan_statistics */
a.*, b.id as b_id, b.filler as b_filler
from (
/* Aggregate inline view without having */
select
id
, max(a_date1) as max_a_date1
, min(a_date2) as min_a_date2
from
t
group by
id
) a
, t b
where
/* These filter predicates will be pushed into the view */
max_a_date1 >= date '2000-01-01'
and min_a_date2 <= date '2000-01-01' + 100
and a.id > 50000
/* A join between the view and something else */
and a.id = b.id
) x
where
rownum > 1
;

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |
| 1 | COUNT | | 1 | | 0 |
|* 2 | FILTER | | 1 | | 0 |
| 3 | VIEW | | 1 | 126 | 50000 |
| 4 | NESTED LOOPS | | 1 | | 50000 |
| 5 | NESTED LOOPS | | 1 | 126 | 50000 |
| 6 | VIEW | | 1 | 126 | 50000 |
|* 7 | FILTER | | 1 | | 50000 |
| 8 | HASH GROUP BY | | 1 | 126 | 50000 |
|* 9 | TABLE ACCESS FULL | T | 1 | 50001 | 50000 |
|* 10 | INDEX UNIQUE SCAN | T_IDX | 50000 | 1 | 50000 |
| 11 | TABLE ACCESS BY INDEX ROWID| T | 50000 | 1 | 50000 |
----------------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
7 - filter((MAX("A_DATE1")>=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
MIN("A_DATE2")<=TO_DATE(' 2000-04-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
9 - filter("ID">50000)
10 - access("A"."ID"="B"."ID")
filter("B"."ID">50000)

Notice how the incorrect cardinality estimate leads to a NESTED LOOP for the join operation, which is very likely a bad idea in such cases where the number of actual loop iterations is much higher than estimated.In general such bad cardinality estimates can echo through the whole execution plan with potentially devastating results.For my particular case here one potential workaround besides using undocumented CARDINALITY or OPT_ESTIMATE hints is to prevent the optimizer from pushing the filter into the view (and thereby avoiding the implicit generation of the HAVING clause) by wrapping the view with another view on top that includes a reference to ROWNUM:


select /*+ no_merge(x) */ * from (
select /*+ gather_plan_statistics */
a.*, b.id as b_id, b.filler as b_filler
from (
/* ROWNUM in projection prevents pushing of filters */
select id
, max_a_date1
, min_a_date2
, rownum as rn
from
(
/* The unchanged aggregate view */
select
id
, max(a_date1) as max_a_date1
, min(a_date2) as min_a_date2
from
t
group by
id
)
) a
, t b
where
max_a_date1 >= date '2000-01-01'
and min_a_date2 <= date '2000-01-01' + 100
and a.id > 50000
and a.id = b.id
) x
where
rownum > 1
;

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

---------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |
| 1 | COUNT | | 1 | | 0 |
|* 2 | FILTER | | 1 | | 0 |
| 3 | VIEW | | 1 | 50001 | 50000 |
|* 4 | HASH JOIN | | 1 | 50001 | 50000 |
|* 5 | VIEW | | 1 | 100K| 50000 |
| 6 | COUNT | | 1 | | 100K|
| 7 | VIEW | | 1 | 100K| 100K|
| 8 | HASH GROUP BY | | 1 | 100K| 100K|
| 9 | TABLE ACCESS FULL| T | 1 | 100K| 100K|
|* 10 | TABLE ACCESS FULL | T | 1 | 50001 | 50000 |
---------------------------------------------------------------------

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

2 - filter(ROWNUM>1)
4 - access("A"."ID"="B"."ID")
5 - filter(("MAX_A_DATE1">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MIN_A_DATE2"<=TO_DATE(' 2000-04-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."ID">50000))
10 - filter("B"."ID">50000)

This way the cardinality estimate is much better for my particular case here and a more suitable HASH JOIN gets used instead.There are however a couple of noticable drawbacks with that workaround:- The optimizer is still clueless: If for example the filter on the aggregates actually filtered any data, it would still assume NO filtering at all (still 100K in this case here), which might be just as bad, but in the opposite direction (over-estimate instead of under-estimate)- Any other potentially useful filters (the "ID > 50000" in my case here) will also be prevented from being pushed into the view, so in my case the GROUP BY has to operate on a much larger data set than actually necessary (100K rows instead of 50K rows) - not good- The ROWNUM evaluation causes overhead and will be a problem when trying to run this statement using Parallel Execution, as it will cause the plan to be split into multiple DFOs (you'll find multiple PX COORDINATOR operations in such plans which can have nasty side effects, for more info read my OTN mini-series on Parallel Execution) with a serialized operation in between to determine the row number

Summary

Be careful whenever the cardinality estimates for HAVING clauses become relevant to your execution plan, as the optimizer simply applies default selectivities that can be way off.If the aggregation is one of the final steps of execution, the cardinality estimate is probably not that relevant, but there are other cases possible where it matters a lot.

Footnote

From 11.2.0.3 on there is a new undocumented parameter "_optimizer_filter_pushdown" that defaults to "true".When setting it to "false" the "Filter Pushdown" (FPD) transformation used above and prevented via ROWNUM will be disabled, however on a global statement / session level and not only for a particular view / query block as with the ROWNUM workaround.