Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle database on the hit rate of query summary     - CentOS environment prepared crontab scheduled tasks (Linux)

- Diagnose and resolve the SSH connection slow (Linux)

- RHEL 6.4 installed MySQL 5.6.27 (Database)

- Use netcat [nc] command on Linux and Unix port scan (Server)

- SQL Server automatic backup script (Database)

- ImportTsv-HBase data import tool (Database)

- HTML5 Fundamentals study notes (Programming)

- CentOS 6 Install Linux kernel source (Linux)

- Windows 8.1 and Ubuntu 14.04 dual system uninstall Ubuntu Tutorial (Linux)

- Detailed Linux platform chip programming process (Programming)

- Matters Oracle 11.2 single instance when connecting ASM need to pay attention and deal with the problem (Database)

- Java memory area Explanation (Programming)

- Use Oracle 11g show spparameter command (Database)

- The signature can not be verified under Debian (Linux)

- Install and manage Java under mac (Linux)

- Nginx reverse proxy and self-signed https (Server)

- MySQL server after an unexpected power outage can not start (Database)

- tar command to extract a file error: stdin has more than one entry (Linux)

- Linux system find command Detailed (Linux)

- How to use nmap command in Linux (Linux)

 
         
  Oracle database on the hit rate of query summary
     
  Add Date : 2018-11-21      
         
         
         
  1) Library Cache hit rate
Formula: Library Cache Hit Ratio = SUM (PINHITS) / SUM (PINS)
SELECT SUM (PINHITS) / SUM (PINS) FROM V $ LIBRARYCACHE;

Hit rate is not less than 99%, otherwise need to consider whether that is the shared pool size, bind variables, and other factors affect cursor_sharing.
SELECT NAME, VALUE FROM V $ PARAMETER WHERE NAME = 'cursor_sharing'

2) Shared Pool usage
Formula: Shared Pool Hit Ratio = (100-Free memory / shared_pool_size * 100)%
If the database AMM manage memory using the following statement to obtain Shared Pool Size:

SELECT POOL, ROUND (SUM (BYTES) / 1024 / 1024,2) FROM V $ SGASTAT where POOL = 'shared pool' group by POOL;

If Manual mode is to take the memory management:
SELECT NAME, VALUE FROM V $ PARAMETER WHERE NAME = 'shared_pool_size';

Query 1:

SELECT 100 - ROUND ((SELECT ROUND (SUM (BYTES) / 1024/1024, 2) BYTES
                      FROM V $ SGASTAT
                    WHERE NAME = 'free memory'
                      AND POOL = 'shared pool'
                    GROUP BY POOL) / ROUND (SUM (BYTES) / 1024/1024, 2),
                  4) * 100 || '%' RATIOS
 FROM V $ SGASTAT
 where POOL = 'shared pool'
 GROUP BY POOL

Query 2:
SELECT 100 - ROUND (B.BYTES / ROUND (SUM (A.BYTES) / 1024/1024, 2), 4) * 100 || '%' RATIOS
  FROM V $ SGASTAT A,
      (SELECT POOL, ROUND (SUM (BYTES) / 1024/1024, 2) BYTES
          FROM V $ SGASTAT
        WHERE NAME = 'free memory' AND POOL = 'shared pool' GROUP BY POOL) B
 where A.POOL = 'shared pool' GROUP BY B.BYTES, A.POOL

Shared pool usage should be stable at between 75% -90%, the waste is too small, too large or insufficient memory description statement reusability is not high.
     
         
         
         
  More:      
 
- Teach you how to choose to install CentOS 6.5 installation package (Linux)
- Protect your files, modify the Linux value Umask (Linux)
- Python regular expressions: how to use regular expressions (Programming)
- To build Spring RestTemplate use HttpClient4 (Programming)
- Download Manager uGet 2.0 installed in Debian, Ubuntu, Linux Mint and Fedora (Linux)
- Depth understanding of JavaScript new mechanism (Programming)
- To install PXE + Kickstart under CentOS 6.x operating system (Linux)
- Ubuntu will be written in a command file, executable file, source command (Linux)
- Linux common network tools: ping host sweep (Linux)
- Linux Variable content removal and replacement (Linux)
- CentOS7 install NTFS-3G driver (Linux)
- CentOS 5.5 install ntop (Linux)
- Ubuntu 14.04 compile, install, configure, the latest development version GoldenDict (Linux)
- Free compiler install MySQL-5.6.14 (Database)
- Oracle11g CRS-0184 Problem Solving (Database)
- AWR Report (Database)
- secureCRT remote login Linux must first open the connection protocol (Linux)
- VPN built on CentOS (Server)
- Talking about the shortcomings and deficiencies of the firewall (Linux)
- GitLab installation configuration notes (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.