Shulou(Shulou.com)06/01 Report--

This article introduces how to analyze the sql hit rate of shared_pool. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

If a SQL statement hits, it will greatly reduce the load on the database server, because the hard parsing of an sql to generate an execution plan is very resource-consuming.

The following is a list of the execution of a sql statement.

1. Pass the SQL statement through the hash algorithm to get a value Hash_Value

two。 If this value exists in memory, it is called hit to perform soft analysis.

3. If the value does not exist, perform hard parsing

4. Perform grammatical analysis

5. Conduct semantic analysis

6 if there is a view, take out the definition of the view

7. Automatically rewrite SQL statements, such as changing subqueries to connections

8. Select the best implementation plan

9. Binding of variables

10. Run the execution plan

11. Return the result to the user

If it is a soft analysis, run the steps after 9 directly.

Hit rate of shared pool

Select namespace,pins,pinhits,reloads,invalidations from v$librarycache order by namespace

NAMESPACE PINS PINHITS RELOADS INVALIDATIONS

--

BODY 29530 29446 0 0

CLUSTER 429 421 0 0

INDEX 60 3 0 0

JAVA DATA 0 0 0

JAVA RESOURCE 0 0 0

JAVA SOURCE 0 0 0

OBJECT 0 0 0

PIPE 0 0 0

SQL AREA 134280 128465 226 32

TABLE/PROCEDURE 63727 59391 4 0

TRIGGER 2375 2356 0 0

11 lines have been selected.

SQL > desc v$librarycache

Name Null Type

-

NAMESPACE VARCHAR2 (15)

GETS NUMBER can be understood as the number of lookups when an object is parsed (parsing phase)

Number of GETHITS NUMBER get hits

The value of GETHITRATIO NUMBER equals gethits/gets

The number of times PINS NUMBER has been executed after an object parsing (occurs during the execution phase)

Number of PINHITS NUMBER pin hits

The value of PINHITRATIO NUMBER equals pinhits/pins

RELOADS NUMBER the number of times an object has been reloaded after parsing (the object needs to be read from disk again), that is, it is not cached in the library cache, usually because the shared pool is too small

INVALIDATIONS NUMBER an object is not valid, usually because the definition of the object has been changed, it needs to be parsed again

DLM_LOCK_REQUESTS NUMBER

DLM_PIN_REQUESTS NUMBER

DLM_PIN_RELEASES NUMBER

DLM_INVALIDATION_REQUESTS NUMBER

DLM_INVALIDATIONS NUMBER

Check that the total library cache pinhitratio should be greater than 90%, ideally greater than 95%

Hit rate since the instance was launched

Select sum (pinhits) / sum (pins) from v$librarycache

SUM (PINHITS) / SUM (PINS)

--

. 955756135

You can view the shared_pool recommended size according to the following view

Select * from v$shared_pool_advice

If the hit rate of SQL is less than 90%, we need to optimize it. The optimization methods are as follows:

1. Increase the size of shared_pool_size, v$shared_pool_advice according to this view

two。 Use variables instead of constants when writing programs

3. Set the large package in memory

4. Modify initialization parameter cursor_sharing

A.Force is an ideal time to use, if your business logic is very clear and the application design is very good, then you can use FORCE. I set it to FORCE for some small projects, which can reduce the cost of shared_pool. This setting is not recommended in 9i. It is not recommended in versions before 10.2.0.3. There are a lot of bug.

B.EXACE is a parsing method for precisely matching variables. In this mode, if aquired 1 and 2 are written in the where condition of a sql query, the optimizer will generate a new execution plan instead of thinking that it is always sql, which takes up a serious shared_pool ratio. Versions later than 10.2.0.3 are not recommended.

C.SIMILAR is a compromise, and letting the optimizer make its own judgment is the preferred way for Oracle, but no matter it is rule-based optimizer or cost-based optimizer, it is not very good at present. Similar currently has more bug than FORCE.

The experiment is carried out as follows:

Conn scott/tiger

Create table T1 as select * from emp

Insert into T1 select * from T1

/

/

Commt

Update t1 set empno=1000

Commit

Update t1 set empno=2000 where rownum=1

Commit

Create index i_t1 on T1 (empno)

/ / analyze the table and tell the database the size of the table

Analyze table t1 compute statistics

/ / analyze the columns, telling the database that the data distribution of the empno columns is uneven, with only one row being 2000 and all other behaviors 1000

Analyze table t1 compute statistics for columns empno

Show parameter cursor_sharing

Exact

SQL > set autot traceonly explain

SQL > select * from scott.t1 where empno=1000

Carry out the plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 229K | 6943K | 320 (5) | 00:00:04 |

| | * 1 | TABLE ACCESS FULL | T1 | 229K | 6943K | 320 (5) | 00:00:04 |

SQL > select * from scott.t1 where empno=2000

Carry out the plan

Plan hash value: 4068921349

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 31 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 31 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0) | 00:00:01 |

Experimental SIMILAR

Conn / as sysdba

Alter system set cursor_sharing=SIMILAR scope=spfile

Startup force

Set autotrace traceonly

Show parameter cursor_sharing

SIMILAR

SQL > set autot traceonly explain

SQL > select * from scott.t1 where empno=1000

Carry out the plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 229K | 6943K | 320 (5) | 00:00:04 |

| | * 1 | TABLE ACCESS FULL | T1 | 229K | 6943K | 320 (5) | 00:00:04 |

SQL > select * from scott.t1 where empno=2000

Carry out the plan

Plan hash value: 4068921349

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 31 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 31 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0) | 00:00:01 |

Experimental FORCE

Conn / as sysdba

Alter system set cursor_sharing=FORCE scope=spfile

Startup force

Set autotrace traceonly

Show parameter cursor_sharing

FORCE

SQL > set autot traceonly explain

SQL > select * from scott.t1 where empno=1000

Carry out the plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 229K | 6943K | 320 (5) | 00:00:04 |

| | * 1 | TABLE ACCESS FULL | T1 | 229K | 6943K | 320 (5) | 00:00:04 |

SQL > select * from scott.t1 where empno=2000

Carry out the plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 229K | 6943K | 320 (5) | 00:00:04 |

| | * 1 | TABLE ACCESS FULL | T1 | 229K | 6943K | 320 (5) | 00:00:04 |

Erroneous execution plan

A summary of the experiment:

Forced matching (FORCE) treats all where conditions as variables, which improves the hit rate of SQL, but can not distinguish the data sensitivity of column values, which will lead to

The execution plan of the sub-sql statement is not correct.

Approximate matching (SIMILAR) treats all where conditions as variables, which improves the hit rate of SQL, but can distinguish the data sensitivity of column values, which ensures

The reuse of statements can improve the hit rate, and can distinguish the conditional differences of columns. But oralce sometimes has bug, which causes good things to become

It came to nothing. So we must observe the performance after we change it.

Exact matching (EXACT) does not process the original statement, which reduces the hit rate of SQL, but ensures that the execution plan is correct. Exact match is the default value.

On how to analyze the sql hit rate of shared_pool is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.