首页 > 代码库 > Oracle一条SQL语句时快时慢

Oracle一条SQL语句时快时慢

2024-08-07 01:51:52   221人阅读

  今天碰到一个非常奇怪的问题问题，一条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;

Oracle一条SQL语句时快时慢


联系
我们