Oracle Common Performance Monitoring SQL statements

--oracle Common Performance Monitoring SQL statements
--1
SELECT * from SYS. V_$sqlarea WHERE disk_reads > 100;

--2 waiting for the monitoring case
SELECT EVENT,
SUM (DECODE (wait_time, 0, 0, 1)) "Prev",
SUM (DECODE (wait_time, 0, 1, 0)) "Curr",
COUNT (*) "Tot"
From v$session_wait
GROUP by EVENT
ORDER by 4;

--3 race condition for rollback segments
Select NAME, WAITS, GETS, waits/gets "Ratio"
From V$rollstat A, V$rollname B
WHERE A.usn = B.usn;

--4 viewing the SQL statements being issued by the foreground
SELECT user_name, Sql_text
From V$open_cursor
WHERE SID in (SELECT SID
From (SELECT SID, serial#
, USERNAME, program
From V$session
WHERE STATUS = ' ACTIVE ');

--5 the size of the data table
SELECT segment_name, Tablespace_name, BYTES, BLOCKS
From User_segments
WHERE segment_type = ' TABLE '
ORDER by BYTES Desc, BLOCKS desc;

--6 Viewing table space fragmentation size
SELECT Tablespace_name,
ROUND (SQRT (MAX (BLOCKS)/SUM (BLOCKS)) *
(100/sqrt (SQRT (COUNT (BLOCKS))),
2) FSFI
From Dba_free_space
GROUP by Tablespace_name
ORDER by 1;

--7 Viewing table space consumption of disk
Select b.file_id "File ID number",
B.tablespace_name "tablespace name",
Round (B.bytes/(1024 * 1024), 2) "Total Space (M)",
Round ((B.bytes-sum (NVL (a.bytes, 0))/(1024 * 1024), 2) "Used space remaining (M)",
Round (SUM (NVL (a.bytes, 0))/(1024 * 1024), 2) "Space remaining (M)",
Round (SUM (NVL (a.bytes, 0))/(b.bytes) * 100, 2) "percent remaining"
From Dba_free_space A, Dba_data_files B
WHERE a.file_id = b.file_id
GROUP by B.tablespace_name, b.file_id, b.bytes
ORDER by b.file_id;

--8 viewing session using rollback segment
SELECT r.name rollback segment name,
S.sid,
s.serial#,
S.username User Name,
T.status,
T.cr_get,
T.phy_io,
T.USED_UBLK,
T.noundo,
SUBSTR (S.program, 1, +) as "operating procedure"
From SYS. V_$session S, SYS. V_$transaction T, SYS. V_$rollname R
WHERE t.addr = s.taddr
and T.xidusn = R.usn
ORDER by T.cr_get, T.phy_io;

--9 viewing the remaining available memory in the SGA area
SELECT NAME,
sgasize/1024/1024 as "allocated (M)",
bytes/1024 as "* * Space (K)",
ROUND (Bytes/sgasize *, 2) as "* * space percentage (%)"
From (The SELECT SUM (BYTES) sgasize from SYS. V_$sgastat) S,
SYS. V_$sgastat F
WHERE f.name = ' free memory ';

--10 Monitoring table space I/O scale
SELECT DF. Tablespace_name NAME,
Df. file_name "File",
F.phyrds PYR,
F.phyblkrd PBR,
F.phywrts PYW,
F.phyblkwrt PBW
From V$filestat F, Dba_data_files DF
WHERE f.file# = DF. file_id
ORDER by DF. Tablespace_name;

--11 monitoring SGA Hit rate
Select A.value + b.value "Logical_reads",
C.value "Phys_reads",
ROUND ((A.value + b.value)-C.value)/(A.value + b.value)) "BUFFER hit RATIO"
From V$sysstat A, V$sysstat B, V$sysstat C
WHERE a.statistic# = 38
and b.statistic# = 39
and c.statistic# = 40;

--12 monitor the dictionary buffer hit ratio in the SGA
SELECT PARAMETER,
GETS,
Getmisses,
Getmisses/(GETS + getmisses) * "Miss ratio",
(1-(SUM (getmisses)/(SUM (GETS) + sum (getmisses))) * "Hit ratio"
From V$rowcache
WHERE GETS + getmisses <> 0
GROUP by PARAMETER, GETS, getmisses;

--13 Monitor the SGA * Cache hit rate, should be less than 1%
Select SUM (PINS) "Total PINS",
SUM (reloads) "Total reloads",
SUM (reloads)/SUM (PINS) * Libcache
From V$librarycache;

--14 Monitor the hit rate of redo log buffers in the SGA, should be less than 1%
SELECT NAME,
GETS,
MISSES,
Immediate_gets,
Immediate_misses,
DECODE (GETS, 0, 0, misses/gets *) RATIO1,
DECODE (Immediate_gets + immediate_misses,
0,
0,
Immediate_misses/(immediate_gets + immediate_misses) * +) RATIO2
From V$latch
WHERE NAME in (' Redo allocation ', ' redo copy ');

The--15 monitors the memory and the hard disk's sorting ratios, preferably making it less than. 10
SELECT NAME, VALUE
From V$sysstat
WHERE NAME in (' Sorts (memory) ', ' sorts (disk) ');

--16 Monitoring Dictionary Buffers
Select SUM (gets) "DICTIONARY gets",
SUM (getmisses) "DICTIONARY CACHE GET MISSES"
From V$rowcache;

--17 tables created by non-system users in the system table space
SELECT OWNER, table_name
From Dba_tables
WHERE tablespace_name in (' SYSTEM ', ' user_data ')
and OWNER not in
(' SYSTEM ', ' SYS ', ' outln ', ' Ordsys ', ' Mdsys ', ' SCOTT ', ' hosteac ');

--18 worst-performing SQL
SELECT *
From (SELECT parsing_user_id executions,
Sorts,
Command_type,
Disk_reads,
Sql_text
From V$sqlarea
ORDER by Disk_reads DESC)
WHERE ROWNUM < 100;

--19 use the following SQL tools to locate inefficient SQL
SELECT executions,
Disk_reads,
Buffer_gets,
ROUND ((buffer_gets-disk_reads)/buffer_gets, 2) Hit_radio,
ROUND (Disk_reads/executions, 2) Reads_per_run,
Sql_text
From V$sqlarea
WHERE executions > 0
and buffer_gets > 0
and (buffer_gets-disk_reads)/Buffer_gets < 0.8
ORDER by 4 DESC;

--20 read disk 100 times SQL
SELECT * from SYS. V_$sqlarea WHERE disk_reads > 100;

--21 most frequently executed SQL
SELECT * from SYS. V_$sqlarea WHERE executions > 100;

--22 query user session with CPU
Select Vs.sid,
Vs. SQL_ID,
Vs. Machine,
Vp.spid,
Vs.status,
SUBSTR (Vs.program, 1, prog),
Vs.terminal,
Vs.osuser,
Round (VSTAT.VALUE/60 *, 6) value
From V$session vs, v$process VP, V$sesstat vstat
where vstat.statistic# = 12
and Vstat.sid = Vs.sid
and vs.paddr = Vp.addr
Order BY value Desc;

--23 the number of objects currently used per session
SELECT A.sid, S.terminal, S.program, COUNT (A.SID)
From V$access A, V$session S
WHERE a.owner <> ' SYS '
and S.sid = A.sid
GROUP by A.sid, S.terminal, S.program
ORDER by COUNT (A.SID);

Oracle Common Performance Monitoring SQL statements