资源描述
单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,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,
展开阅读全文