共計 11758 個字符，預計需要花費 30 分鐘才能閱讀完成。
Oracle11g/12c dbms_stat extended stats 實驗的示例分析，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
目的：
1. 測試下 dbms_stats 的 extended stats 包的使用效果，是否可以在選擇率低的情況下，解決 執行計劃 /cardinatlity 基數行不準的問題，
并且是只驗證 extended stats 包的使用效果。:D:
環境：
1. windows 7 64 位
2. oracle 12.2.0.1 64 位
3. 使用 demo 程序的 sales history , SH 用戶 測試。
4. 先手動安裝下 sales history 范例數據
步驟：
1. sales 表先插入 50 行，
2. 分析表，獲得統計信息
3. 插入大量數據，讓統計信息不準
4. 使用 extended status 分析包
5. 對比執行計劃
步驟 1 -2：
alter session set statistics_level = all ;
set linesize 120
show parameter statistics_level ;
NAME TYPE VALUE
———————————— ———————- ——————–
statistics_level string ALL
SQL
insert into sales select * from sales_02 where rownum = 50 ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, SALES
select column_name, num_distinct, histogram from user_tab_columns where table_name = SALES ;
SQL select column_name, num_distinct, histogram from user_tab_columns where table_name = SALES ;
COLUMN_NAM NUM_DISTINCT HISTOGRAM
———- ———— ——————————
PROD_ID 1 FREQUENCY
CUST_ID 50 NONE
TIME_ID 2 NONE
CHANNEL_ID 2 FREQUENCY
PROMO_ID 1 FREQUENCY
QUANTITY_S 1 NONE
AMOUNT_SOL 2 FREQUENCY
select num_rows from user_tables where table_name = SALES ;
SQL select num_rows from user_tables where table_name = SALES ;
NUM_ROWS
———-
50
select count(*) from sales a where amount_sold
(select avg(amount_sold) from sales b
where b.prod_id = a.prod_id
and channel_id = 3 and promo_id = 999 )
and channel_id = 3 and promo_id = 999 ;
COUNT(*)
———-
309
已用時間: 00: 00: 00.02
SQL
select * from table(dbms_xplan.display_cursor(NULL,NULL, allstats last)) ;
PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 18vj1zs6jut5g, child number 0
————————————-
select count(*) from sales a where amount_sold (select
avg(amount_sold) from sales b where b.prod_id = a.prod_id and
channel_id = 3 and promo_id = 999 ) and channel_id = 3 and promo_id =
999
Plan hash value: 1265065521
—————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 14 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 14 |
|* 2 | FILTER | | 1 | | 0 |00:00:00.01 | 14 |
|* 3 | TABLE ACCESS FULL | SALES | 1 | 48 | 49 |00:00:00.01 | 7 |
| 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 5 | TABLE ACCESS FULL| SALES | 1 | 48 | 49 |00:00:00.01 | 7 |
—————————————————————————————-
CHANNEL_ID 2 FREQUENCY
PROMO_ID 1 FREQUENCY
NUM_ROWS
———-
50
計算 cardinality
1/2 * 1 * 50 = 25 ,
基數算出來是 25
步驟 3：
SQL select count(*) from sales ;
COUNT(*)
———-
2756579
再次執行 sql，并看執行計劃：
select count(*) from sales a where amount_sold
(select avg(amount_sold) from sales b
where b.prod_id = a.prod_id
and channel_id = 3 and promo_id = 999 )
and channel_id = 3 and promo_id = 999 ;
select * from table(dbms_xplan.display_cursor(NULL,NULL, allstats last)) ;
PLAN_TABLE_OUTPUT
——————————————————————————————
SQL_ID 18vj1zs6jut5g, child number 0
————————————-
select count(*) from sales a where amount_sold (select
avg(amount_sold) from sales b where b.prod_id = a.prod_id and
channel_id = 3 and promo_id = 999 ) and channel_id = 3 and promo_id =
999
Plan hash value: 1265065521
—————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
——————————————————————————————
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:53.45 | 2336K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:53.45 | 2336K|
|* 2 | FILTER | | 1 | | 709K|00:01:53.33 | 2336K|
|* 3 | TABLE ACCESS FULL | SALES | 1 | 48 | 1554K|00:00:00.98 | 13431 |
| 4 | SORT AGGREGATE | | 173 | 1 | 173 |00:01:51.30 | 2323K|
|* 5 | TABLE ACCESS FULL| SALES | 173 | 48 | 2131K|00:01:50.93 | 2323K|
—————————————————————————————-
48 VS 2131000 = 50000 倍
SQL select num_rows from user_tables where table_name = SALES ;
NUM_ROWS
———-
50
使用 extended 包
select
dbms_stats.create_extended_stats(ownname = SH , tabname =
SALES , extension = (CHANNEL_ID,PROMO_ID) ) from dual ;
DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME= SH ,TABNAME= SALES ,EXTENSION= (CHANNEL_ID,PROMO_ID) )
————————————————————————————————————————
SYS_STU7$MLVU9QOBUF89709XS1VC9
已用時間: 00: 00: 01.65
SQL
SQL
exec dbms_stats.gather_table_stats(null, SALES , method_opt = for columns SYS_STU7$MLVU9QOBUF89709XS1VC9 size 2
select count(*) from sales a where amount_sold
(select avg(amount_sold) from sales b
where b.prod_id = a.prod_id
and channel_id = 3 and promo_id = 999 )
and channel_id = 3 and promo_id = 999 ;
alter system flush shared_pool;
select * from table(dbms_xplan.display_cursor(NULL,NULL, allstats last)) ;
PLAN_TABLE_OUTPUT
———————————————————————————————————————–
SQL_ID 18vj1zs6jut5g, child number 0
————————————-
select count(*) from sales a where amount_sold (select
avg(amount_sold) from sales b where b.prod_id = a.prod_id and
channel_id = 3 and promo_id = 999 ) and channel_id = 3 and promo_id =
999
Plan hash value: 4009253081
———————————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.79 | 26898 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.79 | 26898 | | | |
|* 2 | HASH JOIN | | 1 | 94910 | 709K|00:00:01.79 | 26898 | 1316K| 1316K| 1666K (0)|
| 3 | VIEW | VW_SQ_1 | 1 | 72 | 72 |00:00:00.92 | 13431 | | | |
| 4 | HASH GROUP BY | | 1 | 72 | 72 |00:00:00.92 | 13431 | 1106K| 1106K| 2480K (0)|
|* 5 | TABLE ACCESS FULL| SALES | 1 | 1554K| 1554K|00:00:00.48 | 13431 | | | |
|* 6 | TABLE ACCESS FULL | SALES | 1 | 1554K| 1554K|00:00:00.49 | 13431 | | | |
———————————————————————————————————————-
SQL select num_rows from user_tables where table_name = SALES ;
NUM_ROWS
———-
2756579
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, SALES
SQL select column_name, num_distinct, histogram from user_tab_columns where table_name = SALES ;
COLUMN_NAM NUM_DISTINCT HISTOGRAM
———- ———— ——————————
PROD_ID 72 FREQUENCY
CUST_ID 7059 NONE
TIME_ID 1460 NONE
CHANNEL_ID 4 FREQUENCY
PROMO_ID 4 FREQUENCY
QUANTITY_S 1 NONE
AMOUNT_SOL 3586 HYBRID
已選擇 7 行。
CHANNEL_ID PROMO_ID 各選擇一個值，就是：
1/4 * 1/4 * 2756579 = 172286
基數是 172286
estimated rows : 1554000
去掉統計信息
這些都無效，
exec DBMS_STATS.DROP_EXTENDED_STATS(null, SALES , (CHANNEL_ID,PROMO_ID)
exec DBMS_STATS.DELETE_TABLE_STATS(ownname = SH , tabname = SALES
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID = 18vj1zs6jut5g
ADDRESS HASH_VALUE
—————- ———-
000007FF0465AB40 220030127
exec SYS.DBMS_SHARED_POOL.PURGE (000007FF0465AB40,220030127 , C
select count(*) from sales a where amount_sold
(select avg(amount_sold) from sales b
where b.prod_id = a.prod_id
and channel_id = 3 and promo_id = 999 )
and channel_id = 3 and promo_id = 999 ;
SQL select column_name, num_distinct, histogram from user_tab_columns where table_name = SALES ;
COLUMN_NAM NUM_DISTINCT HISTOGRAM
———- ———— ——————————
PROD_ID NONE
CUST_ID NONE
TIME_ID NONE
CHANNEL_ID NONE
PROMO_ID NONE
QUANTITY_S NONE
AMOUNT_SOL NONE
已選擇 7 行。
SQL select num_rows from user_tables where table_name = SALES ;
NUM_ROWS
———-
select * from table(dbms_xplan.display_cursor(NULL,NULL, allstats last)) ;
Plan hash value: 4009253081
———————————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
PLAN_TABLE_OUTPUT
————————————————————————————————————————
———————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.67 | 26898 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.67 | 26898 | | | |
|* 2 | HASH JOIN | | 1 | 95637 | 709K|00:00:01.67 | 26898 | 1316K| 1316K| 1583K (0)|
| 3 | VIEW | VW_SQ_1 | 1 | 72 | 72 |00:00:00.87 | 13431 | | | |
| 4 | HASH GROUP BY | | 1 | 72 | 72 |00:00:00.87 | 13431 | 1106K| 1106K| 2480K (0)|
|* 5 | TABLE ACCESS FULL| SALES | 1 | 1566K| 1554K|00:00:00.45 | 13431 | | | |
|* 6 | TABLE ACCESS FULL | SALES | 1 | 1566K| 1554K|00:00:00.45 | 13431 | | | |
———————————————————————————————————————-
最后還是 truncate table，重新建立數據
truncate table sales ;
insert into sales select * from sales_02 where rownum = 50 ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, SALES
SQL select column_name, num_distinct, histogram from user_tab_columns where table_name = SALES ;
COLUMN_NAM NUM_DISTINCT HISTOGRAM
———- ———— ——————————
PROD_ID 1 FREQUENCY
CUST_ID 50 NONE
TIME_ID 2 NONE
CHANNEL_ID 2 FREQUENCY
PROMO_ID 1 FREQUENCY
QUANTITY_S 1 NONE
AMOUNT_SOL 2 FREQUENCY
已選擇 7 行。
SQL select num_rows from user_tables where table_name = SALES ;
NUM_ROWS
———-
50
select count(*) from sales a where amount_sold
(select avg(amount_sold) from sales b
where b.prod_id = a.prod_id
and channel_id = 3 and promo_id = 999 )
and channel_id = 3 and promo_id = 999 ;
COUNT(*)
———
709087
select * from table(dbms_xplan.display_cursor(NULL,NULL, allstats last)) ;
Plan hash value: 1265065521
—————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
—————————————————————————————–
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:32.27 | 2339K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:32.27 | 2339K|
|* 2 | FILTER | | 1 | | 709K|00:01:32.13 | 2339K|
|* 3 | TABLE ACCESS FULL | SALES | 1 | 1 | 1554K|00:00:00.67 | 13571 |
| 4 | SORT AGGREGATE | | 173 | 1 | 173 |00:01:30.73 | 2326K|
|* 5 | TABLE ACCESS FULL| SALES | 173 | 1 | 2131K|00:01:30.44 | 2326K|
—————————————————————————————-
1 VS 2131k 差無數倍
1 – SEL$1
3 – SEL$1 / A@SEL$1
4 – SEL$2
5 – SEL$2 / B@SEL$2
使用 hint
select /*+UNNEST(@ SEL$2)*/ count(*) from sales a where amount_sold
(select avg(amount_sold) from sales b
where b.prod_id = a.prod_id
and channel_id = 3 and promo_id = 999 )
and channel_id = 3 and promo_id = 999 ;
select * from table(dbms_xplan.display_cursor(NULL,NULL, allstats last)) ;
Plan hash value: 4009253081
———————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
PLAN_TABLE_OUTPUT
———————————————————————————-
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.64 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.64 |
|* 2 | HASH JOIN | | 1 | 2 | 709K|00:00:01.64 |
| 3 | VIEW | VW_SQ_1 | 1 | 1 | 72 |00:00:00.85 |
| 4 | HASH GROUP BY | | 1 | 1 | 72 |00:00:00.85 |
|* 5 | TABLE ACCESS FULL| SALES | 1 | 48 | 1554K|00:00:00.44 |
|* 6 | TABLE ACCESS FULL | SALES | 1 | 48 | 1554K|00:00:00.44 |
———————————————————————————-
看完上述內容，你們掌握 Oracle11g/12c dbms_stat extended stats 實驗的示例分析的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！
