[20200819]12c Global Temporary table 統計資訊的收集的疑問.txt
[20200819]12c Global Temporary table 統計資訊的收集的疑問.txt
Prior to Oracle 12c the database don't maintain separate statistics for global temporary table (GTT), the database
maintains one version of statistics shared by all session, even though the data across sessions could differ.
Starting with 12c we can set the table-level preference GLOBAL_TEMP_TABLE_STATS to either shared or session-specific
(global preference default GLOBAL_TEMP_TABLE_STATS to SESSION). Users can gather statistics on GTT and can have own
version of session statistics. During optimization the optimizer first check if session statistics exists if yes, then
make use of them. If not optimizer uses Shared statistics if they exist.
--//我總感覺這項功能不大實用,應用中誰會分析臨時表.除非資料倉儲類的應用.我個人還是建議使用SHARED模式.當然給具體問題具體分
--//析.本文僅僅分析我的一些疑問.
1.測試環境：
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> SELECT DBMS_STATS.get_param ('GLOBAL_TEMP_TABLE_STATS') GLOBAL_TEMP_TABLE_STATS FROM DUAL;
GLOBAL_TEMP_TABLE_STATS
-----------------------
SESSION
--//預設引數設定為SESSION。
2.測試:
--//session 1:
SCOTT@test01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
97 48554 5428:6256 DEDICATED 4912 25 4 alter system kill session '97,48554' immediate;
SCOTT@test01p> create global temporary table T1 (id number, name varchar2(20)) on commit DELETE rows;
Table created.
--//注意定義on commit DELETE rows;
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 SHARED
SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=100;
100 rows created.
SCOTT@test01p> select count(*) from t1;
COUNT(*)
----------
100
SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select count(*) from t1;
COUNT(*)
----------
100
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 SHARED
T1 1 100 SESSION
--//具有on commit DELETE rows的表以前是無法分析的.因為分析前必須隱含一個提交.而GLOBAL_TEMP_TABLE_STATS採用session方式,
--//不會出現資訊丟失的情況,也就是分析前後有不會導致資訊消失.
--//開啟一個新的會話,session 2:
SCOTT@test01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------
96 43472 6464:5472 DEDICATED 5412 41 6 alter system kill session '96,43472' immediate;
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 SHARED
--//另外的會話沒有scope=SESSION的資訊,也就是分析僅僅對當前會話有效.
--//session 1:
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> select count(*) from t1;
COUNT(*)
----------
0
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 SHARED
T1 1 100 SESSION
--//可以發現即使我提交了,記錄數為0,session 的統計資訊還在.
3.10046跟蹤看看:
SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=10;
10 rows created.
SCOTT@test01p> @ 10046on 12
Session altered.
SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SCOTT@test01p> @ 10046off
Session altered.
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 SHARED
T1 1 10 SESSION
SCOTT@test01p> select count(*) from t1;
COUNT(*)
----------
10
--//檢視跟蹤檔案,我自己也沒看出來門道.我估計採用自治事務的形式來實現提交.那位知道?
4.如果修改GLOBAL_TEMP_TABLE_STATS=SHARED:
--//退出,重新登陸:
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 SHARED
SCOTT@test01p> exec dbms_stats.set_table_prefs(user,'T1','GLOBAL_TEMP_TABLE_STATS','SHARED');
PL/SQL procedure successfully completed.
SCOTT@test01p> SELECT DBMS_STATS.GET_PREFS ('GLOBAL_TEMP_TABLE_STATS','SCOTT','T1') GLOBAL_TEMP_TABLE_STATS FROM DUAL;
GLOBAL_TEMP_TABLE_STATS
------------------------
SHARED
SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=20;
20 rows created.
SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select count(*) from t1;
COUNT(*)
----------
0
--//在執行分析前暗含一個提交.這樣記錄數量0.
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 0 0 SHARED
--//雖然有統計,但是這樣可能致命的.因為統計記錄是0.很有可能導致執行計劃畸形.
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select * from dept,t1 ;
no rows selected
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8w67v982tdp0c, child number 0
-------------------------------------
select * from dept,t1
Plan hash value: 3780723038
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 0 |00:00:00.01 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 1 | 45 | 5 (0)| 00:00:01 | 0 |00:00:00.01 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 25 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
| 3 | BUFFER SORT | | 0 | 4 | 80 | 3 (0)| 00:00:01 | 0 |00:00:00.01 |
| 4 | TABLE ACCESS FULL | DEPT | 0 | 4 | 80 | 3 (0)| 00:00:01 | 0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
4 - SEL$1 / DEPT@SEL$1
23 rows selected.
--//可以發現優先選擇T1表.像以前這種情況只能偽造統計資訊.或者就是採用動態分析方式.
SCOTT@test01p> exec dbms_stats.set_table_stats(user,'T1',numrows=>100, numblks=>1);
PL/SQL procedure successfully completed.
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
-------------------- ---------- ---------- --------------------
T1 1 100 SHARED
SCOTT@test01p> select * from dept,t1;
no rows selected
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cd332qxynx6dd, child number 0
-------------------------------------
select * from dept,t1
Plan hash value: 3973748831
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 7 |
| 1 | MERGE JOIN CARTESIAN| | 1 | 400 | 18000 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 7 |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
| 3 | BUFFER SORT | | 4 | 100 | 2500 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 100 | 2500 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
4 - SEL$1 / T1@SEL$1
5.最後看看兩個資訊都存在的情況下會選擇那個.
SCOTT@test01p> exec dbms_stats.set_table_prefs(user,'T1','GLOBAL_TEMP_TABLE_STATS','SESSION');
PL/SQL procedure successfully completed.
SCOTT@test01p> insert into T1 select rownum id,'test' name from dual connect by level <=200;
200 rows created.
SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SCOTT@test01p> SELECT table_name, blocks, num_rows, scope,last_analyzed FROM user_tab_statistics WHERE table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE LAST_ANALYZED
-------------------- ---------- ---------- -------------------- -------------------
T1 1 100 SHARED 2020-08-27 22:16:24
T1 1 200 SESSION 2020-08-27 22:17:51
SCOTT@test01p> select count(*) from t1;
COUNT(*)
----------
200
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 3724264953
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 200 | 2 (0)| 00:00:01 | 200 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Note
-----
- Global temporary table session private statistics used
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--//優先使用session的統計.不過有點奇怪的是:
SCOTT@test01p> select count(*) from t1 where id=1;
COUNT(*)
----------
1
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fmfby2uppmz68, child number 0
-------------------------------------
select count(*) from t1 where id=1
Plan hash value: 3724264953
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 4 | | | 1 |00:00:00.01 | 3 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 4 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=1)
Note
-----
- Global temporary table session private statistics used
--//這裡E-ROWS=1,搞不懂....估計其它資訊比如欄位資訊沒有匯入.
6.總結:
--//12c之後注意臨時表的收集方式,我看了生產系統我一般選擇定義on commit preserve rows;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2715672/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- GLOBAL TEMPORARY TABLE(轉)
- [20200120]12c線上統計收集問題.txt
- [20230227][20230109]Oracle Global Temporary Table ORA-01555 and Undo Retention.tOracle
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- 手動收集——收集統計資訊
- [20190524]DISABLE TABLE LOCK(12c).txt
- 收集統計資訊方案
- Oracle收集統計資訊Oracle
- [20221014]資料檔案2的小疑問.txt
- Oracle統計資訊的收集和維護Oracle
- 【TABLE】Oracle表資訊收集指令碼Oracle指令碼
- [20190401]那個更快的疑問.txt
- 收集全庫統計資訊
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- 6 收集資料庫統計資訊資料庫
- [20210910]table scan相關統計.txt
- [20210926]並行執行計劃疑問.txt並行
- [20191209]降序索引疑問.txt索引
- [20220308]查詢x$ksmmem遇到的疑問.txt
- [20190510]rman備份的疑問8.txt
- [20190510]rman備份的疑問7.txt
- [20190509]rman備份的疑問5.txt
- [20190423]那個更快的疑問3.txt
- oracle 統計資訊檢視與收集Oracle
- [20190622]收集SQLNet Message From Client資訊.txtSQLclient
- [20181024]修改awr收集資訊設定.txt
- [20220304]測試library cache mutex遇到的疑問.txtMutex
- [20190411]linux stat 命令疑問.txtLinux
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- Nebula Graph 特性講解——RocksDB 統計資訊的收集和展示
- Oracle運維指令碼-收集統計資訊Oracle運維指令碼
- [20180322]檢視統計資訊的儲存歷史.txt
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20201207]關於ORACLE IMU的一些疑問.txtOracle
- 微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢SQL
- [20191209]降序索引疑問2.txt索引
- [20200303]降序索引疑問5.txt索引