Oracle Statistics no_invalidate parameter configuration (bottom)

Reprint: http://blog.itpub.net/17203031/viewspace-1067620/

In this article we continue to discuss the no_invalidate parameters.

From the discussion in the previous article (http://blog.itpub.net/17203031/viewspace-1067312/), whether the value of true or false,oracle behavior is a lack of consideration. If you choose True to indicate that the old execution plan will continue to reside in the shared pool, the new execution plan will not be generated, and if the system SQL is running more frequently and the age out is less, better execution of the plan may not occur.

The other extreme is false, and Oracle sets all the shared pool involved in the new statistic to fail at once. The benefit is that the build of the plan is guaranteed to be better, but there is also a performance spike phenomenon. Usually the collection of statistics is a centralized work process, that is, most business data tables are usually the same time the statistical generation process. If set to false, it means that in a short time, most of the shared cursor in the Oracle shared pool is invalidated and the execution plan is rebuilt. In this way, there will be an overall hard parse peak, and severe words will affect the operation of the business.

4 , no_invalidate=dbms_stats.auto_invalidate

In response to this dilemma, Oracle 10g introduces the parameter dbms_stats.auto_invalidate as the default value for No_invalidate. From the official explanation, the job of this parameter is "let Oracle decide whether to fail the shared cursor." So, what are the algorithmic principles? Let's discuss the value-taking process in this article.

The principle of the auto_invalidate process is to avoid the extreme cases of true and false, both to achieve the generation of the new execution plan and to avoid the appearance of performance Spike. The strategy chosen by Oracle is "time-lapse", which means that shared cursors are not invalidated at a time, but "slowly" and "differentiated". This avoids spike in the hard parse process.

The shared cursor failure principle is as follows in the case of a Auto_invalidate value for statistical collection:

ü When the statistics of a new object are obtained, the shared cursor object with which it is dependent is not a one-time failure but is annotated. In Oracle, it is called "rolling invalidation";

ü When the second SQL is parsed, the timestamp information is logged. This timestamp is compared with the implicit parameter "_optimizer_invalidation_period" + a random time-second number in the system. If the time difference does not exceed this setting, the second SQL will still use the previous shared cursor. is still a soft parsing process;

ü When a SQL parsing process, the set time exceeds the time interval. Oracle initiates a hard parsing process to generate a new child cusor execution plan. The original child cursor is marked as roll_invalidate and is invalidated. We can see through the view v$sql_shared_cursor;

From the Auto_invalidate rule, Oracle does not fail the shared cursor, but instead disperses it in a time range, with the implied parameter "_optimizer_invalidation_period" to control the start of the time range. This method is used to alleviate the performance spike phenomenon caused by hard parsing.

Let's try to prove the conclusion by experiment. To prevent 11g adaptive cursor effects, we chose a simple 10g version for testing.

Sql> select * from V$version;

BANNER

-----------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod

PL/SQL Release 10.2.0.1.0-production

CORE 10.2.0.1.0 Production

TNS for Linux:version 10.2.0.1.0-production

Nlsrtl Version 10.2.0.1.0–production

The default parameter value is Dbms_stats.no_invalidate.

Sql> Select Dbms_stats.get_param (' No_invalidate ') from dual;

Dbms_stats. Get_param (' No_inval

-------------------------------------------------

Dbms_stats. Auto_invalidate

The default implied parameter value is 18000s, which is 5 hours.

Sql> Select X.ksppinm Name,

2 Y.KSPPSTVL value,

3 Y.KSPPSTDF IsDefault,

4 Decode (Bitand (Y.KSPPSTVF, 7),

5 1,

6 ' MODIFIED ',

7 4,

8 ' System_mod ',

9 ' FALSE ') Ismod,

Ten decode (Bitand (Y.KSPPSTVF, 2), 2, ' TRUE ', ' FALSE ') Isadj

One from Sys.x$ksppi x, SYS.X$KSPPCV y

where x.inst_id = Userenv (' Instance ')

y.inst_id = Userenv (' Instance ')

X.indx = Y.indx

and x.ksppinm like ' _optimizer_invalidation_period ';

NAME VALUE ISDEFAULT ismod Isadj

------------------------------ ---------- --------- ---------- -----

_optimizer_invalidation_period 18000 TRUE false false

For the sake of experimentation, we will set this time period slightly shorter.

Sql> alter system set "_optimizer_invalidation_period" = 300;

System Altered

Create experimental data table T for related settings and first-time statistics collection.

Sql> CREATE TABLE T as select * from Dba_objects;

Table created

Sql> CREATE index idx_t_id on T (object_id);

Index created

sql> exec dbms_stats.gather_table_stats (user, ' T ', cascade = TRUE);

PL/SQL procedure successfully completed

Sql> Select To_char (last_analyzed, ' Yyyy-mm-dd hh24:mi:ss ') from Dba_tables where table_name= ' T ';

To_char (last_analyzed, ' yyyy-mm

------------------------------

2014-01-06 10:13:57

The first time we execute the SQL statement, we still use the Autotrace platform, and the result set is omitted.

Sql> Set Autotrace traceonly stat

Sql> Select/*+demo*/* from t where object_id=1000;

Statistical information

---------------------------------------------------------

381 Recursive calls

0 db Block gets

Consistent gets

Rows processed

The Shared cursor situation is as follows:

Sql> Select sql_id, executions, Version_count, first_load_time from V$sqlarea where Sql_text like ' select/*+demo*/* fr Om t% ';

sql_id executions Version_count First_load_time

------------- ---------- ------

4RW3PYSKDGQTC 1 1 2014-01-06/10:16:55

form the first cursor share and execute once. The second time SQL is executed, the cursor is shared.

Sql> Select sql_id, executions, Version_count, First_load_time, To_char (Last_load_time, ' yyyy-mm-dd hh24:mi:ss ') From V$sqlarea where Sql_text like ' select/*+demo*/* from t% ';

sql_id executions Version_count first_load_time to_char (last_load_time, ' yyyy-m

------------- ---------- ------------- -------------------- ------------------------------

4RW3PYSKDGQTC 2 1 2014-01-06/10:16:55 2014-01-06 10:16:55

The execution plan at this time is as follows:

Sql> select * FROM table (dbms_xplan.display_cursor (' 4RW3PYSKDGQTC '));

Plan_table_output

--------------------------------------------------------------------------------

sql_id 4RW3PYSKDGQTC, child number 0

-------------------------------------

Select/*+demo*/* from T where object_id=1000

Plan Hash value:514881935

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

| 0 |          SELECT STATEMENT |       |       |     | 2 (100) |

|  1 | TABLE ACCESS by INDEX rowid|     T |    1 |     93 | 2 (0) | 00

|* 2 | INDEX RANGE SCAN |     idx_t_id |       1 |     | 1 (0) | 00

--------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("object_id" =1000)

Rows selected

Executes the index Range scan path. In view v$sql_shared_cursor, there is shared information. Modify the data distribution below to change the layout.

Sql> update T set object_id=1000;

49745 rows Updated

Sql> commit;

Commit Complete

sql> exec dbms_stats.gather_table_stats (user, ' T ', cascade = true,method_opt = ' For columns size object_id ') ;

PL/SQL procedure successfully completed

The default parameter is auto_invalidate. From experience, Oracle only chooses FTS to be the optimal path. Executes the SQL statement for the third time.

Sql> Select/*+demo*/* from t where object_id=1000;

49745 rows have been selected.

Statistical information

----------------------------------------------------------

0 Recursive calls

0 db Block gets

7441 consistent gets

49745 rows processed

At this point, the shared cursor situation is as follows:

Sql> Select sql_id, executions, Version_count, First_load_time, To_char (Last_load_time, ' yyyy-mm-dd hh24:mi:ss ') From V$sqlarea where Sql_text like ' select/*+demo*/* from t% ';

sql_id executions Version_count first_load_time to_char (last_load_time, ' yyyy-m

------------- ---------- ------------- -------------------- ------------------------------

4RW3PYSKDGQTC 3 1 2014-01-06/10:16:55 2014-01-06 10:16:55

The third execution still uses the original index Range scan execution plan, with no new parent-child cursor object generation and an increase in the number of executions.

It will be executed for the fourth time in a second.

10:23:44 sql> Select/*+demo*/* from t where object_id=1000;

49745 rows have been selected.

Statistical information

--------------------------------

0 Recursive calls

0 db Block gets

7441 consistent gets

0 physical Reads

49745 rows processed

Sql> Select sql_id, executions, Version_count, First_load_time, To_char (Last_load_time, ' yyyy-mm-dd hh24:mi:ss ') From V$sqlarea where Sql_text like ' select/*+demo*/* from t% ';

sql_id executions Version_count first_load_time to_char (last_load_time, ' yyyy-m

------------- ---------- ------------- -------------------- ------------------------------

4RW3PYSKDGQTC 4 1 2014-01-06/10:16:55 2014-01-06 10:16:55

After the fourth execution, Oracle still does not invalidate the cursor. After three or four minutes, different effects are performed.

10:23:51 sql> Select/*+demo*/* from t where object_id=1000;

49745 rows have been selected.

Statistical information

-------------------------------------

173 Recursive calls

0 db Block gets

3987 consistent gets

49745 rows processed

10:27:16 sql>

Cursor sharing occurs as follows:

Sql> Select sql_id, executions, Version_count, First_load_time, To_char (Last_load_time, ' yyyy-mm-dd hh24:mi:ss ') From V$sqlarea where Sql_text like ' select/*+demo*/* from t% ';

sql_id executions Version_count first_load_time to_char (last_load_time, ' yyyy-m

------------- ---------- ------------- -------------------- ------------------------------

4RW3PYSKDGQTC 5 2 2014-01-06/10:16:55 2014-01-06 10:27:11

A new child cursor object is formed, and a new parsing action occurs. View the V$sql_shared_cursor view to see the changes.

Sql> Select sql_id, Child_number,roll_invalid_mismatch from v$sql_shared_cursor where sql_id= ' 4RW3PYSKDGQTC ';

sql_id Child_number Roll_invalid_mismatch

------------- ------------ ---------------------

4RW3PYSKDGQTC 0 N

4RW3PYSKDGQTC 1 Y

Child Cursor No. 0 was denied sharing due to roll invalidate. The cursor 1 information is as follows:

Sql> Select Child_number, executions, First_load_time, last_load_time from V$sql where sql_id= ' 4RW3PYSKDGQTC ';

Child_number executions First_load_time Last_load_time

------------ ---------- -------------------- --------------------------------------------------------------------- -------

0 4 2014-01-06/10:16:55 2014-01-06/10:16:55

1 1 2014-01-06/10:16:55 2014-01-06/10:27:11

The child cursors 1 and 0 represent different execution plans, respectively.

Sql> select * FROM table (dbms_xplan.display_cursor (' 4rw3pyskdgqtc ', ' 1 '));

Plan_table_output

--------------------------------------------------------------------------------

sql_id 4RW3PYSKDGQTC, child number 1

-------------------------------------

Select/*+demo*/* from T where object_id=1000

Plan Hash value:1601196873

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

| 0 |      SELECT STATEMENT |       |       |   |          155 (100) | |

|* 1 | TABLE ACCESS full| T |  49740 |   4420k| 155 (3) | 00:00:02 |

--------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("object_id" =1000)

Rows selected

Sql> select * FROM table (dbms_xplan.display_cursor (' 4RW3PYSKDGQTC ', ' 0 '));

Plan_table_output

--------------------------------------------------------------------------------

sql_id 4RW3PYSKDGQTC, child number 0

-------------------------------------

Select/*+demo*/* from T where object_id=1000

Plan Hash value:514881935

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------

| 0 |          SELECT STATEMENT |       |       |     | 2 (100) |

|  1 | TABLE ACCESS by INDEX rowid|     T |    1 |     93 | 2 (0) | 00

|* 2 | INDEX RANGE SCAN |     idx_t_id |       1 |     | 1 (0) | 00

--------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("object_id" =1000)

Rows selected

From the above experiment, we can conclude that the existing shared cursor will be invalidated after a period of time when the statistic collection takes no_invalidate=dbms_stats.auto_invalidate. Such a strategy avoids centralized hard sparse and ensures the smooth performance of the system.

5 , conclusions

Oracle statistics are critical to the execution plan, and understanding the No_invalidate parameter meanings and settings can help us better understand how Oracle works and design ideas.

Oracle Statistics no_invalidate parameter configuration (bottom)