Shulou(Shulou.com)05/31 Report--

Oracle11g/12c dbms_stat extended stats experiment example analysis, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Objective:

1. Test whether the use of dbms_stats 's extended stats package can solve the problem of inaccurate execution plan / cardinatlity cardinality under the condition of low selection rate.

And only verify the effectiveness of the extended stats package. : D:

Environment:

1. Windows 7 64-bit

2. Oracle 12.2.0.1 64-bit

3. Use sales history, SH user testing of demo programs.

4. Install the sales history sample data manually first

Steps:

1. Insert 50 rows into the sales table first

two。 Analyze tables to get statistical information

3. Insert a large amount of data to make the statistics inaccurate

4. Analyze packages using extended status

5. Contrastive implementation plan

Step 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 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

-

fifty

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 (*)

-

three hundred and nine

Time spent: 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 =

nine hundred and ninety nine

Plan hash value: 1265065521

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.00 SELECT STATEMENT 00.01 | 14 |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 000.00 SORT AGGREGATE 00.01 | 14 |

| | * 2 | FILTER | | 1 | | 0 | 00001 | 14 |

| | * 3 | TABLE ACCESS FULL | SALES | 1 | 48 | 49 | 00Rank 00.01 | 7 |

| | 4 | SORT AGGREGATE | | 1 | 1 | 1 | 00001 | 00.01 | 7 |

| | * 5 | TABLE ACCESS FULL | SALES | 1 | 48 | 49 | 00Rank 00.01 | 7 |

-

CHANNEL_ID 2 FREQUENCY

PROMO_ID 1 FREQUENCY

NUM_ROWS

-

fifty

Calculate cardinality

1ax 2 * 1 * 50 = 25

The base number is 25.

Step 3:

SQL > select count (*) from sales

COUNT (*)

-

2756579

Execute sql again and look at the execution plan:

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 =

nine hundred and ninety nine

Plan hash value: 1265065521

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

PLAN_TABLE_OUTPUT

-

-

| | 0 | SELECT STATEMENT | | 1 | 1 | 000.1 53.45 | 2336K |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 000.1 53.45 | 2336K |

| | * 2 | FILTER | | 1 | | 709k | 00 01purl 53.33 | 2336k |

| | * 3 | TABLE ACCESS FULL | SALES | 1 | 48 | 1554K | 000.98 | 13431 |

| | 4 | SORT AGGREGATE | | 173 | 1 | 173 | 000.1 51.30 | 2323K |

| | * 5 | TABLE ACCESS FULL | SALES | 173 | 48 | 2131K | 000.93 | 2323K |

-

48 VS 2131000 = 50000 times

SQL > select num_rows from user_tables where table_name = 'SALES'

NUM_ROWS

-

fifty

Use the extended package

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

Time spent: 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 =

nine hundred and ninety nine

Plan hash value: 4009253081

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

| | 0 | SELECT STATEMENT | | 1 | 1 | 0000 SELECT STATEMENT 01.79 | 26898 |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 01.79 | 01.79 | 26898 |

| | * 2 | HASH JOIN | | 1 | 94910 | 709K | 00VERV 01.79 | 26898 | 1316K | 1316K | 1666K (0) |

| | 3 | VIEW | VW_SQ_1 | 1 | 72 | 72 | 000.92 | 13431 |

| | 4 | HASH GROUP BY | | 1 | 72 | 72 | 00 00.92 | 13431 | 1106K | 1106K | 2480K (0) |

| | * 5 | TABLE ACCESS FULL | SALES | 1 | 1554K | 1554K | 000.48 | 13431 |

| | * 6 | TABLE ACCESS FULL | SALES | 1 | 1554K | 1554K | 000.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

Seven rows have been selected.

CHANNEL_ID PROMO_ID selects a value for each, that is:

1 * 4 * 1 * 4 * 2756579 = 172286

The base number is 172286

Estimated rows: 1554000

Get rid of statistics

None of these are valid.

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 Magna 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

Seven rows have been selected.

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 | 0000 SELECT STATEMENT 01.67 | 26898 |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 000.00 SORT AGGREGATE 01.67 | 26898 |

| | * 2 | HASH JOIN | | 1 | 95637 | 709K | 00VERV 01.67 | 26898 | 1316K | 1316K | 1583K (0) |

| | 3 | VIEW | VW_SQ_1 | 1 | 72 | 72 | 000.87 | 13431 |

| | 4 | HASH GROUP BY | | 1 | 72 | 72 | 00 00.87 | 13431 | 1106K | 1106K | 2480K (0) |

| | * 5 | TABLE ACCESS FULL | SALES | 1 | 1566k | 1554K | 000.45 | 13431 |

| | * 6 | TABLE ACCESS FULL | SALES | 1 | 1566k | 1554K | 000.45 | 13431 |

-

Finally, there is truncate table, which rebuilds the data.

Truncate table sales

Insert into sales select * from sales_02 where rownum 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

Seven rows have been selected.

SQL > select num_rows from user_tables where table_name = 'SALES'

NUM_ROWS

-

fifty

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 | 000.1 32.27 | 2339K |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 000.1 32.27 | 2339K |

| | * 2 | FILTER | | 1 | | 709k | 00rig 01purl 32.13 | 2339k |

| | * 3 | TABLE ACCESS FULL | SALES | 1 | 1 | 1554K | 000.67 | 13571 |

| | 4 | SORT AGGREGATE | | 173 | 1 | 173 | 00VO1VERV 30.73 | 2326K |

| | * 5 | TABLE ACCESS FULL | SALES | 173 | 1 | 2131K | 00VO1VRO 30.44 | 2326K |

-

1 VS 2131k difference innumerable times

1-SEL$1

3-SEL$1 / A@SEL$1

4-SEL$2

5-SEL$2 / B@SEL$2

Use 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 | 000.00 SELECT STATEMENT 01.64 |

| | 1 | SORT AGGREGATE | | 1 | 1 | 1 | 00GRV 01.64 |

| | * 2 | HASH JOIN | | 1 | 2 | 709K | 01.64 | 01.64 |

| | 3 | VIEW | VW_SQ_1 | 1 | 1 | 72 | 000.85 | 00.85 |

| | 4 | HASH GROUP BY | | 1 | 1 | 72 | 000.85 | 00.85 |

| | * 5 | TABLE ACCESS FULL | SALES | 1 | 48 | 1554K | 000.044 | 00.44 |

| | * 6 | TABLE ACCESS FULL | SALES | 1 | 48 | 1554K | 000.044 | 00.44 |

After reading the above, have you mastered the method of example analysis of the Oracle11g/12c dbms_stat extended stats experiment? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.