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

Customers often mention that the execution plan of a SQL has deteriorated, resulting in performance problems, and then ask why the new

Carry out the plan. First of all, it is certain that the sudden emergence of a new implementation plan indicates that sql has reparsed hard (note that hard parsing may not be necessary.

Generate a new execution plan), so why does a good sql need to be reparsed? Today we will list a few common reasons:

1. Automatically collect statistics

In order to ensure the best execution performance of sql, oracle needs to find an optimal execution plan, and the optimizer based on CBO pattern must

To know the latest statistics, such as the number of entries, the number of block, the selection rate of a field, etc., so oracle will run a

A job that automatically collects statistics to collect the latest statistics for tables that have changed by more than 10%. When the collection is complete, of course

To use the new sql, you need to do hard parsing. By default, oracle will not invalid immediately after collecting certain table statistics.

All related cursor, because this is too violent, will cause hard parsing related performance problems, so cleverly designed, when a related sql execution found that a dependent object recently collected statistics, it will randomly type a timestamp, the timestamp is

There is a timestamp within 5 hours. If this timestamp is found in the next sql parsing, it will be compared with the current time. If it is exceeded, hard parsing will be performed immediately, otherwise soft parsing will continue.

two。 There is no qualified child cursor such as bind mismatch (for other reasons, please refer to v$sql_shared_cursor)

When a sql uses a binding variable, ORACLE records the metadata associated with the binding variable when the cursor first hard parses it

It will be checked when it is parsed later, and will be re-parsed if it is found that the type or length of the bound variable does not match. Let's use a small example to verify it:

CREATE TABLE MAOB_T AS SELECT FROM DBA_TABlES

VAR B1 char (20)

EXEC: B1: = 'MAOB'

SELECT COUNT () FROM MAOB_T WHERE TABLE_NAME=:B1

Check the cursor situation

Select sql_id,child_number,first_load_time from V$SQL WHERE SQL_TEXT LIKE'% COUNT%MAOB_T%'

4v22rgk83gjnc 0 2017-12-15 Compact 22 purl 52purl 46

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.