<table id="ql6un"><wbr id="ql6un"><center id="ql6un"></center></wbr></table>

<video id="ql6un"><sup id="ql6un"></sup></video>

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×

忘記密碼？

登錄注冊×

獲取短信驗證碼

其他方式登錄

點擊登錄注冊即表示同意《億速云用戶服務條款》

用戶登錄×

賬戶密碼登錄

請使用微信掃描上方二維碼

使用幫助

請求超時！

請點擊重新獲取二維碼

Oracle11g/12c dbms_stat extended stats 實驗的示例分析

發(fā)布時間：2021-11-29 09:44:42 來源：億速云閱讀：108 作者：柒染欄目：關系型數(shù)據(jù)庫

Oracle11g/12c dbms_stat extended stats 實驗的示例分析，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

目的：
1. 測試下 dbms_stats 的 extended stats 包的使用效果，是否可以在選擇率低的情況下，解決執(zhí)行計劃/cardinatlity 基數(shù)行不準的問題，
并且是只驗證  extended stats 包的使用效果 。 :D:

環(huán)境：
1. windows 7 64 位
2. oracle 12.2.0.1  64 位
3. 使用demo程序的 sales history , SH 用戶測試。
4. 先手動安裝下 sales history 范例數(shù)據(jù)

步驟：
1. sales 表先插入 50 行，
2. 分析表，獲得統(tǒng)計信息
3. 插入大量數(shù)據(jù)，讓統(tǒng)計信息不準
4. 使用extended status 分析包
5. 對比執(zhí)行計劃

步驟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  ,
基數(shù)算出來是 25


步驟3：

SQL> select count(*) from sales ;

  COUNT(*)
----------
2756579

再次執(zhí)行sql，并看執(zhí)行計劃：

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

基數(shù)是 172286
estimated rows : 1554000

去掉統(tǒng)計信息

這些都無效，
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 ，重新建立數(shù)據(jù)

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 差無數(shù)倍

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 實驗的示例分析的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業(yè)資訊頻道，感謝各位的閱讀！

向AI問一下細節(jié)

推薦閱讀：

免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經查實，將立刻刪除涉嫌侵權內容。

上一篇新聞：
11g RAC打開DG的redo實時應用是怎樣的
下一篇新聞：
C/C++?Qt?TreeWidget單層樹形組件怎么應用

猜你喜歡

AI
助
手

產品服務

地區(qū)劃分

專題活動

幫助支持

關于我們

售后咨詢

7*24小時在線電話：400-100-2938

7*24小時在線 QQ：800811969

關注億速云

億速云公眾號

手機網站二維碼

<samp id="27t77"><listing id="27t77"><dl id="27t77"></dl></listing></samp>