Explore SQL Profiles - Part I

Suppose we identified a problem SQL whose access path is not optimal and we want to tune it, however we can not modify the text of the SQL. In this case, we can use SQL profiles (10g feature) to guide Oracle to choose optimal execution plan. In this post, I am going to describe a test case in order to be familiar with the use of SQL profile.

First of all, I created a test table (the test case in based on ML 271196.1 Automatic SQL Tuning - SQL Profiles)

create table test (n number);
declare
begin
  for i in 1 .. 20000
  loop
     insert into test values(i);
     commit;
  end loop;
end;
/
create index test_idx on test(n);
exec dbms_stats.gather_table_stats(user, 'TEST');

The problem SQL is as follows:

select /*+ no_index(test test_idx) */ * from test where n=1;

We know the best way to access the table TEST with predicate n=1 is through index scan, however due to the no_index hint we will have FTS. We are going to see with SQL profile, Oracle can choose index scan and ignore this hint.


Summary of the steps:

Step 1: Create a tuning task
Step 2: Execute the tuning task
Step 3: Report the tuning task
Step 4: Check SQL profile rationale
Step 5: Accept the SQL profile
Step 6: Verify SQL profile is used for the problem SQL
Step 7: Disable and Enable the SQL profile
Step 8: Modify category attribute of SQL profile
Step 9: Query DBA_SQL_PROFILES


Step 1: Create a tuning task

SQL>
SQL> declare
  2   my_task_name varchar2(30);
  3   my_sqltext clob;
  4  begin
  5   my_sqltext :='select /*+ no_index(test test_idx) */ * from test where n=1';
  6   my_task_name := dbms_sqltune.create_tuning_task (
  7      sql_text  => my_sqltext,
  8      user_name => 'Z983294',
  9      scope     => 'COMPREHENSIVE',
 10      time_limit => 60,
 11      task_name => 'my_sql_tuning_task_3',
 12      description => 'Task to tune a query on a specified table');
 13  end;
 14  /

PL/SQL procedure successfully completed.

Step 2: Execute the tuning task

SQL> begin
  2   dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_3');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Step 3: Report the tuning task

SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_3') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_3')
--------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task_3
Tuning Task Owner                 : Z983294
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 06/30/2009 05:24:39
Completed at                      : 06/30/2009 05:24:40
Number of SQL Profile Findings    : 1

--------------------------------------------------------------------------
Schema Name: Z983294
SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

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

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

  Recommendation (estimated benefit: 89.76%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_3', replace => TRUE);

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

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

------------------------------------------------------------------------
 Id   Operation          Name  Rows   Bytes  Cost (%CPU) Time
-------------------------------------------------------------------------
*  1   TABLE ACCESS FULL TEST      1      4     10  (10) 00:00:01
-------------------------------------------------------------------------

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

   1 - filter("N"=1)

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

-------------------------------------------------------------------------
 Id   Operation         Name     Rows  Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT              1      4     1   (0) 00:00:01
*  1   INDEX RANGE SCAN TEST_IDX    1      4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=1)

Step 4: Check SQL profile rationale

SQL>
SQL> select
  2   rat.attr1
  3  from
  4   sys.wri$_adv_tasks      tsk,
  5   sys.wri$_adv_rationale      rat
  6  where
  7        tsk.name = 'my_sql_tuning_task_3'
  8  and     rat.task_id   = tsk.id;

ATTR1
--------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS

Step 5: Accept the SQL profile

SQL>
SQL> -- Accept the tuning profile
SQL> -- SQLs different only in case and space are treated as same
SQL> -- in applying the SQL profile. If  force_match = TRUE is specified,
SQL> -- differences in literals are also ignored
SQL>
SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3  BEGIN
  4    my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5      task_name => 'my_sql_tuning_task_3',
  6      name      => 'my_sql_profile',
  7      force_match => TRUE);
  8    END;
  9  /

PL/SQL procedure successfully completed.

Step 6: Verify SQL Profile is used for the problem SQL

SQL> -- Index scan due to sql profile
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL_ID  apxdba96mcfqt, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=1

Plan hash value: 1416057887

-------------------------------------------------------------------------
 Id   Operation         Name     Rows  Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT                           1 (100)     
*  1   INDEX RANGE SCAN TEST_IDX     1     4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


22 rows selected.

SQL>
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 1416057887

-------------------------------------------------------------------------
 Id   Operation         Name      Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------
   0  SELECT STATEMENT                           1 (100)     
*  1   INDEX RANGE SCAN TEST_IDX      1    4     1   (0) 00:00:01
-------------------------------------------------------------------------

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

   1 - access("N"=9)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Step 7: Disable and Enable the SQL profile

SQL> -- disable the profile
SQL> BEGIN
  2     DBMS_SQLTUNE.ALTER_SQL_PROFILE(
  3       name     => 'my_sql_profile',
  4      attribute_name  => 'STATUS',
  5      value    => 'DISABLED');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- FTS due to profile is disabled
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 217508114

----------------------------------------------------------------------
 Id   Operation          Name Rows  Bytes Cost (%CPU) Time
----------------------------------------------------------------------
   0  SELECT STATEMENT                       10 (100)     
*  1   TABLE ACCESS FULL TEST     1     4    10  (10) 00:00:01
----------------------------------------------------------------------

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

   1 - filter("N"=9)


18 rows selected.

SQL>
SQL> -- enable the profile
SQL> BEGIN
  2     DBMS_SQLTUNE.ALTER_SQL_PROFILE(
  3       name     => 'my_sql_profile',
  4      attribute_name  => 'STATUS',
  5      value    => 'ENABLED');
  6  END;
  7  /

PL/SQL procedure successfully completed.

Step 8: Modify category attribute of SQL profile

SQL> -- Modify the category to TEST
SQL> --  category default value: 'DEFAULT'
SQL>
SQL> begin
  2   DBMS_SQLTUNE.ALTER_SQL_PROFILE (
  3      name     => 'my_sql_profile',
  4      attribute_name  =>  'CATEGORY',
  5      value    =>  'TEST');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> -- FTS after sql profile's category is modified
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 0
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 217508114

---------------------------------------------------------------------
 Id   Operation          Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------
   0  SELECT STATEMENT                      10 (100)     
*  1   TABLE ACCESS FULL TEST     1    4    10  (10) 00:00:01
---------------------------------------------------------------------

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

   1 - filter("N"=9)


18 rows selected.

SQL>
SQL>
SQL> alter session set sqltune_category='TEST';

Session altered.

SQL>
SQL> -- Index scan after change the session's sqltune_category
SQL> select /*+ no_index(test test_idx) */ * from test where n=9;

         N
----------
         9

SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID  7daa3xa9mxy0f, child number 1
-------------------------------------
select /*+ no_index(test test_idx) */ * from test where n=9

Plan hash value: 1416057887

-----------------------------------------------------------------------
 Id   Operation         Name     Rows Bytes Cost (%CPU)Time
-----------------------------------------------------------------------
   0  SELECT STATEMENT                          1 (100)    
*  1   INDEX RANGE SCAN TEST_IDX     1    4     1   (0)00:00:01
-----------------------------------------------------------------------

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

   1 - access("N"=9)

Note
-----
   - SQL profile "my_sql_profile" used for this statement

Step 9: Query DBA_SQL_PROFILES

SQL> -- Query dba_sql_profiles
SQL> col name format a10
SQL> col category format a10
SQL> col sql_text format a30
SQL> col force_matching format a5
SQL>
SQL> select name, category,sql_text,force_matching from dba_sql_profiles;

NAME       CATEGORY   SQL_TEXT                       FORCE
---------- ---------- ------------------------------ -----
my_sql_pro TEST       select /*+ no_index(test test_ YES
file                  idx) */ * from test where n=1

What if Oralce does not recommend a SQL Profile to the problem SQL? And we know there is a better access path? This is what I will explore next about SQL profiles.

Go to Part II

Update by ROWID and using bulk collection

From time to time, DBA team is asked to do "scrubs" against our on line database. Sometimes, these scrubs can be very large updates. Usually the database is required to be online and accessible for users all the time. From my observation, our development team seems liking update-one-row-at-a-time approach very much. In their code handed over to us, typically they build driver tables based on some ids (most likely primary keys) and then update the target tables looping through the driver tables and commit every certain number of rows. From my recent research,there are probably two approaches that worth considering in order to improve the performance of large scrubs.

1. Update/delete based on ROWID.
Rowid access is the most efficient access path in Oracle. Here is a nice post at ITPUB (in Chinese) demonstrating this concept with a live production example.

2. Bulk collection feature

Below is a simple code example for using ROWID based driver table and bulk collection:

declare
   cursor c is
     select rowid id from t where val=0 order by rowid;
   counter number :=0;

   rowids dbms_sql.urowid_table;

begin
   open c;
   loop
      fetch c bulk collect into rowids limit 1000;
      forall i in 1..rowids.count
         update t set val=1
              where rowid = rowids(i);
      commit;
      counter := counter + 1;
      dbms_output.put_line('Updated batch '  counter   );
      exit when c%notfound;
   end loop;
   close c;
end;
/

Nested loop join puzzle - unique scan or range scan?

In a 9.2.0.8 database, I puzzled about a nested loop join execution plan which showed index range scan on the unique index of the inner table. After reading the Chapter 11 "Nested loops" of Jonathan Lewis's execllent book "Cost-Based Oracle Fundamentals", things are clear.

There could be two forms of the nested loop join. The classic form shows unique scan on the index of the inner table, whereas the new form shows range scan. Oracle can switch from one to another depending on the number of rows of the driving table. Below is the execution plan (new form) of a query from the sql_trace/tkprof (object names are modified as usual):

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        7     57.61    1585.67     153862     379507          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9     57.62    1585.67     153862     379507          0          81

Rows     Row Source Operation
-------  ---------------------------------------------------
81  TABLE ACCESS BY INDEX ROWID SMT
188718   NESTED LOOPS
94359    TABLE ACCESS FULL SIFD
94358    INDEX RANGE SCAN PK_SMT (object id 227634)

The CPU costing model has to be enabled for Oracle to pick up this new form. We can verify it by the following query

SQL> select pname, pval1
2 from sys.aux_stats$
3 where sname = 'SYSSTATS_MAIN';

PNAME PVAL1
------------------------------ ----------
SREADTIM 12.052
MREADTIM 10.325
CPUSPEED 560
MBRC 7
MAXTHR 115623936
SLAVETHR 31744


To test the classic form, I issued the following command to turn off the CPU costing.

SQL> alter session set "_optimizer_cost_model"=io;

The following was obtained from the sql_trace/tkprof for the classic nested loop execution plan:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        7     54.98    2268.90     160543     383987          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9     54.98    2268.90     160543     383987          0          81


Rows     Row Source Operation
-------  ---------------------------------------------------
81  NESTED LOOPS
94178   TABLE ACCESS FULL SIFD
81   TABLE ACCESS BY INDEX ROWID SMT
94177    INDEX UNIQUE SCAN PK_SMT (object id 227634)

The run time of the classic form is about 39 min as compared to 28 min of the new form. Noticed the total IIO is 544,530 vs 533,369. The slight difference of the number of rows of table SIFD seen in the tkprof report should be due to execution at different time (last afternoon and this morning for new form and classic form respectively).

GATHER AUTO != GATHER EMPTY + GATHER STALE

GATHER AUTO, GATHER EMPTY and GATHER STALE are the possible values of a parameter called OPTIONS in the DBMS_STATS.GATHER_DATABASE_STATS and DBMS_STATS.GATHER_SCHEMA_STATS procuedures. Their meanings have acutally been documented well. From the doc:

GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.

GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.

GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.

However DBA made a mistake. In a 10.2.0.4 production database, we execute following statement every four hour:

DBMS_STATS.gather_database_stats( ESTIMATE_PERCENT => 30, CASCADE=>TRUE,
OPTIONS=> 'GATHER AUTO', DEGREE=> 6, METHOD_OPT=> 'FOR ALL COLUMNS SIZE 1');

Now I understand with OPTIONS=> 'GATHER AUTO' in this statement, Oracle just ignores other settings. The results are Oracle uses parallel degree 32 (we have 16 CPUs) and Oracle collects histograms for some columns, which are certainly not our intentions. The problem becomes visible due to the execution of this statement causing CPU spike up to 100% in a short period of time (Normal CPU utilization ~10%) . Monitoring tool captures this spike and in turn causes upset at the management layer. In coincidence with an application problem occurred yesterday, the spike was pointed by every finger, although it has been there for quite a long time and no hard evidence it caused any problems. DBA now changed 'GATHER AUTO' to 'GATHER STALE'.

Remember everything is connected when troubleshooting database issue

Yesterday (Monday) users of a reporting database experienced slowness in extracting data from the database. They indicated that at the speed of current extracting, they would miss the business requirement to deliver the data to executives. I was called to check the database. I noticed their sessions were waiting for "buffer busy wait" event frequently, other than that nothing seemed abnormal in the database. Later, several sessions finished. "Buffer busy wait" become not outstanding. However, one of their major job was still running. Another job errored out with ORA-1555 snapshot too old eventually. They noticed the volume of data that they extracted was larger than usual.

Discussing for a while among users and DBAs, finally we realized that this was due to a large scrub that was carried out against the online production database by me per development team's request last Sunday night. Our online production database is replicated to the reporting database. In that scrub, one DATE type column of a table was updated to SYSDATE', whereas the reporting database users extract their data based on the value of that column. This caused the increased volume of data because more records now fall into the range of time that needs to be extracted.

I felt lucky secretly that I was able to provide the log file of that scrub with starting time and ending time information. This is of course not something I should be proud of. We should always have logs as detail as possible of whatever changes we make against a database, right?. The truth is in my current working environment, DBA is always handed over scripts without taking this into consideration. So if DBA does not take extra step to modify the script to generate useful log information , he could be in trouble if something goes wrong. The scrub took more than 8 hours from about 9:30 PM Saturday to 5:40 AM Sunday. So we knew what was the range of values that the DATE column has when it was updated to 'SYSDATE'. The users decided to exclude that range of data as a temporary fix for their Monday's report. Development team was requested to review their scrubs (i.e. Why updating the DATE column? Initial discussion gave me impression that this was not necessary), since we have even larger scrubs yet to complete of this kind.

Why you need to know a job's LOG_USER when using DBMS_JOB?

Log in as SYS, I executed the following command to change a job's attributes, however, I recieved ORA-23421 error.

sys@MYDB> begin
dbms_job.change(
1001,
what=>'BEGIN DBMS_STATS.gather_database_stats(ESTIMATE_PERCENT => 30, CASCADE=>TRUE, OPTIONS=> ''GATHER STALE'', DEGREE=> 7,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1''); END;',
next_date=> sysdate+1,
interval=> 'trunc(sysdate+1)+1/48'
);
end;
/ 2 3 4 5 6 7 8 9
begin
*
ERROR at line 1:
ORA-23421: job number 1001 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 207
ORA-06512: at "SYS.DBMS_JOB", line 188
ORA-06512: at line 2


Later, I learned that every job has a LOG_USER attribute, which is the login user when the job is submitted. Even SYS can not change other user's job:


sys@MYDB> ho oerr ora 23421
23421, 00000, "job number %s is not a job in the job queue"
// *Cause: There is no job visible to the caller with the given job number.
// *Action: Choose the number of a job visible to the caller.
//

sys@MYDB> select job, log_user from dba_jobs;

JOB LOG_USER
---------- ------------------------------
... ...
1001 OPS$ORACLE


After log in as OPS$ORACLE, the above statement got executed successfully.

ALTER SESSION SET CURRENT_SCHEMA

Recently I have learned how to use ALTER SESSION SET CURRENT_SCHEMA=schmea. This setting makes subsequent unqualified references to schema objects during the session resolve to objects in the specified schema.

Sometimes, developers give us scripts without qualifying the objects with the schema name, and We DBA usually execute the scripts in our own database user account. So this setting provides a convenient way to resolve this issue, otherwise we may have to modify the script to add schema names. Before I tend to complain about it and emphasize developers should have used qualified name. Actaully, I learned this trick from a developer.

A concern is thus how can I know what is my CURRENT_SCHMEA setting? If I am not careful, I may end up drop a table t that belongs to another schema. It is not difficult to find the answer by google.

The following is the query:

z98xxx4@DBT920> ho cat sess_user_sch.sql
col current_user format a15
col current_schema format a15

select sys_context('USERENV','SESSION_USER') current_user,
sys_context('USERENV','SESSION_SCHEMA') current_schema
from dual
/


z98xxx4@DBT920> create table denis.t as select 'denis' name from dual;

Table created.

z98xxx4@DBT920> create table z98xxx4.t as select 'z98xxx4' name from dual;

Table created.
z98xxx4@DBT920> @sess_user_sch.sql
CURRENT_USER CURRENT_SCHEMA
--------------- ---------------
Z98xxx4 Z98xxx4

z98xxx4@DBT920> select * from t;

NAME
------------------------------
z98xxx4

z98xxx4@DBT920> alter session set current_schema=denis;

Session altered.

z98xxx4@DBT920> @sess_user_sch.sql
CURRENT_USER CURRENT_SCHEMA
--------------- ---------------
Z98xxx4 DENIS

z98xxx4@DBT920> select * from t;

NAME
------------------------------
denis

A case of wrong cardinality estimation by CBO

A developer handed over us a query, asking us to run in the report database. There is a predicate in the query for the table 'om', which looks like:

AND om.order_id = SUBSTR (om.order_id,1, 13)

The problem is that CBO can not correctly estimate the cardinality from such a predicate. (It showed cardinality equals 1, CBO choose this table as the build table in the hash join )

Below is a simple test to demonstrate this point:

1. Create and analyze a test table

DB10G> create table t(a varchar2(10));

Table created.

DB10G>
DB10G>
DB10G> begin
  2  for i in 1..1000
  3  loop
  4    insert into t values(dbms_random.string('x', mod(i,10) + 1 ));
  5
  6  end loop;
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

DB10G>
DB10G>
DB10G> exec dbms_stats.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

2. Case 1 - Predicate: a=substr(a, 1, 5)

In this case, we can see CBO estimates cardinality to be 1, however, the actual number of row is 500

DB10G> select count(* ) from t where a=substr(a, 1, 5);

  COUNT(*)
----------
       500


Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1      6      3   (0) 00:00:01
   1   SORT AGGREGATE               1      6                
*  2    TABLE ACCESS FULL T         1      6      3   (0) 00:00:01
---------------------------------------------------------------------------

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

   2 - filter("A"=SUBSTRA",1,5))

3. Case 2 - length(a) <=5 I have suggested use length function, but it still can not result in good estimation of the cadinality in the problem query. In my test, the carnality and the actual are different by one order of magnitude.

DB10G> select count(*) from t where length(a) <=5 ; 
COUNT(*)
----------     
500
Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362
---------------------------------------------------------------------------
Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time 
---------------------------------------------------------------------------
 0  SELECT STATEMENT              1      6      3   (0) 00:00:01 
 1   SORT AGGREGATE               1      6                  
*2    TABLE ACCESS FULL T        50    300      3   (0) 00:00:01
---------------------------------------------------------------------------  Predicate Information (identified by operation id):
--------------------------------------------------- 
 2 - filter(LENGTH("A")<=5)

Finally, I suggested to use /*+ ordered */ hint to force Oracle choose the table 'om' as the probe table in the hash join, instead of as a build table by default. The table 'om' is relatively big with approximately 37M rows. I also suggested to add parallel hint. The query was able to be finished in about 3.5 hours.

Different explain-time and run-time execution plans due to parallel degree setting of a table

I have recently faced a query that has different explain-time and run-time execution plans due to parallel degree setting of a table.

The query involvs joining three tables, SEED, SSD and SOD. The execution plan shown by AUTOTRACE is as follows:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=1 Bytes=124)
   1    0   SORT (UNIQUE) (Cost=21 Card=1 Bytes=124)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SEED' (Cost=12 Card=1 Bytes=38)
   3    2       NESTED LOOPS (Cost=20 Card=1 Bytes=124)
   4    3         NESTED LOOPS (Cost=9 Card=1 Bytes=86)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'SOD' (Cost=6 Card=1 Bytes=54)
   6    5             INDEX (RANGE SCAN) OF 'RECV_DATE' (NON-UNIQUE) (Cost=4 Card=3)
   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'SSD' (Cost=4 Card=1 Bytes=32)
   8    7             INDEX (RANGE SCAN) OF 'SSD_UQ1' (UNIQUE) (Cost=3 Card=1)
   9    3         INDEX (RANGE SCAN) OF 'SEED_IDX11' (NON-UNIQUE) (Cost=4 Card=3)

It looks like a simple plan and the optimzer statistics is up-to-date. However, when run in a 9i database, it runs 2 hours without return before being killed.

When I checked the run-time execution plan, I found it used parallel execution (truncated some middle columns):

--------------------------------------------------------------~~~------------------------------
 Id   Operation                          Name     Rows   ~~~  TQ    IN-OUT PQ Distrib
--------------------------------------------------------------~~~-----------------------------
   0  SELECT STATEMENT                                   ~~~                       
   1   SORT UNIQUE                                33174  ~~~                       
   2    FILTER                                           ~~~                       
   3     HASH JOIN                                33174  ~~~ 72,02   P->S  QC (RAND)
   4      TABLE ACCESS BY INDEX ROWID   SSD           1  ~~~ 72,02   PCWP          
   5       NESTED LOOPS                           31793  ~~~ 72,02   PCWP          
   6        TABLE ACCESS BY INDEX ROWID SOD       33786  ~~~ 72,02   PCWP          
   7         INDEX RANGE SCAN           RECV_DATE   266K ~~~ 72,02   PCWP          
   8        INDEX RANGE SCAN            SSD_UQ1       1  ~~~ 72,02   PCWP          
   9      TABLE ACCESS FULL             SEED       2566K ~~~ 72,01   P->P  HASH    
--------------------------------------------------------------~~~------------------------------

From the dba_tables, I can see the "DEGREE" of the table SEED is 'DEFAULT'. This setting is questionable.

Oracle will use the instance-level defaults whenever the keyword DEFAULT is used in a hint or in a table or index definition.Oracle also will use the instance-level defaults when there are no hints and when no degree of parallelism has been specified at the table or index level. Check this to know hwo Oracle determines the default degree of a table.

I tested with hint: no_parallel(SEED), not helpful, Oracle still uses parallel execution plan. When using parallel(SEED, 1), the query returns in less than a second.