Oracle wait problems and potential solutions
Wait Problem Potential Fix Sequential Read Indicates many index reads—tune the code (especially joins) Scattered Read Indicates many full table scans—tune the code; cache small tables Free Buffer Increase the DB_CACHE_SIZE; shorten the checkpoint; tune
the code Buffer Busy Segment header—add freelists or freelist groups Buffer Busy Data block—separate ¡°hot¡± data; use reverse key indexes; use
smaller blocks; increase initrans (debatable); reduce block
popularity; make I/O faster Buffer Busy Undo header—add rollback segments or areas Buffer Busy Undo block—commit more; larger rollback segments or areas Latch Free Investigate the detail (a listing later in this chapter includes fixes) Enqueue – ST Use LMTs or preallocate large extents Enqueue – HW Preallocate extents above the high water mark Enqueue – TX4 Increase initrans or use a smaller block size on the table or index Enqueue – TX6 Fix the code that is making the block unsharable (use v$lock to find) Enqueue – TM Index foreign keys; check application locking of tables Log Buffer Space Increase the log buffer; use faster disks for the redo logs Log File Switch Archive destination slow or full; add more or larger redo logs Log file sync Commit more records at a time; use faster redo log disks; use
raw devices Write complete waits Add database writers; checkpoint more often; buffer cache too small
Ïà¹ØÎĵµ£º
Ò»¡¢ ³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ
1.Y»òYY»òYYY ÄêµÄ×îºóһ룬Á½Î»»òÈýλ
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from ......
--ÈçºÎÓÃgrade±íµÄ×ÊÁÏÈ¥¸üÐÂusertable±íµÄ×ÊÁÏ£¨ÓйØÁªµÄ×Ö¶Îuserid£©
update usertable u set u.grade =
(select g.grade from grade g where g.userid = u.userid);
--ÈçºÎʹ²éѯ½á¹û×Ö¶ÎÉú³ÉÐòºÅ
select rownum, t.* from sm_t_pad_new t
--ÈçºÎ¿ìËÙ×öÒ»¸öºÍÔ±íÒ»ÑùµÄ±¸·Ý±í
create ......
Êý¾Ý±íµÄÁ¬½ÓÓÐ:
1¡¢ÄÚÁ¬½Ó(×ÔÈ»Á¬½Ó): Ö»ÓÐÁ½¸ö±íÏàÆ¥ÅäµÄÐвÅÄÜÔÚ½á¹û¼¯ÖгöÏÖ
2¡¢ÍâÁ¬½Ó: °üÀ¨
£¨1£©×óÍâÁ¬½Ó(×ó±ßµÄ±í²»¼ÓÏÞÖÆ)
£¨2£©ÓÒÍâÁ¬½Ó(ÓÒ±ßµÄ±í²»¼ÓÏÞÖÆ)
£¨3£©È«ÍâÁ¬½Ó(×óÓÒÁ½±í¶¼²»¼ÓÏÞÖÆ)
3¡¢×ÔÁ¬½Ó(Á¬½Ó·¢ÉúÔÚÒ»Õ ......
select count(*) from t1;
Õâ¾ä»°±È½Ï¼òµ¥£¬µ«ºÜÓÐÐþ»ú£¡¶ÔÕâ¾ä»°ÔËÐеÄÀí½â£¬·´Ó³ÁËÄã¶ÔÊý¾Ý¿âµÄÀí½âÉî¶È£¡
½¨Á¢ÊµÑéµÄ´ó±íËût1
SQL> conn scott/tiger
ÒÑÁ¬½Ó¡£
SQL> drop table t1 purge;
±íÒÑɾ³ý¡£
SQL> create table t1 as select * from emp where 0=9;
±íÒÑ´´½¨¡£
SQL> insert into t1 sele ......
SQL*LOADERÊÇORACLEµÄÊý¾Ý¼ÓÔع¤¾ß£¬Í¨³£ÓÃÀ´½«²Ù×÷ϵͳÎļþǨÒƵ½ORACLEÊý¾Ý¿âÖС£SQL*LOADERÊÇ´óÐÍÊý¾Ý
²Ö¿âÑ¡ÔñʹÓõļÓÔØ·½·¨¡£
¡¡¡¡ÔÚNTÏ£¬SQL*LOADERµÄÃüÁîΪSQLLDR£¬ÔÚUNIXÏÂÒ»°ãΪsqlldr/sqlload¡£
¡¡¡¡ÈçÖ´ÐУºd:\oracle>sqlldr
SQL*Loader: Release 8.1.6.0.0 - Production on ÐÇÆÚ¶þ 1ÔÂ 8 11:06:42 2 ......