干预oracle执行计划实验

最新推荐文章于 2023-06-24 23:00:20 发布
最新推荐文章于 2023-06-24 23:00:20 发布
阅读量262 收藏
点赞数
分类专栏： DB-ORACLE 文章标签： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38352765/article/details/109897882
版权

干预oracle执行计划实验
OS: CentOS7.7 x86-64
DB: 11.2.0.4
FM: single + FS

  1. 环境准备
    删除snapshot:
    variable dbid number;
    variable min1 number;
    variable max1 number;
    begin
    select dbid into :dbid from v$database;
    select min(snap_id) into :min1 from dba_hist_snapshot;
    select max(snap_id) into :max1 from dba_hist_snapshot;
    dbms_workload_repository.drop_snapshot_range(low_snap_id=>:min1,high_snap_id=>:max1 + 1,dbid=>:dbid);
    end;
    /
    select min(snap_id),max(snap_id) from dba_hist_snapshot;

删除执行计划(sys用户):
删除shared_pool中的执行计划:
col sql_text for a64;
select address, hash_value,sql_text from v$sqlarea
where sql_id=‘60ffan48x220x’;
ADDRESS HASH_VALUE SQL_TEXT

00000000799E6448 298911773 select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
exec dbms_shared_pool.purge(‘00000000799E6448,298911773’,‘C’);
删除数据字典中的执行计划:
truncate table wrh s q l p l a n ; t r u n c a t e t a b l e w r h _sql_plan; truncate table wrh s​qlp​lan;truncatetablewrh_sqltext;
truncate table wrh$_sqlstat;
select distinct sql_id,plan_hash_value,
to_char(timestamp, ‘yyyymmdd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
order by timestamp;

测试数据:
drop table t4 purge;
create table t4(c1 number,c2 number,c3 varchar2(20));
begin
for i in 1 … 50000 loop
insert into t4 values(i,i,‘Manstein.Tang’);
if mod(i,10)=0 then
commit;
end if;
end loop;
end;
/

  1. 当statistics_level = typical时
    show parameter optimizer_mode;
    NAME TYPE VALUE

optimizer_mode string ALL_ROWS
alter system set statistics_level = typical;
show parameter statistics_level
NAME TYPE VALUE

statistics_level string TYPICAL

全表扫描时(如dba_hist_sql_plan视图无值则执行多次):
execute dbms_workload_repository.create_snapshot(‘ALL’);
var i number;
var j number;
var k varchar2(10);
exec :i := 1;
exec :j := 1;
exec :k := ‘A’;
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 2560505625

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 68 (100)| |
|* 1 | TABLE ACCESS FULL| T4 | 3 | 114 | 68 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter((“C3”=:K AND “C1”>=:I AND “C2”>=:J))
Note

  • dynamic sampling used for this statement (level=2)
    22 rows selected.

execute dbms_workload_repository.create_snapshot(‘ALL’);
select distinct sql_id,plan_hash_value,
to_char(timestamp, ‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
order by timestamp;
SQL_ID PLAN_HASH_VALUE TIMESTAMP

60ffan48x220x 2560505625 2020-11-21 12:07:51

col options for a15
col operation for a20
col object_name for a20
select sql_id,plan_hash_value,id,operation,
options,object_name,depth,cost,
to_char(timestamp, ‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
and plan_hash_value in (2560505625)
order by timestamp,id;
SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TIMESTAMP

60ffan48x220x 2560505625 0 SELECT STATEMENT 0 68 2020-11-21 12:07:51
60ffan48x220x 2560505625 1 TABLE ACCESS FULL T4 1 68 2020-11-21 12:07:51

c1列创建索引时:
create index idx_t4_c1 on t4(c1);
var i number;
var j number;
var k varchar2(10);
exec :i := 499990;
exec :j := 499990;
exec :k := ‘A’;
execute dbms_workload_repository.create_snapshot(‘ALL’);
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 2285563422

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| T4 | 3 | 114 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T4_C1 | 3 | | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter((“C3”=:K AND “C2”>=:J))
2 - access(“C1”>=:I)
Note

  • dynamic sampling used for this statement (level=2)
    24 rows selected.

execute dbms_workload_repository.create_snapshot(‘ALL’);
select distinct sql_id,plan_hash_value,
to_char(timestamp, ‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
order by timestamp;
SQL_ID PLAN_HASH_VALUE TIMESTAMP

60ffan48x220x 2560505625 2020-11-21 12:07:51
60ffan48x220x 2285563422 2020-11-21 12:10:44

col options for a15
col operation for a20
col object_name for a20
select sql_id,plan_hash_value,id,operation,
options,object_name,depth,cost,
to_char(timestamp, ‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
and plan_hash_value in (2560505625,2285563422)
order by timestamp,id;
SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TIMESTAMP

60ffan48x220x 2560505625 0 SELECT STATEMENT 0 68 2020-11-21 12:07:51
60ffan48x220x 2560505625 1 TABLE ACCESS FULL T4 1 68 2020-11-21 12:07:51
60ffan48x220x 2285563422 0 SELECT STATEMENT 0 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 1 TABLE ACCESS BY INDEX ROWID T4 1 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 2 INDEX RANGE SCAN IDX_T4_C1 2 2 2020-11-21 12:10:44

C1/C2列创建索引时:
create index idx_t4_c1c2 on t4(c1,c2);
var i number;
var j number;
var k varchar2(10);
exec :i := 1;
exec :j := 1;
exec :k := ‘A’;
execute dbms_workload_repository.create_snapshot(‘ALL’);
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 2560505625

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 68 (100)| |
|* 1 | TABLE ACCESS FULL| T4 | 3 | 114 | 68 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter((“C3”=:K AND “C1”>=:I AND “C2”>=:J))
Note

  • dynamic sampling used for this statement (level=2)
    22 rows selected.

C1/C2/C3列创建索引时:
create index idx_t4_c1c2c3 on t4(c1,c2,c3);
var i number;
var j number;
var k varchar2(10);
exec :i := 300000;
exec :j := 300000;
exec :k := ‘A’;
execute dbms_workload_repository.create_snapshot(‘ALL’);
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 222352819

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T4_C1C2C3 | 3 | 114 | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - access(“C1”>=:I AND “C2”>=:J AND “C3”=:K AND “C1” IS NOT NULL)
filter((“C3”=:K AND “C2”>=:J))
Note

  • dynamic sampling used for this statement (level=2)
    23 rows selected.

execute dbms_workload_repository.create_snapshot(‘ALL’);
select distinct sql_id,plan_hash_value,
to_char(timestamp,‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
order by timestamp;
SQL_ID PLAN_HASH_VALUE TIMESTAMP

60ffan48x220x 2560505625 2020-11-21 12:07:51
60ffan48x220x 2285563422 2020-11-21 12:10:44
60ffan48x220x 222352819 2020-11-21 12:13:24

col options for a15
col operation for a20
col object_name for a20
select sql_id,plan_hash_value,id,operation,
options,object_name,depth,cost,
to_char(timestamp, ‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
and plan_hash_value in (2560505625,2285563422,222352819)
order by timestamp,id;
SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TIMESTAMP

60ffan48x220x 2560505625 0 SELECT STATEMENT 0 68 2020-11-21 12:07:51
60ffan48x220x 2560505625 1 TABLE ACCESS FULL T4 1 68 2020-11-21 12:07:51
60ffan48x220x 2285563422 0 SELECT STATEMENT 0 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 1 TABLE ACCESS BY INDEX ROWID T4 1 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 2 INDEX RANGE SCAN IDX_T4_C1 2 2 2020-11-21 12:10:44
60ffan48x220x 222352819 0 SELECT STATEMENT 0 2 2020-11-21 12:13:24
60ffan48x220x 222352819 1 INDEX RANGE SCAN IDX_T4_C1C2C3 1 2 2020-11-21 12:13:24
7 rows selected.

统计信息改变时:
col owner for a15
col table_name for a20
col index_type for a10
select owner,table_name,index_type,clustering_factor from dba_indexes where owner=‘YVH’ and index_name=‘IDX_T4_C1C2C3’;
OWNER TABLE_NAME INDEX_TYPE CLUSTERING_FACTOR

YVH T4 NORMAL 198
exec dbms_stats.delete_table_stats(‘yvh’, ‘t4’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2c3’);
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => ‘YVH’,
indname => ‘IDX_T4_C1C2C3’,
numrows => 1000000000,
numlblks => 10000000,
numdist => 1000,
avglblk => 100,
avgdblk => 100,
indlevel => 100,
clstfct => 100,
cachehit => 100,
cachedblk => 100,
guessq => 100,
force => true,
no_invalidate => false
);
end;
/
select owner,table_name,index_type,clustering_factor from dba_indexes where owner=‘YVH’ and index_name=‘IDX_T4_C1C2C3’;

var i number;
var j number;
var k varchar2(10);
exec :i := 300000;
exec :j := 300000;
exec :k := ‘A’;
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 1840
Session ID: 27 Serial number: 9
出现ORA-03113错误,报错原因是"indlevel => 100"导致

conn yvh/yvh
exec dbms_stats.delete_table_stats(‘yvh’, ‘t4’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2c3’);
exec dbms_stats.gather_table_stats(‘yvh’, ‘t4’,cascade=>true);
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => ‘YVH’,
indname => ‘IDX_T4_C1C2C3’,
numrows => 10000,
numlblks => 100,
numdist => 10,
avglblk => 100,
avgdblk => 10,
indlevel => 10,
clstfct => 10,
cachehit => 10,
cachedblk => 10,
guessq => 10,
force => true,
no_invalidate => false
);
end;
/
var i number;
var j number;
var k varchar2(10);
exec :i := 300000;
exec :j := 300000;
exec :k := ‘A’;
execute dbms_workload_repository.create_snapshot(‘ALL’);
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 574965781

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T4_C1C2 | 1 | | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(“C3”=:K)
2 - access(“C1”>=:I AND “C2”>=:J AND “C1” IS NOT NULL)
filter(“C2”>=:J)
21 rows selected.

execute dbms_workload_repository.create_snapshot(‘ALL’);
select distinct sql_id,plan_hash_value,
to_char(timestamp,‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
order by timestamp;
SQL_ID PLAN_HASH_VALUE TIMESTAMP

60ffan48x220x 2560505625 2020-11-21 12:07:51
60ffan48x220x 2285563422 2020-11-21 12:10:44
60ffan48x220x 222352819 2020-11-21 12:13:24
60ffan48x220x 574965781 2020-11-21 12:18:11

col options for a15
col operation for a20
col object_name for a20
select sql_id,plan_hash_value,id,operation,
options,object_name,depth,cost,
to_char(timestamp, ‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
and plan_hash_value in (2560505625,2285563422,222352819,574965781)
order by timestamp,id;
SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TIMESTAMP

60ffan48x220x 2560505625 0 SELECT STATEMENT 0 68 2020-11-21 12:07:51
60ffan48x220x 2560505625 1 TABLE ACCESS FULL T4 1 68 2020-11-21 12:07:51
60ffan48x220x 2285563422 0 SELECT STATEMENT 0 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 1 TABLE ACCESS BY INDEX ROWID T4 1 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 2 INDEX RANGE SCAN IDX_T4_C1 2 2 2020-11-21 12:10:44
60ffan48x220x 222352819 0 SELECT STATEMENT 0 2 2020-11-21 12:13:24
60ffan48x220x 222352819 1 INDEX RANGE SCAN IDX_T4_C1C2C3 1 2 2020-11-21 12:13:24
60ffan48x220x 574965781 0 SELECT STATEMENT 0 3 2020-11-21 12:18:11
60ffan48x220x 574965781 1 TABLE ACCESS BY INDEX ROWID T4 1 3 2020-11-21 12:18:11
60ffan48x220x 574965781 2 INDEX RANGE SCAN IDX_T4_C1C2 2 2 2020-11-21 12:18:11
10 rows selected.

  1. 当statistics_level = all时:
    删除统计信息:
    exec dbms_stats.delete_table_stats(‘yvh’, ‘t4’);
    exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2c3’);
    exec dbms_stats.gather_table_stats(‘yvh’, ‘t4’,cascade=>true);
    alter system set statistics_level=all;
    var i number;
    var j number;
    var k varchar2(10);
    exec :i := 300000000;
    exec :j := 300000000
    exec :k := ‘B’;
    execute dbms_workload_repository.create_snapshot(‘ALL’);
    select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
    select * from table(dbms_xplan.display_cursor());
    PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 222352819

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T4_C1C2C3 | 1 | 24 | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - access(“C1”>=:I AND “C2”>=:J AND “C3”=:K AND “C1” IS NOT NULL)
filter((“C3”=:K AND “C2”>=:J))
19 rows selected.

exec dbms_stats.delete_table_stats(‘yvh’, ‘t4’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2c3’);
exec dbms_stats.gather_table_stats(‘yvh’, ‘t4’,cascade=>true);
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => ‘YVH’,
indname => ‘IDX_T4_C1C2C3’,
numrows => 10000,
numlblks => 100,
numdist => 10,
avglblk => 100,
avgdblk => 10,
indlevel => 10,
clstfct => 10,
cachehit => 10,
cachedblk => 10,
guessq => 10,
force => true,
no_invalidate => false
);
end;
/
var i number;
var j number;
var k varchar2(10);
exec :i := 300000000;
exec :j := 300000000
exec :k := ‘B’;
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 574965781

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T4_C1C2 | 1 | | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(“C3”=:K)
2 - access(“C1”>=:I AND “C2”>=:J AND “C1” IS NOT NULL)
filter(“C2”>=:J)
21 rows selected.

  1. 当optimizer_mode=first_rows时:
    exec dbms_stats.delete_table_stats(‘yvh’, ‘t4’);
    exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2c3’);
    exec dbms_stats.gather_table_stats(‘yvh’, ‘t4’,cascade=>true);
    alter system set statistics_level=all;
    alter session set optimizer_mode =first_rows;
    var i number;
    var j number;
    var k varchar2(10);
    exec :i := 300000000;
    exec :j := 300000000
    exec :k := ‘B’;
    execute dbms_workload_repository.create_snapshot(‘ALL’);
    select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
    select * from table(dbms_xplan.display_cursor());
    PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 0

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 222352819

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T4_C1C2C3 | 1 | 24 | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - access(“C1”>=:I AND “C2”>=:J AND “C3”=:K AND “C1” IS NOT NULL)
filter((“C3”=:K AND “C2”>=:J))
19 rows selected.

conn yvh/yvh
exec dbms_stats.delete_table_stats(‘yvh’, ‘t4’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2c3’);
exec dbms_stats.gather_table_stats(‘yvh’, ‘t4’,cascade=>true);
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => ‘YVH’,
indname => ‘IDX_T4_C1C2’,
numrows => 10000000,
numlblks => 10000000,
numdist => 1000,
avglblk => 100,
avgdblk => 100,
indlevel => 100,
clstfct => 100,
cachehit => 100,
cachedblk => 100,
guessq => 100,
force => true,
no_invalidate => false
);
end;
/
var i number;
var j number;
var k varchar2(10);
exec :i := 300000000;
exec :j := 300000000
exec :k := ‘B’;
execute dbms_workload_repository.create_snapshot(‘ALL’);
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2031
Session ID: 44 Serial number: 27
出现ORA-03113错误,报错原因是"indlevel => 100"导致

conn yvh/yvh
exec dbms_stats.delete_table_stats(‘yvh’, ‘t4’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2’);
exec dbms_stats.delete_index_stats(‘yvh’, ‘idx_t4_c1c2c3’);
exec dbms_stats.gather_table_stats(‘yvh’, ‘t4’,cascade=>true);
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => ‘YVH’,
indname => ‘IDX_T4_C1C2C3’,
numrows => 1,
numlblks => 1,
numdist => 1,
avglblk => 1,
avgdblk => 1,
indlevel => 1,
clstfct => 1,
cachehit => 1,
cachedblk => 1,
guessq => 1,
force => true,
no_invalidate => false
);
end;
/
var i number;
var j number;
var k varchar2(10);
exec :i := 300000000;
exec :j := 300000000
exec :k := ‘B’;
execute dbms_workload_repository.create_snapshot(‘ALL’);
select * from t4 where c1 >= :i and c2 >= :j and c3 = :k;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT

SQL_ID 60ffan48x220x, child number 1

select * from t4 where c1 >= :i and c2 >= :j and c3 = :k
Plan hash value: 4202443879

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | INDEX FAST FULL SCAN| IDX_T4_C1C2C3 | 1 | 24 | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter((“C1”>=:I AND “C2”>=:J AND “C3”=:K))
18 rows selected.
注意child number 1

execute dbms_workload_repository.create_snapshot(‘ALL’);
select distinct sql_id,plan_hash_value,
to_char(timestamp,‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
order by timestamp;
SQL_ID PLAN_HASH_VALUE TIMESTAMP

60ffan48x220x 2560505625 2020-11-21 12:07:51
60ffan48x220x 2285563422 2020-11-21 12:10:44
60ffan48x220x 222352819 2020-11-21 12:13:24
60ffan48x220x 574965781 2020-11-21 12:18:11
60ffan48x220x 4202443879 2020-11-21 12:24:52

col options for a15
col operation for a20
col object_name for a20
select sql_id,plan_hash_value,id,operation,
options,object_name,depth,cost,
to_char(timestamp, ‘yyyy-mm-dd hh24:mi:ss’) timestamp
from dba_hist_sql_plan
where sql_id = ‘60ffan48x220x’
and plan_hash_value in (2560505625,2285563422,222352819,574965781,4202443879)
order by timestamp,id;
SQL_ID PLAN_HASH_VALUE ID OPERATION OPTIONS OBJECT_NAME DEPTH COST TIMESTAMP

60ffan48x220x 2560505625 0 SELECT STATEMENT 0 68 2020-11-21 12:07:51
60ffan48x220x 2560505625 1 TABLE ACCESS FULL T4 1 68 2020-11-21 12:07:51
60ffan48x220x 2285563422 0 SELECT STATEMENT 0 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 1 TABLE ACCESS BY INDEX ROWID T4 1 3 2020-11-21 12:10:44
60ffan48x220x 2285563422 2 INDEX RANGE SCAN IDX_T4_C1 2 2 2020-11-21 12:10:44
60ffan48x220x 222352819 0 SELECT STATEMENT 0 2 2020-11-21 12:13:24
60ffan48x220x 222352819 1 INDEX RANGE SCAN IDX_T4_C1C2C3 1 2 2020-11-21 12:13:24
60ffan48x220x 574965781 0 SELECT STATEMENT 0 3 2020-11-21 12:18:11
60ffan48x220x 574965781 1 TABLE ACCESS BY INDEX ROWID T4 1 3 2020-11-21 12:18:11
60ffan48x220x 574965781 2 INDEX RANGE SCAN IDX_T4_C1C2 2 2 2020-11-21 12:18:11
60ffan48x220x 4202443879 0 SELECT STATEMENT 0 2 2020-11-21 12:24:52
60ffan48x220x 4202443879 1 INDEX FAST FULL SCAN IDX_T4_C1C2C3 1 2 2020-11-21 12:24:52
12 rows selected.

weixin_38352765
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
ORACLE 手动创建或删除snapshot
gaobudong1234的博客
06-27 7880
--如果需要的话DBA可以通过DBMS_WORKLOAD_REPOSITORY过程手动创建、删除或修改snapshots. 调用DBMS_WORKLOAD_REPOSITORY包需要拥有DBA权限。 --1.手动创建Snapshots 手动创建Snapshots通过DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT过程 --例如 exec dbms_w
DBMS_WORKLOAD_REPOSITORY包应用三:CREATE_SNAPSHOT
xinxiangsui2008的专栏
04-15 4999
CREATE_SNAPSHOT函数和过程 这个函数和过程创建快照。在该功能的情况下，返回的抽点打印ID。 句法 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT（ flush_level IN VARCHAR2 DEFAULT'典型'）; DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT（ flush_le
statistics_level 参数的应用（转）
congza2755的博客
07-06 131
statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如...
优化Oracle数据库性能：合理设置数据库参数
最新发布
小蓝枣的博客
06-24 4200
Oracle 数据库是一种功能强大的关系型数据库管理系统，但在处理大量数据和复杂查询时，性能问题可能会成为一个挑战。为了提高数据库的响应速度和效率，我们可以采取一系列的优化措施。 合理设置数据库参数是提高 Oracle 数据库性能的重要策略之一。通过调整内存管理参数、并发处理参数和日志恢复参数，可以最大程度地利用数据库资源，提高查询性能、并发性能和可靠性。在设置数据库参数时，需要考虑数据库的大小、负载情况和业务需求。因此，建议在优化数据库性能之前，进行充分的测试和评估，以确定最佳的参数设置策略。
ORACLE初始化参数：STATISTICS_LEVEL的介绍
weixin_34413065的博客
06-11 1090
初始化参数STATISTICS_LEVEL用于指定数据库和OS统计参数搜集级别，其取值为TYPICAL、BASIC和ALL，默认值为TYPICAL。当设置该参数为TYPICAL时，Oracle会搜集数据库自管理特征所需的主要统计；当设置该参数为BASIC时，oracle关闭了所有性能数据的收集,也就是如果要关闭AWR或statspack收集,只要设置a...
oracle三种状态,设置ORACLE的statistics_level的三种状态和解释
weixin_39916360的博客
04-03 472
statistics_level 参数是oracle9.2开始引入的一个控制系统统计参数收集的一个开关.一共有三个值:basic,typical,all.支持alter session,alter system 动态修改.如果要用statspack或者AWR收集系统性能统计数据.那么这个参数的值必须为typical或all.通常all是一个全面收集,包括 OS以及sql执行路径方面的一些统计信息,...
通过分析SQL语句的执行计划优化SQL
01-10
本文档主要介绍了与SQL调整有关的内容，涉及多个方面：SQL语句执行的过程、ORACLE优化器、表之间的关联、如何得到SQL执行计划、如何分析执行计划等内容。通过从浅入深的方式了解SQL优化的过程，使大家逐步步入SQL...
SQL语句性能调整 ORACLE的执行计划.
05-14
Oracle 提供了多种方法来干预执行计划，例如使用 hints 提示和使用全套的 hints。这些方法可以帮助开发者干预执行计划，以便于提高 SQL 语句的执行效率和性能。 使用全套的 hints 使用全套的 hints 是 Oracle 中的...
DBMS_WORKLOAD_REPOSITORY包
03-11 490
强制MMON进程立即收集快照： execute DBMS_WORKLOAD_REPOSITORY.create_snapshot; 调整快照管理： execute DBMS_WORKLOAD_REPOSITORY.m...
Oracle执行计划 讲解（二）
guanyi的博客
09-17 446
现在我们讲讲oracle执行计划里面每个参数的含义 我们以下面的一个例子来讲解 这里做个补充：trace的类型一共有以下几种 序号 命令 解释 1 SET AUTOTRACE OFF 此为默认值，即关闭Autotrace 2 SET AUTOTRACE ON EXPLAIN 只显示执行计划 3 SET AUTOTRACE ON STAT
Oracle执行计划filter下多个节点的优化
coco3600的博客
07-03 637
Oracle执行计划filter下多个节点的优化 FILTER操作是执行计划中常见的操作，这种操作有两种情况： l只有一个子节点，那么就是简单过滤操作。 ...
OracleAWR删除历史快照说明
茄肥猫的窝
12-24 4942
从Oracle 10g 开始，Oracle 推出一个工具：AWR（Automatic Workload Repository），并建议用AWR 代替9i 的Statspack. 　　AWR 实质上是一个 Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。 　　AWR的快照由一个MMON的后台进程及其从进程自动地每小时采集一次。为了节
oracle清空表空间对象,oracle11.2.0.4辅助表空间的对象清理：
weixin_33001177的博客
04-10 260
oracle11.2.0.4辅助表空间的对象清理：查询对象占用空间的多少SELECToccupant_name "Item",space_usage_kbytes / 1048576 "Space Used (GB)",schema_name "Schema",move_procedure "Move Procedure"FROM v$sysaux_occupantsORDER BY1;最后发现A...
Oracle AWR快照管理与常见问题
老班长的博客
08-15 548
1、手动创建Snapshots exec dbms_workload_repository.create_snapshot(); –或者 BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END; 2、手动删除Snapshots exec dbms_workload_repository.drop_snapshot_range(...
oracle predicate information,Oracle执行计划显示 (网上的参考文章）
weixin_39645306的博客
04-03 999
Oracle执行计划显示 (网上的参考文章)上一篇 /下一篇 2013-05-01 17:25:30/ 个人分类：oracle生成和显示SQL语句的执行计划，是大多数DBA、SQL开发人员以及性能优化专家经常做的工作，因为执行计划能够提供SQL语句性能相关的信息。执行计划解释了SQL语句执行的详细过程，这个过程记录了一系列的数据库操作以及每个操作涉及到的数据行数和生成的数据行数。优化器使用查询转...
索引唯一扫描
海鲨MYSQL精英架构师
03-07 2056
一 创建表和数据 create table zfk select * from dba_objects; create table ZFK ( object_name VARCHAR2(128), object_id NUMBER not null, data_object_id NUMBER, ) pctfree 10二创建唯一索引 alter tabl
Oracle SQL执行计划与Hints干预实验分析
本实验是关于Oracle数据库中的SQL语句执行计划分析，通过创建测试环境并执行特定查询，展示如何查看和理解执行计划，并利用hints进行语句级干预。 在Oracle数据库中，执行计划是数据库管理系统为了执行SQL查询而...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值