Oracle一条SQL语句,在sql developer执行慢，在sql plus中执行快的解决办法。

以下是网友的处理方法。
今天碰到一个非常奇怪的问题问题，一条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;
我的处理方法：
SQL> exec dbms_stats.gather_schema_stats(ownname => 'smda',cascade => TRUE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
收集smda下的所有表的统计信息。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22569416/viewspace-1868225/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22569416/viewspace-1868225/