Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what basic tasks can be divided into SQL implementation plan management". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Use the dbms_spm and dbms_xplan packages to perform most of the SQL execution plan management tasks. SQL execution plan management can be divided into the following basic tasks:

. Configure SQL execution plan management

. Displays the execution plan in the baseline of the SQL execution plan

. Load SQL execution plan baseline

. Manual evolve execution plan execution plan in the baseline

. Delete the SQL execution plan baseline

. Manage SQL Management Base (SMB)

. Migrate Stored Outlines to SQL Plan Baselines

Configure SQL execution plan management

. Configure capture and use of SQL Plan Baselines

. Manage SPM Evolve Advisor Task

Configure capture and use of SQL Plan Baselines

You can use the optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines parameters to control SQL plan management.

SQL > show parameter sql_planNAME TYPE VALUE-- optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_ Use_sql_plan_baselines boolean TRUE

The default value for optimizer_capture_sql_plan_baselines is false. For any duplicate SQL statements that are not in the execution plan history, the database does not automatically create an initial SQL Plan Baseline for the SQL statement. If the optimizer_capture_sql_plan_baselines parameter is set to true, you can use the dbms_spm.configure procedure to configure the filter to determine which SQL statements meet the capture criteria. There is no filter configured by default, which means that all repeatedly executed SQL statements meet the capture condition.

The default value for optimizer_use_sql_plan_baselines is true. For any SQL statements that already exist in SQL plan baseline, the database automatically adds a new SQL plan to the SQL plan baselines with an unaccepted execution plan.

Enable automatic initialization of Plan capture for SQL Plan management

Setting the optimizer_capture_sql_plan_baselines parameter to true is necessary to automatically create an initialization SQL Plan baseline for any SQL statement that does not exist in the plan history. By default, when automatic SQL plan baseline capture is enabled, the database creates a SQL Plan baseline for each repeated SQL statement, including all recursive SQL statements and monitoring SQL statements. Therefore, the auto-capture feature may result in a large amount of SQL Plan Baseline. To limit the number of SQL Plan Baselines captured, you can use the dbms_spm.configure procedure to configure the filter criteria. The optimizer_capture_sql_plan_baselines parameter does not control the automatic addition of newly discovered execution plans to the previously created SQL plan baseline.

To enable automatic capture of SQL plan baseline, do the following:

1. Log in to the database with SQL*Plus as a user with relevant permissions

[oracle@jytest1] $sqlplus sys/abcd@jypdb as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

two。 Displays the settings for the current SQL Plan management

SQL > show parameter sql_planNAME TYPE VALUE-- optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_ Use_sql_plan_baselines boolean TRUE

3. To enable automatic generation of SQL Plan Baseline for duplicate SQL statements, execute the following statement

SQL > alter system set optimizer_capture_sql_plan_baselines=true scope=both sid='*' System altered.SQL > show parameter optimizer_capture_sql_plan_baselinesNAME TYPE VALUE-- optimizer_capture_sql_plan_baselines boolean TRUE

When the SQL Plan Baselines auto-capture feature is enabled, you can see the capture of the execution of all duplicate SQL statements from the following results

SQL > select t.sqlpowerhandle.sqltextnotet.creatorcirt.origin from DBA_SQL_PLAN_BASELINES t SQL_HANDLE SQL_TEXT CREATOR ORIGIN -SQL_187ebe987c151d1b select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS' SYS AUTO-CAPTURESQL_65afdf280fbfa69f select * from DBA_SQL_PLAN_BASELINES t SYS AUTO-CAPTURESQL_6807bab99db0361a select value from v$sesstat where sid =: sid order by statistic# SYS AUTO-CAPTURE

Configure filter criteria for automatic SQL Plan Baseline capture

If optimizer_capture_sql_plan_baselines is set to true, you can use the dbms_spm.configure procedure to create an automatic capture filter for repeated SQL statements. Automatic filtering can capture only the desired SQL statements and side by side except for key statements, which saves the use of SYSAUX tablespaces. You can configure multiple parameters for different types, or you can specify multiple parameter values for the same parameter in a separate statement, and the database combines them. This setting is additional: a parameter setting does not overwrite the previous setting. For example, the following filtering settings are used to capture SQL statements in the parsing scheme SYS or SYSTEM:

Exec dbms_spm.configure ('auto_capture_parsing_schema_name','sys',true); exec dbms_spm.configure (' auto_capture_parsing_schema_name','system',true)

However, you cannot specify multiple parameter values for the same parameter in the same process. For example, you cannot specify more than one SQL text string for AUTO_CAPTURE_SQL_TEXT. The DBA_SQL_MANAGEMENT_CONFIG view can be used to display the current parameter values.

The following operation assumes that the optimizer_capture_sql_plan_baselines parameter is set to true. Just capture all executed SQL statements of the sh scheme and want to exclude statements that contain test_only text

1. Log in to the database with SQL*Plus as a user with relevant permissions

[oracle@jytest1] $sqlplus sys/abcd@jypdb as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

two。 To delete any filter conditions that already exist for the parsing scheme and the SQL text, execute the following statement:

SQL > exec dbms_spm.configure ('auto_capture_parsing_schema_name',null,true); PL/SQL procedure successfully completed.SQL > exec dbms_spm.configure (' auto_capture_sql_text',null,true); PL/SQL procedure successfully completed.SQL > select parameter_name, parameter_value 2 from dba_sql_management_config 3 where parameter_name like'% AUTO%' PARAMETER_NAME PARAMETER_VALUE---AUTO_CAPTURE_PARSING_SCHEMA_NAMEAUTO_CAPTURE_MODULEAUTO_CAPTURE_ACTIONAUTO_CAPTURE_SQL_TEXT

3. Enable automatic capture only for statements executed by the sh scheme

SQL > exec dbms_spm.configure ('auto_capture_parsing_schema_name','sh',true); PL/SQL procedure successfully completed.

4. Exclude any statement that contains test_only text from automatic capture

SQL > exec dbms_spm.configure ('auto_capture_sql_text','%test_only%',false); PL/SQL procedure successfully completed.

5. Confirm the configured filter criteria by querying the dba_sql_management_config view

SQL > col parameter_name format a32SQL > col parameter_value format a32SQL > select parameter_name, parameter_value 2 from dba_sql_management_config 3 where parameter_name like'% AUTO%' PARAMETER_NAME PARAMETER_VALUE---AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (SH) AUTO_CAPTURE_MODULEAUTO_CAPTURE_ACTIONAUTO_CAPTURE_SQL_TEXT (sql_text NOT LIKE% test_only%)

Disable all SQL Plan Baselines

When the optimizer_use_sql_plan_baselines parameter is set to false, the database does not use any SQL Plan Baseline. To disable all SQL Plan baselines, do the following:

1. Log in to the database with SQL*Plus as a user with relevant permissions

[oracle@jytest1] $sqlplus sys/abcd@jypdb as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > show parameter sql_planNAME TYPE VALUE -optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_use_sql_plan_baselines boolean TRUE

two。 To ignore all existing SQL Plan Baselines, execute the following statement

SQL > alter system set optimizer_use_sql_plan_baselines=false scope=both sid='*' System altered.SQL > show parameter sql_planNAME TYPE VALUE-- optimizer_capture_sql_plan_baselines boolean FALSEoptimizer_use_sql_plan_baselines boolean FALSE

Manage SPM Evolve Advisor Task

SPM Evolve Advisor is a SQL Advisor that can evolve the SQL Plan that was recently added to the SQL Plan Baseline. By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduling maintenance window. SPM Evolve Advisor Task does the following:

1. Locate unaccepted SQL Plan

two。 Rank all unaccepted SQL Plan

3. Test and execute as many SQL Plan as possible in the maintenance window

4. Choose an implementation plan with the lowest cost and compare it with each unaccepted implementation plan

5. Use a cost-based algorithm to automatically accept any unaccepted execution plan that performs better than the existing accepted execution plan

Enable and disable SPM Evolve Advisor Task

There is no separate scheduling client for automatic SPM Evolve Advisor Task. A scheduling client controls automatic SQL Tuning Advisor and automatic SPM Evolve Advisor.

Configure automatic SPM Evolve Advisor Task

Configure automatic SQL Plan Evolve by using the dbms_spm.set_evolve_task_parameter procedure to specify task parameters. Because the owner of the SYS_AUTO_SPM_EVOLVE_TASK task is SYS, only the SYS user can set the task parameters.

Dbms_spm.set_evolve_task_parameter has the following parameters

Alternate_plan_source: the search source that decided to add SQL Plan: cursor_cache,automatic_workload_repository or sql_tuning_sets. You can use the + sign to combine multiple parameter values. The default value is cursor_cache+automatic_workload_repository.

Alternate_plan_baseline: decide which alternative plan should be loaded. EXISING, which is the default, uses the existing SQL Plan baseline to load SQL plan for the statement. NEW does not use the existing SQL plan baseline to load the SQL plan for the statement, and creates a new SQL Plan baseline. You can use the + sign to combine multiple parameter values.

Alternate_plan_limit: specifies the maximum number of SQL Plan that can be loaded, with a default value of 0.

Accept_plans: specifies whether to automatically accept the recommended SQL Plan. When accept_plans is set to true (the default), SQL Plan management automatically accepts all SQL Plan recommended by SPM Evolve Advisor Task. When set to false, if an alternative SQL plan,SPM Evolve Advisor Task is found, the SQLPlan is validated and a report is generated, but the SQL plan is not evolve.

The following actions meet the following conditions

. Want the database to accept SQL Plan automatically

. I want to time out 1200 seconds after each execution of the task.

. Want the evolve task to find up to 500 SQL Plan in the shared SQL area and AWR archives

Set automatic evolve task parameters

1. Log in to the database as sys user

[oracle@jytest1] $sqlplus sys/abcd@jypdb as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

two。 Query the current parameter settings of the sys_auto_spm_evolve_task task

SQL > col parameter_name format a25SQL > col value format a42SQL > select parameter_name, parameter_value as "value" 2 from dba_advisor_parameters 3 where ((task_name = 'SYS_AUTO_SPM_EVOLVE_TASK') and 4 ((parameter_name =' ACCEPT_PLANS') or 5 (parameter_name LIKE'% ALT%') or 6 (parameter_name = 'TIME_LIMIT')) PARAMETER_NAME value--TIME_LIMIT 3600ALTERNATE_PLAN_LIMIT 10ALTERNATE_PLAN_SOURCE CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORYALTERNATE_PLAN_BASELINE EXISTINGACCEPT_PLANS TRUE

3. Use the following PLSQL block to configure the sys_auto_spm_evolve_task task to automatically receive SQL plan, find up to 500 SQL plan in the shared SQL area and the AWR archive, and the task will time out after 20 minutes of execution.

SQL > begin 2 dbms_spm.set_evolve_task_parameter (task_name = > 'SYS_AUTO_SPM_EVOLVE_TASK', parameter = >' TIME_LIMIT', value = > '1200'); 3 dbms_spm.set_evolve_task_parameter (task_name = >' SYS_AUTO_SPM_EVOLVE_TASK', parameter = > 'ACCEPT_PLANS', value = >' true') 4 dbms_spm.set_evolve_task_parameter (task_name = > 'SYS_AUTO_SPM_EVOLVE_TASK', parameter = >' ALTERNATE_PLAN_LIMIT', value = > '500'), 5 end; 6 / PL/SQL procedure successfully completed.

4. Confirm the current parameter settings for the sys_auto_spm_evolve_task task

SQL > col parameter_name format a25SQL > col value format a42SQL > select parameter_name, parameter_value as "value" 2 from dba_advisor_parameters 3 where ((task_name = 'SYS_AUTO_SPM_EVOLVE_TASK') and 4 ((parameter_name =' ACCEPT_PLANS') or 5 (parameter_name LIKE'% ALT%') or 6 (parameter_name = 'TIME_LIMIT')) PARAMETER_NAME value--TIME_LIMIT 1200ALTERNATE_PLAN_LIMIT 500ALTERNATE_PLAN_SOURCE CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORYALTERNATE_PLAN_BASELINE EXISTINGACCEPT_PLANS true

Displays the execution plan in the baseline of the SQL execution plan

To see the SQL Plan that the specified SQL statement is stored in SQL Plan Baseline, you can use the dbms_xplan.display_sql_plan_baseline function. This function uses the execution plan information stored in plan history to display SQL Plan. It has the following parameters:

Sql_handle: the SQL handle of a statement can be queried by concatenating v$sql.sql_plan_baseline and dba_sql_plan_baselines.plan_name columns

Plan_name: the name of the statement execution plan

Suppose you want to show that the execution plan of a statement whose SQL ID is 34q7g1h59b79n is stored in SQL Plan Baseline executes the following statement

SQL > select * from hr.jobs JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY--AD_PRES President 20080 4000AD _ VP Administration Vice President 15000 30000AD_ASST Administration Assistant 3000 6000FI_MGR Finance Manager 8200 16000FI_ACCOUNT Accountant 4200 9000AC_MGR Accounting Manager 8200 16000AC_ACCOUNT Public Accountant 4200 9000SA_MAN Sales Manager 10000 20080SA_REP Sales Representative 6000 12008PU_MAN Purchasing Manager 8000 15000PU_CLERK Purchasing Clerk 2500 5500JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY- -ST_MAN Stock Manager 5500 8500ST_CLERK Stock Clerk 2008 5000SH_CLERK Shipping Clerk 2500 5500IT_PROG Programmer 4000 10000MK_MAN Marketing Manager 9000 15000MK_REP Marketing Representative 4000 9000HR_REP Human Resources Representative 4000 9000PR_REP Public Relations Representative 4500 1050019 rows selected.SQL > select * from table (dbms_xplan.display_cursor (null Null,'advanced')) PLAN_TABLE_OUTPUT----SQL_ID 4gc64454ax64x Child number 1--select * from hr.jobsPlan hash value: 944056911 Murray- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | 0 | SELECT STATEMENT | | | 3 (100) | | 1 | TABLE ACCESS FULL | JOBS | 19 | 627 | 3 (0) | 00:00:01 | PLAN_TABLE_OUTPUT---- | -Query Block Name / Object Alias (identified by operation id):- -1-SEL$1 / JOBS@SEL$1Outline Data- / * + PLAN_TABLE_OUTPUT -BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE ('12.2.0.1') DB_VERSION ('12.2.0.1') OPT_PARAM ('optimizer_dynamic_sampling' 0) ALL_ROWS NO_PARALLEL OUTLINE_LEAF (@ "SEL$1") FULL (@ " SEL$1 "" JOBS "@" SEL$1 ") END_OUTLINE_DATA * / PLAN_TABLE_OUTPUT----Column Projection Information (identified by operation id):- -1-"JOBS". "JOB_ID" [VARCHAR2,10] "JOBS". "JOB_TITLE" [VARCHAR2,35], "JOBS". "MIN_SALARY" [NUMBER,22], "JOBS". "MAX_SALARY" [NUMBER 22] Note--automatic DOP: Computed Degree of Parallelism is 1 because of parallel thresholdPLAN_TABLE_OUTPUT-----SQL plan baseline SQL_PLAN_bmz5xhst6b31y41975532 used For this statement45 rows selected.SQL > SELECT PLAN_TABLE_OUTPUT 2 FROM V$SQL s DBA_SQL_PLAN_BASELINES b, 3 TABLE (4 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (b.sqlgift handledjiggc64454ax64x') t 6 WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE 7 AND b.PLAN_NAME=s.SQL_PLAN_BASELINE 8 AND s.SQLroomID cards 4gc64454ax64x' PLAN_TABLE_OUTPUT- -SQL handle: SQL_b9fcbd8632658c3eSQL text: select * from hr.jobs -Plan name: SQL_PLAN_bmz5xhst6b31y41975532 Plan id: 1100436786Enabled: YES Fixed : NO Accepted: YES Origin: AUTO-CAPTUREPlan rows: From dictionary----PLAN_TABLE_OUTPUT- -Plan hash value: 944056911-| Id | Operation | Name |- -| 0 | SELECT STATEMENT | 1 | TABLE ACCESS FULL | JOBS |-- 20 rows selected.

The above results show that the selected execution plan in which SQL ID is 4gc64454ax64x is called SQL_PLAN_bmz5xhst6b31y41975532 and is automatically captured.

Load SQL execution plan baseline

Using dbms_spm, you can bulk load a set of existing execution plans into a SQL Plan Baseline. The dbms_spm package can load execution plans from the following sources:

.awr: to load an execution plan from an AWR snapshot, you must specify a range for the start and end of the snapshot, or you can apply filtering criteria to load only execution plans that meet the criteria. By default, the optimizer uses the execution plan loaded into SQL Plan Baseline the next time the database executes the SQL statement.

. Shared SQL zone: load the execution plan directly from the shared SQL zone. You can identify SQL statements or a set of SQL ID statements that need to be captured by applying filter criteria to the module name, scheme name, or SQL. The next time the database executes the SQL statement, the optimizer uses the execution plan loaded into the SQL Plan Baseline. It is useful to load the execution plan directly from the shared hints zone after the application SQL has been optimized through handwritten SQL. Because you may not be able to change SQL, including hint, using SQL Plan Baseline ensures that the application SQL uses the optimal execution plan.

SQL tuning set (STS): capture the execution plan of the SQL workload into a STS, and then load the execution plan into the SQL Plan Baselines. The next time the database executes the SQL statement, the optimizer uses the execution plan loaded into the SQL Plan Baseline. Bulk loading the execution plan from STS is an effective way to prevent the execution plan from returning after a database upgrade.

Staging table: using the dbms_spm package, you can define a staging table, and the dbms_spm.pack_stgtab_baseline process can copy SQLPlan baseline into a staging table and use Oracle data pump to transfer a common staging table to another database. In the target database, use the dbms_spm.unpack_stgtab_baseline procedure to load SQL plan baseline from the staging table into SMB.

.stored outline: migrate stroed outlines to SQL Plan Baselines. After migration, you can maintain the same execution plan stability through the more advanced features provided by SQL Plan Management.

Load an execution plan from AWR

Suppose we want to load the execution plan of the following query statement into SQL Plan Baseline, then make sure that user sh has permission to query dba_hist_snapshot and dba_sql_plan_baselines views, execute dbms_workload_repository.create_snapshot and dbms_spm.load_plans_from_awr

SELECT / * LOAD_AWR*/ * FROM sh.salesWHERE quantity_sold > 40ORDER BY prod_id

To load the execution plan from AWR into SQL Plan Baselines, do the following

1. Log in to the database as a user with relevant permissions, and then query the 3 recently generated AWR snapshots

SQL > select * 2 from (select instance_number,snap_id, snap_level, 3 to_char (begin_interval_time, 'dd/mm/yy hh34:mi:ss') begin 4 from dba_hist_snapshot 5 order by snap_id desc) 6 where rownum

< = 3;INSTANCE_NUMBER SNAP_ID SNAP_LEVEL BEGIN--------------- ---------- ---------- ----------------- 1 7061 1 14/02/19 16:00:09 2 7061 1 14/02/19 16:00:09 1 7060 1 14/02/19 15:00:35 2.查询sh.sales表，使用load_awr标记来识别这个SQL语句 SQL>

Select / * load_awr*/ * 2 from sh.sales 3 where quantity_sold > 40 4 order by prod_id;no rows selected

3. Generate a new AWR snapshot

SQL > exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completed.

4. Query the 3 recently generated AWR snapshots to ensure that new AWR snapshots have been generated

SQL > select * 2 from (select instance_number,snap_id, snap_level, 3 to_char (begin_interval_time, 'dd/mm/yy hh34:mi:ss') begin 4 from dba_hist_snapshot 5 order by snap_id desc) 6 where rownum

< = 3;INSTANCE_NUMBER SNAP_ID SNAP_LEVEL BEGIN--------------- ---------- ---------- ----------------- 1 7062 1 14/02/19 17:00:09 2 7062 1 14/02/19 17:00:09 1 7061 1 14/02/19 16:00:09 5.使用最近生成的2个AWR快照来加载执行计划 SQL>

Variable v_plan_cnt numberSQL > exec: v_plan_cnt: = dbms_spm.load_plans_from_awr (begin_snap = > 7061, end_snap = > 7062), PL/SQL procedure successfully completed.

6. Query the data dictionary to ensure that the execution plan of the load_awr statement is loaded into the SQL Plan Baselines

SQL > col sql_handle format a20SQL > col sql_text format a20SQL > col plan_name format a30SQL > col origin format a20SQL > select sql_handle, sql_text, plan_name, 2 origin, enabled, accepted 3 from dba_sql_plan_baselines 4 where sql_text like'% load_awr%' SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_495d29c5f4612cda select / * load_awr*/ SQL_PLAN_4kr99sru62b6u54bc8843 MANUAL-LOAD-FROM-AWR YES YES * from sh.sales where quantity_sold > 40 order by prod_id

7. Execute the load_awr statement again and look at its execution plan. You can see information like SQL plan baseline SQL_PLAN_4kr99sru62b6u54bc8843 used for this statement, indicating that the baseline of the generated execution plan is applied to the statement.

SQL > select / * load_awr*/ * 2 from sh.sales 3 where quantity_sold > 40 4 order by prod_id;no rows selectedSQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced')) PLAN_TABLE_OUTPUT-SQL_ID dybku83zppk0d Child number 1--select / * load_awr*/ * from sh.sales where quantity_sold > 40 order byprod_idPlan hash value: 3803407550 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |-- -| 0 | SELECT STATEMENT | 511,100 | 1 | SORT ORDER BY | | 1 | 29 | 511 (2) | 00:00:01 | | 2 | PARTITION RANGE ALL | | 1 | 29 | 510 (2) | 00:00:01 | 1 | 28 | * 3 | TABLE ACCESS FULL | SALES | 1 | 29 | 510 (2) | 00:00:01 | 1 | 28 |- -Query Block Name / Object Alias (identified by operation id):- -1-SEL$1 3-SEL$1 / SALES@SEL$1Outline Data- / * + BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE ('12.2.0.1') DB_VERSION ('12.2.0.1') OPT_PARAM ('optimizer_dynamic_sampling' 0) ALL_ROWS NO_PARALLEL OUTLINE_LEAF (@ "SEL$1") FULL (@ "SEL$1"SALES" @ "SEL$1") END_OUTLINE_DATA * / Predicate Information (identified by operation id):-3-filter ("QUANTITY_SOLD" > 40) Column Projection Information (identified by operation id) :-1-(# keys=1) "SALES". "PROD_ID" [NUMBER 22], "SALES". "CUST_ID" [NUMBER,22], "SALES". "TIME_ID" [DATE,7], "SALES". "CHANNEL_ID" [NUMBER,22], "SALES". "PROMO_ID" [NUMBER,22], "SALES". "QUANTITY_SOLD" [NUMBER,22], "SALES". "AMOUNT_SOLD" [NUMBER,22] 2-(rowset=256) "SALES". "PROD_ID" [NUMBER,22] "SALES". "CUST_ID" [NUMBER,22], "SALES". "TIME_ID" [DATE,7], "SALES". "CHANNEL_ID" [NUMBER,22], "SALES". "PROMO_ID" [NUMBER,22], "QUANTITY_SOLD" [NUMBER,22], "SALES". "AMOUNT_SOLD" [NUMBER,22] 3-(rowset=256) "SALES". "PROD_ID" [NUMBER,22] SALES. "CUST_ID" [NUMBER,22], "SALES". "TIME_ID" [DATE,7], "SALES". "CHANNEL_ID" [NUMBER,22], "SALES". "PROMO_ID" [NUMBER,22], "QUANTITY_SOLD" [NUMBER,22], "SALES". "AMOUNT_SOLD" [NUMBER 22] Note--automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold-SQL plan baseline SQL_PLAN_4kr99sru62b6u54bc8843 used for this statement64 rows selected.

Load an execution plan from a shared SQL zone

Suppose you want to load the execution plan of the following query statement into SQL Plan Baseline from a shared SQL area

1. Execute SQL statement

SQL > SELECT / * LOAD_CC*/ * 2 FROM sh.sales 3 WHERE quantity_sold > 40 4 ORDER BY prod_id;no rows selected

two。 Query v$sql view query SQL ID of execution statement

SQL > SELECT SQL_ID, CHILD_NUMBER AS "Child Num", 2 PLAN_HASH_VALUE AS "Plan Hash", 3 OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash" 4 FROM V$SQL 5 WHERE SQL_TEXT LIKE 'SELECT / * LOAD_CC*/%' SQL_ID Child Num Plan Hash Opt Env Hash--09x8cz4wrn655 0 3803407550 4099961812

3. Load the execution plan of the specified statement from the shared SQL area into SQL Plan Baseline

SQL > VARIABLE v_plan_cnt NUMBERSQL > EXECUTE: v_plan_cnt: = DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id = > '09x8cz4wrn655'); PL/SQL procedure successfully completed.

4. Query the dba_sql_plan_baselines view to confirm that the execution plan of the statement is loaded into the SQL Plan Baselines

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED 3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE'% LOAD_CC%' SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_f6cb7f742ef93547 SELECT / * LOAD_CC*/ * SQL_PLAN_gdkvzfhrgkda754bc8843 MANUAL-LOAD-FROM-CUR YES YES SOR-CACHE FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id

Load an execution plan from SQL Tuning Set

A SQL Tuning Set is a database object that includes one or more SQL statements, execution statistics and execution context information. Suppose SQLTuning Set contains the following statement, and to load the execution plan of the statement from SQLTuning Set into SQL Plan Baselines, do the following

1. Execute the SQL statement and find its SQL ID

SQL > SELECT / * LOAD_STS*/ * 2 FROM sh.sales 3 WHERE quantity_sold > 40 4 ORDER BY prod_id;no rows selectedSQL > SELECT SQL_ID, CHILD_NUMBER AS "Child Num", 2 PLAN_HASH_VALUE AS "Plan Hash", 3 OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash" 4 FROM V$SQL 5 WHERE SQL_TEXT LIKE 'SELECT / * LOAD_STS*/%' SQL_ID Child Num Plan Hash Opt Env Hash--bma11r5a6r26j 0 3803407550 4099961812

two。 Load the executed SQL statement into SQL Tuning Set

SQL > exec dbms_sqltune.create_sqlset (sqlset_name= > 'sql_tuning_set'); PL/SQL procedure successfully completed.SQL > DECLARE 2 cur DBMS_SQLTUNE.SQLSET_CURSOR 3 BEGIN 4 OPEN cur FOR 5 SELECT VALUE (P) 6 FROM table (DBMS_SQLTUNE.SELECT_CURSOR_CACHE ('sql_id=''bma11r5a6r26j''', 7 NULL, 8 NULL, 9 NULL) 10 NULL, 11 1, 12 NULL, 13 'ALL') P 14 DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name = > 'sql_tuning_set', 15 populate_cursor = > cur); 16 END; 17 / PL/SQL procedure successfully completed.SQL > SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME =' sql_tuning_set';SQL_TEXT-SELECT / * LOAD_STS*/*FROM sh.salesWHERE quantity_sold > 40ORDER BY prod_id

3. Load the execution plan from SQL Tuning Set into SQL Plan Baseline

SQL > VARIABLE v_plan_cnt NUMBERSQL > EXECUTE: v_plan_cnt: = DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name = > 'sql_tuning_set',basic_filter = >' sql_text like''SELECT / * LOAD_STS*/%'''); PL/SQL procedure successfully completed.

The basic_filter parameter specifies that a where clause is used to load only the required SQL statements, and v_plan_cnt is used to store the number of execution plans loaded from SQL Tuning Set.

4. Query the data dictionary to ensure that the execution plan of the statements in SQL Tuning Set is successfully loaded into SQL Plan Baselines

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED 3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE'% LOAD_STS%' SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_a8632bd857a4a25e SELECT / * LOAD_STS*/ SQL_PLAN_ahstbv1bu98ky54bc8843 MANUAL-LOAD-FROM-STS YES YES * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id

5. Delete SQL Tuning Set

SQL > exec dbms_sqltune.drop_sqlset (sqlset_name= > 'sql_tuning_set'); PL/SQL procedure successfully completed.

Load an execution plan from Staging Table

Sometimes you may need to transfer an optimized execution plan from a source database to a target database, then you need to do the following

1. Use the create_stgtab_baseline procedure to create an staging table

SQL > BEGIN 2 DBMS_SPM.CREATE_STGTAB_BASELINE (3 table_name = > 'stage1'), 4 END; 5 / PL/SQL procedure successfully completed.

two。 In the source database, package SQL Plan Baseline from the SQL management infrastructure into the staging table

DECLAREv_plan_cnt NUMBER;BEGINv_plan_cnt: = DBMS_SPM.PACK_STGTAB_BASELINE (table_name = > 'stage1', enabled = >' yes', creator = > 'spm'); END;/

3. Export the staging table stage1 to a dump file using Oracle Data Pump Export

4. Transfer the dump file to the target database

5. In the target database, import the data from the dump file into the staging table stage1 using Oracle Data Pump Import

6. In the target database, extract the SQL Plan Baseline from the staging table into the SQL management infrastructure

DECLAREv_plan_cnt NUMBER;BEGINv_plan_cnt: = DBMS_SPM.UNPACK_STGTAB_BASELINE (table_name = > 'stage1', fixed = >' yes'); END;/

SQL Plan baselines Evolve

Here is how to use the command line to evolve sql plan baselines. To evolve a specific sql execution plan, do the following:

1. Create an evolve task

two。 Set evolve task parameters

3. Perform evolve tasks

4. Implement the recommendations given in the task

5. Display the results of task execution

The following examples are given, assuming that the following conditions are met

. Automatic evolve tasks are not enabled in the database

. Create a SQL Plan Baseline for the following query

SELECT / * q2_group_by * / prod_name, sum (quantity_sold) FROM products p, sales sWHERE p.prod_id = s.prod_idAND p.prod_category_id = 204GROUP BY prod_name

. If you want to create two indexes to improve the performance of the query statement, if the performance of using the index is better than that of the current execution plan in SQL Plan Baseline, then evolve should execute the plan

To evolve a specific execution plan, you need to do the following

1. Perform initialization setting operation

Clear shared pools and buffer caches

SQL > ALTER SYSTEM FLUSH SHARED_POOL;System altered.SQL > ALTER SYSTEM FLUSH BUFFER_CACHE;System altered.

Enable automatic capture of SQL Plan Baselines

SQL > ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true System altered.SQL > show parameter sql_planNAME TYPE VALUE-- optimizer_capture_sql_plan_baselines boolean TRUEoptimizer_use_sql_plan_baselines boolean TRUE

Log in to the database as a sh user and set the display parameters for SQLPLUS

[oracle@jytest1] $sqlplus sh/sh@jypdbSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:30:09 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Thu Feb 14 2019 23:01:23 + 08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > SET PAGES 10000 LINES 140SQL > SET SERVEROUTPUT ONSQL > COL SQL_TEXT FORMAT A20SQL > COL SQL_HANDLE FORMAT A20SQL > COL PLAN_NAME FORMAT A30SQL > COL ORIGIN FORMAT A12SQL > SET LONGC 60535SQL > SET LONG 60535SQL > SET ECHO ON

two。 Execute the SQL statement, so it can be captured automatically

Execute the following SQL statement

SQL > SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id = 2035 GROUP BY prod_name PROD_NAME SUM (QUANTITY_SOLD)-Envoy External 6X CD-ROM 11526Model SM26273 Black Ink Cartridge 15910PCMCIA modem/fax 28800 baud 19278Multimedia speakers- 3 "cones 10969Internal 8X CD-ROM 11197Deluxe Mouse 11609Model CD13272 Tricolor Ink Cartridge 12321Model NM500X High Yield Toner Cartridge 646618 "Flat Panel Graphics Monitor 4415External 8X CD-ROM 13886SIMM-8MB PCMCIAII card 17544PCMCIA modem/fax 19200 baud 20467Envoy External 8X CD-ROM 14850Envoy External Keyboard 2857External 6X CD-ROM 11732Model A3827H Black Image Cartridge 17314Internal 6X CD-ROM 853317 "LCD w/built-in HDTV Tuner 4874 Simm-16MB PCMCIAII card 14191Multimedia speakers- 5" cones 10419Standard Mouse 871421 rows selected.

The query data dictionary confirms that there is no execution plan in SQL Plan Baseline because only repeated SQL statements are captured

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, 2 ACCEPTED, FIXED, AUTOPURGE 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE'% Q1 roomgroup% destroy no rows selected

Execute the SQL statement again

SQL > SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id = 2035 GROUP BY prod_name PROD_NAME SUM (QUANTITY_SOLD)-Envoy External 6X CD-ROM 11526Model SM26273 Black Ink Cartridge 15910PCMCIA modem/fax 28800 baud 19278Multimedia speakers- 3 "cones 10969Internal 8X CD-ROM 11197Deluxe Mouse 11609Model CD13272 Tricolor Ink Cartridge 12321Model NM500X High Yield Toner Cartridge 646618 "Flat Panel Graphics Monitor 4415External 8X CD-ROM 13886SIMM-8MB PCMCIAII card 17544PCMCIA modem/fax 19200 baud 20467Envoy External 8X CD-ROM 14850Envoy External Keyboard 2857External 6X CD-ROM 11732Model A3827H Black Image Cartridge 17314Internal 6X CD-ROM 853317 "LCD w/built-in HDTV Tuner 4874 Simm-16MB PCMCIAII card 14191Multimedia speakers- 5" cones 10419Standard Mouse 871421 rows selected.

3. Query the data dictionary to ensure that the execution plan has been loaded into SQL Plan Baseline, and the following query shows that the execution plan has been accepted, which means that the execution plan has been stored in SQL Plan Baselines. The origin column is displayed as AUTO-CAPTURE, which means that the execution plan is automatically captured

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED, FIXED 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE'% Q1 groups%' SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX -SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO y * / prod_name Sum (quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. Prod_id AND p. Prodleware id = 203 GROUP BY prod_name

4. The following parses the SQL statement and verifies that the optimizer uses the execution plan in SQL Plan Baseline

SQL > EXPLAIN PLAN FOR 2 SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id = 2036 GROUP BY prod_name;Explained.SQL > SELECT * FROM TABLE (null, null, 'basic + note')) PLAN_TABLE_OUTPUT- -Plan hash value: 3535171836-| Id | Operation | Name |-- -| 0 | SELECT STATEMENT | | 1 | HASH GROUP BY | 2 | HASH JOIN | | 3 | TABLE ACCESS FULL | PRODUCTS | 4 | PARTITION RANGE ALL | 5 | TABLE ACCESS FULL | SALES |-- | Note--SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement16 rows selected.

You can see from the Note section of the execution plan that SQL Plan Baseline has been applied to this SQL statement

5. Create two indexes to improve the performance of the above SQL statement

SQL > CREATE INDEX ind_prod_cat_name ON products (prod_category_id, prod_name, prod_id); Index created.SQL > CREATE INDEX ind_sales_prod_qty_sold ON sales (prod_id, quantity_sold); Index created.

6. Execute the SQL statement again, and because automatic capture is enabled, the new execution plan will be loaded into the SQL Plan Baseline

SQL > SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id = 2035 GROUP BY prod_name PROD_NAME SUM (QUANTITY_SOLD)-Envoy External 6X CD-ROM 11526Model SM26273 Black Ink Cartridge 15910PCMCIA modem/fax 28800 baud 19278Multimedia speakers- 3 "cones 10969Internal 8X CD-ROM 11197Deluxe Mouse 11609Model CD13272 Tricolor Ink Cartridge 12321Model NM500X High Yield Toner Cartridge 646618 "Flat Panel Graphics Monitor 4415External 8X CD-ROM 13886SIMM-8MB PCMCIAII card 17544PCMCIA modem/fax 19200 baud 20467Envoy External 8X CD-ROM 14850Envoy External Keyboard 2857External 6X CD-ROM 11732Model A3827H Black Image Cartridge 17314Internal 6X CD-ROM 853317 "LCD w/built-in HDTV Tuner 4874 Simm-16MB PCMCIAII card 14191Multimedia speakers- 5" cones 10419Standard Mouse 871421 rows selected.

7. Query the data dictionary to ensure that the new execution plan is loaded into SQL Plan Baseline

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED 2 FROM DBA_SQL_PLAN_BASELINES 3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342') 4 ORDER BY SQL_HANDLE, ACCEPTED SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO y * / prod_name Sum (quantity_sold) FROM products p Sal es s WHERE p.prod_id = s. Prod_id AND p. Produdes id = 203GROUP BY prod_nameSQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES y * / prod_name Sum (quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. Prod_id AND p. Prodleware id = 203 GROUP BY prod_name

The above query results show that the new execution plan is accepted.

8. Parse the SQL statement again and verify that the optimizer uses the original unindexed execution plan

SQL > EXPLAIN PLAN FOR 2 SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id = 2036 GROUP BY prod_name;Explained.SQL > SELECT * FROM TABLE (null, null, 'basic + note')) PLAN_TABLE_OUTPUT- -Plan hash value: 3535171836-| Id | Operation | Name |-- -| 0 | SELECT STATEMENT | | 1 | HASH GROUP BY | 2 | HASH JOIN | | 3 | TABLE ACCESS FULL | PRODUCTS | 4 | PARTITION RANGE ALL | 5 | TABLE ACCESS FULL | SALES |-- | Note--SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement16 rows selected.

The Note section above indicates that the optimizer uses the original unindexed execution plan

9. Log in to the database as an administrator user, and then create an evolve task that contains all SQL statements related to the execution plan that are not accepted

[oracle@jytest1] $sqlplus sys/xxzx7817600@jypdb as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:48:51 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > VARIABLE cnt NUMBERSQL > VARIABLE tk_name VARCHAR2 (50) SQL > VARIABLE exe_name VARCHAR2 (50) SQL > VARIABLE evol_out CLOBSQL > EXECUTE: tk_name: = DBMS_SPM.CREATE_EVOLVE_TASK (sql_handle = > 'SQL_07f16c76ff893342',plan_name = >' SQL_PLAN_0gwbcfvzskcu20135fd6c'); PL/SQL procedure successfully completed.SQL > SELECT: tk_name FROM DUAL : TK_NAME- -TASK_11

10. Perform evolve tasks

SQL > EXECUTE: exe_name: = DBMS_SPM.EXECUTE_EVOLVE_TASK (task_name= >: tk_name); PL/SQL procedure successfully completed.SQL > SELECT: exe_name FROM DUAL;:EXE_NAME----EXEC_1

11. View report

EXECUTE: evol_out: = DBMS_SPM.REPORT_EVOLVE_TASK (task_name= >: tk_name,execution_name= >: exe_name); SELECT: evol_out FROM DUAL GENERAL INFORMATION SECTION----Task Information:-- -Task Name: TASK_11Task Owner: SYSExecution Name: EXEC_1Execution Type: SPM EVOLVEScope: COMPREHENSIVEStatus: COMPLETEDStarted: 02 17:49:32Finished: 02 17:49:35Global Time Limit 2019 17:49:35Global Time Limit: 2147483646Per-Plan Time Limit: UNUSEDNumber of Errors: 0- -SUMMARY SECTION -- Number of plans processed: 1Number of findings: 1Number of recommendations: 1Number of errors: 0Murray- DETAILS SECTION----Object ID: 2Test Plan Name: SQL_PLAN_0gwbcfvzskcu20135fd6cBase Plan Name: SQL_PLAN_0gwbcfvzskcu242949306SQL Handle: SQL_07f16c76ff893342Parsing Schema: SHTest Plan Creator: SHSQL Text: SELECT / * q1_group_by*/ prod_name Sum (quantity_sold) FROM products p Sales sWHERE p.prod_id=s.prod_id AND p.prod_category_id=203GROUP BY prod_nameExecution Statistics:--Base Plan Test Plan---Elapsed Time (s): .044336 .012649 Time (s): .012445 buffer Gets: 044003 99Optimizer Cost: 924 891Disk Reads: 341CPU: 0 0Rows Processed: 4 2Executions: 5 9FINDINGS SECTION----Findings (1): -1. The plan was verified in 2.18 seconds. It passed the benefit criterionbecause its verified performance was 2.01 times better than that of thebaseline plan.Recommendation:--Consider accepting the plan. Executedbms_spm.accept_sql_plan_baseline (task_name = > 'TASK_11', object_id = > 2pm taskkeeper owner = >' SYS') EXPLAIN PLANS SECTION----Baseline Plan--Plan Id: 1Plan Hash Value: 1117033222 Murray- -| Id | Operation | Name | Rows | Bytes | Cost | Time |- -| 0 | SELECT STATEMENT | | 21 | 861 | 924 | 00:00:12 | | 1 | HASH GROUP BY | | 21 | 861 | 924 | 00:00:12 | * 2 | HASH JOIN | | | | 267996 | 10987836 | 742 | 00:00:09 | | * 3 | TABLE ACCESS FULL | PRODUCTS | 21 | 714 | 2 | 00:00:01 | 4 | PARTITION RANGE ALL | | 918843 | 6431901 | 662 | 00:00:08 | | 5 | TABLE ACCESS FULL | SALES | 918843 | 6431901 | 662 | 00:00:08 |-| -- Predicate Information (identified by operation id):-- * 2-access ("P". "PROD_ID" = "S". "PROD_ID") * 3-filter ("P" . "PROD_CATEGORY_ID" = 203) Test Plan--Plan Id: 2Plan Hash Value: 20315500 Murray- | Id | Operation | Name | Rows | Bytes | Cost | Time |-- | 0 | SELECT STATEMENT | | 21 | | 1 | SORT GROUP BY NOSORT | | 21 | 861 | 00:00:11 | | 2 | NESTED LOOPS | | 267996 | 10987836 | 891 | 00:00:11 | | * 3 | INDEX RANGE SCAN | IND_PROD_CAT_NAME | 21 | 714 | 1 | 00:00:01 | * 4 | INDEX RANGE SCAN | IND_SALES_PROD_QTY | 12762 | 89334 | 42 | 00:00:01 |-| -Predicate Information (identified by operation id):-* 3-access ("P". "PROD_CATEGORY_" ID "= 203) * 4-access (" P "." PROD_ID "=" S "." PROD_ID ")

The report shows that the execution plan using two indexes performs better than the original execution plan.

twelve。 Implement the recommendations given by evolve tasks

SQL > EXECUTE: cnt: = DBMS_SPM.IMPLEMENT_EVOLVE_TASK (task_name= >: tk_name,execution_name= >: exe_name); PL/SQL procedure successfully completed.

13. Query the data dictionary to ensure that the new execution plan is accepted

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED 2 FROM DBA_SQL_PLAN_BASELINES 3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342') 4 ORDER BY SQL_HANDLE, ACCEPTED SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES y * / prod_name Sum (quantity_sold) FROM products p Sal es s WHERE p.prod_id = s. Prod_id AND p. Produdes _ id = 203 GROUP BY prod_nameSQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC--SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu2ae9b4305 AUTO-CAPTURE YES YES y * / prod_name Sum (quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. Prod_id AND p. Prodleware id = 203 GROUP BY prod_name

14. Perform a cleanup operation

SQL > VARIABLE cnt NUMBERSQL > EXEC: cnt: = DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_07f16c76ff893342'); PL/SQL procedure successfully completed.SQL > DELETE FROM SQLLOG$;13 rows deleted.SQL > commit;Commit complete.SQL > DROP INDEX IND_SALES_PROD_QTY_SOLD;Index dropped.SQL > DROP INDEX IND_PROD_CAT_NAME;Index dropped.

Delete SQL Plan Baselines

Some or all of the execution plans can be removed from SQL Plan Baselines.

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED 3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE'% q3roomgroupby%' SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_50c02f29322b0d02 SELECT SQL_HANDLE S SQL_PLAN_51h2g54t2q38276fe3bd1 AUTO-CAPTURE YES YES QL_TEXT, PLAN_NAME, ORIGIN, ENABLED ACC EPTED FROM DBA_SQL_PLAN_BA SELINES WHERE SQL_TE XT LIKE'% q3_group_b y%'SQL_6d39c79190585ca9 SELECT / * q3_group_b SQL_PLAN_6uff7k685hr5942949306 AUTO-CAPTURE YES YES y * / prod_name Sum (quantity_sold) FROM products p Sal es s WHERE p.prod_id = s. Prod_id AND p. Produdes id = 205GROUP BY prod_nameSQL_6d39c79190585ca9 SELECT / * q3_group_b SQL_PLAN_6uff7k685hr59ae9b4305 AUTO-CAPTURE YES NO y * / prod_name Sum (quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. Prod_id AND p. Prodcast v_dropped_plans number _ id = 205GROUP BY prod_nameSQL > DECLARE 2 v_dropped_plans number 3 BEGIN 4 v_dropped_plans: = DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle = > 'SQL_6d39c79190585ca9'); 5 DBMS_OUTPUT.PUT_LINE (' dropped'| | v_dropped_plans | | 'plans'); 6 END; 7 / PL/SQL procedure successfully completed.SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED 3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE='SQL_6d39c79190585ca9';no rows selected

Manage SQL Management Base

SQL Management Base is part of the data dictionary and is stored in the SYSAUX tablespace. It stores statement logs, execution plan history, SQL execution plan baselines, and SQL Profiles. Use the dbms_spm.configure procedure to set options for SMB and maintain SQL Plan Baselines. The dba_sql_management_config view can be used to view the current configuration information of SMB. The following describes the list of parameters that the parameter_name column can set:

The maximum percentage of SYSAUX tablespaces that space_budget_percent:SQL Management Base can use. The default value is 10%. The allowable range is 1% to 50%.

Plan_retention_weeks: how many weeks do you need to keep an execution plan that was not used before it was cleared? the default is 53.

Auto_capture_parsing_schema_name: it is (% LIKE an OR% LIKE b...) AND (% NOT LIKE c AND% NOT LIKE d...) A formal list that represents the parsing scheme name filter.

Auto_capture_module: it is (% LIKE an OR% LIKE b...) AND (% NOT LIKE c AND% NOT LIKE d...) A formal list that represents module filtering.

Auto_capture_action: it is (% LIKE an OR% LIKE b...) AND (% NOT LIKE c AND% NOT LIKE d...) A formal list that represents operation filtering.

Auto_capture_sql_text: it is (% LIKE an OR% LIKE b...) AND (% NOT LIKE c AND% NOT LIKE d...) A formal list that represents SQL text filtering.

Modify SMB space usage restrictions

A background process called by SMB that runs once a week will detect space usage. When the definition limit is exceeded, the background process will write an alarm message to the alert log file. The database generates an alert every week until the SMB space limit is increased, the SYSAUX tablespace is increased, or the space used by SMB is reduced by clearing SQL Management objects (sql plan baselines or sql profiles).

1. Check the limit size of the space used by the current SMB. From the query results, you can see that the current size is 10% of the SYSAUX tablespace size.

SQL > col parameter_name for a30SQL > col% _ LIMIT for a20SQL > SELECT PARAMETER_NAME, PARAMETER_VALUE AS "% _ LIMIT", 2 (SELECT sum (bytes/1024/1024) FROM DBA_DATA_FILES 3 WHERE TABLESPACE_NAME = 'SYSAUX') AS SYSAUX_SIZE_IN_MB 4 PARAMETER_VALUE/100 * 5 (SELECT sum (bytes/1024/1024) FROM DBA_DATA_FILES 6 WHERE TABLESPACE_NAME = 'SYSAUX') AS "CURRENT_LIMIT_IN_MB" 7 FROM DBA_SQL_MANAGEMENT_CONFIG 8 WHERE PARAMETER_NAME =' SPACE_BUDGET_PERCENT' PARAMETER_NAME% _ LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB -SPACE_BUDGET_PERCENT 10 1260

two。 Change the space limit used by SMB to 30% of the size of SYSAUX tablespace

SQL > EXECUTE DBMS_SPM.CONFIGURE ('space_budget_percent',30); PL/SQL procedure successfully completed.

3. Confirm that all SMB space limit sizes have been successfully modified to 30% of the SYSAUX tablespace size

SQL > SELECT PARAMETER_NAME, PARAMETER_VALUE AS "% _ LIMIT", 2 (SELECT sum (bytes/1024/1024) FROM DBA_DATA_FILES 3 WHERE TABLESPACE_NAME = 'SYSAUX') AS SYSAUX_SIZE_IN_MB, 4 PARAMETER_VALUE/100 * 5 (SELECT sum (bytes/1024/1024) FROM DBA_DATA_FILES 6 WHERE TABLESPACE_NAME =' SYSAUX') AS "CURRENT_LIMIT_IN_MB" 7 FROM DBA_SQL_MANAGEMENT_CONFIG 8 WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT' PARAMETER_NAME% _ LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB -SPACE_BUDGET_PERCENT 30 1260 378

Modify Plan Retention Policy in SMB

Weekly scheduled cleanup tasks to manage the space used by SQL Plan Management. This task is an automated task within the maintenance window. The database automatically clears out unused execution plans that have expired the Plan Retention period, which is identified by the execution plan stored in the last_executed field in the SMB. The retention period of the implementation plan in the absence of life is 53 weeks. The range of this cycle can be set from 5 to 523 weeks.

1. View the current execution plan retention cycle

SQL > SELECT PARAMETER_NAME, PARAMETER_VALUE 2 FROM DBA_SQL_MANAGEMENT_CONFIG 3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS';PARAMETER_NAME PARAMETER_---PLAN_RETENTION_WEEKS 53

two。 Modify the retention period of the execution plan to 105 weeks

SQL > EXECUTE DBMS_SPM.CONFIGURE ('plan_retention_weeks',105); PL/SQL procedure successfully completed.

3. Ensure that the execution plan retention period is successfully modified to 105 weeks

SQL > SELECT PARAMETER_NAME, PARAMETER_VALUE 2 FROM DBA_SQL_MANAGEMENT_CONFIG 3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS' This is the end of PARAMETER_NAME PARAMETER_---PLAN_RETENTION_WEEKS 105What basic tasks can be divided into SQL implementation Plan Management? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.