This article is the sequel of my article
Oracle Database Monitoring for Beginner 1. Query for Status Monitoring
Examples of Status Monitoring are, check tablespace where it's about to fills up, check segment near their maximum extents.
-Tablespace related
This query will show tablespace with percentage free less than 20 percent or equal 20 percent
select ddf.TABLESPACE_NAME,
ddf.BYTES,
ddf.BYTES-DFS.BYTES "BYTES_USED",
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) "PERCENT_USED",
dfs.BYTES "BYTES_FREE",
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) "PERCENT_FREE"
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
where autoextensible='NO'
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME and round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) < 20 or round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) = 20
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc
Query to show tablespace and the datafile
SELECT dfs.TABLESPACE_NAME, SUBSTR (df.NAME, 1, 70) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes, dfs.TABLESPACE_NAME
ORDER BY file_name
-Oracle Objects Extents related
Show objects more than 50% of Max Extents
select OWNER,
TABLESPACE_NAME,
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES,
EXTENTS,
MAX_EXTENTS,
(EXTENTS/MAX_EXTENTS)*100 \"PERCENTAGE\"
from dba_segments
where SEGMENT_TYPE in ('TABLE','INDEX')
and EXTENTS > MAX_EXTENTS/2
order by (EXTENTS/MAX_EXTENTS) desc
Show Segment near their maximum extents
SELECT e.owner,
e.segment_type,
Substr(e.segment_name, 1, 30) "SEGMENT_NAME",
Trunc(s.initial_extent/1024) "INITIAL K",
Trunc(s.next_extent/1024) "NEXT K",
s.max_extents,
Count(*) as extents
FROM dba_extents e,
dba_segments s
WHERE e.owner = s.owner
AND e.segment_name = s.segment_name
AND e.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
HAVING Count(*) > s.max_extents - 10
ORDER BY e.segment_type, e.owner, e.segment_type, Count(*) DESC
-Show Oracle session related and memory used
SELECT NVL(a.username,'(oracle)') AS username,
a.sid,
a.serial#,
a.machine,
a.module,
a.program,
a.LOGON_TIME,
a.status,
a.process,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY MEMORY_KB desc, a.MODULE asc,username desc
2. Query for Performance Monitoring
-Show Oracle Hit Ratio
Dictionary Hit Ratio
The ratio of cache hits to cache lookup attempts in the data dictionary cache. In other words, the rate the database goes to the dictionary instead of the hard disk to retrieve data. A low ratio suggests more RAM should be added
The Dictionary Hit Ratio value have to be more than 90 percent
SELECT sum(gets) "GETS",sum(getmisses) "CACHE MISSES",round((1 - (sum(getmisses) / sum(gets))) * 100,3) "DICTIONARY"
FROM v$rowcache
Buffer Hit Ratio
The rate the database goes to the buffer instead of the hard disk to retrieve data. A low ratio suggests more RAM should be added to the system.
Buffer hit ratio have to be more than 85%
SELECT cur.value "block",con.value "con",phy.value "phy", ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "BUFFER"
FROM v$sysstat@".$dbName." cur, v$sysstat@".$dbName." con, v$sysstat@".$dbName." phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
Library Hit Ratio
Monitors the percentage of entries in the library cache that were parsed more than once (reloads) over the lifetime of the instance.
Library Hit Ratio have to be more than 95%
SELECT sum(pins) "EXEC", sum(pinhits) "EXEC HITS", round((sum(pinhits) / sum(pins)) * 100,3) "HIT RATIO", sum(reloads) "MISSES",round((sum(pins) / (sum(pins) + sum(reloads))) * 100,3) "HITRATIO"
FROM v$librarycache
-Show memory usage by user connected
SELECT NVL(a.username,'(oracle)') AS username,
a.machine,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL
ORDER BY MEMORY_KB desc, a.MODULE asc,username desc
-Show Oracle Sort Information
select name,to_char(value,'999,999,999,999,999') "VALUE"
from sys.v_$sysstat
where name like 'sort%'
Show hit ratio for sort
Sorting percentage on disk have less than 5 %. If the value more than 5 % then change parameter SORT_AREA_SIZE.
select to_char(100*a.value/decode((a.value+b.value),0,1,(A.value+b.value)),'999.90') "VALUE"
from sys.v_$sysstat a, sys.v_$sysstat b
where a.name = 'sorts (disk)'
and b.name = 'sorts (memory)'
-Show Oracle SGA Info
select name, to_char(value,'999,999,999,999,999') "VALUE" from sys.v_$sga
-Show Oracle Invalid Objects
This query is to show invalid objects in Oracle
select OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
from dba_objects
where STATUS = 'INVALID'
order by OWNER, OBJECT_TYPE, OBJECT_NAME
Solution :
if the object invalid you can recompile it or you can delete it if it's not used.
sql : SQL>ALTER PROCEDURE PROC_NAME COMPILE ;
SQL>ALTER PACKAGE PACKAGE_NAME COMPILE ;
SQL>ALTER VIEW VIEW_NAME COMPILE ;
Or if you have many invalid objects run this script reside in : $ORACLE_HOME/rdbms/admin/utlrp.sql
Login to sqlplus
sqlplus > @utlrp
-Show Query running in Oracle
This query will show currently queries running in Oracle.
SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,a.buffer_gets,a.disk_reads,a.executions,a.sorts,a.address FROM v$sqlarea a) WHERE rownum <= 10
To be continued .................................
