Oracle SQL statements are fast and slow, while oraclesql statements are fast and slow.

Oracle SQL statements are fast and slow, while oraclesql statements are fast and slow.

Today, I encountered a very strange problem. One SQL statement is very slow in PL/SQL developer and takes 9 s. The problem SQL:

SELECT * FROM GG_function_location f WHERE f. parent_id = '000000'; The GG_function_location table contains 10 million data records, and the parent_id table has an index.

Diagnosis Step 1: Press F5 in PL/SQL developer. The execution plan is indexed and should not be slow.

Step 2: Use autotrace in sqlplus, which is very fast, 0.06 s.

Part 3: I want to reproduce this slowness, so open a window in PL/SQL developer! It is amazing to execute the SQL statement separately, but it is very fast to use the following statement.

Alter session set tracefile_identifier = 'gg _ test ';
Alter session set events '2017 trace name context forever, level 12 ';
SELECT * FROM GG_function_location f WHERE f. parent_id = '2013 ';
Alter session set events '10046 trace name context off ';

Part 4: I found the execution plan of this SQL statement in v $ SQL.

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 = '000000' dk02nb8mkchna
SELECT * FROM GG_function_location f WHERE f. parent_id = '000000' 2zav8x5kwxb32
SELECT * FROM GG_function_location f WHERE f. parent_id = '000000' bc0k800k6u0x3

First locate SQL _ID 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 '));

Execution Plan 1:
HASH_VALUE 656818826, child number 0
-------------------------------------
SELECT * FROM GG_function_location f WHERE f. parent_id = '2013'
Plan hash value: 1550360901
Certificate -----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Certificate -----------------------------------------------------------------------------------------------------------------------------------
| 0 | select statement | 7 (100) |
| 1 | table access by global index rowid batched | GG_FUNCTION_LOCATION | 3 | 999 | 7 (0) | 00:00:01 | ROWID |
| * 2 | index range scan | IDX_GG_FL_PARENT_ID | 3 | 4 (0) | 00:00:01 |
Certificate -----------------------------------------------------------------------------------------------------------------------------------

Execution Plan 2: 
HASH_VALUE 611124131, child number 0
-------------------------------------
SELECT * FROM GG_function_location f WHERE f. parent_id = '2013'
Plan hash value: 3374024865
Bytes ------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes ------------------------------------------------------------------------------------------------------------
| 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 |
Bytes ------------------------------------------------------------------------------------------------------------

Analysis:I determined that the execution plan was incorrect for parsing this SQL statement. SELECT * FROM GG_function_location f WHERE f. parent_id = '2013', so I changed it

SELECT/* + gg */* FROM GG_function_location f WHERE f. parent_id = '2013', very fast. This approach is simple. After the index is deleted and re-built, the SQL statement will be re-parsed.

Solution:
Drop index IDX_GG_FL_PARENT_ID;
Create index IDX_GG_FL_PARENT_ID on GG_FUNCTION_LOCATION (PARENT_ID) nologging;