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
相关文档:
15. /*+USE_CONCAT*/
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询. (懵懂啊,先存着)
例如:
select /*+use_concat */ * from emp where deptno=10 OR empno=7788;
Execution Plan
----------------------------------------------------------
0 S ......
1、查询两个日期之间的数据。
假设有表Table1,其创建表的sql语句为:
create table Table1(
StationID NUMBER(10) Primary key,
Year NUMBER(4) not null,
Month NUMBER(2) n ......
--如何用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 ......
The DB File Scattered Read wait event generally indicates waits related to full table scans or fast
full index scans. As full table scans are pulled into memory, they are scattered throughout the
buffer cache, since it is usually unlikely that they fall into contiguous buffers. A large numb ......
http://www.javaeye.com/topic/366991#
作为一个开发人员,开始认真系统学习
Oracle SQL也只有月余,之前的
SQL知
识仅仅是项目里面用到什么就去看什么,简单说就是
Read The Fxxking Guide。在系统学习
Oracle
SQL之前,俺的想法就是“按照需求完成功能,剩下的交给
DBA来处理”。这个想法从整体来看显 ......