Oracle An SQL statement is fast and slow

Today, a very strange problem is encountered, a SQL statement is slow in PL/developer, requires 9s, problem sql:

SELECT * from gg_function_location f WHERE f.parent_id = ' 03000000000001 '; Table Gg_function_location has 50 million of the data andis indexed on the parent_id.

Diagnosis first step: in PL/SQL Developer Press F5, see the execution plan is go index, should not slow ah.

The second step: in the Sqlplus with Autotrace look, very fast, 0.06s.

Part III: I want to reproduce this slow, so I open a window in PL/SQL developer, God! It's very slow to execute SQL alone, but it's amazing to use the following statement very quickly.

Alter session Set Tracefile_identifier = ' gg_test ';
Alter session SET Events ' 10046 Trace name Context forever, Level 12 ';
SELECT * from GG_function_location f WHERE f.parent_id = ' 03000000000001 ';
Alter session SET Events ' 10046 Trace name context off ';

Fourth: I think of v$sql found this SQL implementation plan, finally found.

Sql> select banner from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production
PL/SQL Release 12.1.0.1.0-production
CORE 12.1.0.1.0 Production
TNS for Linux:version 12.1.0.1.0-production
Nlsrtl Version 12.1.0.1.0-production

Sql> Select s.sql_text,s.sql_id

From V$sql S
where S.sql_text like
' SELECT * from Gg_function_location F WHERE f.parent_id =% '
and s.sql_text not like '%and% ';
Sql_text sql_id
-------------------------------------------------------------------------  ------------
SELECT * from gg_function_location f WHERE f.parent_id = ' 03000000000001 ' Dk02nb8mkchna
SELECT * from gg_function_location f WHERE f.parent_id = ' 03000000000001 ' 2zav8x5kwxb32
SELECT * from gg_function_location f WHERE f.parent_id = ' 03000000000001 ' bc0k800k6u0x3

Find the sql_id first, and then find the corresponding execution plan.

Select Hash_value, Child_number, sql_text from V$sql s
where s.sql_id = ' bc0k800k6u0x3 ';
SELECT * FROM table (dbms_xplan.display_cursor (611124131, 0, ' advanced '));

Executive Plan One:
Hash_value 656818826, child number 0
-------------------------------------
SELECT * from gg_function_location f WHERE f.parent_id = ' 03000000000001 '
Plan Hash value:1550360901
--------------------------------------------------------------------------------------------------------------- --------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------- --------------------
| 0 |                      SELECT STATEMENT |       |       |     |          7 (100) |       |       | |
|  1 | TABLE ACCESS by GLOBAL INDEX ROWID batched|     gg_function_location |   3 |     999 | 7 (0) | 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN |     idx_gg_fl_parent_id |       3 |     | 4 (0) |       00:00:01 |       | |
--------------------------------------------------------------------------------------------------------------- --------------------

Implementation Plan II:
Hash_value 611124131, child number 0
-------------------------------------
SELECT * from gg_function_location f WHERE f.parent_id = ' 03000000000001 '
Plan Hash value:3374024865
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT |       |       |    |          68 (100) |       |       | |
|  1 |                      PARTITION LIST All |     |   1 |    247 | 68 (0) |     00:00:01 |     1 | 2 |
|   2 |                      PARTITION LIST all|     |   1 |    247 | 68 (0) |     00:00:01 |    1 | 20 |
|* 3 | TABLE ACCESS full|     gg_function_location |   1 |    247 | 68 (0) |     00:00:01 |    1 | 40 |
------------------------------------------------------------------------------------------------------------

Analysis: I judged that parsing this SQL statement went wrong with the execution plan,SELECT * from gg_function_location f WHERE f.parent_id = ' 03000000000001 ', So I changed to

SELECT/*+gg*/* from gg_function_location f WHERE f.parent_id = ' 03000000000001 ', very fast . Close is simple, after the index is deleted, the rebuild will let this SQL re-parse.

Solution:
Drop index idx_gg_fl_parent_id;
Create INDEX idx_gg_fl_parent_id on gg_function_location (parent_id) nologging;

Oracle An SQL statement is fast and slow