sql语句执行缓慢的原因分析

这篇文章主要介绍“sql语句执行缓慢的原因分析”，在日常操作中，相信很多人在sql语句执行缓慢的原因分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”sql语句执行缓慢的原因分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！1.环境:zzzzzz> @ ver1PORT_STRING VERSION BANNER—————————— ————– ——————————————————————————–x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production2.执行脚本如下:$ cat a.txtSELECT /*+ &&2 */ owner, index_name, — index_type, table_NAME, COMPRESSION, UNIQUENESS, distinct_keys, AVG_LEAF_blocks_per_key, AVG_DATA_BLOCKS_PER_KEY, blevel, leaf_blocks, clustering_factor, status, num_rows, sample_size, last_analyzed FROM dba_indexesWHERE owner LIKE ‘XXXXXX_YYY’ AND table_name = UPPER (‘&&1’) AND (owner, index_name) IN (SELECT owner, index_name FROM dba_indexes WHERE owner LIKE ‘XXXXXX_YYY’ AND index_type ‘LOB’ MINUS (SELECT object_owner, object_name FROM v$sql_plan WHERE object_type = ‘INDEX’ AND object_owner LIKE ‘XXXXXX_YYY’ UNION SELECT object_owner, object_name FROM dba_hist_sql_plan WHERE object_type = ‘INDEX’ AND object_owner LIKE ‘XXXXXX_YYY’) MINUS (SELECT owner, constraint_name FROM dba_constraints WHERE owner LIKE ‘XXXXXX_YYY’ AND constraint_type IN (‘P’, ‘U’, ‘R’)));–//主要目的先排除一些现在使用的索引.3.执行:–//session 1:zzzzzz> alter session set statistics_level=all;Session altered.zzzzzz> @ a.txt MS_CF01 aaa…–//慢.等。。。。–//session 1,按ctrl+c中断执行,实际上没有执行完成:zzzzzz> @ dpc ” ”PLAN_TABLE_OUTPUT————————————-SQL_ID frzb5c8tjx9wz, child number 0…Plan hash value: 2331593202——————————————————————————————————————————————————————————————————–| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |——————————————————————————————————————————————————————————————————–| 0 | SELECT STATEMENT | | 1 | | | 485 (100)| | 1 |00:01:42.24 | 2568K| 93 | | | || 1 | NESTED LOOPS OUTER | | 1 | 1 | 179 | 145 (0)| 00:00:02 | 1 |00:01:42.24 | 2568K| 93 | | | || 2 | NESTED LOOPS OUTER | | 1 | 1 | 168 | 144 (0)| 00:00:02 | 1 |00:01:42.24 | 2568K| 93 | | | || 3 | NESTED LOOPS OUTER | | 1 | 1 | 165 | 143 (0)| 00:00:02 | 1 |00:01:42.24 | 2568K| 93 | | | |… 太长…| 38 | SORT UNIQUE | | 3490 | 1 | 375 | 340 (97)| 00:00:05 | 126 |00:03:18.78 | 4909K| 15 | 2048 | 2048 | || 39 | UNION-ALL | | 3490 | | | | | 1461 |00:03:18.73 | 4909K| 15 | | | ||* 40 | FIXED TABLE FULL | X$KQLFXPL | 3490 | 1 | 22 | 3 (100)| 00:00:01 | 826 |00:02:58.63 | 0 | 0 | | | ||* 41 | TABLE ACCESS FULL | WRH$_SQL_PLAN | 3489 | 1 | 17 | 308 (1)| 00:00:04 | 635 |00:00:20.08 | 4909K| 15 | | | |–//很明显主要慢在ID=40,41,而实际上主要是循环次数3490导致执行缓慢.–//很奇怪的是我在toad下很快完成.第1次执行需要实际长一点外,以后都很快.再次查看执行计划–//注不能在toad下看,toad看仅仅看到child_number=0的执行计划.zzzzzz> @ dpc frzb5c8tjx9wz ”….SQL_ID frzb5c8tjx9wz, child number 1————————————-Plan hash value: 1956464785———————————————————————————————————————————————| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |———————————————————————————————————————————————| 0 | SELECT STATEMENT | | | | 570 (100)| | | | ||* 1 | FILTER | | | | | | | | || 2 | NESTED LOOPS OUTER | | 1 | 179 | 230 (0)| 00:00:03 | | | || 3 | NESTED LOOPS OUTER | | 1 | 168 | 229 (0)| 00:00:03 | | | || 4 | NESTED LOOPS OUTER | | 1 | 164 | 228 (0)| 00:00:03 | | | || 5 | NESTED LOOPS | | 1 | 156 | 227 (0)| 00:00:03 | | | |….| 50 | UNION-ALL | | | | | | | | ||* 51 | FIXED TABLE FULL | X$KQLFXPL | 1 | 22 | 3 (100)| 00:00:01 | | | ||* 52 | TABLE ACCESS FULL | WRH$_SQL_PLAN | 1 | 17 | 308 (1)| 00:00:04 | | | || 53 | SORT UNIQUE NOSORT | | 1 | 211 | 15 (7)| 00:00:01 | | | |–//开始想是正常的,因为我在sqlplus下设置了alter session set statistics_level=all;.但是仔细看仔细计划对于的操作id不一样,而且Plan hash value也不同.–//我也尝试在toad下加入alter session set statistics_level=all;按F9执行也会执行缓慢.zzzzzz> @ share frzb5c8tjx9wzSQL_TEXT = SELECT /*+ aaa */ owner, index_name, — index_type, table_NAME, COMPRESSION, UNIQUENESS, distinct_keys, AVG_LEAF_blocks_per_key, AVG_DATA_BLOCKS_PER_KEY,blevel, leaf_blocks, clustering_factor, status, num_rows, sample_size, last_analyzed FROM dba_indexes WHERE owner LIKE :”SYS_B_00″ AND table_name = UPPER (:”SYS_B_01″) AND (owner,index_name) IN (SELECT owner, index_name FROM d免费主机域名ba_indexes WHERE owner LIKE :”SYS_B_02″ AND index_type :”SYS_B_03″MINUS (SELECT object_owner, object_name FROM v$sql_plan WHERE object_type = :”SYS_B_04″ ANDobject_owner LIKSQL_ID = frzb5c8tjx9wzADDRESS = 00000000AA459A38CHILD_ADDRESS = 00000000AB1E7238CHILD_NUMBER = 0REASON = 045NLSSettings(0)2×40003Optimizermismatch(12)2×216 all typical ————————————————–SQL_TEXT = SELECT /*+ aaa */ owner, index_name, — index_type, table_NAME, COMPRESSION, UNIQUENESS, distinct_keys, AVG_LEAF_blocks_per_key, AVG_DATA_BLOCKS_PER_KEY,blevel, leaf_blocks, clustering_factor, status, num_rows, sample_size, last_analyzed FROM dba_indexes WHERE owner LIKE :”SYS_B_00″ AND table_name = UPPER (:”SYS_B_01″) AND (owner,index_name) IN (SELECT owner, index_name FROM dba_indexes WHERE owner LIKE :”SYS_B_02″ AND index_type :”SYS_B_03″MINUS (SELECT object_owner, object_name FROM v$sql_plan WHERE object_type = :”SYS_B_04″ ANDobject_owner LIKSQL_ID = frzb5c8tjx9wzADDRESS = 00000000AA459A38CHILD_ADDRESS = 00000000AB3775D8CHILD_NUMBER = 1OPTIMIZER_MISMATCH = YREASON = 13Optimizer mismatch(12)2×216 typical all –//我发现一个奇特的情况,sqlplus执行的语句和toad下执行的语句不能共享,提示LANGUAGE_MISMATCH= Y.zzzzzz> select sql_id,child_number,PLAN_HASH_VALUE,buffer_gets,executions,ELAPSED_TIME from v$sql where sql_id=’frzb5c8tjx9wz’;SQL_ID CHILD_NUMBER PLAN_HASH_VALUE BUFFER_GETS EXECUTIONS ELAPSED_TIME————- ———— ————— ———– ———- ————frzb5c8tjx9wz 0 2331593202 4978087 1 201931031frzb5c8tjx9wz 1 1956464785 30458 2 1486831–//对比2者NLS*环境:zzzzzz> select * from nls_session_parameters ;PARAMETER VALUE————————– ———————————-NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SSNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FFNLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZH:TZM~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~NLS_TIMESTAMP_TZ_FORMAT YYYY-MM-DD HH24:MI:SS.FF TZH:TZMNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE17 rows selected.–//toad下执行:select * from nls_session_parameters ;PARAMETER,VALUENLS_LANGUAGE,AMERICANNLS_TERRITORY,AMERICANLS_CURRENCY,$NLS_ISO_CURRENCY,AMERICANLS_NUMERIC_CHARACTERS,.,NLS_CALENDAR,GREGORIANNLS_DATE_FORMAT,YYYY-MM-DD HH24:MI:SSNLS_DATE_LANGUAGE,AMERICANNLS_SORT,BINARYNLS_TIME_FORMAT,HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT,YYYY-MM-DD HH24:MI:SS.FFNLS_TIME_TZ_FORMAT,HH.MI.SSXFF AM TZR~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~NLS_TIMESTAMP_TZ_FORMAT,YYYY-MM-DD HH24:MI:SS.FF TZH:TZMNLS_DUAL_CURRENCY,$NLS_COMP,BINARYNLS_LENGTH_SEMANTICS,BYTENLS_NCHAR_CONV_EXCP,FALSE–//注意下划线,仅仅参数NLS_TIME_TZ_FORMAT不同.$ export NLS_TIME_TZ_FORMAT=’HH.MI.SSXFF AM TZR’–//再次登录,问题消失.alter session set statistics_level=all;@ a.txt MS_CF01 aaa–//很慢.登录后执行:@ a.txt MS_CF01 aaa–//可以发现很快,因为我在toad下已经执行过一次.–//真心想不到这个参数,会导致这么奇怪的问题.–//补充说明一下:–//如果写成’HH24.MI.SSXFF AM TZR’,调用sqlplus报错.$ export NLS_TIME_TZ_FORMAT=’HH24.MI.SSXFF AM TZR’$ rlsql system/test12@192.168.31.8:1521/hrp430ERROR:ORA-01818: ‘HH24’ precludes use of meridian indicatorSP2-0152: ORACLE may not be functioning properlyORA-24550: signal received: [si_signo=11] [si_errno=0] [si_code=1] [si_int=0] [si_ptr=(nil)] [si_addr=0x10]kpedbg_dmp_stack()+362
rlwrap: warning: sqlplus crashed, killed by SIGSEGV.rlwrap itself has not crashed, but for transparency,it will now kill itself (without dumping 免费主机域名core) with the same signalwarnings can be silenced by the –no-warnings (-n) optionSegmentation fault–//多次尝试,确认实际上这个问题并不是参数NLS_TIME_TZ_FORMAT导致问题,而是执行计划不稳定.每次分析都有可能导致新的执行计划.–//参数NLS_TIME_TZ_FORMAT不同,仅仅导致产生新的子光标,重新生成的执行计划.两者混合在一起导致问题非常怪异.–//包括设置alter session set statistics_level=all;–//如果我改写成如下,无论什么情况都是很快完成.WITH a AS (SELECT /*+ MATERIALIZE */ object_owner, object_name FROM v$sql_plan WHERE object_type = ‘INDEX’ AND object_owner LIKE ‘XXXXXX_YYY’)SELECT /*+ &&2 */ owner ,index_name ,–index_type, table_NAME ,COMPRESSION ,UNIQUENESS ,distinct_keys ,AVG_LEAF_blocks_per_key ,AVG_DATA_BLOCKS_PER_KEY ,blevel ,leaf_blocks ,clustering_factor ,status ,num_rows ,sample_size ,last_analyzed FROM dba_indexesWHERE owner LIKE ‘XXXXXX_YYY’ AND table_name = UPPER (‘&&1’) AND (owner, index_name) IN (SELECT owner, index_name FROM dba_indexes WHERE owner LIKE ‘XXXXXX_YYY’ AND index_type ‘LOB’ MINUS (SELECT object_owner, object_name FROM a UNION SELECT object_owner, object_name FROM dba_hist_sql_plan WHERE object_type = ‘INDEX’ AND object_owner LIKE ‘XXXXXX_YYY’) MINUS (SELECT owner, constraint_name FROM dba_constraints WHERE owner LIKE ‘XXXXXX_YYY’ AND constraint_type IN (‘P’ ,’U’ ,’R’)));zzzzzz> @ aaa.txt MS_CF01 aaazzzzzz> select sql_id,child_number,PLAN_HASH_VALUE,buffer_gets,executions,ELAPSED_TIME from v$sql where sql_id=’4zapbf2u32kx2′;SQL_ID CHILD_NUMBER PLAN_HASH_VALUE BUFFER_GETS EXECUTIONS ELAPSED_TIME————- ———— ————— ———– ———- ————4zapbf2u32kx2 0 2384590172 9680 1 9038774zapbf2u32kx2 1 115948183 11455 1 847278zzzzzz> @ share 4zapbf2u32kx2SQL_TEXT = with a as ( SELECT /*+ MATERIALIZE */ object_owner, object_name FROM v$sql_plan WHERE object_type = :”SYS_B_00″ AND object_owner LIKE :”SYS_B_01″ ) SELECT /*+ aaa */ owner, index_name, –index_type,table_NAME, COMPRESSION, UNIQUENESS, distinct_keys, AVG_LEAF_blocks_per_key, AVG_DATA_BLOCKS_PER_KEY, blevel, leaf_blocks, clustering_factor, status, num_rows, sample_size,last_analyzed FROM dba_indexes WHERE owner LIKE :”SYS_B_02″ AND table_name = UPPER (:”SYS_B_03″) AND (owner, index_name) IN (SELECT owner, index_name FROM dba_indexesWHERE owner LIKE :”SYS_B_04″ AND index_type :”SYS_B_05″ MINUS (SELECT object_owner, object_name FROM aUNSQL_ID = 4zapbf2u32kx2ADDRESS = 00000000A6303098CHILD_ADDRESS = 00000000A68317F8CHILD_NUMBER = 0USE_FEEDBACK_STATS = YREASON = 03Optimizer mismatch(13)3×4324194560262144————————————————–SQL_TEXT = with a as ( SELECT /*+ MATERIALIZE */ object_owner, object_name FROM v$sql_plan WHERE object_type = :”SYS_B_00″ AND object_owner LIKE :”SYS_B_01″ ) SELECT /*+ aaa */ owner, index_name, –index_type,table_NAME, COMPRESSION, UNIQUENESS, distinct_keys, AVG_LEAF_blocks_per_key, AVG_DATA_BLOCKS_PER_KEY, blevel, leaf_blocks, clustering_factor, status, num_rows, sample_size,last_analyzed FROM dba_indexes WHERE owner LIKE :”SYS_B_02″ AND table_name = UPPER (:”SYS_B_03″) AND (owner, index_name) IN (SELECT owner, index_name FROM dba_indexesWHERE owner LIKE :”SYS_B_04″ AND index_type :”SYS_B_05″ MINUS (SELECT object_owner, object_name FROM aUNSQL_ID = 4zapbf2u32kx2ADDRESS = 00000000A6303098CHILD_ADDRESS = 00000000A841E528CHILD_NUMBER = 1REASON =————————————————–PL/SQL procedure successfully completed.–//不过不能共享的原因是是USE_FEEDBACK_STATS=Y.总结:1.环境变量,执行时参数不同导致产生新的子光标,出现一些怪异的情况,感觉toad下执行快,而sqlplus下执行慢.实际上反过来的情况也出 现过.我估计cardinality FEEDBACK的影响.2.对于复杂视图的执行计划先 MATERIALIZE也许能获得好的效果.3.我发现一个奇怪的情况就是实际上打开alter session set statistics_level=all;就会选择慢的执行计划.–//sqlplus开始第1次执行(硬分析)总是选择慢的执行计划.真心搞不懂….暂时放一下.到此，关于“sql语句执行缓慢的原因分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注云技术网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: 怎么理解Oracle checkpoint

本篇内容主要讲解“怎么理解Oracle checkpoint”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么理解Oracle checkpoint”吧!checkpoint扫盲什么是checkpoint 在数据库…