一：创建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，让其常驻内存