Oracle Database Performance Tuning: Shared Pool & Statistics

Posted on Feb 28, 2025 in Computing and Communications

Shared Pool Analysis

Selecting Shared Pools for Performance Analysis:

To determine the best performing shared pools, use the following query:

SELECT SHARED_POOL_SIZE_FOR_ESTIMATE TAMANHO_EM_MB, SHARED_POOL_SIZE_FACTOR, ESTD_LC_TIME_SAVED TEMPO FROM V$SHARED_POOL_ADVICE;

Checking Shared Pool Size:

To check the current shared pool size, execute:

SHOW PARAMETER SHARED_POOL_SIZE

Altering Shared Pool Size:

To modify the shared pool size, for example, to 60MB, use:

ALTER SYSTEM SET SHARED_POOL_SIZE = 60M;

Oracle Database Statistics

1. Buffer Cache Hit Ratio

Definition: Represents the number of pages that Oracle maintains in memory.

Query to calculate the Buffer Cache Hit Ratio:

SELECT ROUND((1-(PR.VALUE/(BG.VALUE + CG.VALUE)))*100,2) FROM V$SYSSTAT PR, V$SYSSTAT BG, V$SYSSTAT CG WHERE PR.NAME = 'physical reads' AND BG.NAME = 'db block gets' AND CG.NAME = 'consistent gets';

Relevant statistics names:

SELECT NAME FROM V$SYSSTAT WHERE NAME in ('db block gets','consistent gets','physical reads');

2. Dictionary Cache Hit Ratio

Definition: Rate of requests for data already available in memory.

Query to calculate the Dictionary Cache Hit Ratio:

SELECT SUM(gets-getmisses)*100/SUM(gets) FROM V$ROWCACHE;

3. Library Cache Hit Ratio

Definition: Percentage of successful data requests in the Library Cache memory.

Query to calculate the Library Cache Hit Ratio:

SELECT SUM(pinhits)/SUM(pins) "Hit Ratio", SUM(reloads)/SUM(pins) "Reload percent" FROM V$LIBRARYCACHE WHERE NAMESPACE IN ('SQL AREA', 'TABLE/PROCEDURA', 'BODY', 'TRIGGER');

4. Sorts in Memory

Definition: Percentage of data sorted in memory versus on disk.

Query to calculate the percentage of sorts in memory:

SELECT ROUND((MEM.value/(MEM.value + DSK.value))*100,2) FROM V$SYSSTAT MEM, V$SYSSTAT DSK WHERE MEM.NAME = 'sorts (memory)' AND DSK.NAME = 'sorts (disk)';

5. Shared Pool Free Memory

Definition: Determines the percentage of unused memory in the Shared Pool.

Query to calculate the percentage of free memory in the Shared Pool:

select round((sum(decode(name,'free memory',bytes,0)) / sum(bytes)) * 100,2) from v$sgastat;

6. Short/Total Tables Scan Ratio

Query to calculate the ratio of short table scans to total table scans:

select round((shrt.value / (shrt.value + lng.value)) * 100,2) from v$sysstat shrt, v$sysstat lng where shrt.name = 'table scans (short tables)' and lng.name='table scans (long tables)';

7. Tuning Sorts

Definition: Identifies the amount of sorting done in memory and on disk.

Query to retrieve sort statistics:

select name, value from v$sysstat where name in ('sorts (memory)','sorts(disk)');

8. Top 5 Most Frequently Used SQL Statements

Query to find the top 5 most frequently executed SQL statements:

SELECT sql_text, executions FROM (SELECT sql_text, executions, rank() over (order by executions desc) exec_rank FROM V$SQL) WHERE exec_rank <= 5;

9. Top 5 SQL Statements Requiring the Most Disk Reads

Query to find the top 5 SQL statements that require the most disk reads:

select sql_text, disk_reads from (select sql_text, disk_reads, dense_rank() over (order by disk_reads desc) disk_reads_rank from v$sql) where disk_reads_rank <= 5;

10. Top 5 SQL Statements Requiring the Most Logical Reads

Query to find the top 5 SQL statements that require the most logical reads:

select sql_text buffer_gets from (select sql_text, buffer_gets, dense_rank() over (order by buffer_gets desc) buffer_gets_rank from v$sql) where buffer_gets_rank <= 5;