oracle SQL Tuning Script

oracle SQL Tuning Script

======================================
1) use this script for seeing
How many days the database is UP ?
======================================

SET linesize 999
SET pagesize 50000
SET feedback 1
SET trimspool on
SET trimout on

SELECT instance_name, ROUND ((SYSDATE - startup_time), 1) up_days
FROM v$instance
/

======================================================================
2. use this script to see the distribution of code based on cpu cost.
here "sql_statements" means: how many sql consumed the CPU% ?
and "PCT_TOTAL" means:- how many percentise of CPU use?
======================================================================

COL sql_text format a700 trunc
COL pct_total format 990
COMPUTE sum of sql_statements on report
BREAK on report

SELECT cpu_time_log10, sql_statements, cpu_time_rounded,
ROUND (cpu_time) cpu_time,
100 * ratio_to_report (cpu_time) OVER () pct_total,
ROUND (SUM (cpu_time) OVER (ORDER BY cpu_time_log10))
running_cpu_time
FROM (SELECT TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END)
)
/ 1000000
)
) cpu_time_log10,
COUNT (*) sql_statements,
POWER
(10,
TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END
)
)
/ 1000000
)
)
) cpu_time_rounded,
SUM ((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END) / 1000000
) cpu_time
FROM v$sqlarea
WHERE UPPER (sql_text) NOT LIKE 'BEGIN%'
AND UPPER (sql_text) NOT LIKE 'DECLARE%'
GROUP BY TRUNC (LOG (10,
((CASE
WHEN cpu_time <= 0 THEN 1
ELSE cpu_time
END)
)
/ 1000000
)
)) a
ORDER BY a.cpu_time_log10
/

=========================================================
3. here, what are the most CPU consumed SQL (in details)
==========================================================

COMPUTE sum of cpu_seconds on report
BREAK on report

SELECT TRUNC (cpu_time / 1000000) cpu_seconds,
TRUNC (elapsed_time / 1000000) eplapsed_seconds,
ROUND ( (SYSDATE
- TO_DATE (first_load_time, 'rrrr-mm-dd/hh24:mi:ss')
)
* 24
) hours_in_cache,
executions, sql_text
FROM v$sqlarea
WHERE UPPER (sql_text) NOT LIKE 'BEGIN%'
AND UPPER (sql_text) NOT LIKE 'DECLARE%'
AND cpu_time / 1000000 > 100
ORDER BY 1, 2
/