How to Move SQL Profiles from One Database to Another

Solution
What is a SQL Profile?
Managing SQL Profiles
Steps to Create and Transfer Profile from One Database to Another
1. Create SQL Profile in SCOTT schema
2. Creating a staging table to store the SQL Profiles
3. Pack the SQL Profiles into the Staging Table
4. Export the Staging Table to the Target Database
4a. Export from Source Database
4b. Import into Target Database
5. Unpack the SQL Profiles
5a. Test before unpacking
5b. Unpack Staging Table
6. Check the SQL Profile is enabled in Target Database

Solution

What is a SQL Profile?

 

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Managing SQL Profiles

 

For information on SQL Profiles see:

 

Document 271196.1 Automatic SQL Tuning – SQL Profiles

Steps to Create and Transfer Profile from One Database to Another

 

The following example illustrates the process of moving a SQL Profile from 10.2.0.4.0 to 11.2.0.3.0

1. Create SQL Profile in SCOTT schema

 

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:

 

DECLARE 
my_task_name VARCHAR2(30);
my_sqltext CLOB; 
my_sqlprofile_name VARCHAR2(30); 

BEGIN 
  my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839'; 
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, 
        user_name => 'SCOTT', 
        scope => 'COMPREHENSIVE', 
        time_limit => 60, 
        task_name => 'my_sql_tuning_task', 
        description => 'Demo Task to tune a query'); 
  
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task'); 

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>
             'my_sql_tuning_task', name => 'my_sql_profile'); 
END; 
/

PL/SQL procedure successfully completed. 

 

 

 

set lines 130
set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7839)

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

 

 

 

Note: Even though no_index hint included, the plan uses an index as determined by the SQL profile. The Note section provides plan information  that indicates that  “my_sql_profile” is used.

2. Creating a staging table to store the SQL Profiles

 

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.

 

  • table_name => name of the table to store the SQL Profiles.
  • schema_name => name of the schema where the table is to be created.

3. Pack the SQL Profiles into the Staging Table

 

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');

PL/SQL procedure successfully completed.

 

  • staging_table_name => name of the table to store the SQL Profiles.
  • profile_name => name of the SQL Profile to be packed.

 

Note: The table_name and schema_name are case-sensitive.

 

SQL> desc STAGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE_NAME                                       VARCHAR2(30)
 CATEGORY                                           VARCHAR2(30)
 SIGNATURE                                          NUMBER
 SQL_TEXT                                           CLOB
 DESCRIPTION                                        VARCHAR2(500)
 TYPE                                               VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 BOOLEAN_FLAGS                                      NUMBER
 ATTRIBUTES                                         SQLPROF_ATTR
 VERSION                                            NUMBER
 SPARE1                                             CLOB
 SPARE2                                             BLOB

4. Export the Staging Table to the Target Database

 

Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.

4a. Export from Source Database

 

my_linux_1:~> exp scott/tiger tables=STAGE

Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012

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, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                          STAGE          1 rows exported
Export terminated successfully without warnings.

4b. Import into Target Database

 

my_linux_1:~> imp scott/tiger tables=STAGE

Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                        "STAGE"          1 rows imported
Import terminated successfully with warnings.

5. Unpack the SQL Profiles

5a. Test before unpacking

 

SQL> set lines 130
SQL> set autotrace on
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7839)

 

 

 

Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

5b. Unpack Staging Table

 

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');

PL/SQL procedure successfully completed.

 

 

6. Check the SQL Profile is enabled in Target Database

 

set lines 130
set autotrace on

select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7839)

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

 

Advertisements

Slightly improve performance using index_asc or index_desc hints

You can use index_asc and index_desc hints if you want to instruct optimizer to explicitly choose index scan for specified table. If you use index_asc hint Oracle scans index entries in ascending order. If you use index_desc Oracle will scan index entries in descending order.

I’ll show you in my simple example how to slightly improve performance using one of this hints.

For my example I will create “big_table” using Tom Kyte’s script as usual.

You can use index_asc and index_desc hints if you want to instruct optimizer to explicitly choose index scan for specified table. If you use index_asc hint Oracle scans index entries in ascending order. If you use index_desc Oracle will scan index entries in descending order.

I’ll show you in my simple example how to slightly improve performance using one of this hints.

For my example I will create “big_table” using Tom Kyte’s script as usual.

 

Creating big_table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;
declare
l_cnt number;
l_rows number := 1000000;
begin
insert /*+ append*/
into big_table
select rownum, a.*
from all_objects a
where rownum <= 1000000;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+APPEND */ into big_table
select rownum+l_cnt,
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID, DATA_OBJECT_ID,OBJECT_TYPE,
CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
from big_table
where rownum <= l_rows - l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

I will create index on object_id column and gather statistics.

1
2
3
4
5
6
7
8
9
10
create index i_objectid on big_table(object_id);
begin
dbms_stats.gather_table_stats
( ownname    => user,
tabname    => 'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade    => TRUE );
end;
/

It important to mention that Oracle uses index_asc by default so I’ll concentrate my example on index_desc hint. Suppose I have a query that scans table for specified range and prints result in descending order.

1
2
3
select  * from big_table t
where object_id between 30000 and 60000
order by object_id desc;

First I will use index_asc hint to gther data. I’ve flushed buffer for testing purpose.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
msutic@TEST11> set timing on
msutic@TEST11> set autot traceonly
msutic@TEST11>
msutic@TEST11>
msutic@TEST11> select /*+ index_asc(t i_objectid) */ * from big_table t
where object_id between 30000 and 60000
order by object_id desc
4  /
429740 rows selected.
Elapsed: 00:01:35.59
Execution Plan
----------------------------------------------------------
Plan hash value: 4044193925
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   431K|    43M|       |   441K  (1)| 00:26:34 |
|   1 |  SORT ORDER BY               |            |   431K|    43M|    60M|   441K  (1)| 00:26:34 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE  |   431K|    43M|       |   432K  (1)| 00:26:01 |
|*  3 |    INDEX RANGE SCAN          | I_OBJECTID |   431K|       |       |   972   (2)| 00:00:04 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">=30000 AND "OBJECT_ID"<=60000)
Statistics
----------------------------------------------------------
763  recursive calls
11  db block gets
430823  consistent gets
19018  physical reads
0  redo size
5740226  bytes sent via SQL*Net to client
9826  bytes received via SQL*Net from client
861  SQL*Net roundtrips to/from client
10  sorts (memory)
1  sorts (disk)
429740  rows processed

It lasted 1min and 35.59 secs. Notice “SORT ORDER BY” – as resultset was not already sored Oracle used explicit sort to sort output by descending order and that took some time. But what will happen when I use index_desc hint to get resultset sorted in descending order.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
msutic@TEST11> select /*+ index_desc(t i_objectid) */ * from big_table t
where object_id between 30000 and 60000
order by object_id desc
4  /
429740 rows selected.
Elapsed: 00:01:12.21
Execution Plan
----------------------------------------------------------
Plan hash value: 2071862395
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   431K|    43M|   432K  (1)| 00:26:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | BIG_TABLE  |   431K|    43M|   432K  (1)| 00:26:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| I_OBJECTID |   431K|       |   972   (2)| 00:00:04 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=30000 AND "OBJECT_ID"<=60000)
Statistics
----------------------------------------------------------
707  recursive calls
0  db block gets
431681  consistent gets
11957  physical reads
0  redo size
5740226  bytes sent via SQL*Net to client
9826  bytes received via SQL*Net from client
861  SQL*Net roundtrips to/from client
10  sorts (memory)
0  sorts (disk)
429740  rows processed

This execution lasted 1 min and 12.21 secs whitch is slightly faster then execution before. As you can see, in this example I don’t have SORT ORDER BY explicit sort because the resultset was already sorted. Query took advantage of descending index scan to get resultset in descending order. This was just simple example to illustrate how to gain performance benefit using index hints. In many cases it is useless to use this hints but still i made demo case just to try out simple scenario.

 

SQL Plan Management -2-

1. Optimizer_capture_sql_plan_baselines              2. Optimizer_use_sql_plan_baseline 

As known “optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines.”

Does it mean it will not capture any plan for any sql if set to false ?

What if we want to capture baselines for only selected queries automatically.

The whole answer lies in values of “ENABLED,ACCEPTED AND FIXED” column if dba_sql_plan_baselines. How? Lets find out…

As usual, lets create a table and insert few records.

Session1>show parameter capture

NAME                                 TYPE        VALUE

———————————— ———– ——————————

optimizer_capture_sql_plan_baselines boolean     FALSE

create table test (name varchar2(10), id number(*));

begin

for i in 1..1000

loop

insert into test values (‘SUMIT’,i);

end loop;

commit;

end;

/

begin

for i in 1001..10000

loop

insert into test values (‘BHATIA’,i);

end loop;

commit;

end;

/

begin

for i in 10001..100000

loop

insert into test values (‘BASELINE’,i);

end loop;

commit;

end;

/

begin

for i in 100001..1000000

loop

insert into test values (‘SPM’,i);

end loop;

commit;

end;

/

SELECT COUNT(*),name from test group by name order by 1;

COUNT(*) NAME

———- ———-

1000 SUMIT

9000 BHATIA

90000 BASELINE

900000 SPM

4 rows selected.

Time to query table and load the plan into the baseline.

–Inserting hint to easily identify the query

Select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

select sql_id,exact_matching_signature,force_matching_signature from v$sql where sql_text like ‘%id=1000%’;

SQL_ID                EXACT_MATCHING_SIGNATURE         FORCE_MATCHING_SIGNATURE

————- ——————————– ——————————–

duk2ypk5fz9g6              7784548270786280511              4428329137525653294

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

———————————————————————-

SQL_ID  duk2ypk5fz9g6, child number 0

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

———————————-

| Id  | Operation         | Name |

———————————-

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| TEST |

———————————-

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– rule based optimizer used (consider using cbo)

–Creating the baseline for the plan

var v_num number;

exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => ‘duk2ypk5fz9g6’,plan_hash_value => 1357081020 );

select sql_handle, plan_name, enabled, accepted,fixed from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

—————————— —————————— — — —

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO

As we can see, If we load the plan manually, that will plan will be enabled and accepted but not fixed.  Its time create an index and run the query again.

create index test_id on test(id);

Index created.

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

Oracle is still using SYS_SQL_PLAN_7118fc3f97bbe3d0 baseline.

However, if we query dba_sql_plan_baselines, we will see that oracle has started capturing baselines for this sql. (plan name SYS_SQL_PLAN_7118fc3f642e4a26)

PLAN_TABLE_OUTPUT

———————————————————–

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

select sql_handle, plan_name, enabled, accepted,fixed,signature from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX             SIGNATURE

—————————— —————————— — — — ———————

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO NO   7784548270786280511

SYS

So even if capture baseline is set to false, oracle will still capture the new plans and baselines for that particular sql onwards. We can evolve the  new plan and can check if index is getting used or not.

/*Evolving new plan */

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

Enter value for sql_handle:SYS_SQL_6c0845687118fc3f

Enter value for time:60

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT = 60

VERIFY     = YES

COMMIT     = YES

Plan: SYS_SQL_PLAN_7118fc3f642e4a26

———————————–

Plan was verified: Time used .05 seconds.

Passed

performance criterion: Compound improvement ratio >= 504.88

Plan was changed to an accepted plan.

Baseline Plan      Test Plan     Improv. Ratio

————-      ———     ————-

Execution Status:        COMPLETE       COMPLETE

Rows Processed:                 1              1

Elapsed Time(ms):               26             0

CPU Time(ms):                  25              0

Buffer Gets:                 2017              4            504.25

Disk Reads:                     0              0

Direct Writes:                  0              0

Fetches:                        0              0

Executions:                     1              1

——————————————————————————-

Report

Summary

——————————————————————————-

Number of SQL plan baselines verified: 1.

Number of SQL plan baselines evolved: 1.

As we can read in the report, Oracle has marked the new plan as accepted. Oracle keeps a tab if the plan was loaded manually or captured automatically. This can be verified from origin column of dba_sql_plan_baselines.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO MANUAL-LOAD

So far, the story was pretty much straight. Here’s a quick recap what we have done
1. Created Table and Inserted records
2. Query the table and baseline the query
3. Created Index and verified new plan is loaded but not accepted.
4. Evolve the new plan and accepted=yes.
5. New plan(Index Range Scan) started getting used.

Its time to discuss the usage of enabled,accepted and fixed parameters.

TEST1:  ENABLED=NO.

For every sql_id, oracle checks if the baselines exists or not. If yes, then it will filter all the enabled plans. The plans for which enabled=NO will not be considered even if accepted and fixed are set to yes.

Other than this, if any plan is set enabled=yes for a query,oracle will start capturing new plans also.

Therefore, enabled is the first level. If enabled is set to NO,oracle will mark not to use and evolve that plan.

Lets have a test case. I will mark enabled =no for the plan that was using index and check which plan is used.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO MANUAL-LOAD

/* Setting Enabled=NO for SYS_SQL_PLAN_7118fc3f642e4a26 */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘ENABLED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  NO YES  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

2 rows selected.

So, plan SYS_SQL_PLAN_7118fc3f642e4a26 is accepted but not enabled. Lets execute the query again and see which plan is getting used.

PLAN_TABLE_OUTPUT

——————————————————————–

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

Oracle choses SYS_SQL_PLAN_7118fc3f97bbe3, since that plan is still enabled and accepted.
Therefore, minimum requirement for oracle to use a plan as baseline is, it should be marked both as ENABLED and ACCEPTED.

The next level is Accepted. In this level oracle will pick all the plans that are accepted to the users and discard all “ACCEPTED=NO” plans. As we had already seen,there can be more than one plan for accepted=yes. All these plans are already evolved plans and are candidate for being  the final execution plan.

If no plan is marked “FIXED=YES”, then oracle will pick any of these plans based on the costing.

All those plans which are “ENABLED=YES” and “ACCEPTED=NO”, are candidates for evolution. We can also however marked then accepted=yes manually.

Next and the last level is Fixed. If any plan is fixed oracle will use that plan only. If more than one plan is fixed, oracle will use costing as criteria to select the plan among those.

TEST2:  Setting any one plan as FIXED=YES

Lets mark index plan as accepted again and FTS plan as fixed.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  NO  YES NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

/*Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to Enabled */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>’&sql_handle’,plan_name=>’&plan_name’,attribute_name =>’ENABLED’,attribute_value =>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

/*Setting SYS_SQL_PLAN_7118fc3f97bbe3d0 to FIXED */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>’&sql_handle’,plan_name=>’&plan_name’,attribute_name=>’FIXED’,attribute_value=>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

Executing the query and checking the plan again

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

—————————————————————————

SQL_ID  duk2ypk5fz9g6, child number 1

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1357081020

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   554 (100)|          |

|*  1 |  TABLE ACCESS FULL| TEST |  1643 | 32860 |   554   (2)| 00:00:07 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – filter(“ID”=1000)

Note

—–

– SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement

As we can confirm oracle start using FTS as that plan is marked fixed.

TEST3: Setting FIXED=YES for more than one plan

Lets mark second plan “FIXED=YES” and re-execute the query

/*Setting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle=>’&sql_handle’,plan_name=>’&plan_name’,attribute_name=>’FIXED’,attribute_value=>’YES’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES YES AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

SELECT * FROM TABLE(dbms_xplan.display_cursor(‘duk2ypk5fz9g6’));

PLAN_TABLE_OUTPUT

—————————————————————

SQL_ID  duk2ypk5fz9g6, child number 0

————————————-

select /*+ id=1000 */  * FROM TEST where id=1000

Plan hash value: 1699862855

—————————————————————————————

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————

|   0 | SELECT STATEMENT            |         |       |       |     1 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    20 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST_ID |     1 |       |     1   (0)| 00:00:01 |

—————————————————————————————

Predicate Information (identified by operation id):

—————————————————

2 – access(“ID”=1000)

Note

—–

– dynamic sampling used for this statement

– SQL plan baseline SYS_SQL_PLAN_7118fc3f642e4a26 used for this statement

Test 4:

Also, if any of the plan is marked as fixed, oracle will stop capturing and evolving new plans.
To simulate the test, lets again mark the plan(using index) as fixed=no and accepted=no.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES YES YES AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED=NO */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘FIXED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to ACCEPTED=NO */

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘ACCEPTED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

Lets try to evolve the index plan (SYS_SQL_PLAN_7118fc3f642e4a26) again, like we did previously. The only difference is fixed=yes this time for plan using FTS

/* TRYING TO EVOLVE SYS_SQL_PLAN_7118fc3f642e4a26 */

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for time: 60

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT = 60

VERIFY     = YES

COMMIT     = YES

——————————————————————————-

Report

Summary

——————————————————————————-

There were no SQL plan baselines that required processing.

As we can confirm, plan SYS_SQL_PLAN_7118fc3f642e4a26 is not evolved since SYS_SQL_PLAN_7118fc3f97bbe3d0 was already fixed.

TEST5:

Lets go back a step further and drop the baseline as well as index. We will try to simulate a test case to check if oracle captures new plan if any plan is marked as FIXED=YES

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped  PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle => ‘SYS_SQL_6c0845687118fc3f’,

plan_name  => ‘SYS_SQL_PLAN_7118fc3f642e4a26’);

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

/

drop index test_id;

Index dropped.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

So, we are back to square one from where we have started. Lets again create the same index and query the table to see if it still capture new plans.

create index test_id on test(id);

Index created.

select /*+ id=1000 */  * FROM TEST where id=1000;

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

As we can see, oracle has stopped capturing new plans for this sql as SYS_SQL_PLAN_7118fc3f97bbe3d0 is already fixed.

Lets mark fixed=NO again and re-execute the query. Oracle should capturing the plan again for this query.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES YES MANUAL-LOAD

1 row selected.

declare

myplan pls_integer;

begin

myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => ‘&sql_handle’,plan_name  => ‘&plan_name’,attribute_name => ‘FIXED’,   attribute_value => ‘NO’);

end;

/

Enter value for sql_handle: SYS_SQL_6c0845687118fc3f

Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0

select /*+ id=1000 */  * FROM TEST where id=1000

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES YES NO  MANUAL-LOAD

2 rows selected.

Here’s the quick summary,

1.  ENABLED=YES (For any of the plan) :- Oracle will start capturing new plans for those queries.

2. Enabled=NO :-  Than plan won’t we used.

3.  Accepted=Yes (Any one Plan) :- That plan will be used for execution

4.  Accepted=YES (For Multiple plans):- Any one plan can be used if fixed=no for all plans.

5. Accepted=No: Plan wont be used.

6.  FIXED=YES (for only one plan) Only that plan will be used and oracle will stop capturing/evolving new plans.

7.  FIXED=YES (for many plans) Oracle will chose execution plan only from that pool.

SPECIAL CASE :-ENABLED=YES,FIXED=YES BUT ACCEPTED=NO.

What if in our given scenario, we mark SYS_SQL_PLAN_7118fc3f97bbe3d0 as fixed and enabled but not accepted. Will oracle capture the new plan and will it evolve it.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

1 row selected.

Lets discuss the above scenario and try to figure out who takes precedence under what condition

select /*+ id=1000 */  * FROM TEST where id=1000;

NAME               ID

———- ———-

SUMIT            1000

1 row selected.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

2 rows selected.

Oracle has captured new plan. Lets try to evolve that one also.

SET SERVEROUTPUT ON

SET LONG 10000

set lines 175

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => ‘&sql_handle’, time_limit => &time);

DBMS_OUTPUT.PUT_LINE(report);

END;

/

——————————————————————————-

Evolve SQL Plan Baseline

Report

——————————————————————————-

Inputs:

——-

SQL_HANDLE = SYS_SQL_6c0845687118fc3f

PLAN_NAME  =

TIME_LIMIT =

60

VERIFY     = YES

COMMIT     = YES

——————————————————————————-

Report

Summary

——————————————————————————-

There were no SQL plan baselines that required processing.

select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX ORIGIN

—————————— —————————— — — — ————–

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f642e4a26  YES NO  NO  AUTO-CAPTURE

SYS_SQL_6c0845687118fc3f       SYS_SQL_PLAN_7118fc3f97bbe3d0  YES NO  YES MANUAL-LOAD

2 rows selected.

ENABLED takes precedence over FIXED in case of capturing the new plan and vice versa in case of evolving.

Oracle Db Hints with examples

 
Optimizer Approaches
 

ALL_ROWS

The ALL_ROWS hint explicitly chooses the cost-based approach

to optimize a statement block with a goal of best throughput (that is,

minimum total resource consumption).

/*+ ALL_ROWS */

conn / as sysdba

set linesize 121

col name format a30

col value format a30

SELECT name, value

FROM gv$parameter

WHERE name LIKE ‘%optimizer%’;

ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;

set autotrace traceonly explain

SELECT table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

SELECT /*+ ALL_ROWS */ table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;

 

FIRST_ROWS(n)

The FIRST_ROWS hint explicitly chooses the cost-based approach to

optimize a statement block with a goal of best response time

(Minimum resource usage to return first row).

This hint causes the optimizer to make these choices:

  • If an index scan is available, the optimizer may choose it over a full

table scan.

  • If an index scan is available, the optimizer may choose a nested loops

join over a sort-merge join whenever the associated table is the potential

inner table of the nested loops.

  • If an index scan is made available by an ORDER BY clause, the optimizer

may choose it to avoid a sort operation.

  • The optimizer ignores this hint in DELETE and UPDATE statement blocks

and in SELECT statement blocks that contain any of the following:

UNION, INTERSECT, MINUS, UNION ALL, GROUP BY,

FOR UPDATE, aggregating function and the DISTINCT operator.

/*+ FIRST_ROWS(<integer>) */

set autotrace trace exp

SELECT table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

SELECT /*+ FIRST_ROWS(10) */ table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

— the differences are subtle so look closely

 

RULE

Disables the use of the optimizer. This hint is not supported

and should not be used.

/*+ RULE */

set autotrace trace exp

SELECT table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

SELECT /*+ RULE */ table_name

FROM dba_tables

WHERE owner = ‘SYS’

AND table_name LIKE ‘%$’

ORDER BY 1;

 
General
 

APPEND

Instructs the optimizer to use direct-path INSERT if your database

is running in serial mode. Your database is in serial mode if you are

not using Enterprise Edition. Conventional INSERT is the default in

serial mode, and direct-path INSERT is the default in parallel mode.

In direct-path INSERT, data is appended to the end of the table,

rather than using existing space currently allocated to the table.

As a result, direct-path INSERT can be considerably faster than

conventional INSERT.

When you use the APPEND hint for INSERT, data is simply appended

to a table above the HWM which has the effect of not creating UNDO.

Existing free space in blocks is not used.

/*+ APPEND */

CREATE TABLE t AS

SELECT *

FROM servers

WHERE 1=2;

INSERT /*+ NO_APPEND */ INTO t

SELECT * FROM servers;

SELECT COUNT(*) FROM t;

INSERT INTO t

SELECT * FROM servers;

SELECT COUNT(*) FROM t;

INSERT /*+ APPEND */ INTO t

SELECT * FROM servers;

SELECT COUNT(*) FROM t;

COMMIT;

SELECT COUNT(*) FROM t;

 

NOAPPEND

Instructs the optimizer to use conventional INSERT by disabling

parallel mode for the duration of the INSERT statement.

Conventional INSERT is the default in serial mode,

and direct-path INSERT is the default in parallel mode.

/*+ NOAPPEND */

See APPEND Demo Above
 

CACHE

Instructs the optimizer to place the blocks retrieved for the table

at the most recently used end of the LRU list in the buffer cache

when a full table scan is performed. This hint is useful for small lookup tables.

/*+ CACHE([@queryblock] <tablespec>) */

conn hr/hr

set autotrace traceonly exp

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name

FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name

FROM employees hr_emp;

 

NOCACHE

Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.

/*+ NOCACHE([@queryblock] <tablespec>]) */

See CACHE Demo Above
 

CURSOR_SHARING_EXACT

Oracle can replace literals in SQL statements with bind variables,

when it is safe to do so. This replacement is controlled with the

CURSOR_SHARING initialization parameter.

The CURSOR_SHARING_EXACT hint instructs the optimizer to

switch this behavior off. In other words, Oracle executes the

SQL statement without any attempt to replace literals with bind variables.

/*+ CURSOR_SHARING_EXACT */

conn / as sysdba

ALTER SYSTEM SET cursor_sharing=’SIMILAR’ SCOPE=BOTH;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH SHARED_POOL;

— as the client run two similar SQL statements

SELECT latitude FROM uwclass.servers WHERE srvr_id = 1;

SELECT latitude FROM uwclass.servers WHERE srvr_id = 2;

SELECT latitude FROM uwclass.servers WHERE srvr_id = 3;

— as SYS look in the shared pool

set linesize 121

col sql_text format a50

SELECT address, child_address, sql_text, sql_id

FROM gv$sql

WHERE sql_fulltext LIKE ‘%uwclass%’;

SELECT /*+ CURSOR_SHARING_EXACT */ latitude FROM uwclass.servers WHERE srvr_id = 3;

SELECT address, child_address, sql_text, sql_id

FROM gv$sql

WHERE sql_fulltext LIKE ‘%uwclass%’;

 

DRIVING_SITE

Forces query execution to be done at a user selected  site rather

than at a site selected by the database. This hint is useful

if you are using distributed query optimization.

/*+ DRIVING_SITE([@queryblock] <tablespec>) */

SELECT p1.first_name, p2.first_name, p2.last_name

FROM person p1, person@psoug_user p2

WHERE p1.person_id = p2.person_id

AND p1.first_name <> p2.first_name;

SELECT /*+ DRIVING_SITE(p1) AAA */ p1.first_name, p2.first_name, p2.last_name

FROM person p1, person@psoug_user p2

WHERE p1.person_id = p2.person_id

AND p1.first_name <> p2.first_name;

SELECT sql_text, remote

FROM v$sql

WHERE sql_text LIKE ‘%AAA%’;

SELECT /*+ DRIVING_SITE(p2) BBB */ p1.first_name, p2.first_name, p2.last_name

FROM person p1, person@psoug_user p2

WHERE p1.person_id = p2.person_id

AND p1.first_name <> p2.first_name;

SELECT sql_text, remote

FROM v$sql

WHERE sql_text LIKE ‘%BBB%’;

 

DYNAMIC_SAMPLING

The DYNAMIC_SAMPLING hint instructs the optimizer how to

control dynamic sampling to improve server performance by

determining more accurate predicate selectivity and statistics

for tables and indexes.

You can set the value of DYNAMIC_SAMPLING to a value

from 0 to 10. The higher the level, the more effort the compiler

puts into dynamic sampling and the more broadly it is applied.

Sampling defaults to cursor level unless you specify tablespec.

The integer value is 0 to 10, indicating the degree of sampling.

Force dynamic sampling of tables where statistics do not exist

such as Global Temporary Tables.

If the table is aliased the alias name, not the table name must be used

/*+ DYNAMIC_SAMPLING([@queryblock] [<tablespec>] <integer>) */

conn uwclass/uwclass

CREATE TABLE ds AS

SELECT * FROM all_objects

WHERE SUBSTR(object_name,1,1) BETWEEN ‘A’ AND ‘W’;

CREATE INDEX ds_objtype

ON ds(object_type);

SELECT object_type, COUNT(*)

FROM ds

GROUP BY object_type;

set autotrace trace exp

SELECT object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

SELECT /*+ DYNAMIC_SAMPLING(ds 0) */ object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

SELECT /*+ DYNAMIC_SAMPLING(ds 4) */ object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

SELECT /*+ DYNAMIC_SAMPLING(ds 9) */ object_name

FROM ds

WHERE object_type = ‘JAVA CLASS’;

 

MODEL_MIN_ANALYSIS

Instructs the optimizer to omit some compile-time optimizations

of spreadsheet rules primarily detailed dependency graph analysis.

Other spreadsheet optimizations, such as creating filters to

selectively populate spreadsheet access structures and limited

rule pruning, are still used by the optimizer.

This hint reduces compilation time because spreadsheet analysis

can be lengthy if the number of spreadsheet rules is more than several hundreds.

/*+ MODEL_MIN_ANALYSIS */
 

MONITOR

Forces real-time SQL monitoring for the query, even if the statement

is not long running. This hint is valid only when the parameter

CONTROL_MANAGEMENT_PACK_ACCESS is set to

DIAGNOSTIC+TUNING.

/*+ MONITOR */

SELECT value

FROM v$parameter

WHERE name = ‘control_management_pack_access’;

SELECT /*+ MONITOR */ COUNT(*)

FROM user_tables;

NO_MONITOR Disables real-time SQL monitoring for the query, even if

the query is long running.

/*+ NO_MONITOR */

— this SQL statement is made intentionally long running

SELECT /*+ NO_MONITOR */ COUNT(*)

FROM dba_segments s, dba_extents e

WHERE s.owner = e.owner;

 

OPT_PARAM

Lets you set an initialization parameter for the duration of

the current query only. This hint is valid only for the  following

parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING,

and STAR_TRANSFORMATION_ENABLED. For example,

the following hint sets the parameter STAR_TRANSFORMATION_ENABLED

to TRUE for the statement to which it is added.

/*+ OPT_PARAM(parameter_name, parameter_value) */

SELECT name, value

FROM v$parameter

WHERE name LIKE ‘optimizer_index%’;

SELECT /*+ OPT_PARAM(‘optimizer_index_cost_adj’ ’42’) */ *

FROM servers;

 

PUSH_PRED

Instructs the optimizer to push a join predicate into the view.

/*+ PUSH_PRED(<@queryblock> | <[@queryblock> <tablespec>]) */

conn hr/hr

set autotrace trace exp

SELECT *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

 

NO_PUSH_PRED

Instructs the optimizer not to push a join predicate into the view.

/*+ NO_PUSH_PRED(<@queryblock> | <[@queryblock> <tablespec>]) */

conn hr/hr

set autotrace traceonly exp

SELECT *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *

FROM employees e, (

SELECT manager_id

FROM employees) v

WHERE e.manager_id = v.manager_id(+)

AND e.employee_id = 100;

 

PUSH_SUBQ

Instructs the optimizer to evaluate nonmerged subqueries at the

earliest possible step in the execution plan. Generally, subqueries

that are not merged are executed as the last step in the execution

plan. If the subquery is relatively inexpensive and reduces

the number of rows significantly, then evaluating the subquery

earlier can improve performance.

This hint has no effect if the subquery is applied to a remote

table or one that is joined using a merge join.

/*+ PUSH_SUBQ(<@queryblock>) */
 

NO_PUSH_SUBQ

Instructs the optimizer to evaluate nonmerged subqueries as the

last step in the execution plan. Doing so can improve performance

if the subquery is relatively expensive or does not reduce the

number of rows significantly.

/*+ NO_PUSH_SUBQ(<@queryblock>) */
PX_JOIN_FILTER Forces the optimizer to use parallel join bitmap filtering.
/*+ PX_JOIN_FILTER(<tablespec>) */
 

NO_PX_JOIN_FILTER

Prevents the optimizer from using parallel join bitmap filtering.
/*+ NO_PX_JOIN_FILTER(<tablespec>) */
 

QB_NAME

Use the QB_NAME hint to define a name for a query block. This name

can then be used in a hint in the outer query or even in a hint in an

inline view to affect query execution on the tables appearing in the

named query block.

If two or more query blocks have the same name, or if the same query

block is hinted twice with different names, then the optimizer ignores all

the names and the hints referencing that query block. Query blocks that

are not named using this hint have unique system-generated names.

These names can be displayed in the plan table and can also be used

in hints within the query block, or in query block hints.

/*+ QB_NAME(<query_block_name>) */

conn hr/hr

set autotrace traceonly exp

SELECT employee_id, last_name

FROM employees e

WHERE last_name = ‘Smith’;

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name

FROM employees e

WHERE last_name = ‘Smith’;

 

RESULT_CACHE

Instructs the database to cache the results of the current query or

query fragment in memory and then to use the cached results in

future executions of the query or query fragment. The hint is

recognized in the top-level query, the subquery_factoring_clause,

,or FROM clause inline view. The cached results reside in the

result cache memory portion of the shared pool.

/*+ RESULT_CACHE */
 

NO_RESULT_CACHE

The optimizer caches query results in the result cache if the

RESULT_CACHE_MODE initialization parameter is set to FORCE.

In this case, the NO_RESULT_CACHE hint disables such

caching for the current query.

/*+ NO_RESULT_CACHE */
 
Access Method Hints
Each following hint described in this section suggests an access method for a table.
 

FULL

Explicitly chooses a full table scan for the specified table

/*+ FULL(<tablespec>) */

conn uwclass/uwclass

set autotrace traceonly explain

SELECT latitude

FROM servers

WHERE srvr_id = 1;

SELECT /*+ FULL(servers) */ latitude

FROM servers

WHERE srvr_id = 1;

 

INDEX

Explicitly chooses an index scan for the specified table. You can

use the INDEX hint for domain, B*-tree, and bitmap indexes.

However, Oracle recommends using INDEX_COMBINE rather

than INDEX for bitmap indexes because it is a more versatile hint

/*+ INDEX([@queryblock] <tablespec> <index_name>) */

conn oe/oe

CREATE INDEX ix_customers_gender

ON customers(gender);

set autotrace traceonly explain

SELECT *

FROM customers

WHERE gender = ‘M’;

SELECT /*+ INDEX(customers ix_customers_gender) */ *

FROM customers

WHERE gender = ‘M’;

SELECT /*+ INDEX_ASC(customers ix_customers_gender) */ *

FROM customers

WHERE gender = ‘M’;

SELECT /*+ INDEX_DESC(customers ix_customers_gender) */ *

FROM customers

WHERE gender = ‘M’;

 

INDEX_ASC

Explicitly chooses an index scan for the specified table. If the

statement uses an index range scan, Oracle scans the index

entries in ascending order of their indexed values

/*+ INDEX_ASC([@queryblock] <tablespec> <index_name>) */

See INDEX Demo Above
 

INDEX_DESC

Explicitly chooses an index scan for the specified table. If the

statement uses an index range scan, Oracle scans the index

entries in descending order of their indexed values.

/*+ INDEX_DESC([@queryblock] <tablespec> <indexspec>) */

See INDEX Demo Above
 

NO_INDEX

Explicitly disallows a set of indexes for the specified table.

The NO_INDEX hint applies to function-based, B*-tree,

bitmap, cluster, or domain indexes.

/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */

conn uwclass/uwclass

set autotrace traceonly explain

SELECT latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ NO_INDEX(i pk_serv_inst) */ latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

 

INDEX_FFS

Causes a fast full index scan rather than a full table scan.

Appears to be identical to INDEX_FFS_ASC.

/*+ INDEX_FFS([@queryblock] <tablespec> <indexspec>) */

See INDEX SCAN Demos Below
 

INDEX_FFS_ASC

Causes a fast full index scan rather than a full table scan

/*+ INDEX_FFS_ASC([@queryblock] <tablespec> <indexspec>) */

See INDEX SCAN Demos Below
 

INDEX_FFS_DESC

Causes a fast full index scan in descending order rather

than a full table scan

/*+ INDEX_FFS_DESC([@queryblock] <tablespec> <indexspec>) */

See INDEX SCAN Demos Below
 

NO_INDEX_FFS

Instructs the optimizer to exclude a fast full index scan of

the specified indexes.

/*+ NO_INDEX_FFS([@queryblock] <tablespec> <indexspec>) */

conn uwclass/uwclass

set autotrace traceonly exp

SELECT latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ NO_INDEX_FFS(i pk_serv_inst) NO_INDEX_FFS(i ix_serv_inst) */ latitude

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

 

INDEX_RS

Instructs the optimizer to perform an index range

scan for the specified table.

/*+ INDEX_RS([@queryblock] <tablespec> <indexspec>) */
 

INDEX_RS_ASC

Instructs the optimizer to perform an index range

scan for the specified table.

/*+ INDEX_RS([@queryblock] <tablespec> <indexspec>) */
 

INDEX_RS_DESC

Instructs the optimizer to perform an index range

scan for the specified table.

/*+ INDEX_RS([@queryblock] <tablespec> <indexspec>) */
 

NO_INDEX_RS

Instructs the optimizer to exclude an index range

scan of the specified indexes.

/*+ NO_INDEX_RS([@queryblock] <tablespec> <indexspec>) */

conn hr/hr

col column_name format a30

SELECT column_position, column_name

FROM user_ind_columns

WHERE index_name = ‘EMP_NAME_IX’;

set autotrace traceonly explain

SELECT first_name

FROM employees e

WHERE last_name BETWEEN ‘A’ AND ‘B’;

SELECT /*+ NO_INDEX_RS(e emp_name_ix) */ last_name

FROM employees e

WHERE first_name BETWEEN ‘A’ AND ‘B’;

 

INDEX_SS

Instructs the optimizer to perform an index skip scan for

the specified table. If the statement uses an index range

scan, then Oracle scans the index entries in ascending

order of their indexed values. In a partitioned index,

the results are in ascending order within each partition.

/*+ INDEX_SS([@queryblock] <tablespec> <indexspec>) */

See INDEX SCAN Demos Below
 

INDEX_SS_ASC

Instructs the optimizer to perform an index skip scan for

the specified table. If the statement uses an index

range scan, then Oracle Database scans the index entries

in ascending order of their indexed values. In a partitioned

index, the results are in ascending order within each partition.

Each parameter serves the same purpose as in “INDEX Hint”.

The default behavior for a range scan is to scan index entries

in ascending order of their indexed values, or in descending

order for a descending index. This hint does not change the

default order of the index, and therefore does not specify

anything more than the INDEX_SS hint. However, you can

use the INDEX_SS_ASC hint to specify ascending range

scans explicitly should the default behavior change.

/*+ INDEX_SS_ASC([@queryblock] <tablespec> <indexspec>) */

See INDEX SCAN Demos Below
 

INDEX_SS_DESC

Instructs the optimizer to perform an index skip scan for the

specified table. If the statement uses an index range scan and

the index is ascending, then Oracle scans the index entries

in descending order of their indexed values. In a partitioned index,

the results are in descending order within each partition.

For a descending index, this hint effectively cancels out the

descending order, resulting in a scan of the index entries in

ascending order.

/*+ INDEX_SS_DESC([@queryblock] <tablespec> <indexspec>) */

See INDEX SCAN Demos Below
 

NO_INDEX_SS

Instructs the optimizer to exclude a skip scan of the specified indexes.
/*+ NO_INDEX_SS([@queryblock] <tablespec> <indexspec>) */
 

INDEX_COMBINE

Explicitly chooses a bitmap access path for the table. If no

indexes are given as arguments for the INDEX_COMBINE hint,

the optimizer uses whatever Boolean combination of bitmap

indexes has the best cost estimate for the table. If certain

indexes are given as arguments, the optimizer tries to use

some Boolean combination of those particular bitmap indexes.

/*+ INDEX_DESC([@queryblock] <tablespec> <indexspec>) */

conn hr/hr

set autotrace traceonly explain

SELECT *

FROM employees e

WHERE (manager_id = 108) OR (department_id = 110);

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *

FROM employees e

WHERE (manager_id = 108) OR (department_id = 110);

 

INDEX_JOIN

Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.

/*+ INDEX_JOIN([@queryblock] <tablespec> <indexspec>) */

conn oe/oe

set autotrace traceonly explain

SELECT department_id

FROM employees e

WHERE manager_id < 110

AND department_id < 50;

———————————————————————

| Id | Operation                    | Name              | Cost(%CPU)|

———————————————————————

|  0 | SELECT STATEMENT             |                   |    2  (0) |

|* 1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |    2  (0) |

|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    1  (0) |

———————————————————————

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id

FROM employees e

WHERE manager_id < 110

AND department_id < 50;

————————————————————-

| Id | Operation           | Name              | Cost(%CPU) |

————————————————————-

|  0 | SELECT STATEMENT    |                   |    3 (34)  |

|* 1 |  VIEW               | index$_join$_001  |    3 (34)  |

|* 2 |   HASH JOIN         |                   |            |

|* 3 |    INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    2 (50)  |

|* 4 |    INDEX RANGE SCAN | EMP_MANAGER_IX    |    2 (50)  |

————————————————————-

 

Index Scan Demos

conn hr/hr

col column_name format a30

SELECT column_position, column_name

FROM user_ind_columns

WHERE index_name = ‘EMP_NAME_IX’;

set autotrace traceonly explain

SELECT last_name

FROM employees e;

SELECT /*+ INDEX_FFS(e emp_name_ix) */ last_name

FROM employees e;

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name

FROM employees e;

SELECT /*+ INDEX_SS_ASC(e emp_name_ix) */ last_name

FROM employees e;

SELECT /*+ INDEX_DESC(e emp_name_ix) */ last_name

FROM employees e;

Join Order  
The hints in this section suggest join orders:  
 

LEADING

Instructs the optimizer to use the

specified set of tables as the prefix

in the execution plan.

/*+ LEADING([@queryblock] <table_name> <table_name>) */

  conn hr/hr

set autotrace traceonly explain

SELECT *

FROM employees e, departments d, job_history j

WHERE e.department_id = d.department_id

AND e.hire_date = j.start_date;

SELECT /*+ LEADING(e j) */ *

FROM employees e, departments d, job_history j

WHERE e.department_id = d.department_id

AND e.hire_date = j.start_date;

 

ORDERED

Causes Oracle to only join tables

in the order in which they appear

in the FROM clause.

/*+ ORDERED */

  conn oe/oe

set autotrace traceonly explain

SELECT o.order_id, c.customer_id, l.unit_price * l.quantity

FROM customers c, order_items l, orders o

WHERE c.cust_last_name = ‘Mastroianni’

AND o.customer_id = c.customer_id

AND o.order_id = l.order_id;

SELECT /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity

FROM customers c, order_items l, orders o

WHERE c.cust_last_name = ‘Mastroianni’

AND o.customer_id = c.customer_id

AND o.order_id = l.order_id;

   
Join Operation  
Each hint described in this section suggests a join operation for a table.  
 

USE_HASH

Causes Oracle to join each specified

table with another row source with

a hash join.

/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */

  conn uwclass/uwclass

set autotrace traceonly explain

SELECT DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ USE_HASH (s i) */ DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

SELECT /*+ USE_MERGE (s i) */ DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id;

 

NO_USE_HASH

Instructs the optimizer to exclude hash

joins when joining each specified table

to another row source using the specified

table as the inner table.

/*+ NO_USE_HASH([@queryblock] <tablespec> <tablespec>) */

 

USE_MERGE

Causes Oracle to join each specified table with

another row source with a sort-merge join.

/*+ USE_MERGE([@queryblock] <tablespec> <tablespec>) */

  See USE_HASH Demo Above
 

NO_USE_MERGE

Instructs the optimizer to exclude

sort-merge joins when joining each

specified table to another row source

using the specified table as the inner table.

/*+ NO_USE_MERGE([@queryblock] <tablespec> <tablespec>) */

  conn hr/hr

set autotrace traceonly explain

SELECT *

FROM employees e, departments d

WHERE e.department_id = d.department_id;

SELECT /*+ NO_USE_MERGE(e d) */ *

FROM employees e, departments d

WHERE e.department_id = d.department_id;

 

USE_NL

Causes Oracle to join each specified table

to another row source with a nested loops

join using the specified table as the inner table.

/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */

  conn uwclass/uwclass

set autotrace traceonly explain

SELECT DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id+0 = i.srvr_id+0;

SELECT /*+ USE_NL (i s) */ DISTINCT s.srvr_id

FROM servers s, serv_inst i

WHERE s.srvr_id+0 = i.srvr_id+0;

 

USE_NL_WITH_INDEX

Instructs the optimizer to join the

specified table to another row source

with a nested loops join using the

specified table as the inner table.

/*+ USE_NL_WITH_INDEX([@queryblock] <tablespec> <index_name>) */

  conn oe/oe

set autotrace traceonly explain

SELECT *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

SELECT /*+ USE_NL_WITH_INDEX(l item_order_ix) */ *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

 

NO_USE_NL

Instructs the optimizer to exclude nested

loops joins when joining each specified

table to another row source using the

specified table as the inner table.

/*+ NO_USE_NL([@queryblock] <tablespec> <tablespec>) */

  conn oe/oe

set autotrace traceonly explain

SELECT *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

SELECT /*+ NO_USE_NL(l h) */ *

FROM orders h, order_items l

WHERE l.order_id = h.order_id

AND l.order_id > 3500;

 

NATIVE_FULL_OUTER_JOIN

instructs the optimizer to use native

full outer join, which is a native execution

method based on a hash join.

/*+ NATIVE_FULL_OUTER_JOIN( */

  TBD
 

NO_NATIVE_FULL_OUTER_JOIN

Instructs the optimizer to exclude the

native execution method when joining

each specified table. Instead, the full

outer join is executed as a union of left

outer join and anti-join.

/*+ NO_NATIVE_FULL_OUTER_JOIN */

Parallel Execution  
The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.  
 

PARALLEL

Specify the desired number of concurrent

servers that can be used for a parallel

operation. The hint applies to the INSERT,

UPDATE, and DELETE portions of a statement

as well as to the table scan portion. If

any parallel restrictions are violated,

the hint is ignored.

/*+ PARALLEL([@queryblock] <tablespec> <degree | DEFAULT>) */

  conn hr/hr

set autotrace traceonly exp

SELECT last_name

FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 2) */ last_name

FROM employees hr_emp;

— overrides table definition and uses init parmameter

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name

FROM employees hr_emp;

 

NO_PARALLEL

Overrides a PARALLEL specification in the

table clause. In general, hints take

precedence over table clauses.

/*+ NO_PARALLEL([@queryblock] <tablespec>) */

  conn hr/hr

CREATE TABLE employees_demo

PARALLEL (DEGREE 4) AS

SELECT * FROM employees;

SELECT table_name, degree

FROM user_tables;

set autotrace traceonly exp

SELECT last_name

FROM employees_demo hr_emp;

SELECT /*+ NO_PARALLEL(hr_emp) */ last_name

FROM employees_demo hr_emp;

 

PARALLEL_INDEX

Specify the desired number of concurrent

servers that can be used to parallelize

index range scans for partitioned indexes.

/*+ PARALLEL_INDEX([@queryblock] <tablespec> <index_name> <degree | DEFAULT>) */

  TBD
 

NO_PARALLEL_INDEX

Override a PARALLEL attribute setting on an index. In this way you can avoid a parallel index scan operation.

/*+ NO_PARALLEL_INDEX([@queryblock] <tablespec> <index_name>) */

  TBD
 

PQ_DISTRIBUTE

Improve parallel join operation performance.

Do this by specifying how rows of joined

tables should be distributed among producer

and consumer query servers. Using this

hint overrides decisions the optimizer

would normally make.

Outer_distribution is the distribution for the outer table.

Inner_distribution is the distribution for the inner table.

/*+ PQ_DISTRIBUTE([@queryblock] <tablespec> <outer_distribution> <inner_distribution>) */

  TBD
   
Query Transformation  
 

FACT

In the context of the star transformation.

It instructs the optimizer that the table

specified in table specification should

be considered as a fact table.

/*+ FACT([@queryblock] <tablespec>) */

  TBD
 

NO_FACT

Used in the context of the star transformation.

It instruct the optimizer that the

queried table should not be considered

as a fact table.

/*+ NO_FACT([@queryblock] <tablespec>) */

  TBD
 

NO_EXPAND

Prevents the cost-based optimizer from

considering OR-expansion for queries having

OR conditions or INLISTS in the WHERE clause.

Normally, the optimizer would consider using

OR expansion and use this method if it

decides the cost is lower than not using it.

/*+ NO_EXPAND(<@queryblock>);

  conn oe/oe

set autotrace traceonly explain

SELECT *

FROM employees e, departments d

WHERE e.manager_id = 108

OR d.department_id = 110;

SELECT /*+ NO_EXPAND */ *

FROM employees e, departments d

WHERE e.manager_id = 108

OR d.department_id = 110;

 

MERGE

The MERGE hint lets you merge views in a

query. If a view’s query block contains

a GROUP BY clause or DISTINCT operator in

the SELECT list, then the optimizer can

merge the view into the accessing statement

only if complex view merging is enabled.

Complex merging can also be used to merge

an IN subquery into the accessing statement

if the subquery is uncorrelated.

/*+ MERGE(<@queryblock> [tablespec]); */

  conn hr/hr

set autotrace traceonly explain

SELECT e1.last_name, e1.salary, v.avg_salary

FROM employees e1, (

SELECT department_id, AVG(salary) avg_salary

FROM employees e2

GROUP BY department_id) v

WHERE e1.department_id = v.department_id

AND e1.salary > v.avg_salary

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary

FROM employees e1, (

SELECT department_id, AVG(salary) avg_salary

FROM employees e2

GROUP BY department_id) v

WHERE e1.department_id = v.department_id

AND e1.salary > v.avg_salary;

 

NO_MERGE

Instructs the optimizer not to combine the

outer query and any inline view queries

into a single query.

/*+ NO_MERGE(<@queryblock> [tablespecification]); */

  conn hr/hr

set autotrace traceonly explain

SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name

FROM employees e1,

(SELECT location_id, department_id, department_name

FROM departments

WHERE location_id = 1700) seattle_dept

WHERE e1.department_id = seattle_dept.department_id;

SELECT /*+ NO_MERGE(seattle_dept) */ e1.last_name, seattle_dept.department_name

FROM employees e1, (

SELECT location_id, department_id, department_name

FROM departments

WHERE location_id = 1700) seattle_dept

WHERE e1.department_id = seattle_dept.department_id;

 

NO_QUERY_TRANSFORMATION

Instructs the optimizer to skip all query

transformations, including but not limited

to OR-expansion, view merging, subquery

unnesting, star transformation, and

materialized view rewrite.

/*+ NO_QUERY_TRANSFORMATION) */

  conn uwclass/uwclass

set autotrace traceonly explain

SELECT DISTINCT srvr_id

FROM servers

WHERE srvr_id NOT IN (

SELECT srvr_id

FROM servers

MINUS

SELECT srvr_id

FROM serv_inst);

SELECT /*+ NO_QUERY_TRANSFORMATION */ DISTINCT srvr_id

FROM servers

WHERE srvr_id NOT IN (

SELECT srvr_id

FROM servers

MINUS

SELECT srvr_id

FROM serv_inst);

 

NO_REWRITE

Use on any query block of a request. This

hint disables query rewrite for the query

block, overriding the setting of the

parameter QUERY_REWRITE_ENABLED.

/*+ NO_REWRITE(<@queryblock>) */

  conn sh/sh

set autotrace traceonly explain

SELECT SUM(s.amount_sold) AS dollars

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

SELECT /*+ NO_REWRITE */ SUM(s.amount_sold) AS dollars

FROM sales s, times t

WHERE s.time_id = t.time_id

GROUP BY t.calendar_month_desc;

 

NO_UNNEST

Turns off unnesting of subqueries

/*+ NO_UNNEST(<@queryblock>) */

  conn uwclass/uwclass

set autotrace traceonly explain

SELECT srvr_id

FROM servers

WHERE srvr_id IN (

SELECT /*+ unnest */ srvr_id FROM serv_inst);

——————————————————————-

| Id | Operation              | Name         | Rows | Cost (%CPU) |

——————————————————————-

|  0 | SELECT STATEMENT       |              |   11 |    5   (20) |

|* 1 |  HASH JOIN SEMI        |              |   11 |    5   (20) |

|  2 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |    1    (0) |

|  3 |   INDEX FAST FULL SCAN | PK_SERVERS   |  999 |    3    (0) |

——————————————————————-

Predicate Information (identified by operation id):

—————————————————

1 – access(“SRVR_ID”=”SRVR_ID”)

SELECT srvr_id

FROM servers

WHERE srvr_id IN (

SELECT /*+ no_unnest */ srvr_id FROM serv_inst);

—————————————————————

| Id | Operation          | Name         | Rows | Cost (%CPU) |

—————————————————————

|  0 | SELECT STATEMENT   |              |    1 |  128    (0) |

|* 1 |  INDEX FULL SCAN   | PK_SERVERS   |    7 |    1    (0) |

|* 2 |   INDEX FULL SCAN  | PK_SERV_INST |    2 |    2    (0) |

—————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM “SERV_INST”

“SERV_INST” WHERE “SRVR_ID”=:B1))

2 – access(“SRVR_ID”=:B1)

filter(“SRVR_ID”=:B1)

 

REWRITE

Use with or without a view list. If you

use REWRITE with a view list and the

list contains an eligible materialized

view, Oracle uses that view regardless of

its cost. Oracle does not consider views

outside of the list. If you do not specify

a view list, Oracle searches for an eligible

materialized view and always uses it regardless

of its cost.

/*+ REWRITE([@queryblock] <view, view, …>) */

  conn uwclass/uwclass

CREATE MATERIALIZED VIEW mv_rewrite

TABLESPACE uwdata

REFRESH ON DEMAND

ENABLE QUERY REWRITE

AS SELECT s.srvr_id, i.installstatus, COUNT(*)

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id

GROUP BY s.srvr_id, i.installstatus;

set autotrace traceonly exp

SELECT s.srvr_id, i.installstatus, COUNT(*)

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id

AND s.srvr_id = 502

GROUP BY s.srvr_id, i.installstatus;

SELECT /*+ REWRITE */ s.srvr_id, i.installstatus, COUNT(*)

FROM servers s, serv_inst i

WHERE s.srvr_id = i.srvr_id

AND s.srvr_id = 502

GROUP BY s.srvr_id, i.installstatus;

 

STAR_TRANSFORMATION

Makes the optimizer use the best plan in

which the transformation has been used.

Without the hint, the optimizer could

make a cost-based decision to use the

best plan generated without the transformation,

instead of the best plan for the transformed query.

Even if the hint is given, there is no

guarantee that the transformation will

take place. The optimizer will only

generate the subqueries if it seems

reasonable to do so. If no subqueries

are generated, there is no transformed

query, and the best plan for the untransformed

query will be used regardless of the hint.

/*+ STAR_TRANSFORMATION(<@queryblock>) */

  conn sh/sh

set autotrace traceonly exp

SELECT *

FROM sales s, times t, products p, channels c

WHERE s.time_id = t.time_id

AND s.prod_id = p.prod_id

AND s.channel_id = c.channel_id

AND p.prod_status = ‘obsolete’;

SELECT /*+ STAR_TRANSFORMATION */ *

FROM sales s, times t, products p, channels c

WHERE s.time_id = t.time_id

AND s.prod_id = p.prod_id

AND s.channel_id = c.channel_id

AND p.prod_status = ‘obsolete’;

 

NO_STAR_TRANSFORMATION

Instructs the optimizer not to perform

star query transformation.

/*+ NO_STAR_TRANSFORMATION(<@queryblock>) */

  TBD
 

UNNEST

Instructs the optimizer to unnest and

merge the body of the subquery into

the body of the query block that contains

it, allowing the optimizer to consider

them together when evaluating access

paths and joins.

/*+ UNNEST(<@queryblock>) */

  See NO_UNNEST Demo Above
 

USE_CONCAT

Forces combined OR conditions in the

WHERE clause of a query to be transformed

into a compound query using the UNION ALL

set operator. Normally, this transformation

occurs only if the cost of the query using

the concatenations is cheaper than the

cost without them.

The USE_CONCAT hint turns off inlist processing

and OR-expands all disjunctions, including inlists.

  conn hr/hr

set autotrace traceonly explain

SELECT *

FROM employees e

WHERE manager_id = 108

OR department_id = 110;

SELECT /*+ USE_CONCAT */ *

FROM employees e

WHERE manager_id = 108

OR department_id = 110;

   
XML Hints  
 

NO_XMLINDEX_REWRITE

Instructs the optimizer to prohibit

the rewriting of XPath expressions

in SQL statements.

/*+ NO_XMLINDEX_REWRITE */

  SELECT /*+ NO_XMLINDEX_REWRITE */ COUNT(*)

FROM table WHERE existsNode(OBJECT_VALUE, ‘/*’) = 1;

 

NO_XML_QUERY_REWRITE

Instructs the optimizer to prohibit

the rewriting of XPath expressions

in SQL statements.

/*+ NO_XML_QUERY_REWRITE */

  SELECT /*+NO_XML_QUERY_REWRITE*/ XMLQUERY(‘<A/>’)

FROM DUAL;

 

CARDINALITY

Instructs the optimizer to use the

provided intgeger as the computed

cardinality of table (tablespace)

without checking.

/*+ CARDINALITY(<tablespec>, <integer>) */

  conn uwclass/uwclass

set autotrace traceonly explain

SELECT *

FROM serv_inst si

WHERE srvr_id = 1;

SELECT /*+ cardinality(si 999) */ *

FROM serv_inst si

WHERE srvr_id = 1;

 

PUSH_JOIN_PRED

Force pushing of a join predicate into

the view (found in the 8.1.5 docs)

  SELECT /*+ PUSH_JOIN_PRED(v) */ T1.X, V.Y

FROM T1 (

SELECT T2.X, T3.Y

FROM T2, T3

WHERE T2.X = T3.X) v

WHERE t1.x = v.x

AND t1.y = 1;

Invisible Indexes in Oracle Database

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

The following script creates and populates a table, then creates an invisible index on it.

CREATE TABLE ii_tab (
  id  NUMBER
);

BEGIN
  FOR i IN 1 .. 10000 LOOP
    INSERT INTO ii_tab VALUES (i);
  END LOOP;
  COMMIT;
END;
/

CREATE INDEX ii_tab_id ON ii_tab(id) INVISIBLE;

EXEC DBMS_STATS.gather_table_stats(USER, 'ii_tab', cascade=> TRUE);

A query using the indexed column in the WHERE clause ignores the index and does a full table scan.

SET AUTOTRACE ON
SELECT * FROM ii_tab WHERE id = 9999;

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     3 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| II_TAB |     1 |     3 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

Setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
SELECT * FROM ii_tab WHERE id = 9999;

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

Making the index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
ALTER INDEX ii_tab_id VISIBLE;

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

Invisible indexes can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index.

The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.