sql語句執(zhí)行緩慢的原因分析
這篇文章主要介紹“sql語句執(zhí)行緩慢的原因分析”，在日常操作中，相信很多人在sql語句執(zhí)行緩慢的原因分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”sql語句執(zhí)行緩慢的原因分析”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！
成都創(chuàng)新互聯(lián)公司是一家專注于網(wǎng)站制作、網(wǎng)站建設(shè)與策劃設(shè)計(jì),習(xí)水網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:習(xí)水等地區(qū)。習(xí)水做網(wǎng)站價(jià)格咨詢:18982081108
1.環(huán)境:
zzzzzz> @ ver1
PORT_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 Production
2.執(zhí)行腳本如下:
$ cat a.txt
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_indexes
WHERE 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')));
--//主要目的先排除一些現(xiàn)在使用的索引.
3.執(zhí)行:
--//session 1:
zzzzzz> alter session set statistics_level=all;
Session altered.
zzzzzz> @ a.txt MS_CF01 aaa
...
--//慢.等。。。。
--//session 1,按ctrl+c中斷執(zhí)行,實(shí)際上沒有執(zhí)行完成:
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,而實(shí)際上主要是循環(huán)次數(shù)3490導(dǎo)致執(zhí)行緩慢.
--//很奇怪的是我在toad下很快完成.第1次執(zhí)行需要實(shí)際長一點(diǎn)外,以后都很快.再次查看執(zhí)行計(jì)劃
--//注不能在toad下看,toad看僅僅看到child_number=0的執(zhí)行計(jì)劃.
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 | | | |
--//開始想是正常的,因?yàn)槲以趕qlplus下設(shè)置了alter session set statistics_level=all;.但是仔細(xì)看仔細(xì)計(jì)劃對于的操作id不一樣,而且Plan hash value也不同.
--//我也嘗試在toad下加入alter session set statistics_level=all;按F9執(zhí)行也會(huì)執(zhí)行緩慢.
zzzzzz> @ share frzb5c8tjx9wz
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" AND
object_owner LIK
SQL_ID = frzb5c8tjx9wz
ADDRESS = 00000000AA459A38
CHILD_ADDRESS = 00000000AB1E7238
CHILD_NUMBER = 0
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>45</ID><reason>NLS
Settings(0)</reason><size>2x4</size><SessionLengthSemantics>0</SessionLengthSemantics><CursorLengthSemantics>0</CursorLengthSemantics></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer
mismatch(12)</reason><size>2x216</size><statistics_level> all typical </statistics_level></ChildNode>
--------------------------------------------------
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" AND
object_owner LIK
SQL_ID = frzb5c8tjx9wz
ADDRESS = 00000000AA459A38
CHILD_ADDRESS = 00000000AB3775D8
CHILD_NUMBER = 1
OPTIMIZER_MISMATCH = Y
REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x216</size><statistics_level> typical all </statistics_level></ChildNode>
--//我發(fā)現(xiàn)一個(gè)奇特的情況,sqlplus執(zhí)行的語句和toad下執(zhí)行的語句不能共享,提示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 201931031
frzb5c8tjx9wz 1 1956464785 30458 2 1486831
--//對比2者NLS*環(huán)境:
zzzzzz> select * from nls_session_parameters ;
PARAMETER VALUE
-------------------------- ----------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZH:TZM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NLS_TIMESTAMP_TZ_FORMAT YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
--//toad下執(zhí)行:
select * from nls_session_parameters ;
PARAMETER,VALUE
NLS_LANGUAGE,AMERICAN
NLS_TERRITORY,AMERICA
NLS_CURRENCY,$
NLS_ISO_CURRENCY,AMERICA
NLS_NUMERIC_CHARACTERS,.,
NLS_CALENDAR,GREGORIAN
NLS_DATE_FORMAT,YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE,AMERICAN
NLS_SORT,BINARY
NLS_TIME_FORMAT,HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT,YYYY-MM-DD HH24:MI:SS.FF
NLS_TIME_TZ_FORMAT,HH.MI.SSXFF AM TZR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NLS_TIMESTAMP_TZ_FORMAT,YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_DUAL_CURRENCY,$
NLS_COMP,BINARY
NLS_LENGTH_SEMANTICS,BYTE
NLS_NCHAR_CONV_EXCP,FALSE
--//注意下劃線,僅僅參數(shù)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
--//很慢.登錄后執(zhí)行:
@ a.txt MS_CF01 aaa
--//可以發(fā)現(xiàn)很快,因?yàn)槲以趖oad下已經(jīng)執(zhí)行過一次.
--//真心想不到這個(gè)參數(shù),會(huì)導(dǎo)致這么奇怪的問題.
--//補(bǔ)充說明一下:
--//如果寫成'HH24.MI.SSXFF AM TZR',調(diào)用sqlplus報(bào)錯(cuò).
$ export NLS_TIME_TZ_FORMAT='HH24.MI.SSXFF AM TZR'
$ rlsql system/test12@192.168.31.8:1521/hrp430
ERROR:
ORA-01818: 'HH24' precludes use of meridian indicator
SP2-0152: ORACLE may not be functioning properly
ORA-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<-kpeDbgCrash()+192<-kpeDbgSignalHandler()+119<-skgesig_sigactionHandler()+218<-__sighandler()<-pthread_mutex_destroy()<-kpufhndl0()+5461<-kpufhndl()+11<-OCIHandleFree()+19<-afioci()+2372<-aficnthdf()+62<-aficexf()+185<-aficex()+370<-afidrv()+321<-frame_dummy()+123<-__libc_start_main()+244
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 signal
warnings can be silenced by the --no-warnings (-n) option
Segmentation fault
--//多次嘗試,確認(rèn)實(shí)際上這個(gè)問題并不是參數(shù)NLS_TIME_TZ_FORMAT導(dǎo)致問題,而是執(zhí)行計(jì)劃不穩(wěn)定.每次分析都有可能導(dǎo)致新的執(zhí)行計(jì)劃.
--//參數(shù)NLS_TIME_TZ_FORMAT不同,僅僅導(dǎo)致產(chǎn)生新的子光標(biāo),重新生成的執(zhí)行計(jì)劃.兩者混合在一起導(dǎo)致問題非常怪異.
--//包括設(shè)置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_indexes
WHERE 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 aaa
zzzzzz> 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 903877
4zapbf2u32kx2 1 115948183 11455 1 847278
zzzzzz> @ share 4zapbf2u32kx2
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_indexes
WHERE owner LIKE :"SYS_B_04" AND index_type <> :"SYS_B_05" MINUS (SELECT object_owner, object_name FROM a
UN
SQL_ID = 4zapbf2u32kx2
ADDRESS = 00000000A6303098
CHILD_ADDRESS = 00000000A68317F8
CHILD_NUMBER = 0
USE_FEEDBACK_STATS = Y
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer mismatch(13)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>262144</dnum_kksfcxe></ChildNode>
--------------------------------------------------
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_indexes
WHERE owner LIKE :"SYS_B_04" AND index_type <> :"SYS_B_05" MINUS (SELECT object_owner, object_name FROM a
UN
SQL_ID = 4zapbf2u32kx2
ADDRESS = 00000000A6303098
CHILD_ADDRESS = 00000000A841E528
CHILD_NUMBER = 1
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
--//不過不能共享的原因是是USE_FEEDBACK_STATS=Y.
總結(jié):
1.環(huán)境變量,執(zhí)行時(shí)參數(shù)不同導(dǎo)致產(chǎn)生新的子光標(biāo),出現(xiàn)一些怪異的情況,感覺toad下執(zhí)行快,而sqlplus下執(zhí)行慢.實(shí)際上反過來的情況也出
現(xiàn)過.我估計(jì)cardinality FEEDBACK的影響.
2.對于復(fù)雜視圖的執(zhí)行計(jì)劃先 MATERIALIZE也許能獲得好的效果.
3.我發(fā)現(xiàn)一個(gè)奇怪的情況就是實(shí)際上打開alter session set statistics_level=all;就會(huì)選擇慢的執(zhí)行計(jì)劃.
--//sqlplus開始第1次執(zhí)行(硬分析)總是選擇慢的執(zhí)行計(jì)劃.真心搞不懂....暫時(shí)放一下.
到此，關(guān)于“sql語句執(zhí)行緩慢的原因分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
分享標(biāo)題：sql語句執(zhí)行緩慢的原因分析
標(biāo)題來源：http://chinadenli.net/article14/jiidde.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、網(wǎng)站營銷、商城網(wǎng)站、定制開發(fā)、用戶體驗(yàn)、建站公司
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)
- 動(dòng)態(tài)網(wǎng)頁的工作方式 2016-11-09
- 動(dòng)態(tài)網(wǎng)站的功能 2021-11-28
- 網(wǎng)站建設(shè)中的靜態(tài)網(wǎng)站與動(dòng)態(tài)網(wǎng)站 2016-10-26
- 關(guān)于建設(shè)動(dòng)態(tài)網(wǎng)站優(yōu)缺點(diǎn) 2016-05-10
- 動(dòng)態(tài)URl對搜索引擎真的不友好嗎?理解是有偏見的 2016-04-02
- 動(dòng)態(tài)頁面和靜態(tài)頁面的區(qū)別 2016-09-17
- 網(wǎng)站建設(shè)基礎(chǔ)知識之靜態(tài)和動(dòng)態(tài)網(wǎng)站（一） 2016-09-16
- 咸陽網(wǎng)站建設(shè)-動(dòng)態(tài)網(wǎng)站靜態(tài)化方法與制作優(yōu)化技巧 2021-09-13
- 成都網(wǎng)站建設(shè)有限公司談動(dòng)態(tài)網(wǎng)站和靜態(tài)網(wǎng)站的優(yōu)劣勢 2016-07-06
- PHP動(dòng)態(tài)網(wǎng)站制作 2022-05-13
- 行業(yè)動(dòng)態(tài)網(wǎng)站設(shè)計(jì)的扁平化 2022-02-04
- 讓創(chuàng)新互聯(lián)互聯(lián)為你詳解靜態(tài)、動(dòng)態(tài)網(wǎng)站建設(shè)基礎(chǔ)知識！ 2018-04-20