Hit hit rate

 

1 buffer hit rate
That is, the high-speed cache hit rate. This metric refers to a ratio between the data accessed through the memory and all accessed data.
Normal metric range:
The normal buffer hit rate is 90%-100%, but the buffer hit rate may be lower than 90% during the busy operation of the database (batch processing applications and data warehouses), which is a normal indicator.
Calculation formula:
Buffer hit = (1-physical reads Cache/(Consistent gets from cache + db block gets from cache) * 100
Where:
Physical reads cache = physical reads-physical reads direct-physical read direct (LOB)
Consistent gets from cache = consistent gets
Db block gets from cache = db block gets

You can use the performance Dictionary of several databases to obtain the hit rate of high-speed database cache.
Select round (1-(physical. Value-direct. Value-lobs. Value)/logical. Value) *) "buffer cache hit ratio"
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 ';

Influencing factors:
1. The buffer hit rate is affected by the data block buffers parameter settings in Oracle SGA.
2. It depends on the usage of the Oracle buffer pool.
3. cache frequently used small tables in memory
4. Optimize SQL statements to support less accessed data volumes

2 library hit rate
The hit rate of the database cache. It means that the namespace of an object is obtained directly through memory during the execution of an SQL statement in Oracle.
Normal metric range:
Normal library hit rate indicator range: 95%-100%
Calculation formula:
Library hit = sum (PINs)/(sum (PINs) + sum (reloads) * 100

You can use SQL queries to obtain the hit rate of the database cache.
Select round (sum (pins-reloads)/sum (PINs) * 100,2) "library cache hit ratio"
From v $ librarycache;

Influencing factors:
1. The library hit rate is affected by the Shared Pool parameter settings in Oracle SGA.
2. It is closely related to application software development, especially the use of shared SQL

3 latch hit rate
Latch is a simple low-level serialization mechanism used to protect the shared data structure in the global zone of the system. For example, to protect the list of users currently accessing the database, and to protect the data structure of blocks in the description buffer. For a server or background process, you must obtain the accompanied latch to start operations or view the shared data structure. After the process is completed, the accompanied latch must be released, the implementation of latch is related to the operating system platform, especially whether the process needs to wait for latch and how long it will take.
Normal metric range:
Latch hit rate: 99%-100%
Calculation formula:
Latch hit = (1-sum (misses + immediate_misses)/sum (gets + immediate_gets) * 100

You can obtain the latch hit rate through SQL queries.
Select round (1-sum (misses + immediate_misses)/sum (gets + immediate_gets) * 100,2) "latch hit ratio"
From v $ latch;

Influencing factors:
1. Whether the application SQL statement uses the bound variable
2. shared_pool_size parameter settings

4 In-memory sort hit rate
This metric refers to the ratio of sorting completed in memory to sorting on Hard Disk
Normal metric range:
The in-memory sort hit rate is normal. The range of indicators is 99%-100%.
Calculation formula:
In-memory sort hit = (1-sorts (Disk)/(sorts (Disk) + sorts (memory) * 100

You can use SQL queries to obtain the in-memory sort hit rate.
Select round (1-disk. Value/(disk. Value + memory. Value) * 100,2) "In-memory sort"
From v $ sysstat disk, V $ sysstat memory
Where disk. Name = 'sorts (Disk )'
And memory. Name = 'sorts (memory )';

Influencing factors:
1. database parameter sort_area_size or pga_aggregate_target size
2. Write SQL statements of applications

5 buffer Nowait
This indicator refers to the ratio of unwaiting buffer obtained in the buffer zone.
Normal metric range:
Buffer Nowait rate normal metric range: 99%-100%
Calculation formula:
Buffer Nowait = (1-buffer busy wait/session logical reads) * 100

You can use SQL queries to obtain the buffer Nowait hit rate.
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;

Influencing factors:
1. db_block_buffers, db_cache_size, and other parameters
2. Add the table's freelist Parameter
3. Use automatic segment storge Management (assm) to create a tablespace
4. Optimize the SQL statement used by the program

6 redo Nowait
This indicator refers to the ratio of unwaited buffer obtained by the redo buffer.
Normal metric range:
Normal redo Nowait rate indicators range: 99%-100%
Calculation formula:
Redo Nowait = (1-redo log space requests/reso entries) * 100

You can obtain the redo Nowait hit rate through SQL queries.
Select round (1-waits. Value/redos. Value) * 100,2) "redo Nowait ratio"
From v $ sysstat waits, V $ sysstat redos
Where Waits. Name = 'redo log space requests'
And redos. Name = 'redo entries ';

Influencing factors:
1. The value of log_buffer_size is too small.
2. the archiving speed is too slow.
3. The online log file is too small.
4. put online log files on slow disk devices

7 execute to parse
This indicator refers to the proportion of SQL statement execution and analysis in the database.
Normal metric range:
Execute to parse the closer it is to 100%, the better
Calculation formula:
Run to parse = (1-parses/executions) * 100

Execute to parse rate can be obtained through SQL query
Select round (1-hard. Value/total. Value) * 100,2) "execute to parse ratio"
From v $ sysstat hard, V $ sysstat total
Where hard. Name = 'parse count (hard )'
And total. Name = 'parse count (total )';

Influencing factors:
1. The size of the pai_pool_size Parameter
2. The most important factor is whether the application uses the bound variable.

8 parse CPU to PAREs elapsed
This metric refers to the comparison between the CPU time used by the database for analysis and the CPU time used for analysis.
Normal metric range:
The closer the parse CPU to parse elapsed is to 100%, the better.
Calculation formula:
Parse CPU to parse elapsed = (1-parses time CPU/parses time elapsed) * 100

You can obtain the parse CPU to parse elapsed e rate through SQL query.
Select round (1-CPU. Value/total. Value) * 100,2) "parse CPU to parse elapsed ratio"
From v $ sysstat CPU, V $ sysstat total
Where CPU. Name = 'parse time CPU'
And total. Name = 'parse time elapsed ';
Influencing factors:
1. If this ratio is low, the CPU waits for other resources during the analysis.

9 non-Parse CPU
This metric indicates that the CPU waits for other resources when the database is used for non-analysis.
Normal metric range:
The closer the non-Parse CPU is to 100%, the better.
Calculation formula:
Non-Parse CPU = (1-Parse time CPU/CPU used by this session) * 100

You can use SQL queries to obtain the non-Parse CPU rate.
Select round (1-parse. Value/total. Value) * 100,2) "non-Parse CPU ratio"
From v $ sysstat parse, V $ sysstat total
Where parse. Name = 'parse time CPU'
And total. Name = 'cpu used by this session'

Influencing factors:
1. If this ratio is very low, it means that the CPU has consumed a lot of CPU time on the analysis SQL statement, and it may be that no variable is bound.

10 rollback segment Competition
This refers to the ratio of headers waiting for rollback segment
Normal metric range:
Rollback segment: the smaller the waiting rate, the better.
Calculation formula:
Rollback segment = (waits/gets) * 100

You can use SQL queries to obtain the rollback segment wait rate.
Select name, waits, gets, round (waits/gets * 100,2) "ratio"
From v $ rollstat A, V $ rollname B
Where a. USN = B. USN;

Influencing factors:
1. The competition for rollback segments is affected by the size setting of rollback segments.
2. related to application software, especially the use of long runnig Time Transaction

11 I/O ratio of tablespace
This refers to the I/O ratio used by the tablespace.
Normal metric range:
The smaller the tablespace I/O, the better.
Calculation formula:

You can use SQL queries to obtain tablespace I/O information.
Select DF. tablespace_name, sum (F. phyrds), sum (F. phyblkrd ),
Sum (F. phywrts), 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;

Influencing factors:
1. the I/O condition of tablespace is affected by the setting of the db_block_size parameter.
2. It is closely related to the disk distribution of data files.

12 I/O ratio of datafile
This refers to the proportion of I/O used to access data files.
Normal metric range:
The smaller the datafile I/O, the better.
Calculation formula:

You can use SQL queries to obtain datafile I/O information.
Select DF. Name, sum (F. phyrds), sum (F. phyblkrd), sum (F. phywrts), sum (F. phyblkwrt)
From v $ filestat F, V $ datafile DF
Where F. File # = DF. File #
Group by DF. Name
Order by DF. Name;

Influencing factors:
1. the I/O condition of datafile is affected by the setting of the db_block_size parameter.
2. It is closely related to the disk distribution of data files.

13. redo log cache hit rate
This refers to the hit rate of the redo log cache area.
Normal metric range:
The higher the hit rate of the redo log cache, the better. It should be greater than 90%.
Calculation formula:

You can use SQL queries to obtain the log cache hit rate.
Select name, gets, misses, immediate_gets, immediate_misses,
100-round (decode (gets, 100, misses/gets *), 2) ratio1,
100-round (decode (immediate_gets + immediate_misses, 0, immediate_misses/
(Immediate_gets + immediate_misses) * 100), 2) ratio2
From v $ Latch
Where name in ('redo allocation', 'redo copy ');
Influencing factors:
1. Affected by the log_buffer_size setting
2. related to application software, especially the use of shared SQL

14 fragmentation level
This refers to the value of fsfi-free space fragmentation index (Free Space fragmentation index) in the database.
Normal metric range:
The larger the fsfi, the better. It must be greater than 30%.
Calculation formula:
Fsfi = 100 * SQRT (max (extent)/sum (extents) * 1/SQRT (count (extents )))

You can use SQL queries to obtain the log cache hit rate.
Select tablespace_name, SQRT (max (blocks)/sum (blocks) * (100/SQRT (count (blocks) fsfi
From dba_free_space
Group by tablespace_name order by 1tablespace_name;
Influencing factors:
1. The fragmentation is affected by the settings of db_block_size and segment_size.
2. It is closely related to the size of data blocks and segments.