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

Source: Internet
Author: User

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;

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.