sql profile和SPM baseline谁的优先级高
发布网友
发布时间：2022-04-20 05:08
共1个回答
热心网友
时间：2022-04-08 00:59
我建立了一个SQL profile走索引，建立了一个SPM baseline走全表扫描
先看执行计划
SQL> set autot on exp
SQL> Select count(*) FROM hWz Where ID=200;
COUNT(*)
----------
1
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2897229555
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| HWZ | 2 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=200)
Note
-----
- SQL profile "pro_test" used for this statement
- SQL plan baseline "SQL_PLAN_2cmwdxu0450tt1412268d" used for this statement
SQL> set autot off
看见二者都生效了，但是用的是全表扫描
在看下10053trace的结果的情况
SQL> alter session set events '10053 trace name context forever, level 12';
Session altered.
SQL> Select count(*) FROM hWz Where ID=200;
COUNT(*)
----------
1
1 row selected.
SQL> alter session set events '10053 trace name context off';
Session altered.
......................
SPM: statement found in SMB
......................
索引的hint先被应用，sql profile生效了
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("HWZ" "HWZ_IDX01") */ COUNT(*) "COUNT(*)" FROM "ORACLE_OCM"."HWZ" "HWZ" WHERE "HWZ"."ID"=200
kkoqbc: optimizing query block SEL$1 (#0)
......................
Access path analysis for HWZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for HWZ[HWZ]
Table: HWZ Alias: HWZ
Card: Original: 2000.000000 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00
Access Path: index (AllEqRange)
Index: HWZ_IDX01
resc_io: 1.00 resc_cpu: 8371
ix_sel: 0.000999 ix_sel_with_filters: 0.000999
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange --走的是索引
Index: HWZ_IDX01
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 2.00 Bytes: 0
***************************************
......................
SPM: planId's of plan baseline are: 336733837
SPM: using qksan to reproce, cost and select accepted plan, sig = 2760581152094978873
SPM: plan reprocibility round 0 (plan outline + session OFE)
SPM: using qksan to reproce accepted plan, planId = 336733837
Registered qb: SEL$1 0x23afa130 (PARSER)
.....................
SPM: planId in plan baseline = 336733837, planId of reproced plan = 336733837
SPM: best cost so far = 3.01, current accepted plan cost = 3.014448
Starting SQL statement mp
.....................
SPM: re-parse to use selected accepted plan, planId = 336733837
Registered qb: SEL$1 0x23a61dc0 (PARSER)
......................
SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan, planId = 336733837
......................
Final query after transformations:******* UNPARSED QUERY IS *******
baseline的hint被加进去了
SELECT /*+ INDEX ("HWZ" "HWZ_IDX01") FULL ("HWZ") */ COUNT(*) "COUNT(*)" FROM "ORACLE_OCM"."HWZ" "HWZ" WHERE "HWZ"."ID"=200
kkoqbc: optimizing query block SEL$1 (#0)
......................
Access path analysis for HWZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for HWZ[HWZ]
Table: HWZ Alias: HWZ
Card: Original: 2000.000000 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 435607
Resp_io: 3.00 Resp_cpu: 435607
Best:: AccessPath: TableScan
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 2.00 Bytes: 0
走的是全表扫描
***************************************
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL | HWZ | 2 | 8 | 3 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("ID"=200)
Content of other_xml column
===========================
db_version : 11.2.0.1
parse_schema : ORACLE_OCM
plan_hash : 2897229555
plan_hash_2 : 336733837
sql_profile : pro_test
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "HWZ"@"SEL$1")
END_OUTLINE_DATA
*/
总结:
sql profile和SPM baseline是一起作用，hint中会被合并，这可以从显示的执行计划上看出来，但是sql profile要起作用的前提是，不改变执行计划（废话，要它干啥呢？）
