Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand sql architecture shared pool". In daily operation, I believe many people have doubts about how to understand sql architecture shared pool. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to understand sql architecture shared pool"! Next, please follow the editor to study!

1. Shared pool action

(1) parse the sql statement into an execution plan

(2) execute the execution plan and read the data

(3) return the read data to the client

2. Shared pool composition

① free space

② library cache for caching SQL statements and execution plans

③ dictionary cache, which stores information about the database itself, including data dictionaries

3. Query the size of each part of shared pool

SQL > select * from v$sgastat a where a.name='library cache'

SQL > select * from v$sgastat a where a.pool = 'shared pool' and a.name='free memory'

SQL > select * from v$sgastat a where a.name='row cache'

4. Hard analysis and soft analysis

In shard pool, the foreground process parses the SQL statement into an execution plan, which can be parsed in two ways: hard parsing and soft parsing.

(1) hard parsing (hard parse): when a SQL statement enters the shared pool and does not parse into the cache of the execution plan, hard parsing is required.

(2) soft parsing (soft parse): when a SQL statement enters the shared pool and there is a cache of execution plans in the shared pool, soft parsing is performed.

SQL > select name,value from v$sysstat where name like 'parse%'

NAME VALUE

-

Parse time cpu 1479

Parse time elapsed 2524

Parse count (total) 60356

Parse count (hard) 6524

Parse count (failures) 16

Parse count (describe) 50

In this result, parse count (total) represents the total number of parsing that has occurred since it was started from the database. When the amount of failed resolution and hard resolution is relatively large, it shows that there is a problem.

At this point, the study on "how to understand sql architecture shared pool" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.