Mohamed Houri’s Oracle Notes

April 29, 2015

Real time SQL monitoring limitation

Filed under: Oracle — hourim @ 3:56 pm

I was trying to explain a performance deterioration of a very complex query honoured via an execution plan with 386 operations (386 lines). From where would someone start deciphering this complex and big execution plan without the help of a Real Time SQL monitoring report? As far as this query took 2 hours to complete it is fairly likely that Oracle has monitored it. Unfortunately a select against v$sql_monitor view didn’t returned any rows for this particular sql_id. What came to my mind in front of this situation is that the report has been flushed from memory due to a stress on the library cache. Hopefully, I was able to get the bind variable and re-execute the same query. While the query was running I opened a sqlplus window and run this:

SQL> select sql_id from v$sql_monitor where status = 'EXECUTING';
no rows selected

The query was still runing after a couple of minutes but was still not monitored. I was suspecting the number of operations in the execution plan but has no way to proof the correlation between this number of lines and the absence of the monitoring

Plan hash value: 1504525856
----------------------------------------------------------------------
| Id  | Operation                                                    |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |
|   1 |  UNION-ALL                                                   |
|   2 |   SORT UNIQUE                                                |
|   3 |    MERGE JOIN CARTESIAN                                      |
|   4 |     MERGE JOIN CARTESIAN                                     |
|   5 |      NESTED LOOPS                                            |
|*  6 |       HASH JOIN OUTER                                        |
|   7 |        MERGE JOIN CARTESIAN                                  |
|   8 |         NESTED LOOPS OUTER                                   |
|*  9 |          HASH JOIN OUTER                                     |
|* 10 |           HASH JOIN OUTER                                    |
|* 11 |            HASH JOIN OUTER                                   |
|* 12 |             HASH JOIN OUTER                                  |
|* 13 |              HASH JOIN OUTER                                 |
.../...
|*202 |                                               HASH JOIN      |
| 203 | OUTER                                          NESTED LOOPS  |
| 204 |  OUTER                                          NESTED LOOPS |
|*205 |                                                  HASH JOIN   |
|*206 | OUTER                                             HASH JOIN  |
.../...
| 383 |          BUFFER SORT                                         |
| 384 |           PX RECEIVE                                         |
| 385 |            PX SEND BROADCAST                                 |
| 386 |             TABLE ACCESS FULL                                |
----------------------------------------------------------------------

Spot in passing where the OUTER at line 51 and 53 has been placed.

Google being a good friend I asked him and he directed me to this article where Doug Burns pointed that there is a hidden parameter(_sqlmon_max_planlines) which fixes the maximum number of lines an execution plan must not exceed to be, all other things being equal, monitored.

I decided then to give it a try and I have altered this parameter to accept monitoring my 386 plan operations:

SQL> alter session set "_sqlmon_max_planlines" = 400;

Session altered.

And to my pleasant surprise I found that my query started being monitored


SQL Monitoring Report

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  xxxx (626:3043)
 SQL ID              :  315sc2w0cy05w
 SQL Execution ID    :  16777216
 Execution Started   :  04/29/2015 11:29:39
 First Refresh Time  :  04/29/2015 11:29:46
 Last Refresh Time   :  04/29/2015 11:35:26
 Duration            :  348s
 Module/Action       :  sqldeveloper64W.exe/-
 Service             :  SYS$USERS
 Program             :  sqldeveloper64W.exe   

 SQL Plan Monitoring Details (Plan Hash Value=1504525856)
==============================================================================================
| Id    |                                     Operation                                      |
|       |                                                                                    |
==============================================================================================
|     0 | SELECT STATEMENT                                                                   |
|     1 |   UNION-ALL                                                                        |
|     2 |    SORT UNIQUE                                                                     |
|     3 |     MERGE JOIN CARTESIAN                                                           |
|     4 |      MERGE JOIN CARTESIAN                                                          |
|     5 |       NESTED LOOPS                                                                 |
|     6 |        HASH JOIN OUTER                                                             |
|     7 |         MERGE JOIN CARTESIAN                                                       |

|   383 |           BUFFER SORT                                                              |
|   384 |            PX RECEIVE                                                              |
|   385 |             PX SEND BROADCAST                                                      |
|   386 |              TABLE ACCESS FULL                                                     |
==============================================================================================

And now the serious stuff can start 🙂

April 18, 2015

Parallel refreshing a materialized view

Filed under: Materialized view — hourim @ 4:58 pm

I have been asked to troubleshoot a monthly on demand materialized view refresh job which has got the bad idea to crash with the ORA-01555 error after 25,833 seconds (more than 7 hours) of execution. Despite my several years of professional experience this is the first time I have been asked to look at a materialized view refresh. This issue came up Friday afternoon so I was given a week-end to familiarize myself with materialized views. Coincidentally a couple of days before there was an Oracle webcast on Materialized view basics, architecture and internal working  which I have replayed on Saturday and practiced its demo. Christian Antognini book contains a chapter on this topic which I have also gone through as far as Christian book is from where I always like to start when trying to learn an Oracle concept.

Materialized view capabilities

The following Monday morning, armed with this week-end accelerated auto-training, I opened again the e-mail I have been sent about the failing refresh job and started re-reading it. The first thing that has retained my attention this time, in contrast to my last Friday quick pass through reading, was a suggestion made by the DBA to try fast refreshing the materialized view instead of completely refreshing it. I learnt from the Oracle webcast that Oracle is able to let us know wether a materialized view can be fast (also know as incremental) refreshed or not. Here below the steps to do if you want to get this information:

You need first to create the mv_capabilities_table table (in the schema you are going to use against it the dbms_mview package) using the following script :

SQL> $ORACLE_HOME/rdbms/admin/utlxmv.sql

SQL> select * from mv_capabilities_table;
no rows selected

Once this table created you can execute the dbms_mview.explain_mview package as shown below:

SQL> exec dbms_mview.explain_mview ('my_materialied_mv');

PL/SQL procedure successfully completed.

SQL> select
  2     mvname
  3    ,capability_name
  4    ,possible
  5  from
  6    mv_capabilities_table
  7  where
  8     mvname = 'MY_MATERIALIED_MV'
  9  and
 10    capability_name  like '%REFRESH%';

MVNAME                         CAPABILITY_NAME                P
------------------------------ ------------------------------ -
MY_MATERIALIED_MV              REFRESH_COMPLETE               Y  
MY_MATERIALIED_MV              REFRESH_FAST                   N --> spot this
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_INSERT      N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_ONETAB_DML  N
MY_MATERIALIED_MV              REFRESH_FAST_AFTER_ANY_DML     N
MY_MATERIALIED_MV              REFRESH_FAST_PCT               N

As spotted above, fast refreshing this materialized view is impossible.

The first learned lesson: instead of trying the create a materialized view log and fast refreshing a complex materialized view which might be impossible to be refreshed incrementally, try first getting the capabilities of the view using the explain_mview procedure. You will certainly save time and resource.

SQL> SELECT
         refresh_method
       , refresh_mode
       , staleness
       , last_refresh_type
       , last_refresh_date
    FROM
          user_mviews
    WHERE mview_name = 'MY_MATERIALIED_MV';

REFRESH_ REFRES STALENESS           LAST_REF LAST_REFRES
-------- ------ ------------------- -------- --------------------
COMPLETE DEMAND NEEDS_COMPILE       COMPLETE 02-APR-2015 16:16:35

Parallel clause in the SQL create statement : any effect on the mview creation?

Since I have ruled out an incremental refresh I decided to get the materialized view definition so that I can investigate its content

SQL> SELECT
       replace (dbms_metadata.get_ddl(replace(
                                      OBJECT_TYPE, ' ', '_'),    
                                      OBJECT_NAME,OWNER)
                    ,'q#"#'
                    ,'q#''#'
                    )
     FROM DBA_OBJECTS
     WHERE OBJECT_TYPE = 'MATERIALIZED VIEW'
     AND object_name = 'MY_MATERIALIED_MV';

------------------------------------------------------------------
CREATE MATERIALIZED VIEW MY_MATERIALIED_MV
   ({list of columns}) 
  TABLESPACE xxxx
  PARALLEL 16 –----------------------------------> spot this
  BUILD IMMEDIATE
  USING INDEX
  REFRESH COMPLETE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
-- select n°1
 SELECT
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
 GROUP BY
  {list of columns}
.../...
UNION ALL
-- select n°5
SELECT
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
GROUP BY
  {list of columns} ;

Have you noticed that parallel 16 clause in the materialized view create script? The developer intention was to create the materialized view using parallel process. Having a Production equivalent database I was happy enough to try re-creating this materialized view:

SQL> set timing on

SQL> start ddl_mv1.sql

Materialized view created.

Elapsed: 00:22:33.52

Global Information
------------------------------
 Status              :  DONE               
 Instance ID         :  1                  
 Session             :  XZYY (901:25027)  
 SQL ID              :  f9s6kdyysz84m      
 SQL Execution ID    :  16777216           
 Execution Started   :  04/16/2015 09:49:22
 First Refresh Time  :  04/16/2015 09:49:23
 Last Refresh Time   :  04/16/2015 10:11:48
 Duration            :  1346s              
 Module/Action       :  SQL*Plus/-         
 Service             :  XZYY
 Program             :  sqlplus.exe         

Global Stats
========================================================================
| Elapsed |   Cpu   |    IO    | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
========================================================================
|   20338 |    5462 |    14205 |    63M |   3M | 716GB |    2M | 279GB |
========================================================================

Parallel Execution Details (DOP=16 , Servers Allocated=32)

SQL Plan Monitoring Details (Plan Hash Value=853136481)
==================================================================================================
| Id  |                       Operation            | Name    |  Rows   | Execs |   Rows   |Temp  |
|     |                                            |         | (Estim) |       | (Actual) |(Max) |
==================================================================================================
|   0 | CREATE TABLE STATEMENT                     |         |         |    33 |       16 |      |
|   1 |   PX COORDINATOR                           |         |         |    33 |       16 |      |
|   2 |    PX SEND QC (RANDOM)                     | :TQ10036|         |    16 |       16 |      |
|   3 |     LOAD AS SELECT                         |         |         |    16 |       16 |      |
|   4 |      UNION-ALL                             |         |         |    16 |     117M |      |
|   5 |       HASH GROUP BY                        |         |    259M |    16 |      58M |  36G |
|   6 |        PX RECEIVE                          |         |    259M |    16 |     264M |      |
|   7 |         PX SEND HASH                       | :TQ10031|    259M |    16 |     264M |      |
|   8 |          HASH JOIN RIGHT OUTER BUFFERED    |         |    259M |    16 |     264M |  61G |
|   9 |           PX RECEIVE                       |         |      4M |    16 |       4M |      |
|  10 |            PX SEND HASH                    | :TQ10013|      4M |    16 |       4M |      |
|  11 |             PX BLOCK ITERATOR              |         |      4M |    16 |       4M |      |
|     |                                            |         |         |       |          |      |
| 180 |                PX RECEIVE                  |         |     19M |    16 |      20M |      |
| 181 |                 PX SEND HASH               | :TQ10012|     19M |    16 |      20M |      |
| 182 |                  PX BLOCK ITERATOR         |         |     19M |    16 |      20M |      |
| 183 |                   TABLE ACCESS FULL        | TABLE_M |     19M |   268 |      20M |      |
==================================================================================================

Surprisingly the materialized view has been created in less than 23 minutes. And this creation has been parallelised with a DOP of 16 as shown by the corresponding Real Time Sql Monitoring report (RTSM).The master table has been henceforth created with a DOP of 16 as shown below:

SQL> select
  2    table_name
  3   ,degree
  4  from
  5    user_tables
  6  where table_name = 'MY_MATERIALIED_MV';

TABLE_NAME                     DEGREE
------------------------------ ----------
MY_MATERIALIED_MV               16

A simple select against the created materialized view will go parallel as well

SQL> select count(1) from MY_MATERIALIED_MV;               

SQL Plan Monitoring Details (Plan Hash Value=3672954679)
============================================================================================
| Id |          Operation          |           Name           |  Rows   | Execs |   Rows   |
|    |                             |                          | (Estim) |       | (Actual) |
============================================================================================
|  0 | SELECT STATEMENT            |                          |         |     1 |        1 |
|  1 |   SORT AGGREGATE            |                          |       1 |     1 |        1 |
|  2 |    PX COORDINATOR           |                          |         |    17 |       16 |
|  3 |     PX SEND QC (RANDOM)     | :TQ10000                 |       1 |    16 |       16 |
|  4 |      SORT AGGREGATE         |                          |       1 |    16 |       16 |
|  5 |       PX BLOCK ITERATOR     |                          |    104M |    16 |     117M |
|  6 |        MAT_VIEW ACCESS FULL | MY_MATERIALIED_MV        |    104M |   191 |     117M |
============================================================================================

You might have already pointed out in the above RTSM report that the select part of the “create as select” statement has been parallelised as well. It is as if the parallel 16 clause of the “create” part of the SQL  materialized view script induced implicitly its  “select” part to be done in parallel with a DOP of 16.

Parallel clause in the SQL create statement : any effect on the mview refresh ?

As far as I am concerned, the problem I have been asked to trouble shoot resides in refreshing the materialized view and not in creating it.  Since, the materialized view has been created in 23 minutes, I should be optimistic for its refresh time; isn’t it?

SQL> exec dbms_mview.refresh ('MY_MATERIALIED_MV','C',atomic_refresh=>FALSE);

After more than 4,200 seconds of execution time I finally gave up and decided to stop this refresh. Below is an overview of its corresponding Real Time Sql Monitoring (RTSM) report:

Global Information
------------------------------
 Status              :  DONE (ERROR) --> I have cancelled it after more than 1 hour   
 Instance ID         :  1                  
 Session             :  XZYY (901:25027)  
 SQL ID              :  d5n03tuht2cg8      
 SQL Execution ID    :  16777216           
 Execution Started   :  04/16/2015 10:55:46
 First Refresh Time  :  04/16/2015 10:55:52
 Last Refresh Time   :  04/16/2015 12:06:39
 Duration            :  4253s               
 Module/Action       :  SQL*Plus/-         
 Service             :  XZYY
 Program             :  sqlplus.exe         

Global Stats
===================================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
===================================================================================
|    4253 |    1640 |     2563 |       50 |    53M | 824K | 227GB |  570K | 120GB |
===================================================================================

SQL Plan Monitoring Details (Plan Hash Value=998958099)
=============================================================================
| Id |                  Operation                   |Name |  Rows   | Cost  |
|    |                                              |     | (Estim) |       |
=============================================================================
|  0 | INSERT STATEMENT                             |     |         |       |
|  1 |   LOAD AS SELECT                             |     |         |       |
|  2 |    UNION-ALL                                 |     |         |       |
|  3 |     HASH GROUP BY                            |     |    259M |       |
|  4 |      CONCATENATION                           |     |         |       |
|  5 |       NESTED LOOPS OUTER                     |     |       7 |  4523 |
|  6 |        NESTED LOOPS OUTER                    |     |       7 |  4495 |
|  7 |         NESTED LOOPS                         |     |       7 |  4474 |
|  8 |          NESTED LOOPS                        |     |       7 |  4460 |
|  9 |           PARTITION REFERENCE ALL            |     |       7 |  4439 |
…/…

In contrast to the creation process, the materialized view refresh has been done serially. This confirms that the above parallel 16 clause in the create DDL script concerns only the parallel materialized view creation and not its refresh process.

The second learned lesson : I think that a parallel clause specified in the create statement of a materialized view is not used during the refresh of the same materialized view.  The parallel run is considered in this kind of situations only at the materialized view creation time.

dbms_mview.refresh and its parallelism parameter : any effect on the mview refresh ?

The tables on which the materialized view is based have all a degree = 1

 SQL> select
  2      table_name
  3    , degree
  4  from user_tables
  5  where trim(degree) <> '1';

TABLE_NAME            DEGREE
--------------------- -------
MY_MATERIALIED_MV     16

Having said that, what if I try refreshing this materialized view using the parallelism parameter of the dbms_mview.refresh procedure as shown below:

SQL> exec dbms_mview.refresh ('MY_MATERIALIED_MV','C', atomic_refresh=>FALSE, parallelism =>16);

SQL Plan Monitoring Details (Plan Hash Value=998958099)
==========================================================================================
| Id |                  Operation                   |           Name           |  Rows   |
|    |                                              |                          | (Estim) |
==========================================================================================
|  0 | INSERT STATEMENT                             |                          |         |
|  1 |   LOAD AS SELECT                             |                          |         |
|  2 |    UNION-ALL                                 |                          |         |
|  3 |     HASH GROUP BY                            |                          |    259M |
|  4 |      CONCATENATION                           |                          |         |
|  5 |       NESTED LOOPS OUTER                     |                          |       7 |
|  6 |        NESTED LOOPS OUTER                    |                          |       7 |
|  7 |         NESTED LOOPS                         |                          |       7 |
|  8 |          NESTED LOOPS                        |                          |       7 |
|  9 |           PARTITION REFERENCE ALL            |                          |       7 |
| 10 |            TABLE ACCESS BY LOCAL INDEX ROWID | TABLE_XX_ZZ              |       7 |
../..
| 94 |           PARTITION RANGE ALL                |                          |    369M |
| 95 |            PARTITION LIST ALL                |                          |    369M |
| 96 |             TABLE ACCESS FULL                | TABLE_AA_BB_123          |    369M |
==========================================================================================

As confirmed by the above corresponding RTSM report, the parallelism parameter has not been obeyed and the refresh has been done serially in this case as well.

The third learned lesson : using the parameter parallelism of the dbms_mview.refresh procedure has no effect on the parallel refresh of the underlying materialized view.

Adding a parallel hint in the select part of the mview : any effect on the mview refresh ?

At this stage of the troubleshooting process I have emphasized the following points:

  • The parallel clause used in the create statement of a materialized view is considered only during the materialized view creation. This parallel clause is ignored during the refresh process
  • The parallelism parameter of the dbms_mview.refresh procedure will not refresh the materialized view in parallel

Now that I have ruled out all the above steps I was almost convinced that to expedite the refresh process I need to add a parallel hint directly in the materialized view definition (ddl_mv2.sql):

CREATE MATERIALIZED VIEW MY_MATERIALIED_MV
   ({list of columns}) 
  TABLESPACE xxxx
  PARALLEL 16
  BUILD IMMEDIATE
  USING INDEX
  REFRESH COMPLETE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
 SELECT /*+ parallel(8) pq_distribute(tab1 hash hash)*/
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
 GROUP BY
  {list of columns}
UNION ALL
 SELECT /*+ parallel(8) pq_distribute(tab1 hash hash)*/
    {list of columns}
 FROM
  {list of tables}
 WHERE
  {list of predicates}
 GROUP BY
    {list of columns}
;

Having changed the select part of materialized view DDL script I launched again it creation which completes in 25 minutes as shown below:

SQL> start ddl_mv2.sql
Materialized view created.
Elapsed: 00:25:05.37

And immediately after the creation I launched the refresh process :

SQL> exec dbms_mview.refresh ('MY_MATERIALIED_MV','C',atomic_refresh=>FALSE);

PL/SQL procedure successfully completed.
Elapsed: 00:26:11.12

And hopefully this time the refresh completed in 26 minutes thanks to the parallel run exposed below in the corresponding RTSM report:

Global Information
------------------------------
 Status              :  DONE               
 Instance ID         :  1                  
 Session             :  XZYY
 SQL ID              :  1w1v742mr35g3      
 SQL Execution ID    :  16777216           
 Execution Started   :  04/16/2015 13:38:13
 First Refresh Time  :  04/16/2015 13:38:13
 Last Refresh Time   :  04/16/2015 14:04:24
 Duration            :  1571s              
 Module/Action       :  SQL*Plus/-         
 Service             :  XZYY            
 Program             :  sqlplus.exe         

Parallel Execution Details (DOP=8, Servers Allocated=80)

SQL Plan Monitoring Details (Plan Hash Value=758751629)
===============================================================================
| Id  |                       Operation          |           Name   |  Rows   |
|     |                                          |                  | (Estim) |
===============================================================================
|   0 | INSERT STATEMENT                         |                  |         |
|   1 |   LOAD AS SELECT                         |                  |         |
|   2 |    UNION-ALL                             |                  |         |
|   3 |     PX COORDINATOR                       |                  |         |
|   4 |      PX SEND QC (RANDOM)                 | :TQ10005         |    259M |
|   5 |       HASH GROUP BY                      |                  |    259M |
| 177 |                PX RECEIVE                |                  |     19M |
| 178 |                 PX SEND HASH             | :TQ50004         |     19M |
| 179 |                  PX BLOCK ITERATOR       |                  |     19M |
| 180 |                   TABLE ACCESS FULL      | TABLE_KZ_YX      |     19M |
===============================================================================

I’ve added the pq_distribute (tab1 hash hash) hint above because several refreshes crashed because of the broadcast distribution that ended up by overconsuming TEMP space raising the now classical error:

ERROR at line 484:
ORA-12801: error signaled in parallel query server P012
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

The fourth learned lesson : if you want to parallelise your materialized view refresh process you had better to include the parallel hint in the select part of the materialized view. This is better than to change the parallel degree of the tables on which the materialized view is based on.

April 8, 2015

The dark side of using bind variables : sharing everything

Filed under: Tuning — hourim @ 5:48 pm

An interesting academic situation happened last week which I, honestly believe, is worth a blog article as far as experienced DBA have spent time trying to solve it without success. An overnight job was running for hours in the night from 01/04 to 02/04. The on call DBA spent all the night killing and re-launching the job (sql_id) several attempts without any success. When I arrived at work the next day I was asked to help. As far as this job was still running, I generated the Real Time SQL monitoring report (RTSM) for the corresponding sql_id which showed the classical NESTED LOOP having a huge outer data row set driving an inner data set in which at least 50 different operations have been started 519K times while one operation has been executed 2M times. The corresponding execution plan contains 213 operations. The underlying query uses 628 user bind variables and 48 system generated bind variables (thanks to cursor sharing set to FORCE)

SQL Plan Monitoring Details (Plan Hash Value=1511784243)

Global Information
------------------------------
 Status              :  EXECUTING               
 Instance ID         :  2                       
 Session             :  xxxxx (350:9211)   
 SQL ID              :  dmh5vhkcm877v           
 SQL Execution ID    :  33554436                
 Execution Started   :  04/02/2015 07:52:03     
 First Refresh Time  :  04/02/2015 07:52:47     
 Last Refresh Time   :  04/02/2015 10:04:28     
 Duration            :  7947s                   
 Module/Action       :  wwwww
 Service             :  zzzzz               
 Program             :  wwwww  
 DOP Downgrade       :  100%    

Global Stats
===================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   |  Gets  | Reqs | Bytes |
====================================================================================
|    7900 |    7839 |       20 |        0.00 |        0.82 |   243M | 5946 | 659MB |
====================================================================================

The above 7,839 seconds spent consuming CPU with almost no user wait time represents the classical wrong NESTED LOOP operation starting many times several inner operations as mentioned above.

The job was running without any sign of improvement, the client was waiting for its critical report and I have a query with 700 bind variables honored via an execution plan of 213 operations to figure out how to make this report finishing smoothly as soon as possible.

I was dissecting the execution plan when the end user send me an e-mail saying that the same job ran successfully yesterday within 6 min. With that information in mind I have managed to get the RTSM of the yesterday successful job. The first capital information was that the yesterday query and the today not ending one used the same plan_hash_value (same execution plan). Comparing the 628 input bind variable values of both runs, I found that the yesterday job ran for a one month period (monthly job) while the current job is running for a one day interval (daily job).Of course the end user has not supplied any information about the kind of job they are currently running compared to the previous one. All what I have been told is the yesterday job completed in 6 minutes. It is only until I’ve found the difference in the input bind variable values that the end user said “the current run is for the daily job while the previous one was for the monthly job”.

And the sun starts rising. I was able to figure out that the two set of bind variables are not doing the same amount of work and sharing the same execution plan is probably not a good idea.  This is why I have suggested the DBA to do the following:

  • Kill the not ending session
  • Purge the sql_id from the shared pool
  • Ask the end user to re-launch the job
  • Cross fingers 🙂

And you know what?  The job completed within a couple of hundreds of seconds:

SQL Plan Monitoring Details (Plan Hash Value=2729107228)

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)         
 Instance ID         :  2                       
 Session             :  xxxxx (1063:62091) 
 SQL ID              :  dmh5vhkcm877v           
 SQL Execution ID    :  33554437                
 Execution Started   :  04/02/2015 10:43:17     
 First Refresh Time  :  04/02/2015 10:43:20     
 Last Refresh Time   :  04/02/2015 10:47:38     
 Duration            :  261s                    
 Module/Action       :  wwwww
 Service             :  zzzzz
 Program             :  wwwww
 Fetch Calls         :  57790     

Global Stats
==============================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Calls |  Gets  | Reqs | Bytes |
==============================================================================
|     134 |     107 |     7.10 |          17 | 57790 |    18M | 7857 | 402MB |
==============================================================================

This is the dark side of using bind variable: when sharing resource we share also execution plan. The current daily job was running using the plan optimized for the monthly job. The solution was to force the CBO compiling a new execution plan for the new input bind variable. The new plan (2729107228) is still showing 200 operations and several operations started 578K times. I have the intention to study both execution plans to know exactly from where the enhancement is coming. The clue here might be that in the first shared monthly execution plan the query, for a reason I am unable to figure out, run serially

 DOP Downgrade       :  100%   

While the new hard parsed execution has been executed in parallel:

 Parallel Execution Details (DOP=4 , Servers Allocated=20)

Bottom Line: when you have the intention to run a critical report once per day (and once per month) then it is worth to let the CBO compiling a new execution plan for each execution. All what you will have is one hard parse for one execution. This will never hurt from a memory and CPU point of view

April 6, 2015

BIND_EQUIV_FAILURE – Or when you will regret using Adaptive Cursor Sharing

Filed under: Tuning — hourim @ 4:42 pm

Previously, when I was asked to define the Adaptive Cursor Sharing(ACS) feature I’ve often used the following definition: “it represents an answer to the always threating and challenging Oracle task of sharing cursors and optimizing SQL”.

Time passes and I have altered a little bit this definition to become: “it represents a short answer to the always threating and challenging Oracle task of sharing cursors and optimizing SQL’’.

Time passes again and I ended up by drastically altering my initial ACS definition to become:“In certain very plausible situations, It might represent a serious threat for your application where you will be happy to disable it provided you have enough experience to identify the link between ACS and your threat”.

If you want to know what has changed my mind about ACS, then follow this situation taken from a real life running system I am going to summarize:

When you see something like this in the library cache (11.2.0.3.0)

SQL> select
        sql_id
       ,count(1)
     from
        v$sql
     where executions < 2
     group by sql_id
     having count(1) > 10
     order by 2 desc;

SQL_ID          COUNT(1)
------------- ----------
7zwq7z1nj7vga      44217

You start wondering what makes this sql_id having such a big count of different versions in memory.

After few minutes of investigation you end up by ruling out the bind variable hypothesis. And then you finish by asking yourself what the heck is this sql_id?

Hopefully Tanel Poder nonshared script shed a small light on that:

SQL> @nonshared 7zwq7z1nj7vga
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000042CE36F7E8
CHILD_NUMBER         : 0
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>0</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                       <selectivity>1097868685</selectivity>
                      </ChildNode>

-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000045B5C5E478
CHILD_NUMBER         : 1
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>1</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>915662630</selectivity>
                      </ChildNode>
-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000038841E2868
CHILD_NUMBER         : 2
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>2</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>163647208</selectivity>
                      </ChildNode>
-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000038841E2708
CHILD_NUMBER         : 3
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>3</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>4075662961</selectivity>
                      </ChildNode>

…/…

-----------------
SQL_ID               : 7zwq7z1nj7vga
ADDRESS              : 000000406DBB30F8
CHILD_ADDRESS        : 00000045B5C5E5D8
CHILD_NUMBER         : 99
BIND_EQUIV_FAILURE   : Y
REASON               :<ChildNode><ChildNumber>99</ChildNumber><ID>40</ID>
                      <reason>Bindmismatch(33)</reason><size>2x4</size>
                      <init_ranges_in_first_pass>0</init_ranges_in_first_pass>
                      <selectivity>3246589452</selectivity>
                      </ChildNode>

Moreover a direct select on the v$sql_shared_cursor shows this:

SQL> select
       count(1)
     from
         v$sql_shared_cursor
     where
        sql_id = '7zwq7z1nj7vga';

  COUNT(1)
----------
     45125

SQL> select
        count(1)
     from
       v$sql_shared_cursor
     where
        sql_id = '7zwq7z1nj7vga'
     and BIND_EQUIV_FAILURE = 'Y';

  COUNT(1)
----------
     45121

Hmmm…. A huge count of non shared child cursors due to BIND_EQUIV_FAILURE.

The official Oracle documentation about BIND_EQUIV_FAILURE says : the bind value’s selectivity does not match that used to optimize the existing child cursor.This definition together with the selectivity xml tag mentioned above gave me a first clue: Adaptive Cursor Sharing (in this case Extended Cursor Sharing).

SQL> select
       count(1)
    from
        v$sql_cs_selectivity
    where
      sql_id = '7zwq7z1nj7vga';

  COUNT(1)
----------
  16,847,320

That is an impressive number of records in this dynamic view. For a single sql_id we have about 17 million of rows in this ACS monitoring view!!! This is dramatically altering the execution time of the underlying sql_id query.

If you don’t know what v$sql_cs_selectivity view stands for then look:

Once a cursor becomes bind aware, each time this cursor is executed, the Extended Cursor Sharing layer code peeks at the bind variable values (and in this particular case there are 9 bind variables), and execute, behind the scene, a select against v$sql_cs_selectivity view in order to check if any existing child cursor already covers the selectivity of the peeked bind variables. If a child cursor is found it will be shared. If not then a new child cursor is optimized and inserted into v$sql_cs_selectivity with a new range of bind variable value selectivity.

In this particular case each time the Extended Cursor Sharing layer code fails to find a child cursor in v$sql_cs_selectivity with an adequate range of selectivity(BIND_EQUIV_FAILURE) and compile a new execution plan ending up by filling dramatically v$sql view with multiple “optimal” plans.

We have been asked to use ACS to answer the need of sharing cursor and optimizing SQL. We end up by having neither the first nor the second desire in this particular and very plausible case.

Few extra words about this runing system case:

  • Query is using 9 bind variables in 9 different predicates
  • Columns on which bind variables are used have histograms(Frequency and Height Balanced) collected on them
  • Query is a simple select on a single heap table
  • Table has 4 indexes (which produces 4 distinct execution plans among those 99 ones)

 

April 1, 2015

12c Adaptive Cursor Sharing

Filed under: Oracle — hourim @ 7:21 pm

This is neither a 12c new feature you are still not aware of nor an extension of the 11 g Adaptive Cursor Sharing I am going to have the scoop to announce it. It is rather something I have pointed out when writing a complete chapter on Adaptive Cursor Sharing and that I wanted to share with you. Here we go.

11g Release

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

I have a simple query executed against two bind variables values. The first one favors an index range scan path while the second one desires a full table scan. The bind variable against which this query is run is related to a column having a frequency histogram collected on it so that when executed several times its underlying cursor starts being bind sensitive before ending up being bind aware following a warmup period. Once the cursor is bind aware, the Extended Cursor Sharing (ECS) layer code kicks in by peeking at the bind variable (there might be several ones), checking its selectivity and deciding whether to share an existing child cursor or compile a new one and update the v$sql_cs_selectivity dynamic view accordingly.

SQL> select
        sql_id
       ,child_number
       ,is_bind_aware
       ,is_bind_sensitive
       ,to_char(exact_matching_signature) sig
       ,executions
       ,plan_hash_value
    from v$sql
    where sql_id = '6fbvysnhkvugw'
    and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - -------------------- ---------- ---------------
6fbvysnhkvugw            3 Y Y 15340826253708983785    1       3625400295
6fbvysnhkvugw            4 Y Y 15340826253708983785    1       3724264953

In order to finish setting up the blog article scene, I have previously loaded the above two execution plans(plan_hash_value) from cursor cache into a SPM baseline so that I will pre-empt the CBO from using a plan I don’t accept.

SQL> declare
           rs pls_integer;
     begin
           rs := dbms_spm.load_plans_from_cursor_cache('6fbvysnhkvugw');
     end;
     /
PL/SQL procedure successfully completed.

The engineered model (ACS + SPM) is so that when I running the following query alternating between a ”full table scan” bind variable value and an ”index range scan”  one I got the following picture:

SQL> select count(*), max(col2) from t1 where flag = 'N1';-- full table scan

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50135 |  2643K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

And

SQL> select count(*), max(col2) from t1 where flag = 'Y1'; -- index range scan

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     9 |   486 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     9 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FLAG"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg98576eb1f used for this statement

After a short period of ACS warmup which depends on the number of executions we start observing a perfect harmony between ACS and SPM: an index range scan plan for an ‘’index range scan’’ bind variable and a full table scan plan for a ‘’full table scan’’ bind variable.

However, it suffices to create an extra index and this perfect harmony ceases to work as shown below:

SQL> create index i2 on t1(flag,col2);

SQL> select count(*), max(col2) from t1 where flag = 'N2';

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49846 |  2628K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

SQL> select plan_name
    from dba_sql_plan_baselines
    where accepted = 'NO';

PLAN_NAME
------------------------------
SQL_PLAN_d9tch6banyzg9495f4ddb

The CBO has come up with a new execution plan(index fast full scan) which has been constrained by the full table scan SPM plan. This new CBO plan looks like:

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_d9tch6banyzg9495f4ddb'));

--------------------------------------------------------------------------------
SQL handle: SQL_d4e59032d54f7de9
SQL text: select count(*), max(col2) from t1 where flag = :"SYS_B_0"
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d9tch6banyzg9495f4ddb         Plan id: 1230982619
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2348726875
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    54 |   249   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE       |      |     1 |    54 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I2   | 25000 |  1318K|   249   (1)| 00:00:03 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)

Let’s now execute the query with a bind variable favoring an index range scan

SQL> select count(*), max(col2) from t1 where flag = 'Y2'; -- index range scan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49846 |  2628K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

The index range scan plan has not been used. May be it still needs a warm up period?

SQL> select count(*), max(col2) from t1 where flag = 'Y2';
SQL> /
SQL> /
SQL> /
SQL> /
SQL> /

SQL> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49846 |  2628K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

SQL> select
          sql_id
         ,child_number
         ,is_bind_aware
         ,is_bind_sensitive
         ,to_char(exact_matching_signature) sig
         ,executions
         ,plan_hash_value
     from v$sql
     where sql_id = '6fbvysnhkvugw'
     and is_shareable = 'Y'
     ;

SQL_ID        CHILD_NUMBER I I SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - -------------------- ---------- ---------------
6fbvysnhkvugw            3 N N 15340826253708983785  10      3724264953

10 executions later and the switch to the index range scan didn’t occured.

Bizarrely, it suffices to disable the use of sql_plan_baselines and the plan switch occurs immediately

SQL> alter session set optimizer_use_sql_plan_baselines = FALSE;

SQL> select count(*), max(col2) from t1 where flag = 'Y2';

Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |   972 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FLAG"=:SYS_B_0)

Set back the use of sql_plan_baselines and the plan switch will cease to happen immediately as well

SQL> alter session set optimizer_use_sql_plan_baselines = TRUE;

SQL> select count(*), max(col2) from t1 where flag = 'Y2';

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49846 |  2628K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:SYS_B_0)

Note
-----
- SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

12c Release

Repeat exactly the same experiment in 12c and you will realize that things have changed

SQL> select * from v$version where rownum =1;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- ------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production   0

SQL> create index i2 on t1(flag,col2);

SQL> select count(*), max(col2) from t1 where flag = 'N2';

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49874 |  2630K|   273   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:SYS_B_0)

Note
-----
- SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

SQL> select count(*), max(col2) from t1 where flag = 'Y2';
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49874 |  2630K|   273   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:SYS_B_0)

Note
-----
- SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

Let’s see now if a second execution of the same query with a bind variable favoring index range scan will switch to an index range scan plan


SQL> select count(*), max(col2) from t1 where flag = 'Y2';

----------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|
|   1 |  SORT AGGREGATE                      |      |     1 |    54 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    54 |     2   (0)|
|*  3 |    INDEX RANGE SCAN                  | I1   |     1 |       |     1   (0)|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:SYS_B_0)

Note
-----
- SQL plan baseline SQL_PLAN_d9tch6banyzg97823646b used for this statement

And the plan switch happens.

Something new has been introduced in Oracle 12c which has dramatically enhanced the collaboration between ACS and SPM.

In the 11g investigation I’ve executed the same query 10 times without provoking a plan switch. In 12c, I have executed the same query with the ”index range scan” bind variable as many times as the number of executions done with the “full table scan” bind variable (2 executions) to see the ACS kicking off as shown below:

<pre>SQL> select
      sql_id
     ,child_number
     ,is_bind_aware
     ,is_bind_sensitive
     ,to_char(exact_matching_signature) sig
     ,executions
     ,plan_hash_value
    from v$sql
    where sql_id = '6fbvysnhkvugw'
    and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - -------------------- ---------- ---------------
6fbvysnhkvugw            1 Y Y 15340826253708983785     1      3724264953
6fbvysnhkvugw            2 Y Y 15340826253708983785     2       497086120
6fbvysnhkvugw            4 Y Y 15340826253708983785     1       497086120
6fbvysnhkvugw            6 Y Y 15340826253708983785     1      3724264953

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)