SOS：生产库的采样报告（statspack）用户反映速度奇慢各位大侠给看看 [复制链接]

精华贴数: 11
技术积分: 33853
社区积分: 3867
注册时间: 2001-10-18

论坛徽章:: 109

发表于 2005-9-5 08:40:32 |显示全部楼层

晕，怎么那么多人让改绑定变量？

请看清楚TOP 5 EVENT！！！！！！！！！！！

buffer busy waits                            140,583,065 3,365,730 80.13 ！！！！！！

实在想不明白改成绑定变量会对buffer busy waits  有任何改善。

to:楼主，请阅读以下文章，找出buffer busy waits  问题的根源

http://metalink.oracle.com/metal ... T&p_id=155971.1

披发头陀

高级会员

精华贴数: 1
技术积分: 5407
社区积分: 20
注册时间: 2004-4-21

论坛徽章:: 5

发表于 2005-9-5 08:52:09 |显示全部楼层

最初由 hooyang 发布
[B]

谢谢　现在有这个考虑　但是升级服务器肯定能提高速度吗？ [/B]

IBM X235是吧，物理读比较高，我估计改善一下磁盘系统会有些好处

胡杨

精华贴数: 0
技术积分: 1457
社区积分: 935
注册时间: 2003-1-15

论坛徽章:: 28

发表于 2005-9-5 09:00:28 |显示全部楼层

多谢各位　高手的热心指点

lfree

精华贴数: 5
技术积分: 36149
社区积分: 5028
注册时间: 2005-3-4

论坛徽章:: 111

2010广州亚运会纪念徽章:乒乓球
日期:2011-05-04 08:52:11

发表于 2005-9-5 09:04:38 |显示全部楼层

buffer busy waits                            140,583,065 3,365,730 80.13
这个是主要问题。我遇到的问题并非像热点或者freelist有关，或者IO问题，
更多的是不良的sql以及全表扫描，我也不知道什么解析。

》latch free                                     9,072,198    249,187    5.93
》db file scattered read                         3,559,111    148,004    3.52
这个是次要问题，latch free可能与绑定变量有关。db file scattered read这个与全表扫描有关。

Select ca.WEEKNO, ca.WEEKDAYNO, ca.CLASSNO from CARESULT ca
where  TEACHINGCLASSID =:1

是否这个没有索引在TEACHINGCLASSID上。

select 1 from dual 执行这个干什么？？

总之仔细看排前面的sql，是否存在优化的可能？

胡杨

精华贴数: 0
技术积分: 1457
社区积分: 935
注册时间: 2003-1-15

论坛徽章:: 28

发表于 2005-9-5 09:04:52 |显示全部楼层

最初由披发头陀发布
[B]

IBM X235是吧，物理读比较高，我估计改善一下磁盘系统会有些好处 [/B]

您好　机子是IBM X235　
如何改善磁盘系统呢　请给一些参考　谢谢　

lfree

精华贴数: 5
技术积分: 36149
社区积分: 5028
注册时间: 2005-3-4

论坛徽章:: 111

发表于 2005-9-5 09:09:38 |显示全部楼层

》buffer busy waits 140,583,065 3,365,730 80.13
也许全表扫描占据了数据缓冲，导致缓存忙等。

lfree

精华贴数: 5
技术积分: 36149
社区积分: 5028
注册时间: 2005-3-4

论坛徽章:: 111

发表于 2005-9-5 09:13:33 |显示全部楼层

你能将这个的执行计划贴上来吗？
我估计你没有建立CARESULT的TEACHINGCLASSID

Buffer Gets Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
195,263,934    18,240    10,705.3 12.1  2185.69 -85597.12 1783290183
select  weekno ,coursename,classno,weekdayno,B.name,EMPLOYEENAM
E from COURSE_TEACHER C,caresult A ,classroom B where A.CLASSROO
MID = B.CLASSROOMID  and C.TEACHINGCLASSID = A.TEACHINGCLASSID
and A.teachingclassid in (select teachingclassid from teachingta
sk where termid= :1 and teachingclassid in (select teaching

   98,700,683       4,092    24,120.4 6.1 594.77 4297.20 2281063472
select distinct t.GRADEYEAR,t.TERMID executeterm,te.termname,t.
courseid,c.COURSEKINDCODE, t.studytypecode,decode(t.studytypecod
e,'2','专业选修课','3','公共选修课') studytypecodename,t.PERIODN
UM1,t.PERIODNUM2,t.PERIODNUM3,t.PERIODNUM4,t.teachingclassid, t.
teachingclassname, t.studentnum,t.TERMNO,t.COMMENTS, c.coursenam

   23,734,860    44,163       537.4 1.5  1234.41 2676.16 1690170283
SELECT WEEKNO FROM CARESULT                   WHERE WEEKDAYNO=:
b3 AND CLASSNO=:b2 and TEACHINGCLASSID=:b1       order by WEEKN
O

胡杨

精华贴数: 0
技术积分: 1457
社区积分: 935
注册时间: 2003-1-15

论坛徽章:: 28

发表于 2005-9-5 09:18:10 |显示全部楼层

to lfree 谢谢您的热心指点
我再好好查一查其它地方
“select 1 from dual ”是写在配置文件中　用来检测SESSIONS是否过期

胡杨

精华贴数: 0
技术积分: 1457
社区积分: 935
注册时间: 2003-1-15

论坛徽章:: 28

发表于 2005-9-5 09:45:59 |显示全部楼层

最初由 lfree 发布
[B]你能将这个的执行计划贴上来吗？
我估计你没有建立CARESULT的TEACHINGCLASSID
[/B]

远程没有连接上，下面执行计划是在开发用服务器上执行的用户是昨天从现场exp/imp回来的
[php]
SQL> set autotrace traceonly
SQL>  select  weekno ,coursename,classno,weekdayno,B.name,EMPLOYEENAME
  2 from COURSE_TEACHER C,caresult A ,classroom B
  3 where A.CLASSROOMID = B.CLASSROOMID  and C.TEACHINGCLASSID = A.TEACHINGCLA
SSID
  4  and A.teachingclassid in (select teachingclassid from teachingtask
  5  where termid='t010') ;

已选择43958行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1    SORT (JOIN)
3 2    NESTED LOOPS
4 3       NESTED LOOPS
5 4          TABLE ACCESS (FULL) OF 'CARESULT'
6 4          TABLE ACCESS (BY INDEX ROWID) OF 'CLASSROOM'
7 6          INDEX (UNIQUE SCAN) OF 'PK_CLASSROOM' (UNIQUE)
8 3       TABLE ACCESS (BY INDEX ROWID) OF 'TEACHINGTASK'
9 8          INDEX (UNIQUE SCAN) OF 'PK_TEACHINGTASK' (UNIQUE)
  10 1    SORT (JOIN)
  11 10    TABLE ACCESS (FULL) OF 'COURSE_TEACHER'

Statistics
----------------------------------------------------------
      0  recursive calls
      3  db block gets
   224180  consistent gets
   1477  physical reads
      0  redo size
1048164  bytes sent via SQL*Net to client
   32733  bytes received via SQL*Net from client
   2932  SQL*Net roundtrips to/from client
      1  sorts (memory)
      1  sorts (disk)
   43958  rows processed

=============================================================================================================
SQL> SELECT WEEKNO FROM CARESULT WHERE WEEKDAYNO='2' AND CLASSNO='1' and TEACHIN
GCLASSID='CCf65fbad8'
  2  order by WEEKNO;

已选择10行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (FULL SCAN) OF 'PK_CARESULT' (UNIQUE)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      251  consistent gets
      0  physical reads
      0  redo size
      473  bytes sent via SQL*Net to client
      503  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      10  rows processed

=========================================================================================
SQL> select distinct t.GRADEYEAR,t.TERMID executeterm,te.termname,t.courseid,c.C
OURSEKINDCODE, t.studytypecode,decode(t.studytypecode,'2','专业选修课','3','公共
选修课') studytypecodename,
  2  t.PERIODNUM1,t.PERIODNUM2,t.PERIODNUM3,t.PERIODNUM4,t.teachingclassid, t.te
achingclassname, t.studentnum,t.TERMNO,t.COMMENTS, c.coursename,
  3 c.COURSENO,c.DEPARTMENTID,c.STAFFROOMID,c.ISOTHERLANG,c.CREDITHOURNUM,c.TE
ACHMETHODCODE,c.REFBOOKNAME,c.PRECOURSENAME,
  4  decode(c.exammethodcode,'1','笔试','2','口试','3','考查','4','操作','其他')
exammethodcode,c.brief,C.COMMENTS COURSEcomments,
  5  (select count(distinct studentno) from course_student  where TEACHINGCLAS
SID=t.TEACHINGCLASSID)rr,ct.EMPLOYEENAME
  6  from teachingtask t,course c,Course_Teacher ct,term te where  c.COURSEID=t.
COURSEID and t.TERMID=te.TERMID and ct.TEACHINGCLASSID=t.TEACHINGCLASSID and t
.STUDYTYPECODE='3'
  7  and t.TERMID='t010' ;

已选择10行。

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1    NESTED LOOPS
3 2    NESTED LOOPS
4 3       NESTED LOOPS
5 4          TABLE ACCESS (FULL) OF 'COURSE_TEACHER'
6 4          TABLE ACCESS (BY INDEX ROWID) OF 'TEACHINGTASK'
7 6          INDEX (UNIQUE SCAN) OF 'PK_TEACHINGTASK' (UNIQUE
      )

8 3       TABLE ACCESS (BY INDEX ROWID) OF 'TERM'
9 8          INDEX (UNIQUE SCAN) OF 'PK_TERM' (UNIQUE)
  10 2    TABLE ACCESS (BY INDEX ROWID) OF 'COURSE'
  11 10       INDEX (UNIQUE SCAN) OF 'PK_COURSE' (UNIQUE)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   19497  consistent gets
      0  physical reads
      0  redo size
   2715  bytes sent via SQL*Net to client
      503  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      11  sorts (memory)
      0  sorts (disk)
      10  rows processed




[/php]

bulkaunt

老会员

精华贴数: 0
技术积分: 1142
社区积分: 15
注册时间: 2003-9-15

论坛徽章:: 0

发表于 2005-9-5 09:57:47 |显示全部楼层

--------晕，怎么那么多人让改绑定变量？

---------请看清楚TOP 5 EVENT！！！！！！！！！！！

虽然说TOP里面前三位是
buffer busy waits
db file sequential read
latch free

但是看报表里面的情况，很明显类似以下的语句，出现次数非常频繁，调整绑定变量也是调整SQL中的一部份。

Select ca.WEEKNO, ca.WEEKDAYNO, ca.CLASSNO ,cl.name from CARESUL
T ca inner join CLASSROOM cl  on ca.CLASSROOMID=cl.CLASSROOMI
D where  TEACHINGCLASSID =  'CC97b66408'  order by weekno,weekda
yno,classno,name

   5,026,183       4,105       1,224.4 0.3 223.38  -9040.33  969647633
Select ca.WEEKNO, ca.WEEKDAYNO, ca.CLASSNO ,cl.name from CARESUL
T ca inner join CLASSROOM cl  on ca.CLASSROOMID=cl.CLASSROOMI
D where  TEACHINGCLASSID =  'CC5fbf463c'  order by weekno,weekda
yno,classno,name

   5,022,574       4,108       1,222.6 0.3 225.94 2355.69  484715011
Select ca.WEEKNO, ca.WEEKDAYNO, ca.CLASSNO ,cl.name from CARESUL
T ca inner join CLASSROOM cl  on ca.CLASSROOMID=cl.CLASSROOMI
D where  TEACHINGCLASSID =  'CC9851a620'  order by weekno,weekda
yno,classno,name

   5,021,292       4,106       1,222.9 0.3 222.91 -27327.67 2688569652
Select ca.WEEKNO, ca.WEEKDAYNO, ca.CLASSNO ,cl.name from CARESUL
T ca inner join CLASSROOM cl  on ca.CLASSROOMID=cl.CLASSROOMI
D where  TEACHINGCLASSID =  'CC9755f618'  order by weekno,weekda
yno,classno,name