优化由直方图信息导致的sql性能问题
下午在生产库上捕获一条sql，查询了系统的几个主要大表,cost有34776，并且在某列上使用了绑定变量，需要对其进行优化
sql大致内容如下
select si.id1,si.id2,si.id13
from (select jusint_it.id1,jusint_it.id2,jusint_it.id3
from justin, jusint_it
where justin.id = jusint_it.order_id
and justin.jusint_id in (:1)
union all
select jusitin_it_hist.id1,jusitin_it_hist.id2,jusitin_it_hist.id3
from justin_hist, jusitin_it_hist
where justin_hist.order_id = jusitin_it_hist.order_id
and justin_hist.jusint_id in (:2)) si
执行计划
| SELECT STATEMENT | | | | 34776 | | |
| VIEW | | 163K| 90M| 34776 | | |
| UNION-ALL | | | | | | |
| HASH JOIN | | 163K| 10M| 34723 | | |
| TABLE ACCESS BY INDEX ROWID |justin_it | 6 | 366 | 3 | | |
| NESTED LOOPS | | 125 | 8K| 54 | | |
| TABLE ACCESS BY INDEX ROWID |justin_hist | 23K| 209K| 2053 | | |
| TABLE ACCESS FULL |justin_it_hist | 5M| 306M| 32607 | | |
| INDEX RANGE SCAN |idx_justin_order | 6 | | 2 | | |
| INDEX RANGE SCAN |idx_hist_justin_id | 23K| | 102 | | |
| TABLE ACCESS BY INDEX ROWID |justin | 21 | 189 | 7 | | |
| INDEX RANGE SCAN |idx_so_justin_id | 21 | | 3 | | |
------------------------------------------------------------------------------------------------------------
经查看，绑定变量列有直方图，信息如下
SQL> select table_name,num_distinct,density,num_nulls,num_buckets,last_analyzed from user_tab_col_statistics t where t.table_name in ('justin','justin_hist') and t.column_name='justin_id';
TABLE_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ ---------- ---------- ----------- -------------
justin 9570 7.90613223 27923 254 2011-1-19 上午
justin_hist 114 1.83563894 86491 114 2011-1-14 上午
注意density列，当没有直方图的时候，density=1/num_distinct;其值越接近0，表示该列的选择性越高，而这里两个值都超过了1
查看绑定变量justin_id在表justin和justin_hist中的分布状况
SQL> select count(*),count(distinct justin_id) from justin_hist;
COUNT(*) COUNT(DISTINCTjustin_id)
---------- ---------------------------
2887588 32146
SQL> select justin_id,count(*) from justin_hist group by justin_id having count(*) >1000;
justin_id COUNT(*)
------------------- ----------
99300
0 2729587
SQL> select count(*),count(distinct justin_id) from justin;
COUNT(*) COUNT(DISTINCT justin_id)
---------- ---------------------------
239715 9733
SQL> select justin_id,count(*) from justin group by justin_id having count(*) >1000;
justin_id COUNT(*)
------------------- ----------
27934
0 198284
可以看到justin_id的分布情况及其不均匀，其中justin_id=0占了全部值的80%还要多
SQL> select 2729587/2887588,198284/239715 from dual;
2729587/2887588 198284/239715
--------------- -------------
0.9452827065356 0.82716559247
将绑定变量替换成常量，重新观察执行计划
1. Null
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 754K| 417M| 93401 (2)| 00:18:41 |
| 1 | VIEW | | 754K| 417M| 93401 (2)| 00:18:41 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN | | 162K| 10M| 9149 (1)| 00:01:50 |
|* 4 | TABLE ACCESS FULL| justin | 27923 | 245K| 6767 (1)| 00:01:22 |
| 5 | TABLE ACCESS FULL| justin_it | 295K| 17M| 2378 (2)| 00:00:29 |
|* 6 | HASH JOIN | | 592K| 38M| 84251 (2)| 00:16:52 |
|* 7 | TABLE ACCESS FULL| justin_hist | 86491 | 760K| 51580 (1)| 00:10:19 |
| 8 | TABLE ACCESS FULL| justin_it_hist | 5354K| 306M| 32607 (2)| 00:06:32 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("justin"."ID"="justin_it"."ORDER_ID")
4 - filter("justin"."justin_id" IS NULL)
6 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
7 - filter("justin_hist"."justin_id" IS NULL)
23 rows selected.
2. 0
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5649K| 3124M| | 115K (2)| 00:23:08 |
| 1 | VIEW | | 5649K| 3124M| | 115K (2)| 00:23:08 |
| 2 | UNION-ALL | | | | | | |
|* 3 | HASH JOIN | | 295K| 19M| 3936K| 10368 (1)| 00:02:05 |
|* 4 | TABLE ACCESS FULL| justin | 191K| 1686K| | 6768 (1)| 00:01:22 |
| 5 | TABLE ACCESS FULL| justin_it | 295K| 17M| | 2378 (2)| 00:00:29 |
|* 6 | HASH JOIN | | 5354K| 352M| 53M| 105K (2)| 00:21:04 |
|* 7 | TABLE ACCESS FULL| justin_hist | 2671K| 22M| | 51590 (1)| 00:10:20 |
| 8 | TABLE ACCESS FULL| justin_it_hist | 5354K| 306M| | 32607 (2)| 00:06:32 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("justin"."ID"="justin_it"."ORDER_ID")
4 - filter("justin"."justin_id"=0)
6 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
7 - filter("justin_hist"."justin_id"=0)
23 rows selected.
3. 除了null和0外的任意一个值
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1408 | 797K| 492 (0)| 00:00:06 |
| 1 | VIEW | | 1408 | 797K| 492 (0)| 00:00:06 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | justin_it | 6 | 366 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 8 | 560 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| justin | 1 | 9 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | idx_so_justin_id | 1 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | idx_justin_order | 6 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | justin_it_hist | 7 | 420 | 3 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1400 | 96600 | 485 (0)| 00:00:06 |
| 10 | TABLE ACCESS BY INDEX ROWID| justin_hist | 204 | 1836 | 20 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | idx_hist_justin_id | 204 | | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | idx_hist_justin_order_id | 7 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 - access("justin"."justin_id"=864477)
7 - access("justin"."ID"="justin_it"."ORDER_ID")
11 - access("justin_hist"."justin_id"=864477)
12 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
27 rows selected.
解决方法：
去掉该列上的直方图信息，使其采用稳定的执行计划
因为这两个表比较大，在主库收集统计信息会造成一定的压力，所以考虑在逻辑备库收集，然后将统计信息导入主库.
逻辑备库上上重新收集这两个表，去掉所有列的直方图,在11g，可以通过调用dbms_stats.delete_column_stats直接删除，同时收集完成后lock其统计信息，以避免再出现直方图统计
收集统计信息
begin
dbms_stats.gather_table_stats(ownname => 'JUSTIN',tabname => 'justin_hist',method_opt => 'for all columns size 1',estimate_percent => 10);
end;
begin
dbms_stats.gather_table_stats(ownname => 'JUSTIN',tabname => 'justin',method_opt => 'for all columns size 1',estimate_percent => 10);
end;
将统计信息导出至数据库表
begin
dbms_stats.create_stat_table(ownname => 'JUSTIN',stattab => 'temp_justin_stats');
end;
begin
dbms_stats.export_table_stats(ownname => 'JUSTIN',tabname => 'JUSTIN',stattab => 'temp_justin_stats');
end;
begin
dbms_stats.create_stat_table(ownname => 'JUSTIN',stattab => 'temp_justin_hist_stats');
end;
begin
dbms_stats.export_table_stats(ownname => 'JUSTIN',tabname => 'JUSTIN_HIST',stattab => 'temp_justin_hist_stats');
end;
采用exp导出并传输至主库
[oracle@std ~]$ exp JUSTIN/xxxxx tables=temp_justin_stats file=temp_justin_stats.dmp
[oracle@std ~]$ scp temp_justin_stats.dmp 192.168.132.21:/home/oracle
[oracle@racdg1 ~]$ imp JUSTIN/xxxxx fromuser=JUSTIN touser=JUSTIN file=temp_justin_stats.dmp
在主库上将统计信息导入
begin
dbms_stats.import_table_stats(ownname => 'JUSTIN',tabname => 'justin',stattab => 'temp_justin_stats');
end;
在主库上查看其直方图信息，bucket为1,已经删掉了直方图信息
SQL> select table_name,num_distinct,density,num_nulls,num_buckets,last_analyzed from user_tab_col_statistics t where t.table_name in ('justin','justin_hist') and t.column_name='justin_id';
TABLE_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ ---------- ---------- ----------- -------------
justin 1321 0.00075700 27650 1 2011-1-19 下午
justin_hist 5441 0.00018378 100300 1 2011-1-19 下午
然后锁住统计信息
SQL> begin
2 dbms_stats.lock_table_stats(ownname => 'JUSTIN',tabname => 'justin');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_stats.lock_table_stats(ownname => 'JUSTIN',tabname => 'justin_hist');
3 end;
4 /
PL/SQL procedure successfully completed
检验
SQL> select table_name,u.STATTYPE_LOCKED from user_tab_statistics u where u.STATTYPE_LOCKED is not null;
TABLE_NAME STATTYPE_LOCKED
------------------------------ ---------------
justin ALL
justin_hist ALL
此时查看该sql执行计划，
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4445 | 2517K| 1596 (1)| 00:00:20 |
| 1 | VIEW | | 4445 | 2517K| 1596 (1)| 00:00:20 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | JUSTIN_IT | 6 | 366 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 945 | 66150 | 387 (0)| 00:00:05 |
| 5 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 162 | 1458 | 29 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_JUSTIN_HIST_ID | 162 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_JUSTIN_ORDER | 6 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | JUSTIN_IT_HIST | 7 | 420 | 3 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 3500 | 235K| 1209 (1)| 00:00:15 |
| 10 | TABLE ACCESS BY INDEX ROWID| JUSTIN_HIST | 511 | 4599 | 47 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_JUSTIN_HIST_ID | 511 | | 5 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_JUSTIN_ORDER | 7 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
sql大致内容如下
select si.id1,si.id2,si.id13
from (select jusint_it.id1,jusint_it.id2,jusint_it.id3
from justin, jusint_it
where justin.id = jusint_it.order_id
and justin.jusint_id in (:1)
union all
select jusitin_it_hist.id1,jusitin_it_hist.id2,jusitin_it_hist.id3
from justin_hist, jusitin_it_hist
where justin_hist.order_id = jusitin_it_hist.order_id
and justin_hist.jusint_id in (:2)) si
执行计划
| SELECT STATEMENT | | | | 34776 | | |
| VIEW | | 163K| 90M| 34776 | | |
| UNION-ALL | | | | | | |
| HASH JOIN | | 163K| 10M| 34723 | | |
| TABLE ACCESS BY INDEX ROWID |justin_it | 6 | 366 | 3 | | |
| NESTED LOOPS | | 125 | 8K| 54 | | |
| TABLE ACCESS BY INDEX ROWID |justin_hist | 23K| 209K| 2053 | | |
| TABLE ACCESS FULL |justin_it_hist | 5M| 306M| 32607 | | |
| INDEX RANGE SCAN |idx_justin_order | 6 | | 2 | | |
| INDEX RANGE SCAN |idx_hist_justin_id | 23K| | 102 | | |
| TABLE ACCESS BY INDEX ROWID |justin | 21 | 189 | 7 | | |
| INDEX RANGE SCAN |idx_so_justin_id | 21 | | 3 | | |
------------------------------------------------------------------------------------------------------------
经查看，绑定变量列有直方图，信息如下
SQL> select table_name,num_distinct,density,num_nulls,num_buckets,last_analyzed from user_tab_col_statistics t where t.table_name in ('justin','justin_hist') and t.column_name='justin_id';
TABLE_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ ---------- ---------- ----------- -------------
justin 9570 7.90613223 27923 254 2011-1-19 上午
justin_hist 114 1.83563894 86491 114 2011-1-14 上午
注意density列，当没有直方图的时候，density=1/num_distinct;其值越接近0，表示该列的选择性越高，而这里两个值都超过了1
查看绑定变量justin_id在表justin和justin_hist中的分布状况
SQL> select count(*),count(distinct justin_id) from justin_hist;
COUNT(*) COUNT(DISTINCTjustin_id)
---------- ---------------------------
2887588 32146
SQL> select justin_id,count(*) from justin_hist group by justin_id having count(*) >1000;
justin_id COUNT(*)
------------------- ----------
99300
0 2729587
SQL> select count(*),count(distinct justin_id) from justin;
COUNT(*) COUNT(DISTINCT justin_id)
---------- ---------------------------
239715 9733
SQL> select justin_id,count(*) from justin group by justin_id having count(*) >1000;
justin_id COUNT(*)
------------------- ----------
27934
0 198284
可以看到justin_id的分布情况及其不均匀，其中justin_id=0占了全部值的80%还要多
SQL> select 2729587/2887588,198284/239715 from dual;
2729587/2887588 198284/239715
--------------- -------------
0.9452827065356 0.82716559247
将绑定变量替换成常量，重新观察执行计划
1. Null
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 754K| 417M| 93401 (2)| 00:18:41 |
| 1 | VIEW | | 754K| 417M| 93401 (2)| 00:18:41 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN | | 162K| 10M| 9149 (1)| 00:01:50 |
|* 4 | TABLE ACCESS FULL| justin | 27923 | 245K| 6767 (1)| 00:01:22 |
| 5 | TABLE ACCESS FULL| justin_it | 295K| 17M| 2378 (2)| 00:00:29 |
|* 6 | HASH JOIN | | 592K| 38M| 84251 (2)| 00:16:52 |
|* 7 | TABLE ACCESS FULL| justin_hist | 86491 | 760K| 51580 (1)| 00:10:19 |
| 8 | TABLE ACCESS FULL| justin_it_hist | 5354K| 306M| 32607 (2)| 00:06:32 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("justin"."ID"="justin_it"."ORDER_ID")
4 - filter("justin"."justin_id" IS NULL)
6 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
7 - filter("justin_hist"."justin_id" IS NULL)
23 rows selected.
2. 0
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5649K| 3124M| | 115K (2)| 00:23:08 |
| 1 | VIEW | | 5649K| 3124M| | 115K (2)| 00:23:08 |
| 2 | UNION-ALL | | | | | | |
|* 3 | HASH JOIN | | 295K| 19M| 3936K| 10368 (1)| 00:02:05 |
|* 4 | TABLE ACCESS FULL| justin | 191K| 1686K| | 6768 (1)| 00:01:22 |
| 5 | TABLE ACCESS FULL| justin_it | 295K| 17M| | 2378 (2)| 00:00:29 |
|* 6 | HASH JOIN | | 5354K| 352M| 53M| 105K (2)| 00:21:04 |
|* 7 | TABLE ACCESS FULL| justin_hist | 2671K| 22M| | 51590 (1)| 00:10:20 |
| 8 | TABLE ACCESS FULL| justin_it_hist | 5354K| 306M| | 32607 (2)| 00:06:32 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("justin"."ID"="justin_it"."ORDER_ID")
4 - filter("justin"."justin_id"=0)
6 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
7 - filter("justin_hist"."justin_id"=0)
23 rows selected.
3. 除了null和0外的任意一个值
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1408 | 797K| 492 (0)| 00:00:06 |
| 1 | VIEW | | 1408 | 797K| 492 (0)| 00:00:06 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | justin_it | 6 | 366 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 8 | 560 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| justin | 1 | 9 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | idx_so_justin_id | 1 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | idx_justin_order | 6 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | justin_it_hist | 7 | 420 | 3 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1400 | 96600 | 485 (0)| 00:00:06 |
| 10 | TABLE ACCESS BY INDEX ROWID| justin_hist | 204 | 1836 | 20 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | idx_hist_justin_id | 204 | | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | idx_hist_justin_order_id | 7 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 - access("justin"."justin_id"=864477)
7 - access("justin"."ID"="justin_it"."ORDER_ID")
11 - access("justin_hist"."justin_id"=864477)
12 - access("justin_hist"."ORDER_ID"="justin_it_hist"."ORDER_ID")
27 rows selected.
解决方法：
去掉该列上的直方图信息，使其采用稳定的执行计划
因为这两个表比较大，在主库收集统计信息会造成一定的压力，所以考虑在逻辑备库收集，然后将统计信息导入主库.
逻辑备库上上重新收集这两个表，去掉所有列的直方图,在11g，可以通过调用dbms_stats.delete_column_stats直接删除，同时收集完成后lock其统计信息，以避免再出现直方图统计
收集统计信息
begin
dbms_stats.gather_table_stats(ownname => 'JUSTIN',tabname => 'justin_hist',method_opt => 'for all columns size 1',estimate_percent => 10);
end;
begin
dbms_stats.gather_table_stats(ownname => 'JUSTIN',tabname => 'justin',method_opt => 'for all columns size 1',estimate_percent => 10);
end;
将统计信息导出至数据库表
begin
dbms_stats.create_stat_table(ownname => 'JUSTIN',stattab => 'temp_justin_stats');
end;
begin
dbms_stats.export_table_stats(ownname => 'JUSTIN',tabname => 'JUSTIN',stattab => 'temp_justin_stats');
end;
begin
dbms_stats.create_stat_table(ownname => 'JUSTIN',stattab => 'temp_justin_hist_stats');
end;
begin
dbms_stats.export_table_stats(ownname => 'JUSTIN',tabname => 'JUSTIN_HIST',stattab => 'temp_justin_hist_stats');
end;
采用exp导出并传输至主库
[oracle@std ~]$ exp JUSTIN/xxxxx tables=temp_justin_stats file=temp_justin_stats.dmp
[oracle@std ~]$ scp temp_justin_stats.dmp 192.168.132.21:/home/oracle
[oracle@racdg1 ~]$ imp JUSTIN/xxxxx fromuser=JUSTIN touser=JUSTIN file=temp_justin_stats.dmp
在主库上将统计信息导入
begin
dbms_stats.import_table_stats(ownname => 'JUSTIN',tabname => 'justin',stattab => 'temp_justin_stats');
end;
在主库上查看其直方图信息，bucket为1,已经删掉了直方图信息
SQL> select table_name,num_distinct,density,num_nulls,num_buckets,last_analyzed from user_tab_col_statistics t where t.table_name in ('justin','justin_hist') and t.column_name='justin_id';
TABLE_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ ---------- ---------- ----------- -------------
justin 1321 0.00075700 27650 1 2011-1-19 下午
justin_hist 5441 0.00018378 100300 1 2011-1-19 下午
然后锁住统计信息
SQL> begin
2 dbms_stats.lock_table_stats(ownname => 'JUSTIN',tabname => 'justin');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_stats.lock_table_stats(ownname => 'JUSTIN',tabname => 'justin_hist');
3 end;
4 /
PL/SQL procedure successfully completed
检验
SQL> select table_name,u.STATTYPE_LOCKED from user_tab_statistics u where u.STATTYPE_LOCKED is not null;
TABLE_NAME STATTYPE_LOCKED
------------------------------ ---------------
justin ALL
justin_hist ALL
此时查看该sql执行计划，
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4445 | 2517K| 1596 (1)| 00:00:20 |
| 1 | VIEW | | 4445 | 2517K| 1596 (1)| 00:00:20 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | JUSTIN_IT | 6 | 366 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 945 | 66150 | 387 (0)| 00:00:05 |
| 5 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 162 | 1458 | 29 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_JUSTIN_HIST_ID | 162 | | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_JUSTIN_ORDER | 6 | | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | JUSTIN_IT_HIST | 7 | 420 | 3 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 3500 | 235K| 1209 (1)| 00:00:15 |
| 10 | TABLE ACCESS BY INDEX ROWID| JUSTIN_HIST | 511 | 4599 | 47 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_JUSTIN_HIST_ID | 511 | | 5 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_JUSTIN_ORDER | 7 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-688363/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
