20181119]sql語句執行緩慢分析.txt

--//上個星期在最佳化時遇到sql語句執行緩慢的問題,百思不知其解,做一個記錄.

--//我們生產系統有一個表索引有點多,肯定一些索引不需要,我想刪除一些不必要的索引(可以先invisiable).再觀察情況.

1.環境:

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.執行指令碼如下:

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

--//主要目的先排除一些現在使用的索引.

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

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------

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

--//我發現一個奇特的情況,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 201931031

frzb5c8tjx9wz 1 1956464785 30458 2 1486831

--//對比2者NLS*環境:

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下執行:

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

--//注意下劃線,僅僅引數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/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

--//多次嘗試,確認實際上這個問題並不是引數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_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.

總結:

1.環境變數,執行時引數不同導致產生新的子游標,出現一些怪異的情況,感覺toad下執行快,而sqlplus下執行慢.實際上反過來的情況也出

現過.我估計cardinality FEEDBACK的影響.

2.對於複雜檢視的執行計劃先 MATERIALIZE也許能獲得好的效果.

3.我發現一個奇怪的情況就是實際上開啟alter session set statistics_level=all;就會選擇慢的執行計劃.

--//sqlplus開始第1次執行(硬分析)總是選擇慢的執行計劃.真心搞不懂....暫時放一下.

sql語句執行緩慢分析

相關文章