Oracle locks the temporary table statistics and whether the hint can be dynamically collected after the lock

The statistical information of a global temporary table cannot be collected. If it is collected, its statistical information must be 0, which will lead to inaccurate execution plans. Therefore, you must lock the statistical information of the global temporary table and disable the system.

The statistical information of a global temporary table cannot be collected. If it is collected, its statistical information must be 0, which will lead to inaccurate execution plans. Therefore, you must lock the statistical information of the global temporary table and disable the system.

The statistical information of the global temporary table cannot be collected. If it is collected, its statistical information must be 0, which will lead to inaccurate execution plans. Therefore, the statistical information of the global temporary table must be locked, disable Automatic Collection.

-- First unlock the statistical information of the table, then delete the statistical information of the table, and finally lock the statistical information of the table.

Declare

V_sqlvarchar2 (500 );

Cursor rowList1 is

Select 'In in dbms_stats.unlock_table_stats (user, ''' | table_name | '''); end ;'

From user_tables s

Where s. temporary = 'y ';

Cursor rowList2 is

Select 'In in dbms_stats.delete_table_stats (user, ''' | table_name | '''); end ;'

From user_tables s

Where s. temporary = 'y ';

Cursor rowList3 is

Select 'In in dbms_stats.lock_table_stats (user, ''' | table_name | '''); end ;'

From user_tables s

Where s. temporary = 'y ';

Begin

Open rowList1;

Open rowList2;

Open rowList3;

Loop

Fetch rowList1 into v_ SQL;

Executeimmediate v_ SQL;

Exitwhen rowList1 % notfound;

Endloop;

Loop

Fetch rowList2 into v_ SQL;

Executeimmediate v_ SQL;

Exitwhen rowList2 % notfound;

Endloop;

Loop

Fetch rowList3 into v_ SQL;

Executeimmediate v_ SQL;

Exitwhen rowList3 % notfound;

Endloop;

Close rowList1;

Close rowList2;

Close rowList3;

End;

-- STATTYPE_LOCKED = 'all' indicates that the statistical information of the table is locked.

Select s. table_name, s. STATTYPE_LOCKED from user_TAB_STATISTICS s where s. STATTYPE_LOCKED = 'all ';

-- When the statistical information of the table is locked, whether the hint dynamically collected is effective or not. The experimental result is that the hint is effective.

SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> exec dbms_stats.lock_table_stats (user, 'test ');
SQL> select s. num_rows, s. last_analyzed
From user_tables s
Where s. table_name = 'test ';
NUM_ROWS LAST_ANALYZED
------------------------

-- The table is locked.
SQL> select s. STATTYPE_LOCKED from user_TAB_STATISTICS s
Where s. table_name = 'test ';
STATTYPE_L
----------
ALL
SQL> select count (*) from test;
COUNT (*)
----------
70384
SQL> set autotrace traceonly
SQL> select/* + dynamic_sampling (test 1) */* from test;
Row 70384 has been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 160K | 31M | 199 (3) | 00:00:03 |
| 1 | table access full | TEST | 160K | 31M | 199 (3) | 00:00:03 |
--------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
4 recursive cballs
0 db block gets
5239 consistent gets
0 physical reads
0 redo size
3186713 bytes sent via SQL * Net to client
51949 bytes encoded ed via SQL * Net from client
4694 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70384 rows processed

SQL> select/* + dynamic_sampling (test 5) */* from test;
Row 70384 has been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 9747 | 1970K | 195 (0) | 00:00:03 |
| 1 | table access full | TEST | 9747 | 1970K | 195 (0) | 00:00:03 |
--------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
4 recursive cballs
0 db block gets
5239 consistent gets
0 physical reads
0 redo size
3186713 bytes sent via SQL * Net to client
51949 bytes encoded ed via SQL * Net from client
4694 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70384 rows processed