Query useful in Performance issue in Oracle

Query used to find bottleneck in Performance issue in Oracle database

Finding Query regarding particular users

SELECT * FROM v$sqlarea WHERE parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') order by executions DESC;

Find query is sorted in decreasing Optimizer cost order

SELECT sa.rows_processed, s.optimizer_cost, s.cpu_time,s.elapsed_time, sa.sql_text FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id =
(SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS')
AND s.sql_text = sa.sql_text ORDER BY s.optimizer_cost DESC;

Find the SQL statement

SELECT * FROM( SELECT st.sql_text FROM v$sqlarea sa, v$sqltext st WHERE sa.hash_value = st.hash_value AND sa.address = st.address AND sa.parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') ORDER BY st.hash_value, st.address, st.piece) WHERE ROWNUM <= 5;

Find SQT Test with Number of Execution Order

SELECT * FROM ( SELECT executions "Execs", rows_processed "Rows", sql_text FROM v$sqlarea WHERE parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') ORDER BY executions DESC ) WHERE ROWNUM <= 10;

DISK+BUFFER reads per row looks at highest data access queries

SELECT * FROM( SELECT ROUND((disk_reads + buffer_gets)/rows_processed)
"Reads/Row" ,disk_reads + buffer_gets "Reads",rows_processed "Rows", sql_text FROM v$sqlarea WHERE parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') AND rows_processed > 0 AND (disk_reads + buffer_gets) > 0 ORDER BY ROUND((disk_reads + buffer_gets)/rows_processed)DESC) WHERE ROWNUM <= 10;

ROW PER SORT Fewer row per sort may indicate lack of indexing

SELECT * FROM( SELECT ROUND(rows_processed/sorts) "Rows/Sort" ,rows_processed "Rows" ,Sorts "Sorts", sql_text FROM v$sqlarea WHERE parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') AND rows_processed > 0 AND sorts > 0 ORDER BY ROUND(rows_processed/sorts) ASC ) WHERE ROWNUM <= 10;

Parses per Execution: More parsing means that previously parsed SQL code in the shared pool is not being reused efficiently. This can indicate lack of SQL code bind variables. The CURSOR_SHARING parameter SIMILAR or FORCE settings can help.

SELECT * FROM ( SELECT ROUND(parse_calls/executions) "Parses/Exec" ,parse_calls "Parses", executions "Execs", sql_text FROM v$sqlarea WHERE parsing_user_id =(SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') AND parse_calls > 0 AND executions > 0 ORDER BY ROUND(parse_calls/executions) ASC) WHERE ROWNUM <= 10;

Find SQL Queries with optimizer cost, CPU and elapsed time

SELECT * FROM( SELECT s.optimizer_cost "Cost", sa.rows_processed "Rows",
sa.sql_text FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') AND s.sql_text = sa.sql_text ORDER BY s.optimizer_cost DESC ) WHERE ROWNUM <= 10;

Find TOP SQL Queries with high consuming CPU Time

SELECT * FROM( SELECT s.cpu_time "CPU", sa.rows_processed "Rows",sa.sql_text
FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') AND s.sql_text = sa.sql_text ORDER BY s.cpu_time DESC ) WHERE ROWNUM <= 10;

Find Top Queries with high consuming Elapsed time

SELECT * FROM( SELECT s.elapsed_time "Time", sa.rows_processed "Rows",
sa.sql_text FROM v$sqlarea sa, v$sql s WHERE sa.parsing_user_id = (SELECT user_id FROM dba_users WHERE username = 'ACCOUNTS') AND s.sql_text = sa.sql_text ORDER BY s.elapsed_time DESC ) WHERE ROWNUM <= 10;

Calculate database buffer cache hit ratio

SELECT 'Database Buffer Cache Hit Ratio ' "Ratio", ROUND((1 - ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')/ ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets') + (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets') ))) * 100)||'%' "Percentage" FROM DUAL;

Index use ratio

SELECT value, name FROM V$SYSSTAT WHERE name IN ('table fetch by rowid', 'table scans (short tables)', 'table scans (long tables)') OR name LIKE 'index fast full%' OR name = 'index fetch by key';

Give index use percentage:

SELECT 'Index to Table Ratio ' "Ratio" , ROUND((SELECT SUM(value) FROM V$SYSSTAT
WHERE name LIKE 'index fast full%' OR name = 'index fetch by key' OR name = 'table fetch by rowid');

(SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)')),0)||':1' "Result"
FROM DUAL;

Dictionary cache hit ratio

SELECT 'Dictionary Cache Hit Ratio ' "Ratio" ,ROUND((1 - (SUM(GETMISSES)/SUM(GETS))) * 100,2)||'%' "Percentage" FROM V$ROWCACHE;

Find the Disk Sort Ratio
Increase the size of the SORT_AREA_SIZE parameter to prevent sorting to disk. When using shared servers a smaller sort buffer may ultimately preserve memory resources for other essential processing. Retain a chunk of sort space memory for idle connections by setting the SORT_AREA_RETAINED_SIZE parameter to a small percentage of the SORT_AREA_SIZE parameter, usually 10%. Do not always set SORT_AREA_RETAINED_SIZE equal to SORT_AREA_SIZE.

SELECT 'Sorts in Memory ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100, 2) ||'%' "Percentage" FROM DUAL;

A ratio of memory to disk sorts of less than 100% is not necessarily a problem.
Ratio                Percentage
——————   ———-
Sorts in Memory      98.89%

Find the Chained row ratio

SELECT 'Chained Rows ' "Ratio" , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch continued row') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch by rowid')) * 100, 3)||'%' "Percentage" FROM DUAL;

Should be between 0 to 10%

Find the ratio of parses:

SELECT 'Soft Parses ' "Ratio", ROUND(((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)') - (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)')) / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count')* 100, 2)||'%' "Percentage" FROM DUAL
UNION
SELECT 'Hard Parses ' "Ratio", ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100, 2)||'%' "Percentage" FROM DUAL
UNION
SELECT 'Parse Failures ' "Ratio", ROUND((SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'parse count (failures)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)') * 100, 2)||'%' "Percentage" FROM DUAL;

One of the most common causes of heavy re-parsing is lack of bind variables in SQL code. Setting the CURSOR_SHARING configuration parameter to SIMILAR or FORCE can help alleviate this problem without recording SQL code.

Also increasing the SESSION_CACHED_CURSORS parameter can help reduce re-parsing by caching more session-specific cursors per session

LATCH HiT ratio

SELECT 'Latch Hit Ratio ' "Ratio", ROUND((SELECT SUM(gets) - SUM(misses) FROM V$LATCH)/ (SELECT SUM(gets) FROM V$LATCH)* 100, 2)||'%' "Percentage" FROM DUAL;

Check wait events of table

COL percentage FORMAT 9999999990;
SELECT event "Event", total_waits "Waits", time_waited "Total Time",TO_CHAR((time_waited / (SELECT SUM(time_waited) FROM v$system_event WHERE IdleEvent(event) IS NOT NULL) )*100, 990.99) "Percentage" FROM v$system_event WHERE IdleEvent(event) IS NOT NULL ORDER BY event;

Buffer Busy Waits Waiting for a database buffer cache block to be available; the block is being read in or changed.
Data file Scattered and Sequential Reads Reads of data file physical blocks on disk, where scattered means potentially random (full physical scans) in scattered non contiguous parts of the buffer and sequential means more focused (index reads).
Direct Path Read and Writes Passing of sorting and hash joins to temporary sort space on disk.
Free Buffer Waits No free or clean blocks available in the database buffer cache and waiting for writing to disk of database buffer cache dirty blocks.
Row Cache Lock Waits Metadata cache waits on schema object definitions.
Library Cache Waits The library cache contains parsed SQL code, PL/SQL blocks, and optimized query plans.
Redo Log Waits Redo log wait events.
Rollback and Automated Undo Waits Wait events can help determine sizes and numbers of rollback segments. Automatic undo is easier to manage but its reliability may be in question due its very recent introduction into Oracle Database. However, I have so far heard only good reports about automated undo.
Enqueue Waits Locking in the database either of data file tables and rows or latches busy in buffers.
Latch Free Waits Waiting to access an area of a buffer; a latch required to access that buffer area is busy.
Buffer Busy Waits buffer busy wait occurs when more than one process is attempting to access a single database buffer cache block at once. In other words, the block is not in memory but another process is already reading it into memory or the block is already in memory but currently being changed by another process.
V$WAITSTAT performance view shows a breakdown of buffer busy wait events by class of buffer:
SELECT * FROM v$waitstat:
Data Block Indicates database buffer cache dirty writes or an inability to read from disk due to contention with other competing requests.
Sort Block Indicates there is not enough sort space or a temporary sort is either nonexistent on disk or is not defaulted for the current user.
Segment Header There is contention for the first block of a table or an index. A segment can be a table or index, among other database objects.
Free List There is too much free space maintained for each block.
Extent Map Extents may be poorly sized if there is too much recursion. Perhaps extent sizes are all different or they are too small. This is likely to be less common using locally managed table spaces as opposed to dictionary-managed table spaces.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s