Use Dbms_stats package to modify statistics, cheat Optimizer, generate bad execution plan

When using the optimizer for cost-based optimizer, the optimizer produces an execution plan that estimates the execution cost of each SQL and chooses the best execution plan to execute the SQL statement. By manipulating statistics, you can profile the generation of a manipulation execution plan.

It is, of course, important to emphasize that this is a very dangerous behavior.

1 Creating a Test table

Sql> CREATE TABLE Test_stats as 2 select * from Dba_objects; Table created.

2 Collecting statistical information

Sql> EXEC dbms_stats.gather_table_stats (ownname = ' DAO ', tabname = ' test_stats ', cascade = TRUE);P l/sql Procedure successfully completed.

3 Creating an Index

Sql> ALTER TABLE Test_stats 2 add constraint DAO_STATS_PK primary key (OBJECT_ID); Table altered.

4 table number of rows and view execution plan

Sql> select  count (*)   2  from dao.test_stats  t ;   count (*)----------     75675Execution  Plan----------------------------------------------------------plan hash value: 2395052010------ ------------------------------------------------------------------------| id  | operation              | Name          | Rows  | Cost  (%CPU) | time      |------------------------------------------------------------------------------|    0 | SELECT STATEMENT      |               |     1 |     44    (0) | 00:00:01 | |   1 |  SORT AGGREGATE       |               |     1  |            |           | |    2 |   index fast full scan| dao_stats_pk |  75675 |    44    (0) | 00:00:01 |----------------------- -------------------------------------------------------Statistics-------------------------------------------------------- --          1  recursive calls           0  db block gets         164  consistent gets          0  physical reads           0  redo size        528  bytes  sent via SQL*Net to client        523   bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts  (memory)            0  sorts  (disk)           1   rows processed

5 modifying table statistics with Dbms_stats.set_table_stats

Sql> BEGIN 2 dbms_stats.set_table_stats (ownname = ' DAO ', tabname = ' test_stats ', numrows = 1,numblks = 1  );  3 END; 4/pl/sql procedure successfully completed.

Sql> select  t.owner,t.table_name,t.num_rows,t.blocks  2  from dba_ tables t  3  where t.table_name = ' Test_stats '   4  AND  t.owner= ' DAO ';owner                           TABLE_NAME                         num_rows     blocks------------------------------ ------------------------ ------ ---------- ----------dao                             test_ stats                               1           1

6 Viewing statistics again

Sql> select  count (*)   2  from dao.test_stats  t ;   count (*)----------     75675Execution  Plan----------------------------------------------------------plan hash value: 1525674154------ -------------------------------------------------------------------| id  | operation           | name       |  Rows  | Cost  (%CPU) | time     |----------------------- --------------------------------------------------|   0 | select statement    |            |      1 |     2    (0) | 00:00:01 | |    1 |  sort aggregate    |            |      1 |            |           | |    2 |   TABLE ACCESS FULL| TEST_STATS |      1 |     2    (0) | 00:00:01 |---------- ---------------------------------------------------------------Statistics------------------------------------------------ ----------          0  recursive calls           0  db block gets         164  consistent gets           0  physical reads          0  redo size         528  bytes sent via sql*net to client         523  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts  (memory)            0  sorts  (disk)            1  rows processed

This article is from the "daoxing (road) space" blog, so be sure to keep this source http://daoye.blog.51cto.com/4190423/1431949