Oracle数据库全表扫描工具,【Oracle】取得数据库全表扫描的语句(get_fullscan_table)...

一：创建plan_table，数据库在默认情况下是没有创建此表的。

SQL> @?/rdbms/admin/utlxplan

Table created.

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant all on plan_table to public ;

Grant succeeded.

SQL> @?/sqlplus/admin/plustrce

二：创建收集全表扫描语句

create table full_sql (sql_text varchar2(1000), executions number);

create or replace procedure p_findfullsql as

v_csr number;

v_rc number;

v_string varchar2(2000);

v_count number;

cursor c1 is select sql_text,executions from v$sqlarea where lower(sql_text) like '%select%';

begin

for x1 in c1 loop

delete from plan_table ;

Begin

v_Csr := DBMS_SQL.OPEN_CURSOR;

v_string := 'explain plan for ' ;

v_string := v_string||x1.sql_text ;

DBMS_SQL.PARSE(v_csr, v_string, DBMS_SQL.V7);

v_rc := DBMS_SQL.EXECUTE(v_csr);

DBMS_SQL.CLOSE_CURSOR(v_csr);

Exception

when others then

null;

End ;

select count(*) into v_count from plan_table where options like '%FULL%' and operation like '%TABLE%' ;

if v_count > 0 then

insert into full_sql(sql_text,executions) values (x1.sql_text, x1.executions) ;

end if;

end loop ;

commit;

end ;

/

execute p_findfullsql ;

select * from full_sql;

drop table full_sql;

通过select * from full_sql;可以知道执行全表扫描的语句，加以着重研究，比如可以讲小表放入keep_buffer，让其常驻内存