db file sequential read等待事件

1、db file sequential readdb file sequential readµÈ´ýʼþÓÐ3¸ö²ÎÊý£ºfile£££¬first block£££¬ºÍblockÊýÁ¿¡£ÔÚ10gÖÐ&#

2、163;¬ÕâµÈ´ýʼþÊܵ½Óû§I/OµÈ´ý¼¶±ðµÄÓ°Ïì¡£µ±´¦Àídb file sequential readµÈ´ý

3、ʼþµÄʱºò£¬ÀμÇÒÔϹؼüÏë·¨¡£l Oracle½ø³ÌÐèÒªÒ»¸öµ±Ç°²»

4、2;ÚSGAÖеĿ飬µÈ´ýÊý¾Ý¿â¿é´Ó´ÅÅ̶ÁÈëµ½SGAÖÐl Òª¿´µÄÁ½¸öÖØÒ&#

5、170;µÄÊý×ÖÊǵ¥¶À»á»°µÄTIME_WAITEDºÍAVERAGE_WAIT¡£l ÖØÒªdb file sequential readµÈ´ýʱ¼ä×î¿ÉÄÜÊ&#

6、199;Ò»¸öÓ¦ÓÃÎÊÌâ¡£db file sequential readµÈ´ýʱ¼äÊÇÓÉÓÚÖ´ÐжÔË÷Òý£¬»Ø¹ö£

7、68;undo£©¶Î£¬ºÍ±í£¨µ±½èÖúrowidÀ´·ÃÎÊ£©£¬¿ØÖÆÎļþºÍÊý¾ÝÎļþÍ·

8、µÄµ¥¿é¶Á²Ù×÷SQLÓï¾ä£¨Óû§ºÍµÝ¹é£©ÒýÆðµÄ¡£¶ÔÓÚÕâЩ¶ÔÏ

9、43;µÄÎïÀíI/OÇëÇóÊǺÜÕý³£µÄ£¬Òò´Ëdb file sequential readµÈ´ýµÄ´æÔÚ²»ÊÇÒ»¶¨Ò&

10、#226;ζ¿â»òÓ¦Óóö´íÁË¡£Èç¹û»á»°ÔÚÕâʼþÉÏ»¨Á˺ó¤Ê¼þ£&

11、#172;Ëü¿ÉÄÜÒ²²»ÊÇÒ»¸öÔã¸âµÄÊÂÇé¡£Ïà·´£¬Èç¹û»á»°»¨ÁË´óÁ&

12、#191;ʱ¼äÔÚequeue»òlatch freeÉÏ£¬ÄÇôһ¶¨ÊÇÓÐÎÊÌâ¡£Õâ¶ùµ¥¿é¶Á±äµÄ¸´

13、12;ÓÁË¡£=Ä¿µÄ£º´ÓµÃµ½¸÷¸ösessionÖÐdb file sequential readµÈ´ýʼþµÄ×ܵĵȴýʱ¼&#

14、228;£¬ºÍµÈ´ýʱ¼äËùÕ¼×ܵĵȴýʱ¼ä(¸÷ÖֵȴýʼþµÄ×ܺÍʱ¼&

15、#228;)µÄ±ÈÀýÖзÖÎöÄÄÒ»¸ösid¸ü¸ß£¬¸üÖØÒª¡£=select a.sid,a. event,a. time_waited,a. time_waited / c.sum_time_waited * 100 pct_wait_time,round(

16、sysdate - b.logon_time) * 24) hours_connectedfrom v$session_event a, v$session b,(select sid, sum(time_waited) sum_time_waitedfrom v$session_eventwhere event not in ('Null event','client message','KXFX: Execution Message Dequeue - Slave','PX De

17、q: Execution Msg','KXFQ: kxfqdeq - normal deqeue','PX Deq: Table Q Normal','Wait for credit - send blocked','PX Deq Credit: send blkd','Wait for credit - need buffer to send','PX Deq Credit: need buffer&a

18、mp;#39;,'Wait for credit - free buffer','PX Deq Credit: free buffer','parallel query dequeue wait','PX Deque wait','Parallel Query Idle Wait - Slaves','PX Idle Wait','slave wait','

19、dispatcher timer','virtual circuit status','pipe get','rdbms ipc message','rdbms ipc reply','pmon timer','smon timer','PL/SQL lock timer','SQL*Net message from client&#

20、39;,'WMON goes to sleep')having sum(time_waited) > 0 group by sid)cwhere a.sid = b.sidand a.sid = c.sidand a.time_waited > 0and a.event = 'db file sequential read'order by hours_connected desc, pct_wait_time;SID EVENT TIME_WAITED PCT_WAIT_TIME HOURS_CONN

21、ECTED- - - - -186 db file sequential read 64446 77.0267848 105284 db file sequential read 1458405 90.992838 105194 db file sequential read 1458708 91.0204316 105322 db file sequential read 1462557 91.1577045 105139 db file sequential read 211325 52.6281055 11256 db file sequential read 247236 58.046

22、9755 11?192 db file sequential read 243113 88.0193625 2ÄãÄÜ×öÁ½¼þÊÂÀ´×îС»¯db file sequential readʼþ£ºl ͨ¹ý½µµÍphysicalº&#

23、205;logical readÀ´ÓÅ»¯µ¼Ö´ó¶àÊýwaitµÄSQLÓï¾äl ½µµÍƽ¾ùµÈ´ýʱ¼ä´ËÍ⣬µ±

24、;Ç°ÕýÔËÐеÄSQLÓï¾ä¿ÉÄÜ»ò²»¿ÉÄÜÊǵ¼ÖÂwaitµÄ¡£Õâ¾ÍÊÇûÓÐÀú

25、2;·Êý¾ÝµÄ½»»¥Ê½Õï¶Ï¾­³£ÊÇÎÞ¹¦¶ø·µµÄÔ­Òò¡£ÄãÄܲéѯv$sqlÊÓ

26、;ͼÀ´²éÕÒÓиßƽ¾ùDISK_READSµÄÓï¾ä£¬µ«È»ºóÄãÔõÑù²ÅÄÜÅжÏËûÃÇ&

27、#202;ôÓڻỰ£¿ÒòΪÕâЩÏÞÖÆ£¬Äã¿ÉÄܱØÐëÈ·¶¨ºÍÏ´ÎÃ÷È·¸ú&

28、#215;ٻỰµÄSQLÓï¾ä¡£Ò»µ©ÄãÒѾ­ÕÒµ½£¬ÓÅ»¯Ä¿±ê¾Í½«½µµÍÎïÀ

29、7;ºÍÂß¼­¶ÁµÄÊýÁ¿¡£×¢Ò⣺³ýÁËDISK_READS×Ö¶ÎÍ⣬oracle10gÖеÄV$SQLºÍV$SQLAREAÊÓͼ&

30、#211;в»´íµÄÐÂ×ֶΣºUSER_IO_WAIT_TIME£¬DIRECT_WRITES£¬APPLICATION_WAIT_TIME£¬CONCURRENCY_WAIT_TIME£¬CLUSTER_WAIT_TIME£¬PLSQL_EXEC_TIMEºÍJAVA_EXEC_TIME¡£

31、96;ãÄÜÕÒµ½ÓÐ×î¸ßµÄÀۼƻòƽ¾ùµÄUSER_IO_WAIT_TIMEµÄsqlÓï¾ä¡£=Ä¿µÄ£º¸ù¾Ýdb fil

32、e sequential readÖеÄP1,P2Á½¸ö²ÎÊýµÃµ½¶ÔÏóÃûºÍ·ÖÇøÃû£¨¸ÃµÈ´ýʼþµ¥¿é&#

33、182;ÁµÈ´ýµÄ¶ÔÏóÃûºÍ·ÖÇøÃû£©£¬Ê¹ÓÃv$bhµÄȱµãÄã±ØÐëµÈ´ý¿é±

34、7;¶ÁÈëµ½buffer cacheÖУ»·ñÔòX$BHÊÓͼÔÚbufferÖÐûÓÐP1,P2²ÎÊýËùÖ¸µÄÐÅÏ¢¡£DBA_OBJECTSÊ&#

35、211;ͼҲ²»°üº¬P1ºÍP2ËùÖ¸µÄrollback»òundo¶Î¶ÔÏó£º=select b.sid,nvl(substr(a.object_name,1,30),'P1='|b.p1|' P2='|b.p2|' P3

36、='|b.p3) object_name,a. subobject_name,a. object_typefrom dba_objects a, v$session_wait b, x$bh cwhere c.obj = a.object_id(+)and b.p1 = c.file#(+)and b.p2 = c.dbablk(+)and b.event = 'db file sequential read'unionselect b.sid,nvl(substr(a.object_name,1,30),'P1=&#39

37、;|b.p1|' P2='|b.p2|' P3='|b.p3) object_name,a. subobject_name,a. object_typefrom dba_objects a, v$session_wait b, x$bh cwhere c.obj = a.data_object_id(+)and b.p1 = c.file#(+)and b.p2 = c.dbablk(+)and b.event = 'db file sequential read'order by 1;SID OB

38、JECT_NAME SUBOBJECT_NAME OBJECT_TYPE- - - -12 DVC_TRX_REPOS DVC_TRX_REPOS_PR64 TABLE PARTITION128 DVC_TRX_REPOS DVC_TRX_REPOS_PR61 TABLE PARTITION154 ERROR_QUEUE ERROR_QUEUE_PR1 TABLE PARTITION192 DVC_TRX_REPOS_1IX DVC_TRX_REPOS_20040416 INDEX PARTITION194 P1=22 P2=30801 P3=1322 P1=274 P2=142805 P3=

39、1336 HOLD_Q1_LIST_PK INDEXÕë¶ÔË÷ÒýµÄsequential reads½â¾ö·½°¸£º1. SQLµ÷ÓÅ£¬µÃµ½sqlÓï¾äselect a.sid, a.seq#, a.event, a.p1text,a. p1

40、, a.p1raw, a.p2text, a.p2,a. p2raw, a.p3text, a.p3, a.p3raw,a. wait_time, a.seconds_in_wait, a.state, b.serial#,b. username, b.osuser, b.paddr, b.logon_time,c. process, b.sql_hash_value, b.saddr, b.module,d. row_wait_obj#, b.row_wait_file#, b.row_wait_block#,e. row_wait_row#from v$session_wait a, v$

41、session bwhere a.sid = b.sidand b.username is not nulland b.type <> 'BACKGROUND'and a.event in ('db file sequential read','db file scattered read','latch free','direct path read','direct path write&

42、amp;#39;,'enqueue','library cache pin','library cache load lock','buffer busy waits','free buffer waits');select hash_value,address,piece,sql_textfrom v$sqltextwhere hash_value = <cursor hash value>order by

43、 piece;2.Èç¹ûÖ´Ðмƻ®ÊÇtable access by index rowid£¬¼ì²éË÷ÒýµÄclustering factorÒ²ÊÇÖµµÃ×öµÄ¡£select

44、id.index_name,tb.table_name,id.clustering_factor,tb.num_rows,tb.blocksfrom dba_indexes id,dba_tables tbwhere id.table_name=tb.table_nameand tb.table_name='&1' and tb.owner='&2'Èç¹ûDBA_INDEXES.CLUSTERING_FACTOR½Ó½ü

45、;±íÖпéµÄÊýÁ¿£¬ÄÇô±íÖдó¶àÊýÐÐÊÇÅÅÐòµÄ¡£ÕâÊÇÆÚÍûµÄ

46、;¡£È»¶ø£¬Èç¹ûclustering factor½Ó½ü±íÖÐÐеÄÊýÁ¿£¬ËüÒâζ×űíÖÐÐÐÊÇË

47、æ»úÅÅÁС£ÕâÖÖÇé¿ö£¬¶ÔÓÚÔÚͬÑùÒ¶¿éÖеÄË÷Òý¿éÀ´Ëµ£¬Ö

48、¸ÏòͬÑùÊý¾Ý¿éÖÐÐÐÊDz»¿ÉÄܵģ¬Òò´ËËüÒªÇó¸ü¶àI/OsÀ´Íê³É&#

49、213;â²Ù×÷¡£Äã¿ÉÒÔ²ÉÈ¡rebuilding±íÀ´¸ÄÉÆË÷Òýclustering fator£¬ÎªÁËÐиù¾ÝË÷Òý

50、¼üÀ´±»ÅÅÐò£¬ÆäºóÖؽ¨Ë÷Òý¡£Èç¹û±í²»Ö»ÓÐÒ»¸öË÷Òý£¬Ê²

51、ô»á·¢Éú£¿ºÃ£¬Ëü»áϽµ¡£Äã½öÄÜÓ­ºÏ×î¶àʹÓõÄË÷Òý3.Ò

52、8;¼ì²éÀ´¿´ÊÇ·ñÓ¦ÓÃÓÐ×î½üÒѾ­ÒýÈëÒ»¸öеÄË÷Òý£¬Í¨¹ýÒ

53、2;ϲéѯ¡£ÐÂË÷ÒýµÄÒýÈë¿ÉÄܵ¼ÖÂÓÅ»¯Æ÷Ϊ·ÃÎʱíµÄSQLÓï¾äÑ&

54、#161;ÔñÒ»¸ö²»Í¬µÄÖ´Ðмƻ®¡£Ð¼ƻ®¿ÉÄܲúÉúÒ»¸ö±È¾É¼Æ»®¸&

55、#252;ºÃµÄ£¬ÖÐÐԵģ¬»òÔã¸âµÄÐÔÄÜ¡£select owner,substr(object_name,1,30) object_name,object_type,createdfrom dba_objectswhere object_type in ('INDEX&#

56、39;,'INDEX PARTITION')order by created;OPTIMIZER_INDEX_COST_ADJºÍOPTIMIZER_INDEX_CACHING³õʼ»¯²ÎÊýÄÜÓ°ÏìÓÅ»¯Æ÷È¥²ÉÓÃnested loops²

57、Ù×÷ºÍÔÚÈ«±íɨÃèÉÏÑ¡ÔñÒ»¸öË÷Òý·ÃÎÊ·¾¶¡£OPTIMIZER_INDEX_COST_ADJ²ÎÊýĬÈ

58、ÏÊÇ100¡£½ÏµÍµÄÖµºåÆ­ÓÅ»¯Æ÷ÈÏΪË÷Òý·ÃÎÊ·¾¶¸ü±ãÒË¡£OPTIMIZER_INDEX

59、_CACHING²ÎÊýĬÈÏÖµÊÇ0¡£½Ï¸ßµÄֵ֪ͨÓÅ»¯Æ÷Ò»¸ö¸ü¸ßµÄ°Ù·Ö±È

60、03;÷Òý¿éÒѾ­ÔÚbuffer cacheÖУ¬nested loops²Ù×÷¸ü±ãÒË¡£Ò»Ð©µÚÈý·½µÄÓ¦ÓÃʹ

61、11;ÃÕâ·½·¨À´¸ÄÉÆË÷ÒýʹÓá£ÕâЩ²ÎÊýµÄ²»ºÏÊʵÄʹÓÃÄܵ¼

62、14;ÂÖØ´óµÄI/OµÈ´ýʱ¼ä¡£²éÃ÷»á»°ÕýÒÔʲôֵÔËÐС£Ö±µ½9iÊý¾

63、1;¿â£¬ÕâÐÅÏ¢½öÄÜͨ¹ý¸ú×ÙÒÔtrace event 10053µÄ¼¶±ð1µÄ»á»°£¬²¢¼ì²étraceÎÄ

64、;¼þ¡£ÔÚoracle10gÖУ¬Õâ¿ÉÒÔ²éѯv$ses_optimizer_envÊÓͼ¡£È·±£ËùÓжÔÏóµÄͳ¼ÆÊ&

65、#253;¾ÝÊǵ±Ç°Êý¾ÝµÄµäÐÍ£¬ÒòΪ²»×¼È·µÄͳ¼ÆÊý¾ÝµÄÈ·»áµ¼Ö&

66、#194;ÓÅ»¯Æ÷Éú³ÉÔã¸âµÄ²»¸ÃÓÃË÷Òý¶ÁÈ´µ÷ÓÃË÷Òý¶ÁµÄÖ´Ðмƻ®¡£¼Çס£¬Í³¼ÆÊý¾ÝÐèÒªÊÇÓдú±íÐԵģ¬¶ø²»±Ø×&#