Use the select statement to view various Oracle hit rates and Performance Statistics

-- Buffer hit rate: the ratio of the buffer hit rate between the obtained data and all accessed data. The normal metric is 90% ~ 100%. However, it may be lower
-- 90%. The buffer hit rate is affected by the data block buffers parameter settings in Oracle sga. You can also adjust the usage of the buffer pool to increase the buffer hit rate.
Select round (1-(physical. value-direct.value-lobs.value)/logical. value) *) "buffer hit rate"
From v $ sysstat physical, v $ sysstat direct, v $ sysstat lobs, v $ sysstat logical
Where physical. NAME = 'physical reads'
And direct. name = 'physical reads direct'
And lobs. name = 'physical reads direct (lob )'
And logical. NAME = 'session logical reads ';

-- Library hit rate database buffer hit rate refers to the ratio of the object namespace obtained through memory during oracle SQL statement execution. The normal metric is 95% ~ 100%
-- Affected by the sharing pool parameter settings in oracle sga
Select round (sum (pins-reloads)/sum (pins) *, 2) "Library Buffer hit rate"
From v $ librarycache;

-- Latch hit rate: Internal Structure Maintenance lock hit rate, normal indicators: 99% ~ 100%. If the hit rate is low, it is usually because the value of shared_pool_size is too large or the bound variable is not used, resulting in too many hard resolutions.
Select round (1-sum (misses + immediate_misses)/sum (gets + immediate_gets) * 100,2) "latch hit rate" from v $ latch;

-- In-memory sort hit rate indicates the number of sorted tasks in the memory and the number of sorted tasks on the hard disk. The normal metric is 99% ~ 100%. Affected by the sort_area_size or pga_aggregate_target Parameter
Select round (1-disk.value/(disk. value + memory. value) *) "in-memory sort hit rate" from v $ sysstat disk, v $ sysstat memory where
Disk. NAME = 'sorts (disk) 'and memory. NAME = 'sorts (memory )';

-- Buffer nowait refers to the ratio of waiting for data to be obtained in the buffer zone. The normal indicator is 99% ~ 100%. Affected by parameters such as db_block_buffers or db_cache_szie
Select round (1-busy.value/tol. value) * 100,2) "buffer busy nowait ratio" from (select sum (count) value from v $ waitstat where
Class in ('data Block', 'segment header', 'undo header', 'undo Block') busy,
(Select value from v $ sysstat where name = 'session logical reads') tol;

-- Redo nowait refers to the ratio in which data is not waiting in the redo buffer. The normal metric is 99% ~ 100%. Affected by parameters such as log_buffer_size, relatively low
-- The reason may be that the archiving speed is too slow, the online log file is too small, or the online log file is stored on a slow storage device.
Select round (1-waits.value/redos. value) * 100,2) "redo nowait" from v $ sysstat waits, v $ sysstat redos where
Waits. NAME = 'redo log space requests'
And redos. NAME = 'redo entries ';

-- Execute to parse refers to the ratio of SQL statement execution to analysis. Normal indicators are close to 100%. Affected by parameters such as pai_pool_size
Select round (1-hard.value/total. value) * 100,2) "execute to parse" from v $ sysstat hard, v $ sysstat total
Where hard. NAME = 'parse count (hard )'
And total. NAME = 'parse count (total )';

-- Parse cpu to parse elapsed refers to the ratio of the time when the database analyzes the cpu and the time when the analysis is completed. A low value indicates that the cpu is waiting for other resources during the analysis.
Select round (1-cpu.value/total. value) * 100,2) "parse cpu to parse elapsed" from v $ sysstat cpu, v $ sysstat total
Where cpu. NAME = 'parse time cpu'
And total. NAME = 'parse time elapsed ';

-- Non-parse cpu refers to the ratio of the actual running time to the sum of SQL statement running and parsing time. If the value is too low, it indicates that it takes too long to parse the SQL statement.
Select round (1-parse.value/total. value) * 100,2) "non-parse cpu" from v $ sysstat parse, v $ sysstat total
Where parse. NAME = 'parse time cpu'
And total. NAME = 'cpu used by this session ';

-- When a rollback segment is competitive, the smaller the competition rate, the better. This value is affected by the rollback size.
Select name, waits, gets, round (waits/gets *, 2) "rollback segment Competition"
From v $ rollstat a, v $ rollname B
Where a. USN = B. usn;

-- I/O ratio of the tablespace. The smaller the ratio, the better. This value is affected by the db_block_size parameter value and is related to the distribution of data files on the disk.
Select df. tablespace_name, sum (f. PHYRDS), sum (f. PHYBLKRD), sum (f. PHYRDS), sum (f. PHYBLKWRT) from v $ filestat f, dba_data_files df
Where f. FILE # = df. file_id
Group by df. tablespace_name order by df. tablespace_name;

-- I/O ratio of the data file. The smaller the proportion, the better. This value is affected by the db_block_size parameter value and is related to the distribution of the data file on the disk.
Select df. name, sum (f. PHYRDS), sum (f. PHYBLKRD), sum (f. PHYRDS), sum (f. PHYBLKWRT) from v $ filestat f, v $ datafile df
Where f. FILE # = df. file #
Group by df. name order by df. name;

-- Redo the log buffer hit rate. The higher the hit rate, the better. Normal indicators should be greater than 90%. The effect of the log_buffer_size parameter value.
Select name, gets, misses, immediate_gets, immediate_misses,
100-round (decode (gets, 100, misses/gets *), 2) ratio1,
100-round (decode (immediate_gets + immediate_misses, 100, immediate_gets/(immediate_gets + immediate_misses) *), 2) ratio2
From v $ latch where name in ('redo allocation', 'redo copy ');

-- The degree of fragmentation. The larger the size, the better. The normal indicator should be greater than 30%. Influence of the values of db_block_size and segment_size
Select tablespace_name, sqrt (max (blocks)/sum (blocks) * (100/sqrt (count (blocks) fsfl
From dba_free_space
Group by tablespace_name order by tablespace_name;

Differences between Select statements for extracting 10 data records from Oracle databases and SQL Server and MySQL

Oracle learning: for loop contains Select statements

Oracle Database SQL --- Select Query

Audit causes Select * to report ORA-01435: user does not exist

Oracle Select statement field connection