Oracle一条SQL语句时快时慢

忘是亡心i 2022-08-06 01:18 388阅读 0赞

今天碰到一个非常奇怪的问题问题，一条SQL语句在PL/SQL developer中很慢，需要9s，问题SQL:

SELECT \* FROM GG\_function\_location f WHERE f.parent\_id ='03000000000001';  表GG\_function\_location有5千万的数据，parent\_id上是有索引的。

诊断第一步：就在PL/SQL developer中按F5，看到的执行计划是走索引的，应该不会慢啊。

第二步：在sqlplus中用autotrace看，非常快，0.06s。

第三部：我想要重现这种慢，于是在PL/SQL developer中开一个窗口，天啊！单独执行SQL非常慢，但使用下面的语句就非常快，真是太神奇了。

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' ;

第四部：我想到v$sql中找到这条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 ='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

**先找到SQL\_ID，再找到对应的执行计划**

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'));  
  
**执行计划一：**  
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 |       |       |  
\-----------------------------------------------------------------------------------------------------------------------------------  
  
**执行计划二：**   
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 |  
\------------------------------------------------------------------------------------------------------------

**分析：**我判断是解析这条SQL语句走错了执行计划，SELECT \* FROM GG\_function\_location f WHERE f.parent\_id ='03000000000001'，于是我把改为

SELECT /\*+gg\*/\* FROM GG\_function\_location f WHERE f.parent\_id ='03000000000001',非常快。接近就简单了，把索引删除后，重建，会让此SQL重新解析。

**解决方案：**  
drop index IDX\_GG\_FL\_PARENT\_ID;  
create index IDX\_GG\_FL\_PARENT\_ID on GG\_FUNCTION\_LOCATION (PARENT\_ID) nologging;