Execution Plan
DB version is 11.2.0.2. Execution plan of sql id from the cursor cache by running the below
select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical')); shows an explain plan with low cost and better path.
Plan hash value: 1562966982
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 705 (100)| |
But running sql tunning advisory for the same sql id gives a execution path with a huge cost and poor plan
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1562966982
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 450 | | 2514K (1)| 01:05:54 |