 |
|
11g
Evaluation Workflow for SQL Plan Management
Oracle 11g New Features Tips by Donald BurlesonJune 28, 2015 |
Oracle 11g New Features Tips
The next graphic shows the evaluation
workflow for SQL Plan Management:
Figure 27:
Evaluation Workflow - SQL Plan Management
The SMB can be monitored in the data
dictionary:
LUTZ
AS SYSDBA @ orcl11g SQL> SELECT * FROM dba_sql_management_config;
PARAMETER_NAME
PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY
-------------------- --------------- -------------
------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
M The SMB
consumes at least 1% and can use a maximum of 50% of the total
size of the SYSAUX tablespace.
M By default,
the space quota for the SMB is limited to 10% of the size of the
SYSAUX tablespace.
M Unused
execution plans are automatically purged from the SMB after 53
weeks by default with a weekly scheduled automatic task
M The
retention policy can be adjusted to any value between 5 and 523 week
(more than 10 years)!
The quota can be adjusted as well as the
retention period for unused SQL execution plans with the built in
package dbms_spm:
LUTZ
AS SYSDBA @ orcl11g SQL> exec dbms_spm.configure -
('space_budget_percent', 20 )
LUTZ AS SYSDBA @ orcl11g SQL>
exec dbms_spm.configure -
('plan_retention_weeks', 25)
LUTZ AS SYSDBA @ orcl11g SQL>
SELECT * FROM dba_sql_management_config;
PRAMETER_VALUE
LAST_MODIFIED
MODIFIED_BY
--------------------- ----------------------------
------------
SPACE_BUDGET_PERCENT 20 12-FEB-08 02.31.52.000000 PM
SYS
PLAN_RETENTION_WEEKS 25 12-FEB-08 02.34.04.000000 PM
SYS
The details can be viewed about existing SQL
plan baselines with dba_sql_plan_baselines:
LUTZ
AS SYSDBA @ racdb11g1 SQL> desc dba_sql_plan_baselines
Name
Null? Type
----------------------------------------- --------
---------------------------
SIGNATURE
NOT NULL NUMBER
SQL_HANDLE
NOT NULL VARCHAR2(30)
SQL_TEXT
NOT NULL CLOB
PLAN_NAME NOT NULL VARCHAR2(30)
CREATOR
VARCHAR2(30)
ORIGIN
VARCHAR2(14)
PARSING_SCHEMA_NAME
VARCHAR2(30)
DESCRIPTION
VARCHAR2(500)
VERSION
VARCHAR2(64)
CREATED
NOT NULL TIMESTAMP(6)
LAST_MODIFIED
TIMESTAMP(6)
LAST_EXECUTED
TIMESTAMP(6)
LAST_VERIFIED
TIMESTAMP(6)
ENABLED
VARCHAR2(3)
ACCEPTED
VARCHAR2(3)
FIXED
VARCHAR2(3)
AUTOPURGE
VARCHAR2(3)
OPTIMIZER_COST
NUMBER
MODULE
VARCHAR2(48)
ACTION
VARCHAR2(32)
EXECUTIONS
NUMBER
ELAPSED_TIME
NUMBER
CPU_TIME
NUMBER
BUFFER_GETS
NUMBER
DISK_READS
NUMBER
DIRECT_WRITES
NUMBER
ROWS_PROCESSED
NUMBER
FETCHES
NUMBER
END_OF_FETCH_COUNT
NUMBER
LUTZ AS SYSDBA @ racdb11g1 SQL>
SELECT count(*) FROM dba_sql_plan_baselines;
COUNT(*)
----------
0
-- Automatic capturing is now enabled
Next, it is shown how Oracle
automatically handles changing execution plans with SQL plan
baselines. A brand new database is being used so there are no parsed
statements for any user tables yet. Run the first SELECT statement:
LUTZ
AS SYSDBA @ racdb11g1 SQL> SELECT last_name, salary
FROM hr.employees
WHERE employee_id=100;
LAST_NAME
SALARY
------------------------- ----------
King
24000
Now find the
10 SQL plans that are in the SMB, the query plus recursive calls:
LUTZ AS SYSDBA @ racdb11g1 SQL> select SQL_TEXT,
SQL_HANDLE from dba_sql_plan_baselines;
SQL_TEXT
---------------------------------------------------------
SQL_HANDLE
------------------------------
delete from sdo_geor_ddl__table$$
SYS_SQL_02a86218930bbb20
SELECT value
FROM gv$osstat
WHE
SYS_SQL_3a2aebd897c53743
SELECT INSTANTIABLE,
supertype_owner, supertype_name, LOCAL_ATTRIBUTES FROM all_
SYS_SQL_3b96046878c8878e
SELECT inst_id,
service_name,
TO_CHAR(CAST(begin_time AS TIMES
SYS_SQL_3fb286c29fe007c1
SELECT instance_name
FROM gv$instance
SYS_SQL_573300848514663d
select emd_url
from mgmt_targets where target_name= :1 and target_type=:2
SYS_SQL_7f900a3c2a229199
select last_name,
salary from hr.employees where employee_id=100
SYS_SQL_818c1879b000a439
SELECT
OWNER,JOB_NAME,COMMENTS FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE
'EM_I
SYS_SQL_82a5064ccbe7ec60
SELECT s.inst_id,
s.name, i.instance_name
FROM gv$active_se
SYS_SQL_bfae8140a25b2697
SELECT value FROM
gv$osstat
WHERE
SYS_SQL_e0292aa6e5e37835
10
rows selected.
This means that 10 SQL plan baselines have
been selected in the SMB, one per captured
statement. The optimizer has created a best cost plan for the SELECT
and the plan for this execution is the only plan in the statement's
SQL plan baseline now. The status of the plan is ENABLED and
ACCEPTED but not FIXED. Fixed SQL Plan Baselines will be covered
later on in the section about manual maintenance of SQL plan
baselines.
ENABLED means that the plan was created
as a best cost plan by the optimizer, ACCEPTED means that the plan
has been verified as a good plan and is in the SQL plan baseline for
this statement and can be used for execution. Obviously the first
best cost plan which is created will always have the status ENABLED
and ACCEPTED. It is the only plan which exists for the statement up
to now.
Look at the SQL plan baseline for the
statement:
LUTZ AS
SYSDBA @ racdb11g1 SQL> SELECT * FROM
table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_818c1879b000a439',
format=> 'basic'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
SQL handle: SYS_SQL_818c1879b000a439
SQL text: select last_name, salary from hr.employees where
employee_id=100
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_b000a439c0e983c6
Enabled: YES Fixed: NO
Accepted: YES Origin: AUTO-CAPTURE
The next time the statement gets
executed, the optimizer will probably create a best cost plan for it.
This time, it will add the new plan to plan history and mark it as
NON-ACCEPTED. The optimizer looks up in the execution history if this
statement has been executed before and verifies it by comparing the
new best cost plan against those which already exist in the SQL plan
baseline. If the ?new? plan already exists in the baseline, the
optimizer will use this plan. If the new plan does not exist in the
baseline but is better in costs then the existing ones, it will be put
into the plan baseline and marked as ACCEPTED and can then get used
for further executions.
If the new plan is worse in cost than the
ones already existing in the plan baseline, the optimizer will just
add it the plan history. Plans marked as ENABLED only but not as
ACCEPTED are plans which have been ACCEPTED once but are not accepted
for execution currently or plans which were created as a best cost
plan, though they have not been verified yet. This means that those
plans are part of the baseline as plans that can be considered for
execution.
Plans which are neither ENABLED nor
ACCEPTED will only be part of the plan history for the statement as a
plan which has been created as a best cost plan by the optimizer.
The SQL plan baseline only consists of plans with status ACCEPTED plus
ENABLED.
Now change something in the design. Drop
the primary key of the table plus the index:
Index dropped.
Run the statement again and look at the
plan baseline. Now there are two plans in the baseline, the new one
which is ENABLED and ACCEPTED for execution and the first one which is
now ENABLED but not ACCEPTED any more:
and include the URL for the page.
