Shulou(Shulou.com)06/01 Report--

Http://www.mamicode.com/info-detail-1943333.html

There are several common situations where you need to bind an SQL execution plan:

SQL execution plan mutation, resulting in database performance degradation, from the historical execution plan to find a reasonable, bind.

SQL cannot use a better execution plan, and there is no historical execution plan, which can be bound by manual construction of hint.

Some Bug causes the optimizer to generate a poor execution plan. Bind before the bug is repaired.

There are 3 ways for ORACLE to execute a plan on a regular basis:

Oracle 9i uses outline (can be used across versions of 10pr 11g)

Oracle 10g uses sql profile (11g can also be used)

Oracle 11g uses sql plan manage

Next, it briefly describes how to use these three ways to fix the execution plan, and gives examples to illustrate the advantages and disadvantages of the three fixed execution plans, and compares and selects the appropriate fixed execution plan to deal with different business scenarios. That is, it is more appropriate to use which execution plan to fix in which scenario.

1. Outline (Stored Outline)

Syntax: (create outline manually)

CREATE [OR REPLACE]

[PUBLIC | PRIVATE] OUTLINE [outline]

[FROM [PUBLIC | PRIVATE] source_outline]

[FOR CATEGORY category]

[ON statement]

1. When the SQL execution plan changes due to the new version, inaccurate statistics, new indexes, parameter changes, etc., the storage outline can keep the execution plan of the SQL statement unchanged. When creating an outline of a statement, ORACLE stores the text of the SQL statement, the execution plan, and the hints used by the statement on three tables OL$,OL$HINTS,OL$NODES of the system default user OUTLN.

2. Use the outline (outline) to fix the execution plan

-- build the environment and set up the test table

SQL > create table zw as select * from dba_objects where object_id is not null

Table created.

SQL > explain plan for select count (*) from zw

Explained.

SQL > set lines 200

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

-

Plan hash value: 249608387

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 339 (1) | 00:00:05 |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | TABLE ACCESS FULL | ZW | 100K | 339 (1) | 00:00:05 |

-

Note

-

-dynamic sampling used for this statement (level=2)

13 rows selected.

-- query data dictionary dba_outlines:

SQL > select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines

No rows selected

-- create an outline (full table scan). The default is private outline

SQL > create or replace outline zwoutline for category mycate on select count (*) from zw

Outline created.

-- query dba_outlines again

Col NAME for a10

Col OWNER for a10

Col CATEGORY for a10

Col SQL_TEXT for a30

Select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines

NAME OWNER CATEGORY USED SQL_TEXT ENABLED TIMESTAMP

-

ZWOUTLINE SYS MYCATE UNUSED select count (*) from zw ENABLED 2017-09-02 15:36:33

-- create an object_id column index and set the column property to non-empty

The index does not store null values

SQL > alter table zw modify object_id not null

Table altered.

SQL > create index idx_zw_obj_id on zw (object_id)

Index created.

SQL > analyze table zw compute statistics

Table analyzed.

SQL > explain plan for select count (*) from zw

Explained.

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

-

Plan hash value: 1836624960

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

| | 0 | SELECT STATEMENT | | 1 | 54 (0) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | INDEX FAST FULL SCAN | IDX_ZW_OBJ_ID | 100K | 54 (0) | 00:00:01 |

-use an outline to fix the execution plan: (alter system/session set use_stored_outlines=mycate;) system level or session level

SQL > alter system set use_stored_outlines=mycate

System altered.

-- query dba_ouitlines (sql has not been applied yet)

SQL > select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines

NAME OWNER CATEGORY USED SQL_TEXT ENABLED TIMESTAMP

-

ZWOUTLINE SYS MYCATE UNUSED select count (*) from zw ENABLED 2017-09-02 15:36:33

-- after a fixed implementation plan, it will be carried out in accordance with the implementation plan at the time of the creation of the outline.

Actual execution verification: (outline will be applied after actual execution)

SQL > select count (*) from zw

COUNT (*)

-

87036

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

PLAN_TABLE_OUTPUT

SQL_ID 1f5n0rapts695, child number 0

-

Select count (*) from zw

Plan hash value: 1836624960

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

| | 0 | SELECT STATEMENT | 54 (100) | |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | INDEX FAST FULL SCAN | IDX_ZW_OBJ_ID | 87036 | 54 (0) | 00:00:01 |

Query Block Name / Object Alias (identified by operation id):

1-SEL$1

2-SEL$1 / ZW@SEL$1

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

DB_VERSION ('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX_FFS (@ "SEL$1"ZW" @ "SEL$1" ("ZW". "OBJECT_ID"))

END_OUTLINE_DATA

, /

Column Projection Information (identified by operation id):

1-(# keys=0) COUNT (*) [22]

39 rows selected.

-- query dba_outlines

Select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines

NAME OWNER CATEGORY USED SQL_TEXT ENABLED TIMESTAMP

-

ZWOUTLINE SYS MYCATE UNUSED select count (*) from zw ENABLED 2017-09-02 15:54:31

The above established outline is a public outline. In order not to affect the use of other users, you can create a private outline as follows:

Create or replace private outline zwoutline2 for category mycate2 on select count (*) from zw

Think about it: why do I build tests with a fixed full table scan instead of a more optimized index scan?

In fact, what I want to say here is that the shortcomings of outline are relatively rigid, when a new index is created, or when the amount of data changes greatly, it cannot be changed accordingly, that is to say, it is fixed.

For more information about outline, please see the following link:

Http://blog.csdn.net/whiteoldbig/article/details/17210079

Starting from 10g, you can create an outline exec DBMS_OUTLN.create_outline by referencing a SQL statement that already exists in the shared pool (hash_value= > 1752921103 childbirth number = > 0 outline = > 'test'); note that this method cannot specify a specific name of outline, which is automatically generated by the system, and can be modified by alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2 to default to default if no category is specified, and cannot be specified as a default category when created here (will report an error). When we use outline to execute a plan, we usually choose this method.

Reference:

Http://www.bubuko.com/infodetail-216529.html

The experiments are as follows:

SQL > create table dh_stat as select rownum id, object_name name, object_type type from dba_objects

Table created.

SQL > create index ind_1 on dh_stat (id) compute statistics

Index created.

SQL > alter system flush shared_pool

System altered.

SQL > exec DBMS_STATS.GATHER_TABLE_STATS (OWNNAME= > 'sys',TABNAME= >' dh_stat',ESTIMATE_PERCENT= > 30 Magnum METHODYOPTS = > 'FOR ALL COLUMNS SIZE 1' FALSE,CASCADE= > TRUE,DEGREE = > 1)

PL/SQL procedure successfully completed.

SQL > col name format A15

SQL > col name format A30

SQL > col sql_text for A55

SQL > select / * outlinetest1 * / * from dh_stat where id=771

ID NAME TYPE

771 SYS_IL0000000772C00002 $$INDEX

SQL > set lines 200

SQL > select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like'% outlinetest1%' and sql_text not like'% vSecretsql%'

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER

Select / * outlinetest1 * / * from dh_stat where id=771 9a69w18a8uuhk 344812050 0

SQL > select * from table (dbms_xplan.display_cursor ('9a69w18a8uhkhammer))

PLAN_TABLE_OUTPUT

SQL_ID 9a69w18a8uuhk, child number 0

-

Select / * outlinetest1 * / * from dh_stat where id=771

Plan hash value: 2780970545

-

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

-

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

| | 1 | TABLE ACCESS BY INDEX ROWID | DH_STAT | 1 | 39 | 2 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

-

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

-

Predicate Information (identified by operation id):

2-access ("ID" = 771)

19 rows selected.

-generate outline according to the existing execution plan of sql:

SQL > exec DBMS_OUTLN.create_outline (hash_value= > 344812050 childcare number = > 0penny = > 'TEST')

PL/SQL procedure successfully completed.

-- query outline:

SQL > select name,category,used,sql_text from dba_outlines

NAME CATEGORY USED SQL_TEXT

SYS_OUTLINE_17090216454529101 TEST UNUSED select / * outlinetest1 * / * from dh_stat where id=771

The USED state of the outline has not changed here because we have not activated the outline of the TEST category. Again, outline must be activated through the use_stored_outlines parameter before the optimizer will use outline.

-the following step activates OUTLINE of the TEST category, or OUTLINE at the system level

SQL > alter session set use_stored_outlines=TEST

Verify ellipsis.

II. SQL_PROFILE

1. DBMS_SQLTUNE is a new feature introduced by 10g, which can automatically optimize the poor performance SQL and give reasonable optimization suggestions, in which the sql_profile file in the optimization proposal is a collection of information stored in the data dictionary. Sql_profile does not contain a separate execution plan and provides information such as database configuration, binding variables, optimization statistics, datasets, and so on, for the optimizer to choose an execution plan. There is no introduction to the SQL optimization recommendation tool SQL Tuning Advisor STA). If you are interested in children's shoes, study the DBMS_SQLTUNE package.

Syntax:

SQL text format:

DBMS_SQLTUNE.CREATE_TUNING_TASK (sql_text IN CLOB, bind_list IN sql_binds: = NULL, user_name IN VARCHAR2: = NULL, scope IN VARCHAR2: = SCOPE_COMPREHENSIVE, time_limit IN NUMBER: = TIME_LIMIT_DEFAULT, task_name IN VARCHAR2: = NULL, description IN VARCHAR2: = NULL) RETURN VARCHAR2

SQL ID format:

DBMS_SQLTUNE.CREATE_TUNING_TASK (sql_id IN VARCHAR2, plan_hash_value IN NUMBER: = NULL, scope IN VARCHAR2: = SCOPE_COMPREHENSIVE, time_limit IN NUMBER: = TIME_LIMIT_DEFAULT, task_name IN VARCHAR2: = NULL, description IN VARCHAR2: = NULL) RETURN VARCHAR2

Workload Repository format:

DBMS_SQLTUNE.CREATE_TUNING_TASK (begin_snap IN NUMBER, end_snap IN NUMBER, sql_id IN VARCHAR2, plan_hash_value IN NUMBER: = NULL, scope IN VARCHAR2: = SCOPE_COMPREHENSIVE, time_limit IN NUMBER: = TIME_LIMIT_DEFAULT, task_name IN VARCHAR2: = NULL, description IN VARCHAR2: = NULL) RETURN VARCHAR2; experiment 1:sql_id format1. Sys users create oracle optimization tasks (set long 1000000 linesize 10000000 pagesize 0 serveroutput on size 1000000 verify off declare my_task_name varchar2 (30); v_sqlid varchar2 (50); begin vault qlid task 9y4a7' My_task_name: = dbms_sqltune.create_tuning_task (sql_id= > v_sqlid, scope = > 'comprehensive', time_limit= > 160,task_name= >' task_00000', description = > 'tuning task'); dbms_sqltune.execute_tuning_task (' task_00000'); end / Note: dbms_sqltune.execute_tuning_task ('task_00000'), which performs optimization task 2. Print optimization task, there are some specific improvement measures select dbms_sqltune.report_tuning_task ('task_00000') from dual; 3. According to the optimization suggestion, binding profile can theoretically improve the efficiency of sentence execution execute dbms_sqltune.accept_sql_profile (task_name = > 'task_00000',task_owner = >' sys', replace = > true); experiment 2:sql_text formatSQL > alter session set statistics_level=all;Session altered.SQL > set serveroutput offSQL > select * from scott.emp where ename='SCOTT' and DEPTNO=20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO -7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20SQL > SELECT * FROM table (dbms_xplan.display_cursor (NULL NULL,'runstats_last')) PLAN_TABLE_OUTPUT----SQL_ID 4dsqbp572auuu Child number 0--select * from scott.emp where ename='SCOTT' and DEPTNO=20Plan hash value: 3956160932 Murray- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |- -| 0 | SELECT STATEMENT | | 1 | 1 | 0 | 00 SELECT STATEMENT 00.01 | 7 | | * 1 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 | 0 0. 0% | 7 | PLAN_TABLE_OUTPUT-- -- Predicate Information (identified by operation id):-1-filter (("ENAME" = 'SCOTT' AND "DEPTNO" = 20)) 18 rows selected.SQL > DECLARE my_task_name VARCHAR2 (30) My_sqltext CLOB;BEGIN my_sqltext: = 'select * from scott.emp where ename=: name and DEPTNO=: deptno' My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (sql_text = > my_sqltext,bind_list = > sql_binds (anydata.convertvarchar2 (10), anydata.convertnumber (2)), user_name = > 'SYS', scope = >' COMPREHENSIVE', time_limit = > 60, task_name = > 'test_sql_tuning', description = > Task to tune a query on emp'); END / PL/SQL procedure successfully completed. Parameter description: bind_list: multiple bound variables are separated by', 'commas. Parameter values must be written according to the type of column corresponding to the bound variable. For example, if the emp.ename type is VARCHAR2 (10), it should be written as bind_list = > sql_binds (anydata.convertvarchar2 (10)), and time_limit: the maximum execution time. The default is 60. Scope:LIMITED, which takes about 1 second to optimize the SQL statement, but does not do SQL Profiling analysis; COMPREHENSIVE, does a comprehensive analysis, including SQL Profiling analysis; and takes longer than LIMITED. -- View task name SQL > select task_name from dba_advisor_log where task_name='test_sql_tuning';TASK_NAME--test_sql_tuning-- execute sql tuning task SQL > exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name= > 'test_sql_tuning') PL/SQL procedure successfully completed.-- View sql tunning task status SQL > select task_name,status from dba_advisor_log where task_name='test_sql_tuning' TASK_NAME STATUS---test_sql_tuning COMPLETED---- displays sql tunning results set long 10000set longchunksize 1000set linesize 100select dbms_sqltune.report_tuning_task ('test_sql_tuning') from dual;. Omit-- according to recommendation accept_sql_profileexecute dbms_sqltune.accept_sql_profile (.)-- delete sql tunning task exec dbms_sqltune.drop_tuning_task ('test_sql_tuning') after completion;-- check the progress of SQL Tuning Advisor (task has been implemented for a long time) set lines 200col opname for a20col ADVISOR_NAME for a20select sid,serial#,username,opname,advisor_name,target_desc,start_time sofar,totalwork from v$advisor_progress where username =' SYS'

3. Coe_xfr_sql_profile.sql fixed execution plan (also generate sql_profile)

Build the environment and set up the test table, just like the outline test

Create table zw as select * from dba_objects where object_id is not null

Alter table zw modify object_id not null;-the index does not store null values

Create index idx_zw_obj_id on zw (object_id)

Analyze table zw compute statistics

Select count (*) from zw

COUNT (*)

-

87038

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

PLAN_TABLE_OUTPUT

SQL_ID 1f5n0rapts695, child number 0

-

Select count (*) from zw

Plan hash value: 1836624960

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

| | 0 | SELECT STATEMENT | 54 (100) | |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | INDEX FAST FULL SCAN | IDX_ZW_OBJ_ID | 87038 | 54 (0) | 00:00:01 |

-- use the hint prompt to force the entire table to generate an execution plan:

SQL > select / * + full (zw) * / count (*) from zw

COUNT (*)

-

87038

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

PLAN_TABLE_OUTPUT

SQL_ID fp5ksbhww4594, child number 0

-

Select / * + full (zw) * / count (*) from zw

Plan hash value: 249608387

-

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

-

| | 0 | SELECT STATEMENT | 339 (100) | | |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | TABLE ACCESS FULL | ZW | 87038 | 339 (1) | 00:00:05 |

-

14 rows selected.

-- run the coe_xfr_sql_profile script: (fixed execution plan)

SQL > @ coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

Enter value for 1: 1f5n0rapts695-the sql_id of the original sql

PLAN_HASH_VALUE AVG_ET_SECS

--

1836624960. 02

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 249608387-the PLAN_HASH_ value of the execution plan you want to select

Values passed to coe_xfr_sql_profile:

~ ~

SQL_ID: "1f5n0rapts695"

PLAN_HASH_VALUE: "249608387"

SQL > BEGIN

2 IF: sql_text IS NULL THEN

3 RAISE_APPLICATION_ERROR (- 20100, 'SQL_TEXT for SQL_ID & & sql_id. Was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).')

4 END IF

5 END

6 /

SQL > SET TERM OFF

SQL > BEGIN

2 IF: other_xml IS NULL THEN

3 RAISE_APPLICATION_ERROR (- 20101, 'PLAN for SQL_ID & & sql_id. And PHV & & plan_hash_value. Was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).)

4 END IF

5 END

6 /

SQL > SET TERM OFF

Execute coe_xfr_sql_profile_1f5n0rapts695_249608387.sql

On TARGET system in order to create a custom SQL Profile

With plan 249608387 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL > @ coe_xfr_sql_profile_1f5n0rapts695_249608387.sql

Omit.

Coe_xfr_sql_profile_1f5n0rapts695_249608387 completed

SQL > explain plan for select count (*) from zw

Explained.

SQL > select * from table (dbms_xplan.display ())

PLAN_TABLE_OUTPUT

Plan hash value: 249608387

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 312 (1) | 00:00:04 |

| | 1 | SORT AGGREGATE | | 1 |

| | 2 | TABLE ACCESS FULL | ZW | 87038 | 312 (1) | 00:00:04 |

-

Note

PLAN_TABLE_OUTPUT

-

-SQL profile "coe_1f5n0rapts695_249608387" used for this statement

-- query:

SQL > select name,category,status,sql_text from dba_sql_profiles

NAME CATEGORY STATUS SQL_TEXT

-

Coe_1f5n0rapts695_249608387 DEFAULE ENABLED select count (*) from zw

When there is only one execution plan and you do not want the application to change the execution plan without changing the sql code, please refer to the automatic / manual type sql_profile (dbms_sqltune/coe_xfr_sql_profile.sql)

Is it easy to use coe_xfr_sql_profile.sql fixed plans? Yes, it's all thanks to the credit on oracle mos. Children's shoes you need can be found and downloaded on matelink. There are two other related scripts: coe_load_sql_baseline.sql,coe_load_sql_profile.sql. Interested children's shoes can be downloaded and studied together.

Think about it: who has the highest priority when using OUTLINE for fixing before using SQL_PROFILE binding?

According to some information on the Internet, it is said that OUTLINE has the highest priority, but it is all a short sentence that has not been proved. However, after countless tests, I found that SQL_PROFILE has a higher priority, so I will not paste out the specific test results. (maybe it's the particularity of my test statement, which needs further verification.)

It is worth mentioning that sql_profile does not store the frozen execution plan in outline. When the data in the table grows or the index is deleted or rebuilt, the execution plan can also change when the sql_profile is unchanged. The storage of information is related to the distribution of the data or the access path.

4. SQL PLAN MANAGE (baseline)

1. Starting from 11g, oracle introduced the new feature of SQL execution Plan Management (SQLPlan Management). Compared with Oracle 9i outline and 10g profile, Oracle 11g SPM is relatively more flexible and allows you to accept multiple execution plans at the same time.

2. Use SQL PlanManagement to perform a fixed execution plan

A SQL statement with bound variables, but when the data is unevenly distributed and heavily skewed, the best execution plan varies depending on the value of the bound variables. When executed, SPM takes very little operation to choose the most appropriate one, depending on the value of the variable.

SQL > select id,count (*) from test group by id order by 2

ID COUNT (*)

--

10 1100

88 10100

999 1000000

Next, define a variable a, assign values of 999 and 10 respectively, and see what its execution plan is.

SQL > alter system flush shared_pool

SQL > var A1 number

SQL > exec: A1 VOL999

SQL > select t.* from test t wheret.id=:a1

1000000 rows selected.

Elapsed: 00:00:25.30

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

PLAN_TABLE_OUTPUT

SQL_ID cpsdn05zdq02p,child number 0

-

Select t.* from test t where t.id=:a1

Plan hash value: 1357081020

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

| | 0 | SELECT STATEMENT | 424 (100) | | |

| | * 1 | TABLE ACCESS FULL | TEST | 337k | 1316K | 424 (2) | 00:00:06 |

PLAN_TABLE_OUTPUT

Predicate Information (identified byoperation id):

1-filter ("T". "ID" =: A1)

-# # there is an index IDX_ID # # on the ID column

SQL > alter system flush shared_pool

SQL > var A1 number

SQL > exec: A1 VOL10

SQL > select t.* from test t wheret.id=:a1

1100 rows selected.

Elapsed: 00:00:00.04

SQL > select * fromtable (dbms_xplan.display_cursor (null,0))

PLAN_TABLE_OUTPUT

SQL_ID cpsdn05zdq02p,child number 0

-

Select t.* from test t where t.id=:a1

Plan hash value: 1357081020

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

| | 0 | SELECT STATEMENT | 424 (100) | | |

| | * 1 | TABLE ACCESS FULL | TEST | 337k | 1316K | 424 (2) | 00:00:06 |

PLAN_TABLE_OUTPUT

Predicate Information (identified byoperation id):

1-filter ("T". "ID" =: A1)

You can see here that the execution plan is the same regardless of whether the assignment is 999 or 10, but according to theory, we all know that id=10 is the most efficient index. Assuming that the data is evenly distributed, the cardinality evaluates the cardinality=density*num_rows. Density can be queried through user_tab_col_statistics.

Select column_name,num_distinct,density from user_tab_col_statistics where table_name='TEST'

COLUMN_NAME NUM_DISTINCT DENSITY

ID 3. 333333333

The estimated 337k of the Rows column we see is that cardinality=density*num_rows=0.3333*1011200 is about 337k rows.

But we all know that ID=10 has only 1100 rows, while ID=999 has 1000000 rows, so full index scan in ID=10 and full table scan in ID=999 is the most reasonable execution plan.

So in the face of this situation, how can we optimize the implementation plan in this situation? There are several methods as follows:

(1) the way of removing binding variables and directly hard parsing (non-ideal, which is very undesirable if the program code is involved)

-- 2. Enable the new feature ACS of 11g (adaptive cursor, this BUG is not as many, so it is not recommended)

-- 3. Collect histogram information (if the resources for collecting histogram information cannot be evaluated during the peak production period)

4. Use SPM to add different execution plans to SQLPlan Baseline.

-- using manual capture

Alter system flush shared_pool

Var a1 number

Exec: A1 Vera 999

Select t.* from test t where t.id=:a1

Select * fromtable (dbms_xplan.display_cursor (null,0))

Var temp varchar2 (1000)

Exec: temp:=dbms_spm.load_plans_from_cursor_cache (sql_id = > 'cpsdn05zdq02p')

Exec: temp: = dbms_spm.alter_sql_plan_baseline (sql_handle= > 'SQL_d230ce970caa0077',plan_name= >' SQL_PLAN_d4c6fkw6an03r97bbe3d0',attribute_name= > 'ENABLED',attribute_value= >' NO');-first change the enabled attribute of the full table scan sql planbaselines to NO, otherwise the index will not be captured.

Exec: a1VlVO10

Select t.* from test t where t.id=:a1

Select * fromtable (dbms_xplan.display_cursor (null,0))

Exec: temp:=dbms_spm.load_plans_from_cursor_cache (sql_id = > 'cpsdn05zdq02p')

Dbms_spm.alter_sql_plan_baseline (sql_handle= > 'SQL_d230ce970caa0077',plan_name= >' SQL_PLAN_d4c6fkw6an03r97bbe3d0',attribute_name= > 'ENABLED',attribute_value= >' YES')

SQL > select sql_handle,plan_name,origin,enabled,accepted,fixed from dba_sql_plan_baselines

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

SQL_d230ce970caa0077SQL_PLAN_d4c6fkw6an03r97bbe3d0 MANUAL-LOAD YES YES NO

SQL_d230ce970caa0077SQL_PLAN_d4c6fkw6an03rf98b55bb MANUAL-LOAD YES YES NO

-- verify the result:

SQL > var A1 number

SQL > exec: A1 VOL10

SQL > select t.* from test t wheret.id=:a1

SQL > select * fromtable (dbms_xplan.display_cursor (null,0))

PLAN_TABLE_OUTPUT

SQL_ID cpsdn05zdq02p,child number 0

-

Select t.* from test t where t.id=:a1

Plan hash value: 578627003

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

| | 0 | SELECT STATEMENT | 5 (100) | |

| | * 1 | INDEX RANGE SCAN | IDX_ID | 1280 | 5120 | 5 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified byoperation id):

1-access ("T". "ID" =: A1)

Note

-

-SQL plan baseline SQL_PLAN_d4c6fkw6an03rf98b55bbused for this statement

22 rows selected.

SQL > var A1 number

SQL > exec: A1 VOL999

SQL > select t.* from test t wheret.id=:a1

SQL > select * fromtable (dbms_xplan.display_cursor (null,0))

PLAN_TABLE_OUTPUT

SQL_ID cpsdn05zdq02p,child number 0

-

Select t.* from test t where t.id=:a1

Plan hash value: 1357081020

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

| | 0 | SELECT STATEMENT | 424 (100) | | |

| | * 1 | TABLE ACCESS FULL | TEST | 1001K | 3912K | 424 (2) | 00:00:06 |

PLAN_TABLE_OUTPUT

Predicate Information (identified byoperation id):

1-filter ("T". "ID" =: A1)

Note

-

-SQL plan baseline SQL_PLAN_d4c6fkw6an03r97bbe3d0used for this statement

22 rows selected.

The flexibility of SPM is that it can be managed dynamically, unlike the storage outline (stored outline) and SQL Profile that need to be created manually by DBA, and of course SPM, because I didn't let it capture automatically in the above demonstration.

Think about: 1. Under what circumstances is it more effective to use a fixed method of implementing the plan? 2. Which priority is higher when all kinds of fixed execution plans are used?

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.