使用OEM 分析占用资源较多的 sql

		顶层 SQL	2004年10月27日 下午03时53分50秒

报告目标: ####

顶层 SQL

(图表为表格形式, 请参见下面的数据表) 散列值 SQL 文本 每次执行的磁盘读取数 每次执行的缓冲区获取数 执行数 磁盘读取数 缓冲区获取数 每一行的缓冲区获取数 缓冲区高速缓存命中率 排序数 可共享内存 已处理行数 每次执行的分析调用数 语法分析调用 加载数 永久性内存 运行时内存 已加载的版本 已打开的版本 正在打开的用户 正在执行的用户 无效数 子级编号 优化程序模式 正在分析用户 ID 正在分析方案 ID 保留版本 地址 模块 模块散列 操作 操作散列 可串行化中止 大纲目录 CPU 时间 经过时间 1306561092 delete from enterp_info_db where id=:1 17543.972973 22858.351351 37 649127 845759 22858.351351 23.249176 0 6544 37 1.0 37 1 528 3400 1 0 0 0 0 0 CHOOSE 21 21 0 8419AC14 JDBC Thin Client 0 0 0 0 19564773 2587249602 insert into enterp_info(id, FULL_NAME, COMP_LOCATION,COMP_POST,COMP_URL,way,relate_name,title,mobile,sex,password,question,answer, NAME,COMP_MAIL,COMP_MAIL2,COMP_PHONE,COMP_LOCATE1,COMP_LOCATE2,COMP_LOCATE3,COMP_LAW,COMP_FAX,REGDATE,CLICKTIMES,NEWENTERP,auth) select :1,:2,:3,:4,:5,:6,relate_name,title,mobile,sex,password,question,answer, NAME,COMP_MAIL,COMP_MAIL2,COMP_PHONE,COMP_LOCATE1,COMP_LOCATE2,COMP_LOCATE3,COMP_LAW,COMP_FAX,sysdate,CLICKTIMES,NEWENTERP,1 from enterp_info_db where id=:7 17538.72973 22866.72973 37 648933 846069 22866.72973 23.300227 0 23211 37 1.0 37 1 672 24636 1 0 0 0 0 0 CHOOSE 21 21 0 841A6B7C JDBC Thin Client 0 0 0 0 29874370 987341707 select a.full_name,a.way,a.comp_url,a.comp_post,a.comp_location,b.product,b.comp_infor from enterp_info_db a,enterp_attribute_info_db b where a.id=b.id and a.id=:1 17403.197674 22861.55814 86 1496675 1966094 22861.55814 23.875715 0 10509 86 1.0 86 1 952 20020 1 0 0 0 0 0 CHOOSE 21 21 0 843B87E4 JDBC Thin Client 0 0 0 0 78990316 1078532078 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IVP"."ENTERP_INFO_DB".* FROM "IVP"."ENTERP_INFO_DB" 15331.666667 154125.166667 6 91990 924751 0.115424 90.052457 0 12577 8011741 1.0 6 1 2068 5792 1 0 0 0 0 0 CHOOSE 21 21 0 87E61B58 exp@bj169 (TNS V1-V3) 0 0 0 0 42904570 1909470160 select id,full_name from enterp_info_db where full_name like :1 and rownum<2 13077.311966 18345.92094 468 6120182 8585891 68141.992063 28.718149 0 6303 126 1.0 468 1 648 2052 1 0 0 0 0 0 CHOOSE 21 21 0 843C02DC JDBC Thin Client 0 0 0 0 488083393 1588280465 select e.pk,e.thread_type,e.thread_title,e.thread_sendscore, e.pic_url,e.is_top,e.is_soul,e.create_time,e.create_user,e.click_num,e.is_lock, e.status,memo,e.thread_class,e.lastreply_time,e.lastreply_person,e.reply_num,d.* from (select b.pk as forum_pk,b.forum_name,c.pk as board_pk,c.board_name from tb_forum b,tb_forum_board c where b.pk=c.forum_pk ) d,(select * from (select a.*, rownum as rn from (select pk,thread_type,thread_title,thread_sendscore, pic_url,is_top,is_soul,create_time,create_user,click_num,is_lock, status,memo,thread_class,lastreply_time,lastreply_person,reply_num,board_pk from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_0" ) and thread_content like :"SYS_B_1" and create_time between to_date(:"SYS_B_2",:"SYS_B_3") and to_date(:"SYS_B_4",:"SYS_B_5") and thread_sendscore >=:"SYS_B_6" and status=:"SYS_B_7" order by lastreply_time desc,pk desc ) a) where rn>:"SYS_B_8" and rn<= :"SYS_B_9") e where d.board_pk=e.board_pk 3081.574468 107608.106383 47 144834 5057581 12041.859524 97.136299 47 55765 420 0.978723 46 1 1996 15144 1 0 0 0 0 1 CHOOSE 21 21 0 878662CC JDBC Thin Client 0 0 0 0 151485095 1233773222 select count(*) from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_0" ) and thread_content like :"SYS_B_1" and create_time between to_date(:"SYS_B_2",:"SYS_B_3") and to_date(:"SYS_B_4",:"SYS_B_5") and thread_sendscore >=:"SYS_B_6" and status=:"SYS_B_7" 3081.574468 107586.914894 47 144834 5056585 109925.76087 97.135735 0 23396 46 0.978723 46 1 732 9028 1 0 0 0 0 1 CHOOSE 21 21 0 8786C644 JDBC Thin Client 0 0 0 0 151311319 247672292 select count(*) from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_0" ) and thread_title like :"SYS_B_1" and thread_content like :"SYS_B_2" and create_time between to_date(:"SYS_B_3",:"SYS_B_4") and to_date(:"SYS_B_5",:"SYS_B_6") and thread_sendscore >=:"SYS_B_7" and status=:"SYS_B_8" 2567.6 80435.8 5 12838 402179 80435.8 96.807889 0 24249 5 1.0 5 1 752 9168 1 0 0 0 0 0 CHOOSE 21 21 0 86887A40 JDBC Thin Client 0 0 0 0 14082221 3101289331 select e.pk,e.thread_type,e.thread_title,e.thread_sendscore, e.pic_url,e.is_top,e.is_soul,e.create_time,e.create_user,e.click_num,e.is_lock, e.status,memo,e.thread_class,e.lastreply_time,e.lastreply_person,e.reply_num,d.* from (select b.pk as forum_pk,b.forum_name,c.pk as board_pk,c.board_name from tb_forum b,tb_forum_board c where b.pk=c.forum_pk ) d,(select * from (select a.*, rownum as rn from (select pk,thread_type,thread_title,thread_sendscore, pic_url,is_top,is_soul,create_time,create_user,click_num,is_lock, status,memo,thread_class,lastreply_time,lastreply_person,reply_num,board_pk from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_00" ) and thread_title like :"SYS_B_01" and thread_content like :"SYS_B_02" and create_time between to_date(:"SYS_B_03",:"SYS_B_04") and to_date(:"SYS_B_05",:"SYS_B_06") and thread_sendscore >=:"SYS_B_07" and status=:"SYS_B_08" order by lastreply_time desc,pk desc ) a) where rn>:"SYS_B_09" and rn<= :"SYS_B_10") e where d.board_pk=e.board_pk 2567.6 80458.4 5 12838 402292 8559.404255 96.808786 5 56565 47 1.0 5 1 2016 15168 1 0 0 0 0 0 CHOOSE 21 21 0 86882428 JDBC Thin Client 0 0 0 0 11747714 242083954 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IVP"."TB_DOWNLOAD_LOG".* FROM "IVP"."TB_DOWNLOAD_LOG" 1505.4 19342.6 5 7527 96713 0.022665 92.217179 0 8619 4267009 1.0 5 2 868 2500 1 0 0 0 1 0 CHOOSE 21 21 0 87EAADC4 exp@bj169 (TNS V1-V3) 0 0 0 0 11177882 3845925164 select e.pk,e.thread_type,e.thread_title,e.thread_sendscore, e.pic_url,e.is_top,e.is_soul,e.create_time,e.create_user,e.click_num,e.is_lock, e.status,memo,e.thread_class,e.lastreply_time,e.lastreply_person,e.reply_num,d.* from (select b.pk as forum_pk,b.forum_name,c.pk as board_pk,c.board_name from tb_forum b,tb_forum_board c where b.pk=c.forum_pk ) d,(select * from (select a.*, rownum as rn from (select pk,thread_type,thread_title,thread_sendscore, pic_url,is_top,is_soul,create_time,create_user,click_num,is_lock, status,memo,thread_class,lastreply_time,lastreply_person,reply_num,board_pk from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_0" ) and create_time between to_date(:"SYS_B_1",:"SYS_B_2") and to_date(:"SYS_B_3",:"SYS_B_4") and thread_sendscore >=:"SYS_B_5" and status=:"SYS_B_6" and pk in ( select thread_pk from tb_forum_reply where reply_content like :"SYS_B_7") order by lastreply_time desc,pk desc ) a) where rn>:"SYS_B_8" and rn<= :"SYS_B_9") e where d.board_pk=e.board_pk 581.068627 266188.519608 102 59269 27151229 13782.349746 99.781708 204 67262 1970 1.0 102 1 2000 83324 1 0 0 0 0 0 CHOOSE 21 21 0 878597D4 JDBC Thin Client 0 0 0 0 608091657 1003152929 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IVP"."TB_USER_MARK_TMP".* FROM "IVP"."TB_USER_MARK_TMP" 533.5 8532.833333 6 3201 51197 0.020076 93.747681 0 6977 2550186 1.0 6 1 808 2368 1 0 0 0 0 0 CHOOSE 21 21 0 8636E454 exp@bj169 (TNS V1-V3) 0 0 0 0 7265889 1585005416 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IVP"."ENTERP_ATTRIBUTE_INFO_DB".* FROM "IVP"."ENTERP_ATTRIBUTE_INFO_DB" 445.5 279232.0 6 2673 1675392 0.209117 99.840455 0 10757 8011735 1.0 6 1 1468 27064 1 0 0 0 0 0 CHOOSE 21 21 0 87E74934 exp@bj169 (TNS V1-V3) 0 0 0 0 44647757 658187376 select a.USERNAME,c.USER_EMAIL,c.USER_JOB_KIND,c.USER_CAREER,c.PK,a.DOWN_DATE,c.user_realname from tb_download_log a,tb_user b,tb_user_profile c where a.USERNAME=b.USER_NAME and b.PK=c.USER_PK and a.memo=:"SYS_B_0" order by a.DOWN_DATE desc 342.0 1071884.5 2 684 2143769 133985.5625 99.968094 2 23574 16 1.0 2 2 956 29080 1 0 0 0 1 0 CHOOSE 21 21 0 89A0513C JDBC Thin Client 0 0 0 0 8439973 3575051415 (select a.s_id,c.user_email,c.User_job_kind,c.user_career,a.pk,a.tag,c.user_realname,a.est_time from leave_msg a,tb_user b,tb_user_profile c where (a.tag=:"SYS_B_0" or a.tag=:"SYS_B_1" or a.tag=:"SYS_B_2") and a.s_id=b.user_name and b.PK=c.user_pk and a.r_id=:"SYS_B_3" union select a.USERNAME,c.USER_EMAIL,c.USER_JOB_KIND,c.USER_CAREER,c.PK,:"SYS_B_4",c.user_realname,a.DOWN_DATE from tb_download_log a,tb_user b,tb_user_profile c where a.USERNAME=b.USER_NAME and b.PK=c.USER_PK and a.memo=:"SYS_B_5") order by est_time desc 335.857143 1072554.285714 7 2351 7507880 153222.040816 99.968686 7 38212 49 1.0 7 3 1128 35208 1 0 0 0 2 0 CHOOSE 21 21 0 89F1BED8 JDBC Thin Client 0 0 0 0 33377021 1269097881 select user_name from tb_user where pk in ( select user_pk from tb_user_profile where regist_time=( select max(regist_time) from tb_user_profile ) ) 329.4375 356522.6875 16 5271 5704363 356522.6875 99.907597 0 14590 16 1.0 16 3 576 4956 1 0 0 0 2 0 CHOOSE 21 21 0 8458FE60 JDBC Thin Client 0 0 0 0 17362447 474737120 select count(*) from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_0" ) and create_time between to_date(:"SYS_B_1",:"SYS_B_2") and to_date(:"SYS_B_3",:"SYS_B_4") and thread_sendscore >=:"SYS_B_5" and status=:"SYS_B_6" and pk in ( select thread_pk from tb_forum_reply where reply_content like :"SYS_B_7") 242.333333 108400.666667 3 727 325202 108400.666667 99.776447 3 30277 3 1.0 3 1 736 24736 1 0 0 0 0 0 CHOOSE 21 21 0 8F328A50 JDBC Thin Client 0 0 0 0 4851290 1616742257 select aa, hv from ( select /*+ ordered use_nl (b st) */ decode( st.piece , :"SYS_B_00" , lpad(to_char((e.buffer_gets - nvl(b.buffer_gets,:"SYS_B_01")) ,:"SYS_B_02") ,:"SYS_B_03")||:"SYS_B_04"|| lpad(to_char((e.executions - nvl(b.executions,:"SYS_B_05")) ,:"SYS_B_06") ,:"SYS_B_07")||:"SYS_B_08"|| lpad((to_char(decode(e.executions - nvl(b.executions,:"SYS_B_09") ,:"SYS_B_10", to_number(null) ,(e.buffer_gets - nvl(b.buffer_gets,:"SYS_B_11")) / (e.executions - nvl(b.executions,:"SYS_B_12"))) ,:"SYS_B_13")) ,:"SYS_B_14") ||:"SYS_B_15"|| lpad((to_char(:"SYS_B_16"*(e.buffer_gets - nvl(b.buffer_gets,:"SYS_B_17"))/:gets ,:"SYS_B_18")) , :"SYS_B_19") ||:"SYS_B_20"|| lpad( nvl(to_char( (e.cpu_time - nvl(b.cpu_time,:"SYS_B_21"))/:"SYS_B_22" , :"SYS_B_23") , :"SYS_B_24"),:"SYS_B_25") || :"SYS_B_26" || lpad( nvl(to_char( (e.elapsed_time - nvl(b.elapsed_time,:"SYS_B_27"))/:"SYS_B_28" , :"SYS_B_29") , :"SYS_B_30"),:"SYS_B_31") || :"SYS_B_32" || lpad(e.hash_value,:"SYS_B_33")||:"SYS_B_34"|| decode(e.module,null,st.sql_text ,rpad(:"SYS_B_35"||e.module,:"SYS_B_36")||st.sql_text) , st.sql_text) aa , e.hash_value hv from stats$sql_summary e , stats$sql_summary b , stats$sqltext st where b.snap_id(+) = :bid and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.hash_value(+) = e.hash_value and b.address(+) = e.address and b.text_subset(+) = e.text_subset and e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num and e.hash_value = st.hash_value and e.text_subset = st.text_subset and st.piece < :"SYS_B_37" and e.executions > nvl(b.executions,:"SYS_B_38") order by (e.buffer_gets - nvl(b.buffer_gets,:"SYS_B_39")) desc, e.hash_value, st.piece ) where rownum < :"SYS_B_40" 202.0 6006.0 1 202 6006 93.84375 96.636697 1 46526 64 1.0 1 1 1560 193980 1 0 0 0 0 0 CHOOSE 22 22 0 889DB1C4 SQL*Plus 0 0 0 0 1747493 1225383154 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IVP"."TOPIC".* FROM "IVP"."TOPIC" 163.166667 926.166667 6 979 5557 0.031244 82.382581 0 8183 177858 1.0 6 1 1108 2460 1 0 0 0 0 0 CHOOSE 21 21 0 87DA2D30 exp@bj169 (TNS V1-V3) 0 0 0 0 2273710 339447521 SELECT /*+NESTED_TABLE_GET_REFS+*/ "IVP"."ALUMNI_MESSAGE_BAK".* FROM "IVP"."ALUMNI_MESSAGE_BAK" 150.833333 26056.5 6 905 156339 1.017514 99.42113 0 7537 153648 1.0 6 1 808 5924 1 0 0 0 0 0 CHOOSE 21 21 0 87ECB378 exp@bj169 (TNS V1-V3) 0 0 0 0 7373651 193993355 select e.pk,e.thread_type,e.thread_title,e.thread_sendscore, e.pic_url,e.is_top,e.is_soul,e.create_time,e.create_user,e.click_num,e.is_lock, e.status,memo,e.thread_class,e.lastreply_time,e.lastreply_person,e.reply_num,d.* from (select b.pk as forum_pk,b.forum_name,c.pk as board_pk,c.board_name from tb_forum b,tb_forum_board c where b.pk=c.forum_pk ) d,(select * from (select a.*, rownum as rn from (select pk,thread_type,thread_title,thread_sendscore, pic_url,is_top,is_soul,create_time,create_user,click_num,is_lock, status,memo,thread_class,lastreply_time,lastreply_person,reply_num,board_pk from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_0" ) and thread_content like :"SYS_B_1" and create_time between to_date(:"SYS_B_2",:"SYS_B_3") and to_date(:"SYS_B_4",:"SYS_B_5") and pic_url is not null and thread_sendscore >=:"SYS_B_6" and status=:"SYS_B_7" order by lastreply_time desc,pk desc ) a) where rn>:"SYS_B_8" and rn<= :"SYS_B_9") e where d.board_pk=e.board_pk 108.0 8924.0 3 324 26772 6693.0 98.78978 3 56425 4 1.0 3 1 1996 15160 1 0 0 0 0 0 CHOOSE 21 21 0 8BF6EC44 JDBC Thin Client 0 0 0 0 895351 2337515147 select count(*) from tb_forum_thread where board_PK=:"SYS_B_0" and thread_content like :"SYS_B_1" and create_time between to_date(:"SYS_B_2",:"SYS_B_3") and to_date(:"SYS_B_4",:"SYS_B_5") and thread_sendscore >=:"SYS_B_6" and status=:"SYS_B_7" 108.0 40519.5 14 1512 567273 40519.5 99.733462 0 21805 14 1.0 14 1 728 7476 1 0 0 0 0 0 CHOOSE 21 21 0 89B8CD84 JDBC Thin Client 0 0 0 0 14486695 3072109460 select e.pk,e.thread_type,e.thread_title,e.thread_sendscore, e.pic_url,e.is_top,e.is_soul,e.create_time,e.create_user,e.click_num,e.is_lock, e.status,memo,e.thread_class,e.lastreply_time,e.lastreply_person,e.reply_num,d.* from (select b.pk as forum_pk,b.forum_name,c.pk as board_pk,c.board_name from tb_forum b,tb_forum_board c where b.pk=c.forum_pk ) d,(select * from (select a.*, rownum as rn from (select pk,thread_type,thread_title,thread_sendscore, pic_url,is_top,is_soul,create_time,create_user,click_num,is_lock, status,memo,thread_class,lastreply_time,lastreply_person,reply_num,board_pk from tb_forum_thread where board_PK=:"SYS_B_0" and thread_content like :"SYS_B_1" and create_time between to_date(:"SYS_B_2",:"SYS_B_3") and to_date(:"SYS_B_4",:"SYS_B_5") and thread_sendscore >=:"SYS_B_6" and status=:"SYS_B_7" order by lastreply_time desc,pk desc ) a) where rn>:"SYS_B_8" and rn<= :"SYS_B_9") e where d.board_pk=e.board_pk 108.0 40563.142857 14 1512 567884 2167.496183 99.733748 14 53610 262 1.0 14 1 1992 13592 1 0 0 0 0 0 CHOOSE 21 21 0 8B3671FC JDBC Thin Client 0 0 0 0 14924303 3366650203 select count(*) from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_0" ) and thread_content like :"SYS_B_1" and create_time between to_date(:"SYS_B_2",:"SYS_B_3") and to_date(:"SYS_B_4",:"SYS_B_5") and pic_url is not null and thread_sendscore >=:"SYS_B_6" and status=:"SYS_B_7" 108.0 8919.666667 3 324 26759 8919.666667 98.789192 0 23701 3 1.0 3 1 732 9412 1 0 0 0 0 0 CHOOSE 21 21 0 8A2E3C7C JDBC Thin Client 0 0 0 0 704723 1061033479 select e.pk,e.thread_type,e.thread_title,e.thread_sendscore, e.pic_url,e.is_top,e.is_soul,e.create_time,e.create_user,e.click_num,e.is_lock, e.status,memo,e.thread_class,e.lastreply_time,e.lastreply_person,e.reply_num,d.* from (select b.pk as forum_pk,b.forum_name,c.pk as board_pk,c.board_name from tb_forum b,tb_forum_board c where b.pk=c.forum_pk ) d,(select * from (select a.*, rownum as rn from (select pk,thread_type,thread_title,thread_sendscore, pic_url,is_top,is_soul,create_time,create_user,click_num,is_lock, status,memo,thread_class,lastreply_time,lastreply_person,reply_num,board_pk from tb_forum_thread where board_pk in ( select pk from tb_forum_board where forum_pk = :"SYS_B_00" ) and thread_content like :"SYS_B_01" and thread_type=:"SYS_B_02" and create_time between to_date(:"SYS_B_03",:"SYS_B_04") and to_date(:"SYS_B_05",:"SYS_B_06") and thread_sendscore >=:"SYS_B_07" and status=:"SYS_B_08" order by lastreply_time desc,pk desc ) a) where rn>:"SYS_B_09" and rn<= :"SYS_B_10") e where d.board_pk=e.board_pk 93.0 4907.0 1 93 4907 0.0 98.104748 1 57083 0 1.0 1 1 2016 15168 1 0 0 0 0 0 CHOOSE 21 21 0 897A26D0 JDBC Thin Client 0 0 0 0 126558

Elapsed Time
Description
This is the amount of total elapsed time that has been used for all executions of this SQL cursor. This is a useful metric in tuning SQL. This time number includes both CPU and wait time. Wait time may include time spent waiting for I/O, or a particular Oracle wait event such as the shared pool latch. Further investigation on how to best tune a particular SQL statement can be done by using the Explain Plan drilldown, or if you are on NT, the Tune SQL Statement drilldown as well. Data Source:
select elapsed_time from v$sql
User Action:
 If the SQL cursor is performing poorly, most likely the elapsed time will be high. Determine how much of the elapsed time is CPU or wait related. Tune the statement accordingly.


Top SQL
This chart provides the ability to identify and show the details of the top
25 (default) resource consuming SQL statements in the shared pool's
library cache. The default count of returned rows (25) and SQL sort
metric can be changed as required. In addition, there are 25 filters that
can be applied to allow a highly customized view of the SQL cache.
For instance, you could create a Top SQL chart that shows the top 50
SQL statements performing more than 1000 disk reads by a particular
user against a particular table or view, etc. Up to 10 filters can be
applied simultaneously in the same query.
Once the list of top resource consuming SQL statements has been
retrieved you can double click on one to get a detailed list of statistics
and full text for a particular statement. Further investigation on how to
best tune a particular SQL statement can be done by using the Explain
Plan drilldown, or if you are on NT, the Tune SQL Statement drilldown
as well.
Improving SQL performance is usually the most effective way for
improving application performance. In identifying statements with the
most potential to improve performance, you may want to consider the
statements with the highest buffer gets or physical reads. Buffer gets,
or logical reads, is one of the contributors to CPU time, as it requires
locating and retrieving buffers from the cache. Looking at poor
performing SQL statements by physical reads gives you an overview of
the number of read requests that resulted in access to datafiles on disk
for each statement.
Another factor worth considering is the frequency at which the
statement is executed. A statement with the a high number of buffer
gets or physical reads may only be executed once a day, far less than
another statement with a lower number of buffer gets that is executed
many thousands of times a day.
There are numerous reasons why a query would result in excessive
buffer gets; for example, a full table scan is being performed.
To help further diagnose a potential problem, a number of drilldown
charts are available including:
PDF created with pdfFactory Pro trial version www.pdffactory.com
Tables Referenced by this Query: Lists tables
referenced by this query as well as various optimizer
statistics.
Top SQL (Physical Reads): Presents SQL sorted by
Physical Reads.
Top SQL (Buffer Gets): Presents SQL sorted by Buffer
Gets.
Top SQL (Sorts): Presents SQL sorted by Sorts.
Tuning these statements will result in less CPU time for the
application. Execution count is also important when determining which
SQL statement should be tuned first. The statement with the highest
buffer get count may be executed once a day, far less often than
another statement in the list. Tuning the SQL statement with the
highest count may not produce as much of an improvement for the
application or system in this case as tuning the statement that is
executed much more frequently.
This chart will identify the SQL statements that are performing the
most buffer gets. There could be many reasons why a query is
performing these buffer gets, with full table scans being a common
culprit. To help further diagnose the cause of the problem there are
number of drilldown charts available. The SQL shown on this chart is
limited to a fixed number of characters. To see which tables this query
references and to check on the validity statistics used by the
optimizer, use the Tables Referenced by this Query Chart. To see SQL
sorted by Physical Reads, use the Top SQL (Physical Reads) Chart.
Further investigation on how to best tune a particular SQL statement
can be done by selecting any one of SQL statements listed and using
the Explain Plan drilldown, or if you are on NT, the Tune SQL
Statement drilldown as well.
To determine which session, program or application is responsible for
executing a particular SQL statement, you can use the Sessions
Currently Executing this SQL Statment drilldown. This will return a list
of sessions that are currently executing the selected SQL statement. If
there are no sessions executing the SQL statement at current
moment, no data will be returned.
This chart includes the following data items:
SQL Text
PDF created with pdfFactory Pro trial version www.pdffactory.com
Disk Reads Per Execution
Buffer Gets Per Execution
Disk Reads
Buffer gets
Executions
Buffer Gets Per Row
Buffer Cache Hit Ratio
Sorts
Shareable Memory
Rows Processed
Parse Calls Per Execution
Parse Calls
Loads
Persistent Memory
Runtime Memory
Loaded Versions
Open Versions
Users Opening
Users Executing
PDF created with pdfFactory Pro trial version www.pdffactory.com
Invalidations
Child Number
Optimizer Mode
Parsing User Id
Parsing Schema Id
Kept Versions
Address
Module
Module Hash
Action
Action Hash
Serializable Aborts
Outline Category
CPU Time
Elapsed Time
PDF created with pdfFactory Pro trial version www.pdffactory.com