分享
分销 收藏 举报 申诉 / 33

类型ORACLE调优性能.ppt

  • 上传人:仙人****88
  • 文档编号:14772562
  • 上传时间:2026-11-19
  • 格式:PPT
  • 页数:33
  • 大小:124.50KB
  • 下载积分:10 金币
  • 配套讲稿:

    如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。

    特殊限制:

    部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。

    关 键  词:
    ORACLE 性能
    资源描述:
    单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Performance Tuning By Example,Zhu,Chao,Chao_Ping,Know The database,How Oracle Store the data,How Oracle Access the data,How Oracle manage concurrency,How Oracle Do Select/DML,Know the application,How application use the data,Store/Manage the data as it is used,How to use oracle,Data is stored in tables,Heap table(Most commonly used),Partitioned table,Index organized table,Cluster,Hash Cluster,Index,Materialized View,External Tables,How oracle store the data,How oracle access the data,Single Table Access Path,Full Table Scan,ROWID unique scan,Index unique scan,Index range scan,Index(fast)full scan,Bitmap Index,Index and-equal,Table Join,Hash Join,Nested loop join,Sort-merge join,How oracle manage concurrency,Row Level Lock,SCN(System,Change Number)and rollback segment,Readers dont block writers,Writers dont block readers,Writers dont block writers(not the same row)-Row Level Lock,Support tens of thousands of users,How Oracle process SQL,Stage 1:Create a Cursor,Stage 2:Parse the Statement,(Most expensive step),Stage 3:Bind Any Variables,Stage 4:Run the Statement,(Most expensive step),Stage 5:Fetch Rows of a Query,(can be expensive),Stage 6:Close the Cursor,Optimizer,Optimizer decide the SQL execution plan,which is critical to SQL performance.,RBO,(Rule Based Optimizer),Since oracle 5(maybe?),No change since oracle 7.3,desupported in oracle 10,Stable,generate the execution plan according fixed rules,Not necessary good,Stable,Do not support new oracle,features(like,IOT,partition,materialized view,),CBO,(Cost based optimizer),Since oracle 7.3,Support all oracle features,Better than RBO in most case,Can be bad in some case,Choose the best execution plan according to its internal,算法,Can use event 10053 to find why oracle choose this,Need to analyze schema to get the best execution plan,SQL Tuning,Requirement,s,:,Know How Oracle Store the data,Know how oracle process SQL,Know how oracle access the data,Target:,Use less resource,let oracle do less work,Make application runs faster,SQL Tuning,When to tune SQL,Need speedup:Application running too slow,Need scale up:Oracle using too much resource,Cannot support more users,How we tune SQL,Understand the business requirement,Analyze the schema design,Tune the SQL,Explain plan,AUTOTRACE(SQL*Plus),SQL,Trace+Tkprof,Event 10046 detailed trace,Try hint/rewrite SQL,Analyze table/index/Build new index/,TOP 10 Error,Bad Connection Management,Bad Use of Cursor And Shared Pool,Getting database I/O wrong,Redo Log setup wrong,Serialization of data blocks in the buffer cache,Long Full Table Scan,Disk Sort,High Amount of Recursive SQL(SYS),Schema Error and Optimizer Problem,Use of Nonstandard init parameters,Case 1,Tune the application,according to business requirement,Case 1,Database System with high Disk read.,Physical Read per second is very high according to,statspack,report.(2000 Disk Read Per Second),Redo size is very high,13-15G redo per day,A lot of session waiting for db file sequential read,log file sync events,IO subsystem is overloaded.,i,ostat,always shows the busy%is 100%.,A,verage service time is slow:100ms,Application response time slow,especially for those IO heavy modules.,Case 1,Buy high end storage?,High end storage is very expensive,no so much budget.,Increase the SGA?,Oracle already used all the physical memory,Need to add the physical memory if we want to increase the SGA,Reduce the PIO request from the application?,Reduce the redo generated by the application.,Reduce the Disk Read request,Maybe we can split some application,let it run on other hosts.,C,ase 1,Lets analyze the Disk Read,S,tatspack,report shows all the SQL with high disk read.,A,nalyze the SQL,tune it.,We found the database contains all the data,while business only need 3 months data.,Does the history data impact the performance?,Case 1:Does the history data impact the performance,SQL:,Physical Reads Executions Reads per Exec%Total Hash Value,-,346,266 13,716 25.2 22.1 1437226764,select NVL(sum(A.QUANTITY),0)into:b0 from items A where,A.SELLER_ID=:b1 and A.DATE_DTM=SYSDATE-90,and A.STATUS:b2,Execution Plan:,SQL query the,seller_id,index,find the rows according to the,rowid,and filter the row that are older than 3 months,or status5 or type:b1.,We can know from the execution path,that all the history data will also be accessed,this really have bad impact on performance.,We need to remove the history data.,Many other SQL also query the data like that.,Case 1,We need to use partition to archive all the history data efficiently.,We used partition to archive the history data.,Case 1,While we tune the SQL,we found that 3 SQL in the,statspack,has the most disk read are not used by business at all!,Case 2,Store the data as it is used.,Case2:High Buffer-Gets/Disk Read,SQL with index range scan with high cost,Disk read high,Buffer Gets high,SQL already has the best execution path,SQL is used very frequently,Can we still fine tune it?,Case 2,SQL,desc,leavemessage,Name Null?Type,-,SHOP_ID NOT NULL NUMBER,MSG_NO NOT NULL NUMBER-Generated by sequence key,PK,USER_ID NUMBER,USER_NAME VARCHAR2(50),MSG_TIME DATE,MSG_TYPE VARCHAR2(10),LONG_COMMENT VARCHAR2(255),Primary Key:,(,msg_no,),Index:,(,Shop_id,),SQL:,select*from,leavemessage,where,shop_id,=:b1 order by,msg_time,Case 2,Total rows:10M rows.,Total Different,shop_id,:around 10K,Insert:1 records per second,Select:10 calls per second,According to business,only 3 months data is needed,while all the history data is kept without any use,Case 2,Oracle first scan the PK index,find the all the,rowid,with that,shop_id,Oracle scan the table according to,rowid,fetch the rows,Oracle do the sort in the end.,Case 2,Solution:,Use Partition to archive the history data,Use IOT to get good clustering factor,New PK:(,shop_id,msg_no,msg_time,),Using IOT to remove,redundency,All the records belong to a shop are physically stored in the same blocks,Store the Data as it is used/Accessed,Store the data according to the way it is accessed.,Case 3:High Enqueue Wait,现象,:,Application running slow,while system still have CPU/IO capacity,StatsPack,report shows 40%-90%Enqueue wait,Case 3,C,heck,v$sessstat,for Enqueue wait,Find out the sessions with most Enqueue wait timeSQL1,Event 10046 to trace the sessions with high Enqueue waitSQL2,Tkprof,with waits=y option to review the output file,Identify the poor SQL causing Enqueue waits,SQL1,1 select*from,2 (select/*+rule*/,a.username,a.sid,a.serial#,b.value,from,v$sesstat,b,v$session,a,v$statname,c,3 where,a.sid,=,b.sid,and,c.statistic,#=,b.statistic,#and,c.name,=,enqueue,waits,4 order by,b.value,desc,5 ),6*where,rownum,/,USERNAME SID SERIAL#VALUE,-,PAYMENT 449 2 641,BUYER 409 5 611,FEEDBACK 705 34049 303,TRUCKER 361 164 170,SQL2,SQL2:,SQLE,xec dbms_system.set_ev(449,2,10046,8,),View the trace file,$,tkprof,tracefilename.trc,enqueue.prf,waits=y(9i version,tkprof,),$v,i,enqueue.prf,OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS,call count,cpu,elapsed disk query current rows,-,Parse 5436 0.91 0.89 4 215 0 0,Execute 6273 3.16 13.88 321 9646 29490 1852,Fetch 4421 0.94 1.05 17 14605 5308 4421,-,total 16130 5.01 15.82 342 24466 34798 6273,Misses in library cache during parse:25,Elapsed times include waiting on following events:,Event waited on Times Max.Wait Total Waited,-Waited -,SQL*Net message to client 8278 0.01,0.01,SQL*Net message from client 8277 5.89 205.46,enqueue,49 0.38 7.05,db file sequential read 338 0.09 2.73,buffer busy waits 1 1.02,1.02,latch free 1 0.02,0.02,select,account_id,_ into:b0,from,balancesheet,where,account_id,=:b1 for update,call count,cpu,elapsed disk query current rows,-,Parse 164 0.02 0.03 0 0 0 0,Execute 164,0.10 8.17,0 553 403 0,Fetch 164 0.00,0.00,0 0 0 164,-,total 492 0.12 8.20 0 553 403 164,Misses in library cache during parse:1,Optimizer goal:FIRST_ROWS,Parsing user id:37,Rows Row Source Operation,-,1 FOR UPDATE,1 INDEX UNIQUE SCAN(object id 7770),Elapsed times include waiting on following events:,Event waited on Times Max.Wait Total Waited,-Waited -,SQL*Net message to client 166 0.00,0.00,SQL*Net message from client 166 0.04 0.63,enqueue,49 0.38 7.05,buffer busy waits 1 1.02,1.02,C,ase 3,V,iew the trace file directly via the vi,view the bind variable :b1 in the select statement that causing the most,enqueue,wait.,It,turns out that all the session are trying to select for update a single row.,BAD design,.,Performance by Design,Bad design.,A,ll the sessions content for the same row.,All the transactions are made serial by the design,
    展开阅读全文

    开通VIP折扣优惠下载文档

    提示  咨信网声明:本文档由用户上传并分享,仅供学习交流、研究之用,未经授权,严禁复制、发行、汇编、翻译或网络传播等,侵权必究。

    咨信网小贴示:
    1、浏览或下载可查看了解,请点击【咨信网告知】。
    2、下载文件中如有侵权或不适当内容,将立即纠正。
    3、如果因为网速或其他原因下载失败请重新下载,重复下载[60天内]不扣币。
    4、开具发票登录电脑端申请,请点击【索取发票】。

    咨信网客服组:【微信客服】

    自信AI创作助手
    关于本文
    本文标题:ORACLE调优性能.ppt
    链接地址:https://www.zixin.com.cn/doc/14772562.html

    Copyright ©2010-2026   All Rights Reserved  宁波自信网络信息技术有限公司 版权所有   |  客服电话:0574-28810668    微信客服:咨信网客服    投诉电话:18658249818   

    违法和不良信息举报邮箱:help@zixin.com.cn    文档合作和网站合作邮箱:fuwu@zixin.com.cn    意见反馈和侵权处理邮箱:1219186828@qq.com   | 证照中心

    12321jubao.png12321网络举报中心 电话:010-12321  jubao.png中国互联网举报中心 电话:12377   gongan.png浙公网安备33021202000488号  icp.png浙ICP备2021020529号-1 浙B2-20240490   


    关注我们 :微信公众号  抖音  微博  LOFTER               

    自信网络  |  ZixinNetwork