在设计、开发阶段我们会加索引,不过难免保证在运维阶段索引加全了。此时就需要把执行计划中带有全表扫描的SQL都找出来。 sqlplus TEST/TEST@10.10.15.25 --数据库的用户名和密码,每个实例上都要运行 set serveroutput on size 100000 spool d:/result.txt set pagesize 200 set linesize 800 declare cursor c_cursor is select hash_value,CHILD_NUMBER,SQL_FULLTEXT from( select /*+use_hash(s,v)*/ s.hash_value,s.CHILD_NUMBER,s.SQL_FULLTEXT, row_number() over(partition by s.HASH_VALUE order by null) rn from v$sql s,v$sql_plan v where s.SQL_ID =v.SQL_ID and v.operation = 'TABLE ACCESS' and v.OPTIONS = 'FULL' and s.CHILD_NUMBER =0 and v.OBJECT_OWNER in ('SPROC1','SPROC2','SPROC3')) where rn=1; c_row c_cursor%rowtype; TYPE t_arry_plan IS VARRAY(1000) OF VARCHAR2(200); array_plan t_arry_plan; begin DBMS_OUTPUT.ENABLE(buffer_size=>null); for c_row in c_cursor loop begin DBMS_OUTPUT.PUT_LINE(c_row.SQL_FULLTEXT); select plan_table_output bulk collect into array_plan from table(dbms_xplan.display_cursor(c_row.hash_value,c_row.child_number,'advanced')); FOR i IN array_plan.FIRST .. array_plan.LAST LOOP DBMS_OUTPUT.PUT_LINE(array_plan(i)); END LOOP; DBMS_OUTPUT.PUT_LINE('**************************************************************************'); DBMS_OUTPUT.PUT_LINE(chr(10)||chr(10)); exception When others then DBMS_OUTPUT.PUT_LINE('有报错'); end; end loop; end; /
spool off;
出来的结果如下,可以看到是没有加索引的:
HASH_VALUE 844842,child number 0 ---------------------------------- select assetaudit0_.ID as ID1064_0_, assetaudit0_.APPROVER_UID as APPROVER7_1064_0_, assetaudit0_.APPROVER_UNAME as APPROVER8_1064_0_, assetaudit0_.AUDIT_COMMENT as AUDIT9_1064_0_, assetaudit0_.AUDIT_EXPLAIN as AUDIT10_1064_0_, assetaudit0_.AUDIT_STATUS as AUDIT11_1064_0_, assetaudit0_.AUDIT_TIME as AUDIT12_1064_0_, assetaudit0_.BUSINESS_BILL_ID as BUSINESS13_1064_0_, assetaudit0_.FULL_PATH as FULL14_1064_0_, assetaudit0_.FUNCTION_LOCATION_ID as FUNCTION15_1064_0_, assetaudit0_.HISTORY_VERSION as HISTORY16_1064_0_, assetaudit0_.OBJECT_ID as OBJECT17_1064_0_, assetaudit0_.OBJECT_NAME as OBJECT18_1064_0_, assetaudit0_.OBJECT_TYPE as OBJECT19_1064_0_, assetaudit0_.TRANSFER_STATE as TRANSFER20_1064_0_, assetaudit0_.WORKSPACE_ID as WORKSPACE21_1064_0_ from GG_ASSET_AUDIT assetaudit0_ where assetaudit0_.ID = :1
Plan hash value: 2126140279 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2466 (100)| | |* 1 | TABLE ACCESS FULL| GG_ASSET_AUDIT | 1 | 218 | 2466 (1)| 00:00:30 | ------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / ASSETAUDIT0_@SEL$1 Outline Data -------------
.....................................................................................
----------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :1 (VARCHAR2(30),CSID=852): '052cc327153f4beba86042bfbcf3b623' Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ASSETAUDIT0_"."ID"=:1) Column Projection Information (identified by operation id): ----------------------------------------------------------- (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|