200 lines
5.7 KiB
SQL
200 lines
5.7 KiB
SQL
-- +----------------------------------------------------------------------------+
|
|
-- | Jeffrey M. Hunter |
|
|
-- | jhunter@idevelopment.info |
|
|
-- | www.idevelopment.info |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | DATABASE : Oracle |
|
|
-- | FILE : perf_performance_snapshot.sql |
|
|
-- | CLASS : Tuning |
|
|
-- | PURPOSE : This script will generate a small performance overview report |
|
|
-- | checking all key database performance indicators. |
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
|
-- | environment before attempting to run it in production. |
|
|
-- +----------------------------------------------------------------------------+
|
|
|
|
SET LINESIZE 145
|
|
SET PAGESIZE 9999
|
|
SET VERIFY off
|
|
|
|
PROMPT
|
|
PROMPT +-----------------------------------+
|
|
PROMPT | Database Startup Date/Time |
|
|
PROMPT +-----------------------------------+
|
|
PROMPT
|
|
|
|
SELECT
|
|
instance "Instance Name"
|
|
, open_time "Open Date/Time"
|
|
FROM
|
|
v$thread
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT +-----------------------------------+
|
|
PROMPT | Buffer Cache Hit Ratio |
|
|
PROMPT +-----------------------------------+
|
|
PROMPT
|
|
|
|
SELECT
|
|
TRUNC( ( 1 - ( SUM(decode(name,'physical reads',value,0)) /
|
|
( SUM(DECODE(name,'db block gets',value,0))
|
|
+
|
|
(SUM(DECODE(name,'consistent gets',value,0)))
|
|
)
|
|
)
|
|
) * 100
|
|
) "Buffer Hit Ratio"
|
|
FROM v$sysstat
|
|
/
|
|
|
|
SELECT
|
|
a.value + b.value "Logical reads"
|
|
, c.value "Physical Reads"
|
|
, d.value "Physical Writes"
|
|
, ROUND (100 * ( (a.value+b.value)-c.value) / (a.value+b.value)
|
|
) "Buffer Hit Ratio"
|
|
, ROUND(c.value * 100 / (a.value + b.value)) "% Missed"
|
|
FROM
|
|
v$sysstat a
|
|
, v$sysstat b
|
|
, v$sysstat c
|
|
, v$sysstat d
|
|
WHERE
|
|
a.statistic#=37
|
|
AND b.statistic#=38
|
|
AND c.statistic#=39
|
|
AND d.statistic#=40
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT +-----------------------------------+
|
|
PROMPT | Data Dictionary Hit Ratio |
|
|
PROMPT +-----------------------------------+
|
|
PROMPT
|
|
|
|
SELECT
|
|
SUM(gets) "Data Dict. Gets"
|
|
, SUM(getmisses) "Data Dict. Cache Misses"
|
|
, ROUND((1-(sum(getmisses)/SUM(gets)))*100) "Data Dict Cache Hit Ratio"
|
|
, ROUND(SUM(getmisses)*100/SUM(gets)) "% Missed"
|
|
FROM
|
|
v$rowcache
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT +-----------------------------------+
|
|
PROMPT | Library Cache Miss Ratio |
|
|
PROMPT +-----------------------------------+
|
|
PROMPT
|
|
|
|
SELECT
|
|
SUM(pins) "Executions"
|
|
, SUM(reloads) "Cache Misses"
|
|
, ROUND((1-(SUM(reloads)/SUM(pins)))*100) "Library Cache Hit Ratio"
|
|
, ROUND(SUM(reloads)*100/SUM(pins)) "% Missed"
|
|
FROM
|
|
v$librarycache
|
|
/
|
|
|
|
SELECT
|
|
namespace "Namespace"
|
|
, TRUNC(gethitratio*100) "Hit Ratio"
|
|
, TRUNC(pinhitratio*100) "Pin Hit Ratio"
|
|
, reloads "Reloads"
|
|
, invalidations "Invalidations"
|
|
FROM
|
|
v$librarycache
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT +-----------------------------------+
|
|
PROMPT | Redo Log Buffer |
|
|
PROMPT +-----------------------------------+
|
|
PROMPT
|
|
|
|
SELECT
|
|
SUBSTR(name,1,30) "Name"
|
|
, TO_CHAR(value, '999,999') "Bytes"
|
|
FROM
|
|
v$sysstat
|
|
WHERE
|
|
name ='redo log space requests'
|
|
/
|
|
|
|
SELECT
|
|
name "Name"
|
|
, TO_CHAR(bytes, '999,999,999,999') "Bytes"
|
|
FROM
|
|
v$sgastat
|
|
WHERE
|
|
name ='free memory'
|
|
/
|
|
|
|
SELECT
|
|
TO_CHAR(SUM(executions), '999,999,999,999,999,999') "Tot SQL since startup"
|
|
, TO_CHAR(SUM(users_executing), '999,999,999,999,999') "SQL executing now"
|
|
FROM
|
|
v$sqlarea
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT +--------------------------------------------------------+
|
|
PROMPT | If miss_ratio or immediate_miss_ratio > 1 then latch |
|
|
PROMPT | contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE |
|
|
PROMPT +--------------------------------------------------------+
|
|
PROMPT
|
|
|
|
SELECT
|
|
SUBSTR(ln.name,1,30) "Name"
|
|
, (misses/(gets+.001)) * 100 "Miss Ratio"
|
|
, (immediate_misses/(immediate_gets+.001)) * 100 "Immd. Miss Ratio"
|
|
FROM
|
|
v$latch l
|
|
, v$latchname ln
|
|
WHERE
|
|
l.latch# = ln.latch#
|
|
AND ( (( misses / (gets+.001)) * 100 > .1 )
|
|
OR
|
|
(( immediate_misses / (immediate_gets+.001)) * 100 > .1)
|
|
)
|
|
ORDER BY
|
|
ln.name
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT +--------------------------------------------------------+
|
|
PROMPT | If these are < 1% of Total Number of requests for data |
|
|
PROMPT | then extra rollback segments are needed. |
|
|
PROMPT +--------------------------------------------------------+
|
|
PROMPT
|
|
|
|
SELECT
|
|
class "Class"
|
|
, count "Count"
|
|
FROM
|
|
v$waitstat
|
|
WHERE
|
|
class IN ( 'free list'
|
|
, 'system undo header'
|
|
, 'system undo block'
|
|
, 'undo header'
|
|
, 'undo block')
|
|
GROUP BY
|
|
class
|
|
, count
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT +-----------------------------------+
|
|
PROMPT | Total Number of Requests for Data |
|
|
PROMPT +-----------------------------------+
|
|
PROMPT
|
|
|
|
SELECT TO_CHAR(SUM(value), '999,999,999,999,999') "Total Requests"
|
|
FROM v$sysstat
|
|
WHERE name IN ('db block gets','consistent gets')
|
|
/
|
|
|