В очередной раз у клиента наткнулся на какое-то адовое количество говнокода, разгребать который руками не было никаких сил и желания.
Добрался автоматизировать это дело. Ниже скрипт собирает все запросы, которые приводили к фулскану табличек за сутки, если в этих табличках было больше миллиона строк.
Значение можете поправить по своей базе по желанию. Но, не забывайте, что совсем не все фулсканы неправильные. На мелких табличках они иногда оправданы.
Если очень доверяете тюнеру (не надо это делать просто так), то можете перед или после репорта добавить строку
SQL код:
dbms_sqltune.accept_sql_profile(task_name => stmt_task,task_owner => 'SYS', replace => TRUE);
чтобы применялось любое, что предложит тюнер профилем. Настоятельно рекомендую это вслепую не делать, разгребать потом непросто будет.
Код:
#!/bin/bash
sqlplus / as sysdba<<EOF
SET ECHO OFF
SET LINESIZE 1200
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET PAUSE OFF
SET TRIMSPOOL ON
spool lst
select distinct s.sql_id from DBA_HIST_SQL_PLAN H, V\$SQL S where h.timestamp>sysdate-1 and options like '%FULL%' and object_owner not in ('SYS') and operation!='INDEX' and (object_owner,object_name) in (select owner,table_name from dba_tables where owner not in ('SYS') and num_rows is not null and num_rows>1000000) and h.sql_id=s.sql_id and executions>0;
spool off
EOF
sed -i '1d;$d' lst.lst
cat lst.lst | while read line
do
echo "sql_id: "$line '==================='
echo "Отчет по фулскановому запросу">report$line.txt
rm -f tmp.tmp
sqlplus / as sysdba<<EOF
SET LINESIZE 1200
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET PAUSE OFF
SET TRIMSPOOL ON
spool tmp.tmp
select distinct module||' '||action from v\$sql where sql_id='$line' order by 1;
spool off
EOF
sed -i '1d;$d' tmp.tmp
echo 'SQL_ID: '$line >>report$line.txt
echo 'Источник запроса: '>>report$line.txt
cat tmp.tmp>>report$line.txt
rm -f tmp.tmp
sqlplus / as sysdba<<EOF
SET LINESIZE 1200
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET PAUSE OFF
SET TRIMSPOOL ON
SET LONG 300000
spool tmp.tmp
select distinct object_owner||'.'||object_name||' -> '||operation||' '||options||' ('||(select num_rows from dba_tables t where h.object_owner=t.owner and h.object_name=t.table_name)||')' from DBA_HIST_SQL_PLAN H, V\$SQL S where options like '%FULL%' and object_owner not in ('SYS') and operation!='INDEX' and (object_owner,object_name) in (select owner,table_name from dba_tables where owner not in ('SYS') and num_rows is not null and num_rows>1000000) and h.sql_id='$line' and h.sql_id=s.sql_id and executions>0;
spool off
EOF
sed -i '1d;$d' tmp.tmp
echo 'Тяжелая операция: '>>report$line.txt
cat tmp.tmp>>report$line.txt
echo >>report$line.txt
rm -f tmp.tmp
sqlplus / as sysdba<<EOF
SET LINESIZE 1200
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET PAUSE OFF
SET LONG 300000
COLUMN SQL_FULLTEXT FORMAT A10000
spool tmp.tmp
select sql_fulltext from v\$sql where sql_id='$line' and rownum=1;
spool off
EOF
sed -i '1d;$d' tmp.tmp
echo 'Запрос: '>>report$line.txt
cat tmp.tmp>>report$line.txt
echo >>report$line.txt
rm -f tmp.tmp
sqlplus / as sysdba<<EOF
SET LINESIZE 1200
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET PAUSE OFF
SET TRIMSPOOL ON
SET LONG 300000
SET SERVEROUTPUT ON
spool tmp.tmp
declare
stmt_task VARCHAR2(40);
report CLOB;
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '$line',time_limit=>600);
dbms_sqltune.Execute_tuning_task (task_name => stmt_task);
report:=DBMS_SQLTUNE.REPORT_TUNING_TASK(stmt_task);
dbms_output.put_line(report);
end;
/
spool off
EOF
sed -i '1,12d; $d' tmp.tmp
cat tmp.tmp>>report$line.txt
done
rm -f lst.lst
rm -f tmp.tmp