-- TOP QUERIES
-- POR TEMPO DE CPU
set pages 200 lines 120
col module for a16
VARIABLE begin_snap NUMBER;
VARIABLE end_snap NUMBER;
SELECT MIN(snap_id), MAX(snap_id) INTO :begin_snap, :end_snap FROM dba_hist_snapshot ORDER BY 1;
select A.hash_value, A.text_subset, A.module, trunc((B.cpu_time-A.cpu_time)/1000) "CPU_TIME(ms)", B.executions-A.executions executions,
trunc(decode(B.executions-A.executions, 0, 0, (B.cpu_time-A.cpu_time)/(B.executions-A.executions))/1000) "CPU_TIME_PER_EXEC(ms)"
from STATS$SQL_SUMMARY A, STATS$SQL_SUMMARY B
where A.hash_value = B.hash_value
and A.snap_id = :begin_snap
and B.snap_id = :end_snap
order by "CPU_TIME(ms)" desc;
-- POR DURAÇÃO
set pages 200 lines 120
col module for a16
select A.hash_value, A.text_subset, A.module, trunc((B.elapsed_time-A.elapsed_time)/1000) "ELAPSED_TIME(ms)", B.executions-A.executions executions,
trunc(decode(B.executions-A.executions, 0, 0, (B.elapsed_time-A.elapsed_time)/(B.executions-A.executions))/1000) "ELAPSED_TIME_PER_EXEC(ms)"
from STATS$SQL_SUMMARY A, STATS$SQL_SUMMARY B
where A.hash_value = B.hash_value
and A.snap_id = :begin_snap
and B.snap_id = :end_snap
order by "ELAPSED_TIME(ms)" desc;
-- addmrpt -- report com recomendações --
-- senior --
@D:\oracle\product\10.2.0\RDBMS\ADMIN\addmrpt.sql
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql
-- maximo e totvs --
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sql
-- ATUALIZA ESTATÍSTICAS DO BANCO DE DADOS.
EXEC DBMS_STATS.gather_database_stats(cascade=>true);
SET LINES 9999
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (43200 = 30 Days).
-- Current value retained if NULL.
interval => 10); -- Minutes. Current value retained if NULL.
END;
/
SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot WHERE begin_interval_time >= SYSDATE -1 ORDER BY 1;
SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot ORDER BY 1;
EXEC dbms_sqltune.create_sqlset('SQL_TUNING_SET_20130926');
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(23382, 23437,
'executions > 1 and disk_reads > 100',NULL,'disk_reads')) p;
DBMS_SQLTUNE.LOAD_SQLSET('SQL_TUNING_SET_20130926', baseline_ref_cursor);
end;
/
select SQL_TEXT from table(DBMS_SQLTUNE.SELECT_SQLSET('SQL_TUNING_SET_20130926'));
DECLARE
workload_name VARCHAR2(30);
BEGIN
workload_name := 'SQL_TUNING_SET_20130926';
DBMS_ADVISOR.CREATE_SQLWKLD(workload_name);
END;
/
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
BEGIN
DBMS_ADVISOR.IMPORT_SQLWKLD_STS('SQL_TUNING_SET_20130926', 'SQL_TUNING_SET_20130926', 'NEW', 1, :saved_stmts, :failed_stmts);
END;
/
PRINT saved_stmts
PRINT failed_stmts
--------------------------------------------------------------------------------------
-- SE INSTÂNCIA SENIOR OU HISTRAINING:
--------------------------------------------------------------------------------------
-- alter session set nls_numeric_characters='.,';
--------------------------------------------------------------------------------------
VARIABLE task_id NUMBER
VARIABLE task_name VARCHAR2(255)
BEGIN
:task_name := 'SQL_TASK_20130926';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name);
END;
/
-- ASSOCIAR O WORKLOAD COM O TUNING TASK
BEGIN
DBMS_ADVISOR.ADD_SQLWKLD_REF('SQL_TASK_20130926', 'SQL_TUNING_SET_20130926');
END;
/
BEGIN
DBMS_ADVISOR.EXECUTE_TASK('SQL_TASK_20130926');
END;
/
--- VER BENEFÍCIOS E RECOMENDAÇÕES ---
SELECT sql_id, rec_id, precost, postcost, (postcost - precost) * 100 / precost AS perc_benefit
FROM user_advisor_sqla_wk_stmts
WHERE task_name = :task_name;
----------------------------------------
EXEC DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name), 'BACKUP', '20130926.sql');
EXEC DBMS_ADVISOR.RESET_TASK('SQL_TASK_20130926');
EXEC DBMS_ADVISOR.DELETE_SQLWKLD_REF('SQL_TASK_20130926', 'SQL_TUNING_SET_20130926');
EXEC DBMS_ADVISOR.DELETE_SQLWKLD('SQL_TUNING_SET_20130926');
EXEC DBMS_ADVISOR.DELETE_TASK(:task_name);
EXEC DBMS_SQLTUNE.DROP_SQLSET('SQL_TUNING_SET_20130926');
-- MAXIMO @E:\BACKUP\MAXIMO\20130926.SQL
-- TOTVS @H:\BACKUP\dtsprod\20130926.SQL
-- SENIOR @E:\oracle\backup\datapump\20130926.SQL
-- HISTRAINING @D:\BACKUP\histraining\20130926.SQL