[20180118]tstats的問題.txt--//關於使用tstats收集處理統計信息，可以看鏈接http://blog.itpub.net/267265/viewspace-1987839/TSTATS in a Nutshell P97The removal of time-sensitiv ...
[20180118]tstats的問題.txt
--//關於使用tstats收集處理統計信息，可以看鏈接http://blog.itpub.net/267265/viewspace-1987839/
TSTATS in a Nutshell P97
The removal of time-sensitive data from object statistics is the main idea behind TSTATS. Here is the essence of
a process that can be used on the back of that idea:
1. Gather statistics on a selected "master" test system.
2. Fabricate statistics for all global temporary tables.
3. Remove time-sensitive data from object statistics.
4. Perform initial performance testing on the master test system and make adjustments as necessary.
5. Copy the statistics to all other test systems and ultimately to production.
6. Lock the object statistics of your application schemas on all systems.
7. Drop all statistics-gathering jobs for application schemas on all your systems.
8. TSTATS only applies to application schemas, so any jobs that gather dictionary statistics are unaffected.
--//突然自己想在一個小的生產系統試驗tstats的思想。我發現自己上存在一些問題。前面我就發現直方圖信息不能刪除。
--//它實際上提供的腳本刪除欄位的最大最小值信息，而不是3. Remove time-sensitive data from object statistics.。
--//實際上刪除最大最小信息直方圖信息會變得無用，通過測試來說明問題。
1.環境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t as select rownum id ,lpad('x',32,'x') name ,'Y' flag from dual connect by level<=1e5;
update t set flag='N' where id=1e5;
commit ;
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR
ALL COLUMNS SIZE 1 for columns flag size 254 ',Cascade => True ,No_Invalidate => false);
2.測試1：
SCOTT@book> select * from t where flag='N';
ID NAME F
---------- -------------------- -
100000 xxxxxxxxxxxxxxxxxxxx N
xxxxxxxxxxxx
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0h7g0tqtzcvzn, child number 0
-------------------------------------
select * from t where flag='N'
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 40 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
--//可以發現使用直方圖。
3.測試2：
--//如果刪除最大最小值：
SCOTT@book> exec system.tstats.adjust_column_stats_v3( 'SCOTT','T');
PL/SQL procedure successfully completed.
SCOTT@book> select * from dba_histograms where owner=user and table_name='T';
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
------ ---------- -------------------- --------------- -------------- ----------
SCOTT T FLAG 100000 4.6211E+35
SCOTT T FLAG 1 4.0500E+35
SCOTT T ID 0
SCOTT T NAME 0
SCOTT T ID 1
SCOTT T NAME 1
6 rows selected.
SCOTT@book> select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM from dba_tab_cols where owner=user and table_name='T';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS HISTOGRAM
-------------------- ---------- ----------- -------------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- ---------------
ID NUMBER 22 100000 .00001 0 1 NONE
NAME VARCHAR2 32 1 1 0 1 NONE
FLAG CHAR 1 2 .5 0 2 FREQUENCY
--//可以發現直方圖信息還存在。但是最大最小值信息已經刪除。
SCOTT@book> select * from t where flag='N';
ID NAME F
---------- -------------------- -
100000 xxxxxxxxxxxxxxxxxxxx N
xxxxxxxxxxxx
--//執行計劃：
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 177 (100)| |
|* 1 | TABLE ACCESS FULL| T | 50000 | 1953K| 177 (1)| 00:00:03 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='N')
--//執行計劃變成全表掃描。也就是直方圖存在的情況下，最大最小值信息不能刪除。
3.實際上真正的情況也許更複雜，我舉一個生產系統遇到的問題。
[email protected]:1521/zzzzz> @ &r/sqlid 22h1xj9x22jf6
SQL_ID SQLTEXT
------------- ---------------------------------------------------------------------------------------
22h1xj9x22jf6 SELECT COUNT ( *) FROM ZY_ZYJS A WHERE A.JZRQ IS NULL AND A.CZGH =:1 AND A.JSRQ < :2
1 row selected.
[email protected]:1521/zzzzz> @ bind_cap 22h1xj9x22jf6 ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -------------------------------
22h1xj9x22jf6 0 YES :1 1 32 2018-01-17 12:05:36 CHAR(32) 829
YES :2 2 7 2018-01-17 12:05:36 DATE 2018/01/01 00:00:00
--//我們系統存在2個索引。czgh+JZRQ(操作工號+結帳日期)是一個複合索引，JSRQ(結算日期)是一個索引。明顯使用JSRQ不好，因為
--//小於2018/1/1的數據很多。而實際的執行計劃是：(在使用tstats包處理後)
Plan hash value: 2747456857
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.01 | 20 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 21 | | | 1 |00:00:00.01 | 20 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 1 | 21 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 20 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 0 |00:00:00.01 | 20 | 1368K| 1368K| 1649K (0)|
|* 4 | INDEX RANGE SCAN| IDX_ZY_ZYJS_JSRQ | 1 | 1 | 21 | 3 (34)| 00:00:01 | 5356 |00:00:00.01 | 14 | | | |
|* 5 | INDEX RANGE SCAN| I_ZY_ZYJS_CZGH_JZRQ | 1 | 1 | 21 | 4 (25)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
--//恢復統計信息。
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'XXXXX'
,TabName => 'ZY_ZYJS'
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR ALL COLUMNS SIZE REPEAT '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
[email protected]:1521/zzzzz> SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, HISTOGRAM
FROM dba_tab_cols
WHERE table_name = 'ZY_ZYJS'
AND column_name = 'JSRQ';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS HISTOGRAM
-------------------- ---------- ----------- -------------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- ---------------
JSRQ DATE 7 5606 78740B0C10 7876011209 .00017838 0 1 NONE
012C 1017
1 row selected.
--//執行計劃變成了。
Plan hash value: 3335725722
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 1 |00:00:00.01 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 | 21 | | | 1 |00:00:00.01 | 5 |
|* 2 | TABLE ACCESS BY INDEX ROWID| ZY_ZYJS | 1 | 16 | 336 | 8 (0)| 00:00:01 | 0 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | I_ZY_ZYJS_CZGH_JZRQ | 1 | 17 | | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------------------------------------------------
--//所以講沒有一成不變的優化方案，必須綜合分析。可惜國內大部分dba很少把精力放在優化sql語句上，我自己也一樣....
4.附上tstats的源代碼，我做了許多修改：
--//註：我一般建立在system用戶,另外要保證編譯通過需要建立一張無用的表：
CREATE TABLE SAMPLE_PAYMENTS
(
PAYGRADE INTEGER,
PAYMENT_DATE DATE,
JOB_DESCRIPTION CHAR(20 BYTE)
);
--//我自己懶的修改代碼，我還加入一個過程adjust_column_stats_v4，在保留直方圖信息以及最大最小值，也就是存在直方圖，不要修改欄位信息。
--//使用這個包還是要小心!!
CREATE OR REPLACE PACKAGE tstats AUTHID CURRENT_USER
AS
PROCEDURE adjust_column_stats_v1 (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE);
PROCEDURE adjust_column_stats_v2 (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE);
PROCEDURE adjust_column_stats_v3 (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE);
PROCEDURE adjust_column_stats_v4 (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE);
PROCEDURE amend_time_based_statistics (
effective_date DATE DEFAULT SYSDATE);
PROCEDURE adjust_global_stats (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE
,p_mode VARCHAR2 DEFAULT 'PMOP');
PROCEDURE gather_table_stats (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE);
PROCEDURE set_temp_table_stats (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE
,p_numrows INTEGER DEFAULT 20000
,p_numblks INTEGER DEFAULT 1000
,p_avgrlen INTEGER DEFAULT 400);
PROCEDURE import_table_stats (
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_table_name all_tab_col_statistics.table_name%TYPE
,p_statown all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT (
'USERENV'
,'CURRENT_SCHEMA')
,p_stat_table all_tab_col_statistics.table_name%TYPE);
END tstats;
/
CREATE OR REPLACE PACKAGE BODY SYSTEM.tstats
AS
FUNCTION get_srec
RETURN DBMS_STATS.statrec
IS
srec DBMS_STATS.statrec;
BEGIN
/*
Workaround for issue in 12.1.0.1
that produces wrong join cardinality
when both tables have NULL for high
and low values. As a workaround this
function sets the high value very high
and the low value very low.
*/
$IF DBMS_DB_VERSION.version >= 12
$THEN
srec.epc := 2; -- Two endpoints
srec.bkvals := NULL; -- No histogram
DBMS_STATS.prepare_column_values
(
srec
,DBMS_STATS.rawarray
(
HEXTORAW
(
-- Min
'0000000000000000000000000000000000000000000000000000000000000000'
)
-- Max
,HEXTORAW
(
'ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
)
)
);
RETURN srec;
$ELSE
RETURN NULL;
$END
END get_srec;
PROCEDURE adjust_column_stats_v1
(
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT
(
'USERENV'
,'CURRENT_SCHEMA'
)
,p_table_name all_tab_col_statistics.table_name%TYPE
)
AS
CURSOR c1
IS
SELECT *
FROM all_tab_col_statistics
WHERE owner = p_owner
AND table_name = p_table_name
AND last_analyzed IS NOT NULL;
BEGIN
FOR r IN c1
LOOP
DBMS_STATS.delete_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,cascade_parts => TRUE
,no_invalidate => TRUE
,force => TRUE
);
DBMS_STATS.set_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,distcnt => r.num_distinct
,density => r.density
,nullcnt => r.num_nulls
,srec => get_srec -- No HIGH_VALUE/LOW_VALUE
,avgclen => r.avg_col_len
,no_invalidate => FALSE
,force => TRUE
);
END LOOP;
END adjust_column_stats_v1;
PROCEDURE adjust_column_stats_v2
(
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT
(
'USERENV'
,'CURRENT_SCHEMA'
)
,p_table_name all_tab_col_statistics.table_name%TYPE
)
AS
CURSOR c1
IS
SELECT *
FROM all_tab_col_statistics
WHERE owner = p_owner
AND table_name = p_table_name
AND last_analyzed IS NOT NULL;
v_num_distinct all_tab_col_statistics.num_distinct%TYPE;
BEGIN
FOR r IN c1
LOOP
DBMS_STATS.delete_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,cascade_parts => TRUE
,no_invalidate => TRUE
,force => TRUE
);
IF r.num_distinct = 1
THEN
v_num_distinct := 1 + 1e-14;
ELSE
v_num_distinct := r.num_distinct;
END IF;
DBMS_STATS.set_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,distcnt => v_num_distinct
,density => 1 / v_num_distinct
,nullcnt => r.num_nulls
,srec => get_srec -- No HIGH_VALUE/LOW_VALUE
,avgclen => r.avg_col_len
,no_invalidate => FALSE
,force => TRUE
);
END LOOP;
END adjust_column_stats_v2;
-- 保留直方圖信息
PROCEDURE adjust_column_stats_v3
(
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT
(
'USERENV'
,'CURRENT_SCHEMA'
)
,p_table_name all_tab_col_statistics.table_name%TYPE
)
AS
CURSOR c1
IS
SELECT *
FROM all_tab_col_statistics
WHERE owner = p_owner
AND table_name = p_table_name
AND last_analyzed IS NOT NULL;
v_num_distinct all_tab_col_statistics.num_distinct%TYPE;
z_distcnt NUMBER;
z_density NUMBER;
z_nullcnt NUMBER;
z_srec DBMS_STATS.statrec;
z_avgclen NUMBER;
BEGIN
FOR r IN c1
LOOP
DBMS_STATS.get_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,distcnt => z_distcnt
,density => z_density
,nullcnt => z_nullcnt
,srec => z_srec
,avgclen => z_avgclen
);
DBMS_STATS.delete_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,cascade_parts => TRUE
,no_invalidate => TRUE
,force => TRUE
);
z_srec.minval := NULL;
z_srec.maxval := NULL;
IF r.num_distinct = 1
THEN
v_num_distinct := 1 + 1e-14;
ELSE
v_num_distinct := r.num_distinct;
END IF;
IF r.num_distinct <> 0
THEN
DBMS_STATS.set_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,distcnt => v_num_distinct
,density => 1 / v_num_distinct
,nullcnt => r.num_nulls
,srec => z_srec -- No HIGH_VALUE/LOW_VALUE
,avgclen => r.avg_col_len
,no_invalidate => FALSE
,force => TRUE
);
END IF;
END LOOP;
END adjust_column_stats_v3;
-- 保留直方圖信息以及最大最小值，也就是存在直方圖，不要修改欄位信息。
PROCEDURE adjust_column_stats_v4
(
p_owner all_tab_col_statistics.owner%TYPE DEFAULT SYS_CONTEXT
(
'USERENV'
,'CURRENT_SCHEMA'
)
,p_table_name all_tab_col_statistics.table_name%TYPE
)
AS
CURSOR c1
IS
SELECT *
FROM all_tab_col_statistics
WHERE owner = p_owner
AND table_name = p_table_name
AND histogram='NONE'
AND last_analyzed IS NOT NULL ;
v_num_distinct all_tab_col_statistics.num_distinct%TYPE;
z_distcnt NUMBER;
z_density NUMBER;
z_nullcnt NUMBER;
z_srec DBMS_STATS.statrec;
z_avgclen NUMBER;
BEGIN
FOR r IN c1
LOOP
DBMS_STATS.get_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,distcnt => z_distcnt
,density => z_density
,nullcnt => z_nullcnt
,srec => z_srec
,avgclen => z_avgclen
);
DBMS_STATS.delete_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,cascade_parts => TRUE
,no_invalidate => TRUE
,force => TRUE
);
z_srec.minval := NULL;
z_srec.maxval := NULL;
IF r.num_distinct = 1
THEN
v_num_distinct := 1 + 1e-14;
ELSE
v_num_distinct := r.num_distinct;
END IF;
IF r.num_distinct <> 0
THEN
DBMS_STATS.set_column_stats
(
ownname => r.owner
,tabname => r.table_name
,colname => r.column_name
,distcnt => v_num_distinct
,density => 1 / v_num_distinct
,nullcnt => r.num_nulls
,srec => z_srec -- No HIGH_VALUE/LOW_VALUE
,avgclen => r.avg_col_len
,no_invalidate => FALSE
,force => TRUE
);
END IF;
END LOOP;
END adjust_column_stats_v4;
PROCEDURE amend_time_based_statistics
(
effective_date DATE DEFAULT SYSDATE
)
IS
distcnt NUMBER;
density NUMBER;
nullcnt NUMBER;
srec DBMS_STATS.statrec;
avgclen NUMBER;
BEGIN
--
-- Step 1: Remove data from previous run
--
DELETE FROM sample_payments;
--
