Oracle历史SQL语句执行计划的对比与分析(二)
--------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 | 167K| 139 (0)| 00:00:02 |
------------------------------------------------------------------------------------
3、生成不同的历史SQL并对比执行计划
[sql]
--对表big_table进行move操作
scott@SYBO2SZ> alter table big_table move;
--检查其表上的索引，如下，索引已经失效
scott@SYBO2SZ> @idx_info
Enter value for owner: scott
Enter value for table_name: big_table
TABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
------------------------- ------------------- -------------------- ------ -------- --------------- ----
BIG_TABLE BIG_TABLE_PK ID 1 UNUSABLE NORMAL ASC
BIG_TABLE I_BIG_TB_OWNER OWNER 1 UNUSABLE NORMAL ASC
--再次执行与之前相同的SQL语句
scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';
COUNT(*)
----------
43560
scott@SYBO2SZ> @my_last_sql
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ----------------------------------------------------------
000000007B9BB7D0 243468085 4hqyjwh7861tp 3 0 select count(*) from big_table where owner='GOEX_ADMIN'
--创建一个新的快照，使之成为历史SQL
scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();
--查看SQL的执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'
Plan hash value: 334839806
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 | 167K| 139 (0)| 00:00:02 |
------------------------------------------------------------------------------------
SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'
Plan hash value: 599409829
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3221 (100)| |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 10073 | 167K| 3221 (1)| 00:00:39 |
--------------------------------------------------------------------------------
28 rows selected.
--从上面的