Comparison of execution plan changes before and after index creation and soft-hardware Parsing

Change of execution plan before and after index creation and comparison environment of soft and hardware resolution: OEL + Oracle 10.2.0.5 Rac answered a netizen's question on itpub today. RT: After I first executed an SQL statement, this SQL statement passes the hard parsing and gets the execution plan. When the SQL statement is executed again, it performs soft parsing, instead of getting a new execution plan through the optimizer. If I add an index, it is better to execute this SQL statement through the index. Is it soft resolution when executing this SQL statement? (Will the SQL statements be hard parsed due to changes in statistics ?) My answer at the time was: With an index, the statistics changed. After indexing, the execution plan has changed. But does soft and hard parsing mean SQL statements? As long as this SQL copy exists in the Shared Pool, soft resolution will be executed directly. I personally think that the soft-resolution answer should be hard-resolved before the analyze table; at lunch break, I reviewed this case, but I always felt a bit wrong after thinking about it. I decided to test everything based on facts. The test process and results are as follows:

[sql] SQL> show user  USER is "SYS"  SQL> drop index tt_idx;  drop index tt_idx             *  ERROR at line 1:  ORA-01418: specified index does not exist      SQL> drop table tt purge;  drop table tt purge             *  ERROR at line 1:  ORA-00942: table or view does not exist  

 

Create a new table TT:

[sql] SQL> create table tt as select * from dba_objects;  

 

Table created. Open autotrace and start searching for the new Table tt for the first time according to the condition:

[sql] SQL> set autotrace on;    SQL> select object_id,object_name from tt where object_id=10;         OBJECT_ID       OBJECT_NAME</span>    ----------       --------------------------------------          10       C_USER#        Execution Plan    ----------------------------------------------------------    Plan hash value: 264906180        --------------------------------------------------------------------------    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    --------------------------------------------------------------------------    |   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    |*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    --------------------------------------------------------------------------        Predicate Information (identified by operation id):    ---------------------------------------------------           1 - filter("OBJECT_ID"=10)        Note    -----       - dynamic sampling used for this statement            Statistics    ----------------------------------------------------------             68  recursive calls              0  db block gets            785  consistent gets            701  physical reads              0  redo size            481  bytes sent via SQL*Net to client            400  bytes received via SQL*Net from client              2  SQL*Net roundtrips to/from client              0  sorts (memory)              0  sorts (disk)              1  rows processed        SQL>     

 

It can be clearly seen from the execution plan that the optimizer performs a full table scan and performs a hard parsing. Execute the preceding query again:

[sql] SQL> select object_id,object_name from tt where object_id=10;         OBJECT_ID      OBJECT_NAME   ----------      ---------------------------------------            10      C_USER#        Execution Plan    ----------------------------------------------------------    Plan hash value: 264906180        --------------------------------------------------------------------------    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    --------------------------------------------------------------------------    |   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    |*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    --------------------------------------------------------------------------        Predicate Information (identified by operation id):    ---------------------------------------------------           1 - filter("OBJECT_ID"=10)        Note    -----       - dynamic sampling used for this statement            Statistics    ----------------------------------------------------------              0  recursive calls              0  db block gets            707  consistent gets              0  physical reads              0  redo size            481  bytes sent via SQL*Net to client            400  bytes received via SQL*Net from client              2  SQL*Net roundtrips to/from client              0  sorts (memory)              0  sorts (disk)              1  rows processed        SQL>     

 

Although the execution plan still performs a full table scan, it executes soft parsing. The main reason here is that there is a copy of the same SQL statement in the Shared Pool (the two statements are identical)

[sql] SQL> select object_id,OBJECT_NAME from tt where object_id=10;       OBJECT_ID          OBJECT_NAME  ----------          -------------------------------------------------            10          C_USER#        Execution Plan    ----------------------------------------------------------    Plan hash value: 264906180        --------------------------------------------------------------------------    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    --------------------------------------------------------------------------    |   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    |*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    --------------------------------------------------------------------------        Predicate Information (identified by operation id):    ---------------------------------------------------           1 - filter("OBJECT_ID"=10)        Note    -----       - dynamic sampling used for this statement            Statistics    ----------------------------------------------------------              4  recursive calls              0  db block gets            779  consistent gets              0  physical reads              0  redo size            481  bytes sent via SQL*Net to client            400  bytes received via SQL*Net from client              2  SQL*Net roundtrips to/from client              0  sorts (memory)              0  sorts (disk)              1  rows processed        SQL> select OBJECT_ID,OBJECT_NAME from tt where object_id=10;       OBJECT_ID      OBJECT_NAME  ----------      ----------------------------------------------            10      C_USER#        Execution Plan    ----------------------------------------------------------    Plan hash value: 264906180        --------------------------------------------------------------------------    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    --------------------------------------------------------------------------    |   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    |*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    --------------------------------------------------------------------------        Predicate Information (identified by operation id):    ---------------------------------------------------           1 - filter("OBJECT_ID"=10)        Note    -----       - dynamic sampling used for this statement            Statistics    ----------------------------------------------------------              4  recursive calls              0  db block gets            779  consistent gets              0  physical reads              0  redo size            481  bytes sent via SQL*Net to client            400  bytes received via SQL*Net from client              2  SQL*Net roundtrips to/from client              0  sorts (memory)              0  sorts (disk)              1  rows processed        SQL>     

 

As shown above, if the SQL statement is different, hard Parsing is executed. Ps: because the table TT is not analyzed, the ROWS value in the above execution plan is 8 instead of 1 (only one record is returned). Continue test: add the index TT_IDX to the object_id column of the table TT, execute the same SQL statement for search:

[sql] SQL> create index tt_idx on tt(object_id);    Index created.    SQL>   [sql] SQL> select object_id,object_name from tt where object_id=10;     OBJECT_ID           OBJECT_NAME  ----------           ----------------------------------------          10           C_USER#        Execution Plan  ----------------------------------------------------------  Plan hash value: 2072537773  --------------------------------------------------------------------------------------  | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  --------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| TT     |     1 |    79 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | TT_IDX |     1 |       |     1   (0)| 00:00:01 |  --------------------------------------------------------------------------------------      Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("OBJECT_ID"=10)    Note  -----     - dynamic sampling used for this statement    Statistics  ----------------------------------------------------------            9  recursive calls            0  db block gets           79  consistent gets            1  physical reads            0  redo size          481  bytes sent via SQL*Net to client          400  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed    SQL>   

 

The experiment shows that although this statement has been executed before and a copy of this statement exists in the SQL sharing area, hard Parsing is still executed and the optimizer performs the index; in version 10g or later, if the table is not analyzed, oracle automatically collects and analyzes data through dynamic sampling.