Managing SQL Plan Baselines
This chapter explains the concepts and tasks relating to SQL plan management using the DBMS_SPM package.
In below section we will cover Confgure to Drop of SQL PLAN MANAGEMENT.
A => Configuring SQL Plan Management
You control SQL plan management with the initialization parametersOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESandOPTIMIZER_USE_SQL_PLAN_BASELINES. The default values are as follows: ·OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=falseFor any repeatable SQL statement that does not already exist in the plan history, the database does not automatically create an initial SQL plan baseline for the statement. ·OPTIMIZER_USE_SQL_PLAN_BASELINES=trueFor any SQL statement that has an existing SQL plan baseline, the database automatically adds new plans to the SQL plan baseline as unaccepted plans. SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE SQL> SQL> alter system set optimizer_capture_sql_plan_baselines=TRUE; System altered. SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines boolean TRUE SQL> NOTE => As soon as we set parameter optimizer_capture_sql_plan_baselines = true all repeatable statements will be captured in Baseline. Thus, automatic capture may result in the creation of an extremely large number of plan baselines. To limit the statements that are eligible for plan baselines, configure filters using theDBMS_SPM.CONFIGUREprocedure. Let we see demo as below. Demo Step 1 => First let we check if any Filter is configured or Not as below. SQL> PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_CAPTURE_ACTION AUTO_CAPTURE_MODULE AUTO_CAPTURE_PARSING_SCHEMA_NAME AUTO_CAPTURE_SQL_TEXT AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 PLAN_RETENTION_WEEKS 53 SPACE_BUDGET_PERCENT 10 9 rows selected. SQL> / So as we can see NO FILTERS are used for SPM. Step 2=> Check current value of sql_plan parameter as below SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines boolean TRUE Step 3 => Now we will verify if any plan exists for ABHI_TEST user in DBA_SQL_PLAN_BASELINES. SQL> select count(*) from DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST' ; COUNT(*) ---------- 0 Step 4 => Now let we connect to ABHI_TEST user and execute REPTEATED statement as below. [oracle@ace2oracledb ~]$ sqlplus abhi_test/Oracle_4U SQL> select count(*) from DATA_EMP; COUNT(*) ---------- 6500 SQL> / COUNT(*) ---------- 6500 Step 5 => Now let we check again from DBA_SQL_PLAN_BASELINES if SQL plan is captured. SQL> select count(*) from DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST'; COUNT(*) ---------- 1 SQL> So we can see that SQL PLAN is being captured for every REPEATED Query. And due to this SYSAUX tablespace may exhaust , so to overcome this problem we need to configure filters using theDBMS_SPM.CONFIGUREprocedure.
B => Configuring Filters for Automatic Plan Capture
IfOPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true, then you can use theDBMS_SPM.CONFIGUREprocedure to create an automatic capture filter for repeatable statements.DBMS_SPM.CONFIGURE ( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2 := NULL, allow IN BOOLEAN := TRUE);We can set below allowed parameters for prameter_name variable.Demo we will set AUTO_CAPTURE_SQL_TEXT to %TECH_FOR_SPD% to capture ONLY sql which contains TECH_FOR_SPD in SQL Query. Step 1 => Login to sys and execute as below SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', '%TECH_FOR_SPD%'); PL/SQL procedure successfully completed. Step 2 => Let we check numbers of plan captures in base line for ABHI_TEST schema as below. SQL> select count(*) from DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST'; COUNT(*) ---------- 7 Step 3 => Now let we login to ABHI_TEST user and execute 2 diffrrent repeated Qeury as below, SQL> select count(*) from DEMO_TAB; ( this will not be captured due to filter) COUNT(*) ---------- 42100 SQL> / COUNT(*) ---------- 42100 Now check if it's got captured. SQL> select count(*) from DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST'; COUNT(*) ---------- 7 as we can see NOT CAPTURED sue to filter. Now let we execute repeated statement containing TECH_FOR_SPD SQL> select count(*) from TECH_FOR_SPD; COUNT(*) ---------- 32090 SQL> / COUNT(*) ---------- 32090 Now check if it's got captured. SQL> select count(*) from DBA_SQL_PLAN_BASELINES where CREATOR Like 'ABHI_TEST'; COUNT(*) ---------- 8 SO we can see filter is okay now. Step 4 =>Now we will check Filter and remove FIlter as below. COL PARAMETER_NAME FORMAT a32 COL PARAMETER_VALUE FORMAT a32 SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG; PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_CAPTURE_ACTION AUTO_CAPTURE_MODULE AUTO_CAPTURE_PARSING_SCHEMA_NAME AUTO_CAPTURE_SQL_TEXT (sql_text LIKE %TECH_FOR_SPD%) AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 PLAN_RETENTION_WEEKS 53 SPACE_BUDGET_PERCENT 10 9 rows selected. SQL> Now let we remove filter as below and set new filter to capture only statement which executed by ABHI_TEST schema. SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', NULL, NULL); PL/SQL procedure successfully completed. SQL> COL PARAMETER_NAME FORMAT a32 COL PARAMETER_VALUE FORMAT a32 SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG; SQL> SQL> SQL> 2 PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_CAPTURE_ACTION AUTO_CAPTURE_MODULE AUTO_CAPTURE_PARSING_SCHEMA_NAME AUTO_CAPTURE_SQL_TEXT AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 PLAN_RETENTION_WEEKS 53 SPACE_BUDGET_PERCENT 10 9 rows selected. SQL> SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'ABHI_TEST'); PL/SQL procedure successfully completed. SQL> COL PARAMETER_NAME FORMAT a32 COL PARAMETER_VALUE FORMAT a32 SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;SQL> SQL> SQL> 2 PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_CAPTURE_ACTION AUTO_CAPTURE_MODULE AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (ABHI_TEST) AUTO_CAPTURE_SQL_TEXT AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 PLAN_RETENTION_WEEKS 53 SPACE_BUDGET_PERCENT 10 9 rows selected.
C => MANUALLY Loading SQL Plan Baselines
In some cases we can not change optimizer_capture_sql_plan_baselines to true due to nature of working for this parameter. So we need to load PLAN manually in SQL PLAN BASELINES , and for that we have DBMS_SPM package to do the same. The DBMS_SPM package enables you to load plans from multiple sources. SQL> SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE SQL> The goal of this task is to load plans from the following sources: 1-> Load SQL PLAN from AWR In this example we will be loeading SQL PLANS from AWR repository with conditions as below, DBMS_SPM.LOAD_PLANS_FROM_AWR begin_snap IN NUMBER, end_snap IN NUMBER, basic_filter IN VARCHAR2 := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES', commit_rows IN NUMBER := 1000) RETURN PLS_INTEGER; Here Basic_Filter can be used from SQLSET_ROW. We will load as per below Step 1 => Get list of SQL_ID's with thier snap_id's for begin and end interval as below, col parsed format a6 col sql_text format a40 set lines 200 set pages 300 select sql_text, parsing_schema_name as parsed, elapsed_time_delta/1000/1000 as elapsed_sec, stat.snap_id, to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime, txt.sql_id from dba_hist_sqlstat stat, dba_hist_sqltext txt, dba_hist_snapshot snap where stat.sql_id=txt.sql_id and stat.snap_id=snap.snap_id and parsing_schema_name like 'ABHI_TEST' and parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS') order by snaptime asc; SQL_TEXT PARSED ELAPSED_SEC SNAP_ID SNAPTIME SQL_ID ---------------------------------------- ------ ----------- ---------- -------------- ------------- select count(*) from DATA_EMP ABHI_TEST .211495 226 16.02 18:30:24 cj309pqvjgzpb select count(*) from DEMO_TAB ABHI_TEST .058343 227 16.02 19:30:42 9srq6w5hstq74 <<< we will load this SQL ID in SQL PLAN BASELINE Step 2 => Let we check if any SQL PLAN loaded for DEMO_TAB sql as below. SQL> COL SQL_HANDLE FORMAT a20 COL SQL_TEXT FORMAT a20 COL PLAN_NAME FORMAT a30 COL ORIGIN FORMAT a20 SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%DEMO_TAB%'; no rows selected Step 3 => Now we will load in SQL PLANBASELINE as below. SQL> EXEC :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR(begin_snap => 226,end_snap => 227,basic_filter => 'parsing_schema_name =''ABHI_TEST'' and sql_id = ''9srq6w5hstq74'''); PL/SQL procedure successfully completed. Step 4 => Let we verify if this is available in SQL PLAN BASELINE. SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%DEMO_TAB%'; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENABLED ACCEPTED -------------------- ---------------------------------------- ------------------------------ -------------------- ------------------------------ ------------------------------ SQL_000c4dc6bf8feb39 select count(*) from DEMO_TAB SQL_PLAN_0032dsuzszuttf74f07de MANUAL-LOAD-FROM-AWR YES YES So as we can see plan is loaded and ACCEPTED is YES as because of Manual loading method. 2-> Load SQL PLAN from Shared SQL area This function loads one or more plans present in the cursor cache for a SQL statement, or a set of SQL statements. It has four overloads: using SQL statement text, using SQL handle, using SQL ID, or usingattribute_nameandattribute_valuepair. DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, sql_text IN CLOB, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, sql_handle IN VARCHAR2, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( attribute_name IN VARCHAR2, attribute_value IN VARCHAR2, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES') RETURN PLS_INTEGER; Loading SQL PLANS from Cursor_Cache as below. Step 1 => Get SQL_ID for sql as below. SQL> select count(*) from UNIQ_TEST; COUNT(*) ---------- 3 SQL> select sql_id from v$sql where sql_text like 'select count(*) from UNIQ_TEST'; SQL_ID ------------- 4gv0au4vua43c Step 2=> Check of any sql plan loaded for SQL QUERY SQL> COL SQL_HANDLE FORMAT a20 COL SQL_TEXT FORMAT a20 COL PLAN_NAME FORMAT a30 COL ORIGIN FORMAT a20 SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%UNIQ_TEST%';SQL> SQL> SQL> SQL> SQL> 2 3 4 no rows selected SQL> Step 3=> Load Using SQL_ID as below SQL> VARIABLE v_plan_cnt NUMBER BEGIN :v_plan_cnt:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '4gv0au4vua43c'); END; / PL/SQL procedure successfully completed. Step 3=> Check if PLAN is available now. SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%UNIQ_TEST%';SQL> 2 3 4 SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENABLED ACCEPTED -------------------- -------------------- ------------------------------ -------------------------------------------------- ------------------------------ ------------------------------ SQL_5046547a4fdeff45 select count(*) from SQL_PLAN_50jkng97xxzu5c1a7b2a8 MANUAL-LOAD-FROM-CURSOR-CACHE YES YES UNIQ_TEST SQL> 3-> Load SQL PLAN from SQL tuning set (STS) This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. DBMS_SPM.LOAD_PLANS_FROM_SQLSET ( sqlset_name IN VARCHAR2, sqlset_owner IN VARCHAR2 := NULL, basic_filter IN VARCHAR2 := NULL, fixed IN VARCHAR2 := 'NO', enabled IN VARCHAR2 := 'YES' commit_rows IN NUMBER := 1000) RETURN PLS_INTEGER Now we will Load SQL PLAN avaialble from STS as below Step 1=> Let we get available STS in database for user as below SQL> SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM USER_SQLSET; NAME SQLCNT DESCRIPTION ------------------------------ ---------- -------------------------- SQLT_FROM_AWR 1 STS From awr SQLT_FROM_TRACE 2 STS From TRACE STS_FROM_QUERY 3 Creating STS from Query Step 2=> Get details of SQL Query from STS for demo purpose as below SQL> select sql_text FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_FROM_AWR' ) ); SQL_TEXT ------------------------------ create table DEP_TEST as select * from HR.DEPARTMENTS Step 3=> Check if SQL PLAN for this Query is already loaded SQL> COL SQL_HANDLE FORMAT a20 COL SQL_TEXT FORMAT a20 COL PLAN_NAME FORMAT a30 COL ORIGIN FORMAT a20 SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%DEP_TEST%'; SQL> SQL> SQL> SQL> SQL> 2 3 4 no rows selected Step 4=> Let we load SQL PLAN now as below SQL> VARIABLE v_plan_cnt NUMBER EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'SQLT_FROM_AWR',sqlset_owner => 'ABHI_TEST'); SQL> PL/SQL procedure successfully completed. Step 5=> Check if SQL PLAN for this Query is already loaded COL SQL_HANDLE FORMAT a20 COL SQL_TEXT FORMAT a20 COL PLAN_NAME FORMAT a30 COL ORIGIN FORMAT a20 SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%DEP_TEST%'; SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENABLED ACCEPTED -------------------- -------------------- ------------------------------ -------------------- ------------------------------ ------------------------------ SQL_3452df65a7e5fdbf create table DEP_TES SQL_PLAN_38nqzcqmybzdzf1f8ff0f MANUAL-LOAD-FROM-STS YES YES T as select * from H R.DEPARTMENTS
D => PLAN Evolutions through SPM
A newly added plan can be evoluted either Automatically throughSYS_AUTO_SPM_EVOLVE_TASK or can be done Manually through DBMS_SPM . We will cover both.=> Automatic Plan Evolutions using SPM Evolve Advisor Task SPM Evolve Advisor is a SQL advisor that evolves plans that have recently been added to the SQL plan baseline. By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window. A SQL plan baseline prevents performance regressions caused by suboptimal plans. If a SQL statement does not have a SQL plan baseline, and if the alternate_plan_baseline parameter is set to AUTO, then SQM Evolve Advisor can sometimes resolve such performance regressions automatically. The advisor compares all available plans and chooses the best-performing plan as the baseline. Whenever it runs in the maintenance window, SPM Evolve Advisor performs the following tasks: => Checks AWR for top SQL AWR stores the most resource-intensive SQL statements. SPM Evolve Advisor searches AWR for statements that are most likely to benefit from SQL plan baselines, and then adds plans for these statements to the baselines. => Looks for alternative plans in all available sources By default (alternate_plan_source=AUTO), the automatic task searches all available repositories for plans that are not yet in the SMB plan history. The setting for alternate_plan_source is shown in the DBA_ADVISORS_PARAMETERS view. => Adds unaccepted plans to the plan history These plans are not yet in the SQL plan baseline for any SQL statement. => Tests the execution of as many plans as possible during the maintenance window For every alternative plan, the database test executes the statement and records the performance statistics. The goal is to use a cost-based algorithm to compare the performance of every alternative plan with the plan that the optimizer would otherwise choose. => Performs either of the following actions, depending on whether the alternative plan performs better than the current plan: If performance is better, then SPM Evolve Advisor accepts the plan. The alternative plan is now in the baseline. If performance is worse, then the plan remains in the statement history, but not the baseline. check parameter settings for auto_task as below, SQL> COL PARAMETER_NAME FORMAT a25 COL VALUE FORMAT a42 SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';SQL> SQL> 2 3 PARAMETER_NAME VALUE ------------------------- ------------------------------------------ ALTERNATE_PLAN_LIMIT UNLIMITED ALTERNATE_PLAN_SOURCE AUTO ALTERNATE_PLAN_BASELINE AUTO ACCEPT_PLANS TRUE DAYS_TO_EXPIRE UNLIMITED JOURNALING INFORMATION MODE COMPREHENSIVE TARGET_OBJECTS 1 TIME_LIMIT 3600 DEFAULT_EXECUTION_TYPE SPM EVOLVE EXECUTION_DAYS_TO_EXPIRE 30 32 rows selected. SQL> => Manually Plan Evolutions using DBMS_SPM We can use PL/SQL to manually evolve an unaccepted plan to determine whether it performs better than any plan currently in the plan baseline. Evolving SQL Plan Baselines Manually You can use PL/SQL or Cloud Control to manually evolve an unaccepted plan to determine whether it performs better than any plan currently in the plan baseline. Oracle recommends that you configure SPM Evolve Advisor to run automatically. You can also evolve SQL plan baselines manually. The following graphic shows the basic workflow for managing SQL plan management tasks. Steps invloved in Manual Plan EvolutionsFirst we will execute Repeated SQL Query , and when SQL PLAN captured in Baseline we will create index for that table which are part of DEMO QUERY. So as per rule INDEX would be faster but PLAN baseline would be using FULL TABLE SCAN plan (OLD) So it will regress and thus we will do manual evolution , Post manual evolution SQL PLAN with index will be selected for QUERY. ==> check if any Filter configured as below. SQL> COL PARAMETER_NAME FORMAT a32 COL PARAMETER_VALUE FORMAT a32 SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;SQL> SQL> SQL> 2 PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_CAPTURE_ACTION AUTO_CAPTURE_MODULE AUTO_CAPTURE_PARSING_SCHEMA_NAME AUTO_CAPTURE_SQL_TEXT AUTO_SPM_EVOLVE_TASK OFF AUTO_SPM_EVOLVE_TASK_INTERVAL 3600 AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800 PLAN_RETENTION_WEEKS 70 SPACE_BUDGET_PERCENT 30 9 rows selected. SQL> SQL> ===> check sql_plan value. It must be true to create initial plan load for demo. SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines boolean TRUE SQL> ==> Now let we execute repeated SQL QUERY from ABH_tEST schema as below and see if plan baseling got created. SQL> SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM PROD p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name; 2 3 4 5 PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 18" Flat Panel Graphics Monitor 5205 Standard Mouse 10156 Model A3827H Black Image Cartridge 20490 SIMM- 16MB PCMCIAII card 15950 Multimedia speakers- 5" cones 11253 Envoy External 8X CD-ROM 17430 Model SM26273 Black Ink Cartridge 19233 PCMCIA modem/fax 19200 baud 22768 Model CD13272 Tricolor Ink Cartridge 15984 SIMM- 8MB PCMCIAII card 19557 Internal 8X CD-ROM 13319 17" LCD w/built-in HDTV Tuner 6010 Multimedia speakers- 3" cones 12163 Envoy External 6X CD-ROM 13008 Deluxe Mouse 12837 PCMCIA modem/fax 28800 baud 20948 External 8X CD-ROM 16494 Internal 6X CD-ROM 9523 Model NM500X High Yield Toner Cartridge 7557 Envoy External Keyboard 3441 External 6X CD-ROM 13043 21 rows selected. SQL> SQL> SQL> / PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 18" Flat Panel Graphics Monitor 5205 Standard Mouse 10156 Model A3827H Black Image Cartridge 20490 SIMM- 16MB PCMCIAII card 15950 Multimedia speakers- 5" cones 11253 Envoy External 8X CD-ROM 17430 Model SM26273 Black Ink Cartridge 19233 PCMCIA modem/fax 19200 baud 22768 Model CD13272 Tricolor Ink Cartridge 15984 SIMM- 8MB PCMCIAII card 19557 Internal 8X CD-ROM 13319 17" LCD w/built-in HDTV Tuner 6010 Multimedia speakers- 3" cones 12163 Envoy External 6X CD-ROM 13008 Deluxe Mouse 12837 PCMCIA modem/fax 28800 baud 20948 External 8X CD-ROM 16494 Internal 6X CD-ROM 9523 Model NM500X High Yield Toner Cartridge 7557 Envoy External Keyboard 3441 External 6X CD-ROM 13043 21 rows selected. SQL> SQL> / PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 18" Flat Panel Graphics Monitor 5205 Standard Mouse 10156 Model A3827H Black Image Cartridge 20490 SIMM- 16MB PCMCIAII card 15950 Multimedia speakers- 5" cones 11253 Envoy External 8X CD-ROM 17430 Model SM26273 Black Ink Cartridge 19233 PCMCIA modem/fax 19200 baud 22768 Model CD13272 Tricolor Ink Cartridge 15984 SIMM- 8MB PCMCIAII card 19557 Internal 8X CD-ROM 13319 17" LCD w/built-in HDTV Tuner 6010 Multimedia speakers- 3" cones 12163 Envoy External 6X CD-ROM 13008 Deluxe Mouse 12837 PCMCIA modem/fax 28800 baud 20948 External 8X CD-ROM 16494 Internal 6X CD-ROM 9523 Model NM500X High Yield Toner Cartridge 7557 Envoy External Keyboard 3441 External 6X CD-ROM 13043 21 rows selected. ==> Let we check of plan baseline got created. SQL> SET PAGES 10000 LINES 140 SET SERVEROUTPUT ON COL SQL_TEXT FORMAT A20 COL SQL_HANDLE FORMAT A20 COL PLAN_NAME FORMAT A30 COL ORIGIN FORMAT A12 SET LONGC 60535 SET LONG 60535 SET ECHO ON SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%test_for_man_evol%' and SQL_TEXT NOT LIKE '%SQL_HANDLE%' ;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX -------------------- -------------------- ------------------------------ ------------ --- --- --- SQL_2fbd2ed148e7bfc5 SELECT /* test_for_ SQL_PLAN_2zg9fu54fggy5ff8cca40 AUTO-CAPTURE YES YES NO man_evol */ prod_nam e, sum(quantity_sold ) FROM PROD p, sales s WHERE p.prod_id = s .prod_id AND p.prod_catego ry_id =203 GROUP BY prod_name SQL> ==>> Now let we execute DEMO Query again an check what is plan in cursor SQL> SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM PROD p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name; 2 3 4 5 PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 18" Flat Panel Graphics Monitor 5205 Standard Mouse 10156 Model A3827H Black Image Cartridge 20490 SIMM- 16MB PCMCIAII card 15950 Multimedia speakers- 5" cones 11253 Envoy External 8X CD-ROM 17430 Model SM26273 Black Ink Cartridge 19233 PCMCIA modem/fax 19200 baud 22768 Model CD13272 Tricolor Ink Cartridge 15984 SIMM- 8MB PCMCIAII card 19557 Internal 8X CD-ROM 13319 17" LCD w/built-in HDTV Tuner 6010 Multimedia speakers- 3" cones 12163 Envoy External 6X CD-ROM 13008 Deluxe Mouse 12837 PCMCIA modem/fax 28800 baud 20948 External 8X CD-ROM 16494 Internal 6X CD-ROM 9523 Model NM500X High Yield Toner Cartridge 7557 Envoy External Keyboard 3441 External 6X CD-ROM 13043 21 rows selected. SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_cursor()); PLAN_TABLE_OUTPUT -------------------------- SQL_ID f7kd44252vg1d, child number 1 ------------------------------------- SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM PROD p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name Plan hash value: 865300185 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1241 (100)| | | 1 | HASH GROUP BY | | 14 | 574 | 1241 (2)| 00:00:01 | |* 2 | HASH JOIN | | 183K| 7357K| 1237 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL| PROD | 14 | 476 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| SALES | 918K| 6281K| 1231 (1)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("P"."PROD_ID"="S"."PROD_ID") 3 - filter("P"."PROD_CATEGORY_ID"=203) Note ----- - SQL plan baseline SQL_PLAN_2zg9fu54fggy5ff8cca40 used for this statement 28 rows selected. SQL> So as we can see SQL PLAN BASELINE is being used and this is for FULL TABLE SCAN, =====>>> Now we will create INDEX as below SQL> CREATE INDEX ind_prod_cat_name ON PROD(prod_category_id, prod_name, prod_id); CREATE INDEX ind_sales_prod_qty_sold ON sales(prod_id, quantity_sold); 2 Index created. SQL> 2 Index created. SQL> SQL> ===>>> Post that we will gather stats as below . SQL> @gather_stats.sql Enter value for owner: ABHI_TEST Enter value for name: PROD old 2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE); new 2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'PROD', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE); PL/SQL procedure successfully completed. SQL> @gather_stats.sql Enter value for owner: ABHI_TEST Enter value for name: SALES old 2: DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname =>'&name', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE); new 2: DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ABHI_TEST', tabname =>'SALES', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => TRUE); PL/SQL procedure successfully completed. SQL> ==>>> Next we will execute repeated DEMO Query as below and check whether SQL PLAN CAPUTRED in BASE LINE or not. SQL> SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM PROD p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name; 2 3 4 5 PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 18" Flat Panel Graphics Monitor 5205 Standard Mouse 10156 Model A3827H Black Image Cartridge 20490 SIMM- 16MB PCMCIAII card 15950 Multimedia speakers- 5" cones 11253 Envoy External 8X CD-ROM 17430 Model SM26273 Black Ink Cartridge 19233 PCMCIA modem/fax 19200 baud 22768 Model CD13272 Tricolor Ink Cartridge 15984 SIMM- 8MB PCMCIAII card 19557 Internal 8X CD-ROM 13319 PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 17" LCD w/built-in HDTV Tuner 6010 Multimedia speakers- 3" cones 12163 Envoy External 6X CD-ROM 13008 Deluxe Mouse 12837 PCMCIA modem/fax 28800 baud 20948 External 8X CD-ROM 16494 Internal 6X CD-ROM 9523 Model NM500X High Yield Toner Cartridge 7557 Envoy External Keyboard 3441 External 6X CD-ROM 13043 21 rows selected. SQL> SQL> SQL> / PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 18" Flat Panel Graphics Monitor 5205 Standard Mouse 10156 Model A3827H Black Image Cartridge 20490 SIMM- 16MB PCMCIAII card 15950 Multimedia speakers- 5" cones 11253 Envoy External 8X CD-ROM 17430 Model SM26273 Black Ink Cartridge 19233 PCMCIA modem/fax 19200 baud 22768 Model CD13272 Tricolor Ink Cartridge 15984 SIMM- 8MB PCMCIAII card 19557 Internal 8X CD-ROM 13319 PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 17" LCD w/built-in HDTV Tuner 6010 Multimedia speakers- 3" cones 12163 Envoy External 6X CD-ROM 13008 Deluxe Mouse 12837 PCMCIA modem/fax 28800 baud 20948 External 8X CD-ROM 16494 Internal 6X CD-ROM 9523 Model NM500X High Yield Toner Cartridge 7557 Envoy External Keyboard 3441 External 6X CD-ROM 13043 21 rows selected. SQL> SQL> SQL> / PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 18" Flat Panel Graphics Monitor 5205 Standard Mouse 10156 Model A3827H Black Image Cartridge 20490 SIMM- 16MB PCMCIAII card 15950 Multimedia speakers- 5" cones 11253 Envoy External 8X CD-ROM 17430 Model SM26273 Black Ink Cartridge 19233 PCMCIA modem/fax 19200 baud 22768 Model CD13272 Tricolor Ink Cartridge 15984 SIMM- 8MB PCMCIAII card 19557 Internal 8X CD-ROM 13319 PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ 17" LCD w/built-in HDTV Tuner 6010 Multimedia speakers- 3" cones 12163 Envoy External 6X CD-ROM 13008 Deluxe Mouse 12837 PCMCIA modem/fax 28800 baud 20948 External 8X CD-ROM 16494 Internal 6X CD-ROM 9523 Model NM500X High Yield Toner Cartridge 7557 Envoy External Keyboard 3441 External 6X CD-ROM 13043 21 rows selected. SQL> SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 8834jdxv5mfjs, child number 1 ------------------------------------- SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM PROD p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name Plan hash value: 865300185 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1243 (100)| | | 1 | HASH GROUP BY | | 21 | 861 | 1243 (2)| 00:00:01 | |* 2 | HASH JOIN | | 267K| 10M| 1237 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL| PROD | 21 | 714 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| SALES | 918K| 6281K| 1231 (1)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("P"."PROD_ID"="S"."PROD_ID") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 3 - filter("P"."PROD_CATEGORY_ID"=203) Note ----- - SQL plan baseline SQL_PLAN_2zg9fu54fggy5ff8cca40 used for this statement 28 rows selected. SQL> =====>>>> CHECK if new SQL PLAN available in Baseline. SET PAGES 10000 LINES 140 SET SERVEROUTPUT ON COL SQL_TEXT FORMAT A20 COL SQL_HANDLE FORMAT A20 COL PLAN_NAME FORMAT A30 COL ORIGIN FORMAT A12 SET LONGC 60535 SET LONG 60535 SET ECHO ON SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%test_for_man_evol%' and SQL_TEXT NOT LIKE '%SQL_HANDLE%' ;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX -------------------- -------------------- ------------------------------ ------------ --- --- --- SQL_2fbd2ed148e7bfc5 SELECT /* test_for_ SQL_PLAN_2zg9fu54fggy56003e314 AUTO-CAPTURE YES NO NO man_evol */ prod_nam e, sum(quantity_sold ) FROM PROD p, sales s WHERE p.prod_id = s .prod_id AND p.prod_catego ry_id =203 GROUP BY prod_name SQL_2fbd2ed148e7bfc5 SELECT /* test_for_ SQL_PLAN_2zg9fu54fggy5ff8cca40 AUTO-CAPTURE YES YES NO man_evol */ prod_nam e, sum(quantity_sold ) FROM PROD p, sales s WHERE p.prod_id = s .prod_id AND p.prod_catego ry_id =203 GROUP BY prod_name SQL> as we can see SQL_PLAN_2zg9fu54fggy56003e314 has been created(and this is sql plan post INDEX creation). Now let we see if this is being used in DEMO QUERY post repeated execution. SQL> SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM PROD p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 8834jdxv5mfjs, child number 1 ------------------------------------- SELECT /* test_for_man_evol */ prod_name, sum(quantity_sold) FROM PROD p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =203 GROUP BY prod_name Plan hash value: 865300185 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1243 (100)| | | 1 | HASH GROUP BY | | 21 | 861 | 1243 (2)| 00:00:01 | |* 2 | HASH JOIN | | 267K| 10M| 1237 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL| PROD | 21 | 714 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| SALES | 918K| 6281K| 1231 (1)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("P"."PROD_ID"="S"."PROD_ID") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 3 - filter("P"."PROD_CATEGORY_ID"=203) Note ----- - SQL plan baseline SQL_PLAN_2zg9fu54fggy5ff8cca40 used for this statement 28 rows selected. SQL> AS WE CAN SEE still OLD PLAN BASE LINE is being used (which is for FULL TABLE SCAN).This may create regression .Now we need to evolve that plan as per below mentioned. VARIABLE cnt NUMBER VARIABLE tk_name VARCHAR2(50) VARIABLE exe_name VARCHAR2(50) VARIABLE evol_out CLOB EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK( sql_handle => 'SQL_07f16c76ff893342',plan_name => 'SQL_PLAN_0gwbcfvzskcu20135fd6c'); SELECT :tk_name FROM DUAL; EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name); SELECT :exe_name FROM DUAL; EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name, execution_name=>:exe_name ); SELECT :evol_out FROM DUAL; ****PENDING FOR SETUP ** =====>>>>
E => Configuring SQL Management Base
The SQL management base is a part of the data dictionary that resides in theSYSAUXtablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles. About Managing the SMB Use theDBMS_SPM.CONFIGUREprocedure to set configuration options for the SMB and the maintenance of SQL plan baselines. We can configure below 2 parameters in terms of SQL Management Base . Parameter Description SPACE_BUDGET_PERCENT => Maximum percent of SYSAUX space that the SQL management base can use. The default is 10. The allowable range for this limit is between 1% and 50%. PLAN_RETENTION_WEEKS => Number of weeks to retain unused plans before they are purged. The default is 53. Changing the Disk Space Limit for the SMB A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until either the SMB space limit is increased, the size of theSYSAUXtablespace is increased, or the disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles). This task explains how to change the limit with theDBMS_SPM.CONFIGUREprocedure. SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB, PARAMETER_VALUE/100 * ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB" FROM DBA_SQL_MANAGEMENT_CONFIG WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT'; PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB ---------------------------------------- ---------------------------------------- ----------------- ------------------- SPACE_BUDGET_PERCENT 10 840 84 SQL> Now let we configure limit to 20% as below . SQL> EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30); PL/SQL procedure successfully completed. SQL> SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB, PARAMETER_VALUE/100 * ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB" FROM DBA_SQL_MANAGEMENT_CONFIG WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT'; 2 3 4 5 6 7 8 PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB ---------------------------------------- ---------------------------------------- ----------------- ------------------- SPACE_BUDGET_PERCENT 30 840 252 SQL> Changing the Plan Retention Policy in the SMB A weekly scheduled purging task manages disk space used by SQL plan management. The task runs as an automated task in the maintenance window. The database purges plans that have not been used for longer than the plan retention period, as identified by the, LAST_EXECUTEDtimestamp stored in the SMB for that plan. The default retention period is 53 weeks. The period can range between 5 and 523 weeks. This task explains how to change the plan retention period with theDBMS_SPM.CONFIGUREprocedure. In Cloud Control, set the plan retention policy in the SQL Plan Baseline subpage SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS'; 2 3 PARAMETER_NAME PARAMETER_VALUE ---------------------------------------- ---------------------------------------- PLAN_RETENTION_WEEKS 53 SQL> We can configure using below method. SQL> EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',70); PL/SQL procedure successfully completed. SQL> SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS'; 2 3 PARAMETER_NAME PARAMETER_VALUE ---------------------------------------- ---------------------------------------- PLAN_RETENTION_WEEKS 70
F => Configuring the High-Frequency Automatic SPM Evolve Advisor Task
The high-frequency SPM Evolve Advisor task complements the standard Automatic SPM Evolve Advisor task.
By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled AutoTask maintenance window.
If data changes frequently between two consecutive task executions, then the optimizer may choose suboptimal plans.
For example, if product list prices change more frequently than executions of SYS_AUTO_SPM_EVOLVE_TASK, then more out-of-range queries may occur, possibly leading to suboptimal plans.
When you enable the high-frequency Automatic SPM Evolve Advisor task, SYS_AUTO_SPM_EVOLVE_TASK runs more frequently,
performing the same operations during every execution. The high-frequency task runs every hour and runs for no longer than 30 minutes.
Both the standard Automatic SPM Evolve Advisor task and high-frequency task have the same name: SYS_AUTO_SPM_EVOLVE_TASK.
In DBA_ADVISOR_EXECUTIONS, the two tasks are distinguished by execution name.
The name of the standard task execution has the form EXEC_number, whereas the name of the high-frequency execution has the form SYS_SPM_timestamp.
DBMS_SPM.CONFIGURE enables the high-frequency task, but has no dependency on the SPM Evolve Advisor.
Enabling the High-Frequency Automatic SPM Evolve Advisor Task: Tutorial
To enable and disable the high-frequency Automatic SPM Evolve Advisor task, use the DBMS_SPM.CONFIGURE procedure.
You can set auto_spm_evolve_task to any of the following values:
=> ON — Enables the high-frequency SPM Evolve Advisor task.
=> OFF — Disables the high-frequency SPM Evolve Advisor task. This is the default.
=> AUTO — Allows the database to determine when to execute the high-frequency SPM Evolve Advisor task. In this release, AUTO is equivalent to OFF.
Note that the task interval and runtime are fixed and cannot be adjusted by the user. This Feature is currently supported on EXADATA Flavors only.
SQL> COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32
SELECT PARAMETER_NAME, PARAMETER_VALUE
FROM DBA_SQL_MANAGEMENT_CONFIG
WHERE PARAMETER_NAME LIKE '%SPM%';SQL> SQL> 2 3
PARAMETER_NAME PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_SPM_EVOLVE_TASK OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL 3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME 1800
3 rows selected.
SQL> alter system set "_exadata_feature_on"=TRUE scope=spfile;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup
ORACLE instance started.
Total System Global Area 3388995104 bytes
Fixed Size 8902176 bytes
Variable Size 687865856 bytes
Database Buffers 2684354560 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> EXEC DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'ON');
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 150
COL TASK_NAME FORMAT a30
COL EXECUTION_NAME FORMAT a30
SELECT TASK_NAME, EXECUTION_NAME, STATUS
FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME LIKE '%SPM%'
AND (EXECUTION_NAME LIKE 'SYS_SPM%' OR EXECUTION_NAME LIKE 'EXEC_%')
ORDER BY EXECUTION_END;SQL> SQL> SQL> SQL> 2 3 4 5
TASK_NAME EXECUTION_NAME STATUS
------------------------------ ------------------------------ ---------
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/13:15:26 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/14:16:04 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK EXEC_6 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/15:16:32 COMPLETED
SYS_AUTO_SPM_EVOLVE_TASK SYS_SPM_2019-06-03/16:17:00 COMPLETED
...
7 rows selected.
SQL>
In the preceding output, EXEC_6 is the execution name of the standard SPM Automatic Advisor task. The other executions are of the high-frequency task.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444