转自:http://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/
PLAN STABILITY
How many times you have noticed a query using Index X when you wanted it to use index Y or query performing Nested Loop join when Hash Join would have completed the query much faster.Or take a scenario when the application suddenly starts using wrong plan after database restart. To solve all these issues oracle provides feature called Plan stability. As per docs
“Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE. “
Oracle provides following ways to ensure it
1) Stored outlines
2) Sql Profiles
3)Sql Plan Management
Stored outlines
A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
You can read about outlines at ORACLE-BASE
Sql Profiles
As per Jonathan Lewis post,
SQL Profile consists of a stored SQL statement, and a set of hints that will be brought into play when that SQL has to be optimised. Unlike Stored Outlines, the hints for SQL Profiles do not attempt to dictate execution mechanisms directly. Instead they supply arithmetical correction factors to the optimizer as it does its arithemetic as, even with a 100% sample size, it is still possible for the optimizer to misinterpret your statistics and produce an unsuitable execution path.
In principle, if the data distributions do not change, then a stored profile will ensure that the optimizer “understands” your data and does the right thing – even when the data volume changes.
Difference between stored outline and stored profiles
Quoting Tom kyte post
Stored outlines are a set of hints that say “use this index, do this table first, do that next, use this access path, perform this filter then that filter”….
Sql profiles are more like extended statistics – they are the result of “analyzing a query”, the information provided to the optimizer is not HOW to perform the query – but rather better information about how many rows will flow out of a step in the plan, how selective something is.
Say you have a query, you generate a stored outline for it. You now add an index to the underlying table. This index would be GREAT for the query. A stored outline won’t use it, the stored outline says “use this index – query that table – then do this”. Since the GREAT index did not exist when the outline was generated – it won’t be used.
Say you have a query, you generate a profile for it. You now add an index to the underlying table. This index would be GREAT for the query. A profile can use it – since the profile is just more information for the CBO – better cardinality estimates.
So Stored outlines will ensure that whatever plan you fix, it will be used whereas a Sql profile might use different plan if we see some change in data distribution or new indexes.
There are two ways of generating sql profiles
1)Using Sql Tuning Advisor – A lot has been written about this, so I won’t discuss this.
2)Manually – This might be a surprising for lot of people as they might not be aware that we can generate a sql profile manually.I will be discussing this in detail below
Oracle provides a script coe_xfr_sql_profile.sql as part of Note 215187.1 – SQLT (SQLTXPLAIN) – Tool That Helps To Diagnose SQL Statements Performing Poorly which can be used to manually create Sql profiles.
It’s usage is pretty simple.
a) You need to execute the script and it will prompt you to pass sql_id for statement.
b)Then it will scan AWR repository and return Plan hash value for all possible execution plans for query along with average elapsed time.
c)You need to select the plan hash value which you think corresponds to good plan.
d)This will generate a script in current working directory which can be run as sys user and it will create the sql profile
This script is also useful if we wish to move the good plan from Non production environment to Production environment. To give a demo, I will create a table PLAN_STABILITY and index IDX_PLAN_STABI_OWNER on owner column.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL> create table plan_stability as select * from dba_objects;
Table created.
SQL> insert into plan_stability select * from dba_objects;
75048 rows created.
SQL> insert into plan_stability select * from plan_stability;
300192 rows created.
.. .. SQL> insert into plan_stability select * from plan_stability;
1200768 rows created.
SQL> commit ;
22:15:05 SQL> create index IDX_PLAN_STABI_OWNER on plan_stability(owner);
Index created.
SQL> select count (*) from plan_stability;
COUNT (*)
---------- 2401536 |
Let’s query the table for count of objects owned by SYS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SQL> select count (*) from plan_stability where owner= 'SYS' ;
COUNT (*)
---------- 1020384 SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID g3wubsadyrt37, child number 0 ------------------------------------- select count (*) from plan_stability where owner= 'SYS'
Plan hash value: 3082746383 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER | 23092 | 135K| 57 (0)| 00:00:01 |
------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access( "OWNER" = 'SYS' )
|
We see that we are using index IDX_PLAN_STABI_OWNER. Since data is less , Index access is best approach.But for this demo I want to force a Full table scan . Let’s generate a plan with FTS for plan_stability. To do this we can use Invisible indexes (11g feature) so that optimizer ignores index.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
SQL> alter index IDX_PLAN_STABI_OWNER invisible;
Index altered.
SQL> select count (*) from plan_stability where owner= 'SYS' ;
COUNT (*)
---------- 1020384 SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID g3wubsadyrt37, child number 0 ------------------------------------- select count (*) from plan_stability where owner= 'SYS'
Plan hash value: 363261562 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter( "OWNER" = 'SYS' )
19 rows selected.
--Let's make it index visible again SQL> alter index IDX_PLAN_STABI_OWNER visible;
Index altered.
|
We will have to take manual AWR snapshots before and end of the test, so that sql plans goes into AWR repository . This is necessary as coe_xfr_profile.sql will look at AWR data for plan history for a sql. Now we run coe_xfr_profile.sql and it will prompt us for sql_id. On passing the sql_id, it reports that there are two plans. We choose plan 363261562 (having higher elapsed time) as we wish to force a Full table scan
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>@coe_xfr_profile.sql Parameter 1: SQL_ID (required) Enter value for 1: g3wubsadyrt37
PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 3082746383 .064 363261562 .184 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 363261562
Values passed:
~~~~~~~~~~~~~ SQL_ID : "g3wubsadyrt37"
PLAN_HASH_VALUE: "363261562"
Execute coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql
on TARGET system in order to create a custom SQL Profile
with plan 363261562 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed. |
This has generated a file coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql in same directory which can be run to create the sql profile.Note that script has force_match => FALSE which means that if sql varies in literal, sql_profile will not work. To force it ,we need to set the paramter to force.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
SQL>@coe_xfr_sql_profile_g3wubsadyrt37_363261562 SQL>REM SQL>REM $Header: 215187.1 coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql 11.4.1.4 2011/12/18 csierra $ SQL>REM SQL>REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
SQL>REM SQL>REM coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql SQL>REM SQL>REM DESCRIPTION SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID g3wubsadyrt37 based on plan hash
SQL>REM value 363261562. SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM SQL>REM PARAMETERS SQL>REM None. SQL>REM SQL>REM EXAMPLE SQL>REM SQL> START coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql; SQL>REM SQL>REM NOTES SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE( 'coe_g3wubsadyrt37_363261562' );
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license SQL>REM for the Oracle Tuning Pack.
SQL>REM SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE; SQL>REM SQL>VAR signature NUMBER; SQL>REM SQL> DECLARE
2 sql_txt CLOB; 3 h SYS.SQLPROF_ATTR; 4 BEGIN
5 sql_txt := q '[
6 select count(*) from plan_stability where owner=' SYS '
7 ]' ;
8 h := SYS.SQLPROF_ATTR( 9 q '[BEGIN_OUTLINE_DATA]' ,
10 q '[IGNORE_OPTIM_EMBEDDED_HINTS]' ,
11 q '[OPTIMIZER_FEATURES_ENABLE(' 11.2.0.3 ')]' ,
12 q '[DB_VERSION(' 11.2.0.3 ')]' ,
13 q '[OPT_PARAM(' _b_tree_bitmap_plans ' ' false ')]' ,
14 q '[OPT_PARAM(' optimizer_dynamic_sampling ' 0)]' ,
15 q '[ALL_ROWS]' ,
16 q '[OUTLINE_LEAF(@"SEL$1")]' ,
17 q '[FULL(@"SEL$1" "PLAN_STABILITY"@"SEL$1")]' ,
18 q '[END_OUTLINE_DATA]' );
19 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); 20 DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 21 sql_text => sql_txt, 22 profile => h, 23 name => 'coe_g3wubsadyrt37_363261562' ,
24 description => 'coe g3wubsadyrt37 363261562 ' ||:signature|| '' ,
25 category => 'DEFAULT' ,
26 validate => TRUE ,
27 replace => TRUE ,
28 force_match => FALSE /* TRUE : FORCE (match even when different literals in SQL). FALSE :EXACT (similar to CURSOR_SHARING) */ );
29 END ;
30 / PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF ;
SIGNATURE --------------------- 4782703451567619555 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_g3wubsadyrt37_363261562 completed |
Let’s verify the plan again now
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID g3wubsadyrt37, child number 1 ------------------------------------- select count (*) from plan_stability where owner= 'SYS'
Plan hash value: 363261562 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter( "OWNER" = 'SYS' )
Note ----- - SQL profile coe_g3wubsadyrt37_363261562 used for this statement
|
In the Note section we can see that Sql Profile “coe_g3wubsadyrt37_363261562″ has been used for this statement.
Suppose we now have to create a sql profile, when we don’t have good plan in AWR, then we will have to do a hack. There are two ways to do it
a)You can use coe_xfr_profile.sql to do same. You will have to run the script twice, one for original statement and secondly for hinted statement. Once done you need to copy the values corresponding to h := SYS.SQLPROF_ATTR from hinted sql and replace it in original script.e.g
I am creating a sql profile for following hinted statement
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
SQL> select /*+ full (plan_stability) */ count (*) from plan_stability where owner= 'SYS' ;
COUNT (*)
---------- 1020384
SQL> select /*+ full (plan_stability) */ count (*) from plan_stability where owner= 'SYS' ;
COUNT (*)
---------- 1020384
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID 9p07a64q8fgrn, child number 0 ------------------------------------- select /*+ full (plan_stability) */ count (*) from plan_stability where
owner= 'SYS'
Plan hash value: 363261562 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter( "OWNER" = 'SYS' )
--Creating Sql profile now SQL>@coe_xfr_profile.sql 9p07a64q8fgrn 363261562 Parameter 1: SQL_ID (required) PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 363261562 .193
Parameter 2: PLAN_HASH_VALUE (required) Values passed:
~~~~~~~~~~~~~ SQL_ID : "9p07a64q8fgrn"
PLAN_HASH_VALUE: "363261562"
Execute coe_xfr_sql_profile_9p07a64q8fgrn_363261562.sql
on TARGET system in order to create a custom SQL Profile
with plan 363261562 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed. |
Now we create sql profile script for original statement using plan_hash_value corresponding to index access
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
20:47:52 SQL> @coe_xfr_profile.sql g3wubsadyrt37 3082746383 Parameter 1: SQL_ID (required) PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 3082746383 .084
363261562 .184
Parameter 2: PLAN_HASH_VALUE (required) Values passed:
~~~~~~~~~~~~~ SQL_ID : "g3wubsadyrt37"
PLAN_HASH_VALUE: "3082746383"
Execute coe_xfr_sql_profile_g3wubsadyrt37_3082746383.sql
on TARGET system in order to create a custom SQL Profile
with plan 3082746383 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed. |
Now copy following section from coe_xfr_sql_profile_9p07a64q8fgrn_363261562.sql and replace in the coe_xfr_sql_profile_g3wubsadyrt37_3082746383.sql. Also change the name from coe_g3wubsadyrt37_3082746383 to coe_g3wubsadyrt37_363261562
1
2
3
4
5
6
7
8
9
|
h := SYS.SQLPROF_ATTR( q '[BEGIN_OUTLINE_DATA]' ,
q '[IGNORE_OPTIM_EMBEDDED_HINTS]' ,
q '[OPTIMIZER_FEATURES_ENABLE(' 11.2.0.3 ')]' ,
q '[DB_VERSION(' 11.2.0.3 ')]' ,
q '[ALL_ROWS]' ,
q '[OUTLINE_LEAF(@"SEL$1")]' ,
q '[FULL(@"SEL$1" "PLAN_STABILITY"@"SEL$1")]' ,
q '[END_OUTLINE_DATA]' );
|
This has again created a sql profile with FTS plan.
1
2
3
4
5
|
SQL> select name ,SIGNATURE,SQL_TEXT,FORCE_MATCHING,STATUS from dba_sql_profiles;
NAME SIGNATURE SQL_TEXT FOR STATUS
------------------------------ ------------------------------ ---------------------------------------------------------------------- --- -------- coe_g3wubsadyrt37_363261562 4782703451567619555 select count (*) from plan_stability where owner= 'SYS' NO ENABLED
|
b) You can use scripts provided by Kerry Osborne in following article
http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take-2/
SQL Plan Management
Let’s use the 11g feature SQL Plan Management to implement plan stability.SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient.
The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system. We can capture plans
a)Automatically – We can use OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true and capture the sql baseline plans. This is pretty useful if you are upgrading database as you can capture good plans and then upgrade the database without worrying about plan change. e.g Post 11g upgrade from 10.2.0.4, set optimizer_features_enable=10.2.0.4 and capture all the plans.Once done you can set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false and also set optimizer_features_enable back to 11.1/11.2. You can then enable/disable the plans or mark them Fixed. Fixed plans get preference over non-fixed plans.
Note that only plans for repeatable statements are stored in the SPM
b)Manually – We can use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and DBMS_SPM.LOAD_PLANS_FROM_SQLSET to load plans from cursor cache and Sqlset respectively.
Loading plan from Cursor Cache
We will discuss fucntion DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to load plans from the cursor cache.In our test,we need to fix plan 363261562 for sql_id g3wubsadyrt37.Since the plan is available in cursor cache, we will use following syntax
1
2
3
4
5
6
7
8
9
10
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => 'g3wubsadyrt37' ,
PLAN_HASH_VALUE =>363261562, FIXED => 'YES' );
dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ |
It will return number of plans loaded.Let’s verify baseline by querying dba_sql_plan_baselines
1
2
3
4
5
|
SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC FIX ------------------------------ ------------------------------ --- --- --- SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES YES |
We can plan corresponding to this baseline
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SQL> select * from table (
dbms_xplan.display_sql_plan_baseline( sql_handle=> 'SQL_425f937308b8fde3' ,
format=> 'basic' ));
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL handle: SQL_425f937308b8fde3 SQL text: select count (*) from plan_stability where owner= 'SYS'
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name : SQL_PLAN_44rwmfc4bjzg3621540b0 Plan id: 1645559984
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL- LOAD
-------------------------------------------------------------------------------- Plan hash value: 363261562 --------------------------------------------- | Id | Operation | Name |
--------------------------------------------- | 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL | PLAN_STABILITY |
--------------------------------------------- 20 rows selected.
|
We can check if our original query is using the plan
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL> select count (*) from plan_stability where owner= 'SYS' ;
COUNT (*)
---------- 1020384 SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID g3wubsadyrt37, child number 1 ------------------------------------- select count (*) from plan_stability where owner= 'SYS'
Plan hash value: 363261562 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter( "OWNER" = 'SYS' )
Note ----- - SQL plan baseline SQL_PLAN_44rwmfc4bjzg3621540b0 used for this statement
|
We see from Note section that SQL plan baseline SQL_PLAN_44rwmfc4bjzg3621540b0 has been used for the statement.
Loading plan from AWR/SQL Tuning set
If you know that good plan of the query is available in AWR, then you can use dbms_spm.load_plans_from_sqlset. To do this we need to first create a sql tuning set. This can be done by using DBMS_SQLTUNE
1
2
3
4
5
6
|
BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'sqlset_g3wubsadyrt37' ,
description => 'Example to show dbms_spm.load_plans_from_sqlset' );
END ;
/ |
Let’s load the sql_id g3wubsadyrt37 in this Sql tuning set from AWR. We need to pass begin_snap and end_snap for SQL. We can also use basic_filter clause to restrict this sqlset to only one particular sql_id.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
DECLARE baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
BEGIN_SNAP => 641, END_SNAP => 667, BASIC_FILTER => 'sql_id = ' 'g3wubsadyrt37' '' ,
ATTRIBUTE_LIST => 'ALL' )) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'sqlset_g3wubsadyrt37' ,
populate_cursor => baseline_cursor); END ;
/ |
Lets verify that both plans are loaded in the sqlset
1
2
3
4
5
6
7
|
SELECT SQL_ID,PLAN_HASH_VALUE FROM TABLE (DBMS_SQLTUNE.SELECT_SQLSET(
'sqlset_g3wubsadyrt37' ));
SQL_ID PLAN_HASH_VALUE ------------- --------------- g3wubsadyrt37 363261562 g3wubsadyrt37 3082746383 |
Next we create sql plan baseline using DBMS_SPM.LOAD_PLANS_FROM_SQLSET
1
2
3
4
5
6
7
8
9
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( SQLSET_NAME => 'sqlset_g3wubsadyrt37'
); dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ |
We now see that two plans are loaded
1
2
3
4
5
6
|
SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC FIX ------------------------------ ------------------------------ --- --- --- SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES NO
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3ec110d89 YES YES NO
|
We can disable the SQL_PLAN_44rwmfc4bjzg3ec110d89 as we only want FTS plan
1
2
3
4
5
6
7
8
9
10
11
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( sql_handle => 'SQL_425f937308b8fde3' ,
PLAN_NAME => 'SQL_PLAN_44rwmfc4bjzg3ec110d89' ,
attribute_name => 'enabled' ,
attribute_value=> 'NO' );
dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ |
Above statement will mark the index plan as disabled and our query will use only FTS plan.
To drop the baselines, we can use following syntax
1
2
3
4
5
6
7
8
9
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE( sql_handle => 'SQL_425f937308b8fde3'
); dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ |
Till now we have discussed scenarios when we have good plan in cursor cache/AWR. Suppose there is no good plan available but we can generate a good plan using hints. With SPM we can easily transfer profile from hinted sql to our original statement. Let’s see it in action
First we need to create a baseline with existing plan for query. This is required as it would generate a sql_handle which can be used to assign a plan. We are using PLAN_HASH_VALUE =>3082746383 i.e Indexed access path to create the sql baseline
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => 'g3wubsadyrt37' ,
PLAN_HASH_VALUE =>3082746383 ); dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ SQL> select sql_handle, plan_name, enabled, accepted, fixed,sql_text from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT ------------------------------ ------------------------------ --- --- --- -------------------------------------------------------------------------------- SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3ec110d89 YES YES NO select count (*) from plan_stability where owner= 'SYS'
|
Querying dba_sql_plan_baselines we get sql_handle as SQL_425f937308b8fde3.Now let’s create a plan for query using hints.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
SQL> select /*+ full (plan_stability) */ count (*) from plan_stability where owner= 'SYS' ;
COUNT (*)
---------- 1020384 SQL> select /*+ full (plan_stability) */ count (*) from plan_stability where owner= 'SYS' ;
COUNT (*)
---------- 1020384 SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID 9p07a64q8fgrn, child number 0 ------------------------------------- select /*+ full (plan_stability) */ count (*) from plan_stability where
owner= 'SYS'
Plan hash value: 363261562 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter( "OWNER" = 'SYS' )
|
We need to now copy plan corresponding to sql_id=9p07a64q8fgrn and plan_hash_value=363261562. This can be done by using sql_handle for original statement and using sql_id/plan_hash_value of hinted statement
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '9p07a64q8fgrn' ,
sql_handle => 'SQL_425f937308b8fde3' ,
PLAN_HASH_VALUE =>363261562, FIXED => 'YES'
); dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ |
Note that I have used FIXED=>YES argument which would mark this plan as Fixed.A fixed plan takes precedence over a non-fixed plan. We can drop the old baseline plan now by passing sql_handle and plan_name. If you use only sql_handle, it will drop both the plans
1
2
3
4
5
6
7
8
9
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE( sql_handle => 'SQL_425f937308b8fde3' ,
PLAN_NAME => 'SQL_PLAN_44rwmfc4bjzg3ec110d89' );
dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ |
Instead of dropping plan, you can also disable the above plan using DBMS_SPM.ALTER_SQL_PLAN_BASELINE
1
2
3
4
5
6
7
8
9
10
11
|
DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( sql_handle => 'SQL_425f937308b8fde3' ,
PLAN_NAME => 'SQL_PLAN_44rwmfc4bjzg3ec110d89' ,
attribute_name => 'enabled' ,
attribute_value=> 'NO' );
dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ |
We now have two plans in baseline but only enabled plan SQL_PLAN_44rwmfc4bjzg3621540b0 will be used by the query.
Note: Whenever we create/drop sql baseline plan, sql is purged from cursor cache and we perform a hard parse.
While testing this , I found that if I create a alias for table , SQL Plan baseline is not working
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
SQL> select /*+ full (a) */ count (*) from plan_stability a where owner= 'SYS' ;
COUNT (*)
---------- 1020384 Elapsed: 00:00:00.18 select /*+ full (a) */ count (*) from plan_stability a where owner= 'SYS' ;
COUNT (*)
---------- 1020384 SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID 85hj3c8570fdu, child number 0 ------------------------------------- select /*+ full (a) */ count (*) from plan_stability a where owner= 'SYS'
Plan hash value: 363261562 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter( "OWNER" = 'SYS' )
SQL> DECLARE
my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '85hj3c8570fdu' ,
sql_handle => 'SQL_425f937308b8fde3' ,
PLAN_HASH_VALUE =>363261562, FIXED => 'YES'
); dbms_output.put_line( 'Value is ' ||my_plans);
END ;
/ SQL> select sql_handle, plan_name, enabled, accepted, fixed,sql_text from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT ------------------------------ ------------------------------ --- --- --- -------------------------------------------------------------------------------- SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES YES select count (*) from plan_stability where owner= 'SYS'
SQL> select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------- SQL_ID g3wubsadyrt37, child number 1 ------------------------------------- select count (*) from plan_stability where owner= 'SYS'
Plan hash value: 363261562 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter( "OWNER" = 'SYS' )
|
We can check baseline information using dbms_xplan.display_sql_plan_baseline function.Let’s see why we didnt use FTS even though profile was successfully created
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
SQL> select * from table (
dbms_xplan.display_sql_plan_baseline( sql_handle=> 'SQL_425f937308b8fde3' ,
format=> 'basic' ));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL handle: SQL_425f937308b8fde3 SQL text: select count (*) from plan_stability where owner= 'SYS'
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name : SQL_PLAN_44rwmfc4bjzg3621540b0 Plan id: 1645559984
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL- LOAD
-------------------------------------------------------------------------------- Plan hash value: 3082746383 -------------------------------------------------- | Id | Operation | Name |
-------------------------------------------------- | 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | | | 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER |
-------------------------------------------------- -------------------------------------------------------------------------------- Plan name : SQL_PLAN_44rwmfc4bjzg3ec110d89 Plan id: 3960540553
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
-------------------------------------------------------------------------------- Plan hash value: 3082746383 -------------------------------------------------- | Id | Operation | Name |
-------------------------------------------------- | 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | | | 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER |
-------------------------------------------------- |
From above we see that SQL_PLAN_44rwmfc4bjzg3621540b0 is showing plan which uses Index and not FTS.I am not sure if this is correct behavior or a bug. One reason could be that same alias is not present in parent table.
I believe Sql Plan Management is superior to SQL Profile and outlines as it allows you to have multiple plans for a given sql statement. Also a nightly maintenance job runs automatic SQL tuning task and targets high-load SQL statements.If it thinks that there is better plan , then it adds that SQL Plan baseline to history. This is called Evolving Sql Plan Baselines.
Christian Antognini has written a good article on Automatic Evolution of SQL Plan Baselines which explains this feature.
Licensing
As per Optimizer blog article ,DBMS_SPM is not licensed until we are loading plans from SQL Tuning set (which requires tuning pack).SQL profiles are licensed and require diagnostic and tuning pack.
相关推荐
profiles, and plan baselines Optimize queries within packaged applications without touching the code Recognize when not to waste time on SQL that is performing optimally Who this book is for Pro ...
色彩管理配置文件(Color Management Profiles)是色彩管理领域中的核心概念之一,它在确保跨不同设备的颜色一致性方面扮演着至关重要的角色。本文将深入探讨色彩管理配置文件的基本原理、工作方式以及两种常见的...
- `SELECT * FROM DBA_SQL_PROFILES;` 来列出所有SQL Profile。 - `EXPLAIN PLAN FOR SELECT...;` 和 `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);` 可以查看SQL的执行计划。 - `ALTER SYSTEM FLUSH SHARED_POOL;` ...
OEM and the Event Management System Chapter 14 Maintaining User Accounts Application Design Requirements Running the Application Documenting the User State A Sample Script Enhanced Oracle ...
MySQL 的 `Profiles` 功能是一个非常强大的工具,它允许数据库管理员和开发者深入了解 SQL 语句的执行过程,从而优化数据库性能。这一特性自 MySQL 5.0.37 版本引入,为用户提供了比 `EXPLAIN` 更深入的洞察,包括 ...
文档可能还会介绍Oracle的SQL优化器,如CBO(Cost-Based Optimizer)的工作原理,以及如何通过SQL Profiles和SQL Plan Baselines来稳定高效的执行计划。 其他如“9.pdf”、“3.pdf”、“4.pdf”等文件,很可能是对...
with offender profiles based on address, age, sex, and ethnicity. Address–age–sex victim profiles were compared with address–age–sex–ethnicity offender profiles. In addition, the extent to which ...
11. **资源管理**:探讨如何通过SQL profiles、SQL plan baselines和Resource Manager来控制SQL执行的资源消耗。 12. **监控和诊断工具**:了解TKPROF、10046事件、ASH和GATHER_SYSTEM_STATISTICS等工具的使用,以...
### HIVE-SQL操作语句详解 #### 一、创建表 (CREATE TABLE) 在Hive中,`CREATE TABLE` 语句用于创建新的表。与传统的关系型数据库类似,但在Hive中有其特殊之处。 ##### 示例 ```sql hive> CREATE TABLE pokes ...
1. 在Eclipse中,打开"Window"菜单,选择"Preferences" -> "Data Management" -> "Connectivity"。 2. 在"Connection Profiles"区域,点击右下角的"+"图标,选择"SQL Server"。 3. 输入"Profile Name",如"MySQL ...
It adopts ASP.NET technology, Client/Server (C/S) architecture, and SQL database for development, ensuring the security and stability of data to realize basic operations such as adding, deleting, ...
The choice of using Java and the SSM framework ensures scalability, stability, and security for the online store. Java's robustness, platform independence, and rich libraries make it an ideal choice ...
Using Firebird Maestro you can operate database objects, build queries visually, represent databases as ER diagrams, execute queries and SQL scripts, view and edit data including BLOBs, represent ...
**Python库dbt_sqlserver-1.0.0rc1-py3-none-any.whl详解** dbt(数据构建和转型)是一个强大的开源工具,专为数据分析师和工程师设计,用于将数据转换过程转变为可重复、可靠且文档化的流程。在Python环境中,dbt...
在安全性方面,Microsoft SQL Server提供了基于角色的服务器安全、数据库和应用程序的配置文件(profiles)、集成的安全审计,能够追踪多种安全事件,支持SSL、Kerberos等加密技术,适合企业级的安全需求。...
要使用该功能,mysql的版本必须在5.0.37版本以上。否则只能使用explain 的方式来检查。 profiling 功能可以了解到cpu io 等更详细的信息。 show profile 的格式如下: SHOW PROFILE [type [, type] ......
S mode system, developed using the Spring Boot framework and the MYSQL database, fully ensuring the stability of the system. With its clear interface, simple operation, and comprehensive functions, ...
This project likely implements measures such as encryption for sensitive data, session management to prevent unauthorized access, and validation checks to protect against SQL injection and cross-site...