Home > TSOP > 11g sql baselines实验

11g sql baselines实验

11g sql baselines 实验
sql baselines 在outline 的基础上,增加了对更好执行计划的捕获及演变功能,而outline只有固化执行计划的能力,使用起来也不是很方便。
sql baselines是将sql的执行计划保存在SYSAUX空间的SQL Management Base中,sql解析产生execution plan的时候会参考baseline的信息,那具体baseline是如何影响sql的执行计划产生的呢?
image

也就是说,解析的时候查找SMB,如果存在baseline,则使用baseline,否则使用当前产生的执行计划,到这里其实跟outline差不多,不过baseline最重要的功能不在这里。
要使用sql bseline需要开启一个系统参数:

alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=true;

1.sql baseline的产生

有3种方式可以为sql产生一个sql baseline:

a.OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES参数,会自动捕获所有sql的baselines

alter [session|system] set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

b.dbms_spm包,dbms_spm包提供了load_plans_from_cursor_cache(从shared pool中装载某些sql的sql baselines,该函数重载了4次以接受不同的参数搭配),load_plans_from_sqlset(从sql tuning set装载)

c.sql tuning advisor

1.1 auto capture

oracle只对执行了2次及以上的sql才自动捕获,一个sql第一次执行的时候,oracle会记录一个sqllog(只有signature信息),第二次执行的时候才会生成一个baseline。

如果发现当前执行计划跟baseline中的不一样,就将当前的执行计划存入baseline history中,等待进一步检验。

image

johnny/JOHNNY@PERFDB_2>show parameter capture

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
johnny/JOHNNY@PERFDB_2>create table c as select * from b;
johnny/JOHNNY@PERFDB_2>create index c_ind on c(id);
johnny/JOHNNY@PERFDB_2>var v number;
johnny/JOHNNY@PERFDB_2>exec :v:=2;
johnny/JOHNNY@PERFDB_2>select /*+ full(c)*/* from c where id<:v;
sys/SYS@PERFDB_2>select sql_id,sql_text,EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE from v$sqlarea where sql_text like 'select /*+ full(c)*/* from c where id<:v%';

SQL_ID        SQL_TEXT                                             EXACT_MATCHING_SIGNATURE   FORCE_MATCHING_SIGNATURE
------------- -------------------------------------------------- -------------------------- --------------------------
5y3y1475q3c0v select /*+ full(c)*/* from c where id<:v                  4574111222212650461        4574111222212650461

1 row selected.

sys/SYS@PERFDB_2>select * from sqllog$ where SIGNATURE='4574111222212650461';

no rows selected

sys/SYS@PERFDB_2>select count(*) from sqlobj$auxdata where SIGNATURE='4574111222212650461';

COUNT(*)
----------
0

1 row selected.

johnny/JOHNNY@PERFDB_2>alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
johnny/JOHNNY@PERFDB_2>select /*+ full(c)*/* from c where id<:v;
sys/SYS@PERFDB_2>select * from sqllog$ where SIGNATURE='4574111222212650461';

SIGNATURE     BATCH#
------------------------ ----------
4574111222212650461          1

1 row selected.

sys/SYS@PERFDB_2>select count(*) from sqlobj$auxdata where SIGNATURE='4574111222212650461';

COUNT(*)
----------
0

1 row selected.

johnny/JOHNNY@PERFDB_2>select /*+ full(c)*/* from c where id<:v;
sys/SYS@PERFDB_2>select * from sqllog$ where SIGNATURE='4574111222212650461';

SIGNATURE     BATCH#
------------------------ ----------
4574111222212650461          1

1 row selected.

sys/SYS@PERFDB_2>select count(*) from sqlobj$auxdata where SIGNATURE='4574111222212650461';

COUNT(*)
----------
1

1 row selected.

johnny/JOHNNY@PERFDB_2>alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false;

1.2 load from library cache

dbms_spm.load_plans_from_cursor_cache提供了4种类型的参数:

FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
ATTRIBUTE_NAME                 VARCHAR2                IN             --只有sql_text,parsing_schema_name,module,action可供选择
ATTRIBUTE_VALUE                VARCHAR2                IN
FIXED                          VARCHAR2                IN     DEFAULT
ENABLED                        VARCHAR2                IN     DEFAULT
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID                         VARCHAR2                IN
PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT
FIXED                          VARCHAR2                IN     DEFAULT
ENABLED                        VARCHAR2                IN     DEFAULT
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID                         VARCHAR2                IN
PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT
SQL_TEXT                       CLOB                    IN
FIXED                          VARCHAR2                IN     DEFAULT
ENABLED                        VARCHAR2                IN     DEFAULT
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID                         VARCHAR2                IN
PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT
SQL_HANDLE                     VARCHAR2                IN
FIXED                          VARCHAR2                IN     DEFAULT
ENABLED                        VARCHAR2                IN     DEFAULT

其中第1种跟第3种可以用来为新上线的sql Load into baselines,如:

ret := dbms_spm.load_plans_from_cursor_cache(
attribute_name => 'sql_text',
attribute_value => '%MySqlStm%'
);
ret := dbms_spm.load_plans_from_cursor_cache(
sql_id => '2y5r75r8y3sj0',
plan_hash_value => NULL  --plan_hash_value为空会将该sql_id所有的plan都load进去
);

第4种可以用来手工调整当前sql baselines的执行计划,如:

johnny/JOHNNY@PERFDB_2>explain plan for
2  select /*+ full(c)*/* from c where id<:v;

Explained.

johnny/JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2174124444

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| C    |
----------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_3yyn1xh1jttfxb47c9154" used for this statement

13 rows selected.

当前为FTS,想把它调成index range scan

--查找sql的sql_handle跟plan_name
sys/SYS@PERFDB_2>l
1* select SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME ,ENABLED,ACCEPTED,FIXED,AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select /*+ full(c)*/* from c where id<:v%'
sys/SYS@PERFDB_2>/

SIGNATURE SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX AUT OPTIMIZER_COST
------------------------ ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- --------------
4574111222212650461 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfxb47c9154 YES YES NO  YES          56

1 row selected.

--手工生产正确的Plan
johnny/JOHNNY@PERFDB_2>var v number;
johnny/JOHNNY@PERFDB_2>set serveroutput off
johnny/JOHNNY@PERFDB_2>select /* +index(c c_ind)*/* from c where id<:v;

no rows selected

Elapsed: 00:00:00.00
johnny/JOHNNY@PERFDB_2>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fvmbygk6h57a3, child number 0
-------------------------------------
select /* +index(c c_ind)*/* from c where id<:v

Plan hash value: 2379350657

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| C     |     1 |   598 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | C_IND |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"<:V)

Note
-----
- dynamic sampling used for this statement (level=2)

23 rows selected.

--load 正确的plan
sys/SYS@PERFDB_2>exec :ret:=dbms_spm.load_plans_from_cursor_cache(-
>       sql_id=>'fvmbygk6h57a3',-
>       plan_hash_value=>'2379350657',-
>       sql_handle=>'SYS_SQL_3f7a81ec031ce5dd');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
sys/SYS@PERFDB_2>select SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME ,ENABLED,ACCEPTED,FIXED,AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select /*+ full(c)*/* from c where id<:v%';

SIGNATURE SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX AUT OPTIMIZER_COST
------------------------ ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- --------------
4574111222212650461 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfx70510f31 YES YES NO  YES           1
4574111222212650461 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfxb47c9154 YES YES NO  YES          56

2 rows selected.

--删除老的plan
sys/SYS@PERFDB_2>exec :ret:=dbms_spm.drop_sql_plan_baseline(-
>       sql_handle=>'SYS_SQL_3f7a81ec031ce5dd',-
>       plan_name=>'SQL_PLAN_3yyn1xh1jttfxb47c9154');

PL/SQL procedure successfully completed.

sys/SYS@PERFDB_2>select SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME ,ENABLED,ACCEPTED,FIXED,AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select /*+ full(c)*/* from c where id<:v%';

SIGNATURE SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX AUT OPTIMIZER_COST
------------------------ ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- --------------
4574111222212650461 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfx70510f31 YES YES NO  YES           1

1 row selected.

--检查下baseline是否生效
johnny/JOHNNY@PERFDB_2>explain plan for
2  select /*+ full(c)*/* from c where id<:v;

Explained.

Elapsed: 00:00:00.03
johnny/JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2379350657

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| C     |
|   2 |   INDEX RANGE SCAN          | C_IND |
---------------------------------------------

Note
-----
- SQL plan baseline "SQL_PLAN_3yyn1xh1jttfx70510f31" used for this statement

13 rows selected.

2. evolve baselines

sql baselines 只会使用ENABLE ACCEPTED的baseline,fixed是说更趋向使用,同时fixed的baselines是不参与evolve的(evolve的结果是空的),也就是fixed相当于执行固定了。

johnny/JOHNNY@PERFDB_2>select /*+ full(c)*/* from c where id<:v;

no rows selected
sys/SYS@PERFDB_2>/

SIGNATURE SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX AUT OPTIMIZER_COST
------------------------ ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- --------------
4574111222212650461 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfx70510f31 YES YES NO  YES           1
4574111222212650461 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfxb47c9154 YES NO  NO  YES          56

2 rows selected.

接着上一步,再执行下sql会发现老的baseline又回来了,但是accept是NO的,就是因为sql解析的时候发现 full的plan跟baseline中的plan不一样,就将full plan记录进baseline,但是no ACCEPT,仍然使用index range scan,就算full 真的比range scan高效。

注意verify+commit的组合,如果是verify=’no’ commit=’yes’,那evolve的baseline会直接accept.

sys/SYS@PERFDB_2>select dbms_spm.evolve_sql_plan_baseline(
2     sql_handle=>'SYS_SQL_3f7a81ec031ce5dd',
3     plan_name=>null,
4     time_limit=>1, --evolve持续的时间
5     verify=>'yes', --yes会执行SQL来检测性能,只有性能比当前baseline高的才会上升到accept状态,no就直接accept
6     commit=>'yes'  --yes oracle会接受evolve的结果,no不会更改当前baseline
7  ) from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_3F7A81EC031CE5DD',PLAN_NA
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
SQL_HANDLE = SYS_SQL_3f7a81ec031ce5dd
PLAN_NAME  =
TIME_LIMIT = 1
VERIFY     = yes
COMMIT     = yes

Plan: SQL_PLAN_3yyn1xh1jttfxb47c9154
------------------------------------
Plan was verified: Time used 0 seconds.
Plan failed performance criterion: 10000 times worse than baseline plan.

Baseline Plan      Test Plan       Stats Ratio
-------------      ---------       -----------
Execution Status:              COMPLETE       COMPLETE
Rows Processed:                       0              0
Elapsed Time(ms):                  .017           .021               .81
CPU Time(ms):                         0              0
Buffer Gets:                          0              1                 0
Physical Read Requests:               0              0
Physical Write Requests:              0              0
Physical Read Bytes:                  0              0
Physical Write Bytes:                 0              0
Executions:                           1              1

-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0  --因为FTS性能不行,可以看dba_sql_plan_baselines.OPTIMIZER_COST对比.

1 row selected.

Elapsed: 00:00:00.02

3.更改baseline属性

attribute_name只接受enabled,fixed,AUTOPURGE,plan_name,description。

sys/SYS@PERFDB_2>var ret number;
sys/SYS@PERFDB_2>exec :ret := dbms_spm.alter_sql_plan_baseline(-
> sql_handle => 'SYS_SQL_3f7a81ec031ce5dd',-
> plan_name => 'SQL_PLAN_3yyn1xh1jttfx70510f31',-
> attribute_name => 'enabled',-
> attribute_value => 'no'-
> );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
sys/SYS@PERFDB_2>select SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME ,ENABLED,ACCEPTED,FIXED,AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select /*+ full(c)*/* from c where id<:v%';

SIGNATURE SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX AUT OPTIMIZER_COST
---------- ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- --------------
4.5741E+18 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfx70510f31 NO  YES YES YES          1
4.5741E+18 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfxb47c9154 YES NO  NO  YES         56

johnny/JOHNNY@PERFDB_2>explain plan for
2  select /*+ full(c)*/* from c where id<:v;

Explained.
johnny/JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic +note'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2174124444

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| C    |
----------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

12 rows selected.

4.SMB管理

SMB占用的是SYSAUX的空间:

sys/SYS@PERFDB_2>l
1* select * from dba_sql_management_config
sys/SYS@PERFDB_2>/

PARAMETER_NAME                 PARAMETER_VALUE LAST_MODIF MODIFIED_BY
------------------------------ --------------- ---------- ------------------------------
SPACE_BUDGET_PERCENT                        10                                          --占用SYSAUX百分比报警阀值,后台是一个weekly process去检测,超过这个值会在alert报出来。
PLAN_RETENTION_WEEKS                        53                                          --没有使用过的,非fixed状态的,auto_purge的baseline 53周后自动清除

2 rows selected.
可以通过configure过程来更改:
dbms_spm.configure(
parameter_name => 'plan_retention_weeks',
parameter_value => 12
);

5.baseline 紧急fix脚本

一般情况下,一个plan经过人工确认肯定是ok的,那可以用dbms_spm.alter_sql_plan_baseline()将plan fix起来,这样plan肯定会没问题,除非plan里面的一些对象失效了比如索引并且non-reproducible(就是对象重新parse也无效,导致plan_value改变),那优化器首先会去找其他fixed并且enabled+accepted cost最低的一个baseline,如果都没有再找unfixed的情况,再没有才使用当前的plan。

所以plan走的极差的情况应该不多,因为收集的baseline都是unaccepted的,需要人工去evolve并且accept之后才会让优化器去接受,如果真出现了plan走错的情况,可以简单的用以下步骤fix:

找出出问题SQL:

 

johnny/JOHNNY@PERFDB_2>l
1* select sql_id,SQL_PLAN_BASELINE,b.sql_handle,sql_fulltext from v$sqlarea a,dba_sql_plan_baselines b where a.SQL_PLAN_BASELINE=b.PLAN_NAME and a.sql_text like 'select /*+ full(c)*/* from c where id<:v%'
johnny/JOHNNY@PERFDB_2>/

SQL_ID        SQL_PLAN_BASELINE              SQL_HANDLE                     SQL_FULLTEXT
------------- ------------------------------ ------------------------------ --------------------------------------------------
5y3y1475q3c0v SQL_PLAN_3yyn1xh1jttfxb47c9154 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v

查找可用baseline:

 

sys/SYS@PERFDB_2>select SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME ,ENABLED,ACCEPTED,FIXED,AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select /*+ full(c)*/* from c where id<:v%';

SIGNATURE SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX AUT OPTIMIZER_COST
---------- ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- --------------
4.5741E+18 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfxb47c9154 YES YES NO  YES         56

发现没有其他可用baseline,故手工load一个(如果存在其他enable的baseline的:a.accepted 直接删除当前baseline;b.unaccepted 手动evolve 再删除当前baseline):

 

johnny/JOHNNY@PERFDB_2>set serveroutput off
johnny/JOHNNY@PERFDB_2>var v number
select /*+ index(c c_ind)*/* from c where id<:v;johnny/JOHNNY@PERFDB_2>

no rows selected

Elapsed: 00:00:00.00
johnny/JOHNNY@PERFDB_2>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dunwbpbt9r6pf, child number 0
-------------------------------------
select /*+ index(c c_ind)*/* from c where id<:v

Plan hash value: 2379350657

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| C     |     1 |   598 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | C_IND |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"<:V)

Note
-----
- dynamic sampling used for this statement (level=2)

declare
sqlid varchar2(13);
ret number;
begin
ret:=dbms_spm.load_plans_from_cursor_cache(
sql_id=>'dunwbpbt9r6pf',
plan_hash_value=>'2379350657',
sql_handle=>'SYS_SQL_3f7a81ec031ce5dd'
);

ret:=dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SYS_SQL_3f7a81ec031ce5dd',
plan_name=>'SQL_PLAN_3yyn1xh1jttfxb47c9154'
);
end;
/

check:

 

sys/SYS@PERFDB_2>select SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME ,ENABLED,ACCEPTED,FIXED,AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select /*+ full(c)*/* from c where id<:v%';

SIGNATURE SQL_HANDLE                     SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX AUT OPTIMIZER_COST
---------- ------------------------------ -------------------------------------------------- ------------------------------ --- --- --- --- --------------
4.5741E+18 SYS_SQL_3f7a81ec031ce5dd       select /*+ full(c)*/* from c where id<:v           SQL_PLAN_3yyn1xh1jttfx70510f31 YES YES NO  YES          3

johnny/JOHNNY@PERFDB_2>explain plan for
2  select /*+ full(c)*/* from c where id<:v;

Explained.

Elapsed: 00:00:00.01
johnny/JOHNNY@PERFDB_2>@plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2379350657

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   821 |   479K|     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| C     |   821 |   479K|     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | C_IND |   148 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"<TO_NUMBER(:V))

Note
-----
- SQL plan baseline "SQL_PLAN_3yyn1xh1jttfx70510f31" used for this statement

已经OK.

另外观察了下,baselines只是执行计划等信息的管理,并不能避免同sql不同环境version_count增加的情况,因为child cursor不能共享的原因又很多,不单单是执行计划的影响。

REF:

SQL Plan Management in Oracle Database 11g(White Paper)
Using SQL Plan Management(Online Doc)
SQL Plan Management(Harald van Breederode)
troubleshooting oracle(Chapter 7)

Categories: TSOP Tags:
  1. No comments yet.
  1. No trackbacks yet.