Oracle Database 12c中引入了一個新的自動系統作業,即SYS_AUTO_SPM_EVOLVE_TASK。 該作業將在每天的自動維護作業視窗中自動執行。 SYS_AUTO_SPM_EVOLVE_TASK負責檢索和排序SPM中未被接受的執行計畫non-accepted plan以便verification。 當此執行計畫經過verified,過該計畫滿足性能閥值則將被自動接受accepted 。因此,當優化器將一個non-accepted的執行計畫加入到SQL statement plan history中,在很多情況下若該計畫確實是更好的,則會在第二天被接受並可以使用。



注意該自動task存在時間上的限制為一個小時(預設TIME_LIMIT=3600s),因此可能造成部分計畫未被verified。 在此種場景下,下一個維護視窗該task執行時將處理剩餘的執行計畫。

 

001.SYS_AUTO_SPM_EVOLVE_TASK
002. 
003.1  SELECT parameter_name, parameter_value
004.2  FROM   dba_advisor_parameters
005.3* WHERE  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
006.SQL> /
007. 
008.PARAMETER_NAME                 PARAMETER_VALUE
009.------------------------------ ----------------------------------------
010.DAYS_TO_EXPIRE                 UNLIMITED
011.END_SNAPSHOT                   UNUSED
012.END_TIME                       UNUSED
013.INSTANCE                       UNUSED
014.JOURNALING                     INFORMATION
015.MODE                           COMPREHENSIVE
016.START_SNAPSHOT                 UNUSED
017.START_TIME                     UNUSED
018.TARGET_OBJECTS                 1
019.TIME_LIMIT                     3600
020.DEFAULT_EXECUTION_TYPE         SPM EVOLVE
021.CON_DBID_MAPPING               UNUSED
022.ORA_EM_PARAM1                  UNUSED
023.ORA_EM_PARAM2                  UNUSED
024.ORA_EM_PARAM3                  UNUSED
025.ORA_EM_PARAM4                  UNUSED
026.ORA_EM_PARAM5                  UNUSED
027.ORA_EM_PARAM6                  UNUSED
028.ORA_EM_PARAM7                  UNUSED
029.ORA_EM_PARAM8                  UNUSED
030.ORA_EM_PARAM9                  UNUSED
031.ORA_EM_PARAM10                 UNUSED
032.EXECUTION_DAYS_TO_EXPIRE       30
033.SQLSET_NAME                    UNUSED
034.SQLSET_OWNER                   UNUSED
035.ACCEPT_PLANS                   TRUE
036._SPM_VERIFY                    TRUE
037.APPLY_CAPTURED_COMPILENV       UNUSED
038.LOCAL_TIME_LIMIT               UNUSED
039. 
040.已选择 29 行。
041. 
042.select execution_name,status,execution_start,execution_end fromdba_advisor_executions where task_name='SYS_AUTO_SPM_EVOLVE_TASK';
043. 
044. 
045.--     time_limit  (IN) - Time limit in number of minutes.  The time limit
046.--                        is global and it is used in the following manner.
047.--                        The time limit for first non-accepted plan is equal
048.--                        to the input value. The time limit for the second
049.--                        non-accepted plan is equal to (input value - time
050.--                        spent in first plan verification) and so on. The
051.--                        default DBMS_SPM.AUTO_LIMIT means let the system
052.--                        choose an appropriate time limit based on the
053.--                        number of plan verifications required to be done.
054.--                        The value DBMS_SPM.NO_LIMIT means no time limit.
055. 
056. 
057. 
058.DECLARE
059.job                   BINARY_INTEGER := :job;
060.next_date             TIMESTAMP WITH TIME ZONE := :mydate;
061.broken                BOOLEAN := FALSE;
062.job_name              VARCHAR2 (30) := :job_name;
063.job_subname           VARCHAR2 (30) := :job_subname;
064.job_owner             VARCHAR2 (30) := :job_owner;
065.job_start             TIMESTAMP WITH TIME ZONE := :job_start;
066.job_scheduled_start   TIMESTAMP WITH TIME ZONE := :job_scheduled_start;
067.window_start          TIMESTAMP WITH TIME ZONE := :window_start;
068.window_end            TIMESTAMP WITH TIME ZONE := :window_end;
069.chain_id              VARCHAR2 (14) := :chainid;
070.credential_owner      VARCHAR2 (30) := :credown;
071.credential_name       VARCHAR2 (30) := :crednam;
072.destination_owner     VARCHAR2 (30) := :destown;
073.destination_name      VARCHAR2 (30) := :destnam;
074.job_dest_id           VARCHAR2 (14) := :jdestid;
075.log_id                NUMBER := :log_id;
076.BEGIN
077.DECLARE
078.ename   VARCHAR2 (30);
079.BEGIN
080.ename := DBMS_SQLTUNE.execute_tuning_task ('SYS_AUTO_SQL_TUNING_TASK');
081.ename := DBMS_SPM.execute_evolve_task ('SYS_AUTO_SPM_EVOLVE_TASK');
082.END;
083. 
084.:mydate := next_date;
085. 
086.IF broken
087.THEN
088.:b := 1;
089.ELSE
090.:b := 0;
091.END IF;
092.END;
093. 
094. 
095. 
096. 
097./* Formatted on 2013/8/4 10:48:19 (QP5 v5.163.1008.3004) */
098.SELECT pl.signature,
099.pl.category,
100.pl.name,
101.pl.plan_id,
102.DECODE (BITAND (pl.flags, :1), 0, :2, :3) flags,
103.pl.sql_handle,
104.pl.sql_text,
105.pl.comp_data,
106.pl.optimizer_env,
107.pl.bind_data,
108.pl.parsing_schema_name,
109.pl.creator,
110.(CASE                                  /* plan is already accepted */
111.WHEN (BITAND (pl.flags, :4) <> 0)
112.THEN
113.:5                       /* plan has recently been verified */
114.WHEN (pl.is_auto IS NOT NULL
115.AND pl.last_verified >
116.SYSTIMESTAMP
117.- :6)
118.THEN
119.:7    /* plan's SQL statement hasn't been recently executed */
120.WHEN (pl.is_auto
121.IS NOT NULL
122.AND pl.last_verified
123.IS NOT NULL
124.AND pl.sql_last_executed <
125.SYSTIMESTAMP
126.- :8)
127.THEN
128.:9
129.ELSE
130.:10
131.END)
132.pruned
133.FROM (SELECT so.signature,
134.so.category,
135.so.name,
136.so.plan_id,
137.so.flags,
138.st.sql_handle,
139.st.sql_text,
140.(DECODE (
141.BITAND (so.flags, 128),
142.128, (SELECT EXTRACT (XMLTYPE (pl.other_xml),
143.'/*/outline_data').getClobVal ()
144.FROM sys.sqlobj$plan pl
145.WHERE     pl.signature = so.signature
146.AND pl.category = so.category
147.AND pl.obj_type = so.obj_type
148.AND pl.plan_id = so.plan_id
149.AND pl.other_xml IS NOT NULL),
150.(SELECT sod.comp_data
151.FROM sys.sqlobj$data sod
152.WHERE     sod.signature = so.signature
153.AND sod.category = so.category
154.AND sod.obj_type = so.obj_type
155.AND sod.plan_id = so.plan_id)))
156.comp_data,
157.sox.optimizer_env,
158.sox.bind_data,
159.sox.parsing_schema_name,
160.sox.creator,
161.sox.last_verified,
162.sox.optimizer_cost,
163.sox.created,
164.:11 is_auto,
165.(SELECT MAX (last_executed)
166.FROM sys.sqlobj$ ob
167.WHERE     ob.signature = so.signature
168.AND ob.obj_type = so.obj_type
169.AND ob.category = so.category)
170.sql_last_executed
171.FROM sys.sqlobj$ so, sys.sqlobj$auxdata sox, sys.sql$text st
172.WHERE (:12 IS NULL
173.OR so.name IN (SELECT EXTRACTVALUE (VALUE (p), '/plan') pname
174.FROM TABLE (
175.XMLSEQUENCE (
176.EXTRACT (XMLTYPE (:13),
177.'/plan_list/*'))) p))
178.AND (:14 IS NOT NULL
179.OR (BITAND (so.flags, :15) <> 0
180.AND BITAND (so.flags, :16) = 0))
181.AND so.obj_type = :17
182.AND so.signature = sox.signature
183.AND so.category = sox.category
184.AND so.obj_type = sox.obj_type
185.AND so.plan_id = sox.plan_id
186.AND so.signature = st.signature) pl
187.ORDER BY DECODE (:18, 0, NULL, pl.name),
188.pl.last_verified NULLS FIRST,
189.pl.sql_last_executed DESC NULLS LAST,
190.pl.optimizer_cost,
191.pl.created,
192.pl.name

 

 

 

 

出处:http://www.askmaclean.com/archives/12c-sys_auto_spm_evolve_task.html

創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(0) 人氣()