chapter4-19一次排序的调整与优化

1、.2Kt6*2& *t*&F0U. ! ! U* *Ktf *uA p * y a&2? &s* .2KtSspack :.U S spack 6*i*DF0 15 k6i, CPU İ/v*F0X,LorofilePer SecondPer TranionRedo size: Logical reads: Block changes: Physical reads: Physical writes:User calls:Parses: Hard parses:Sorts:Logons: Executes:876,497.0639,103.183,662.94287.40180.968,700

2、.70715.310.16143.470.034,277.9933.9425,821.521,151.98107.918.475.33256.3221.070.004.230.00126.03Tranions:06*Rows per Sort End Buffer Gets Threshold:10000- Notet resourreported for PL/SQL includes the resourused byall SQL sements called withhe PL/SQL code. As individual SQLsements are also reported,

3、it issible and valid for the summedtotal % to exceed 100CPUExecutions Gets per Exec %Total Time (s)ElapsdTime (s) Hash ValueBuffer Gets14,991,1608,7991,703.742.6 2888.673172.58 2784903626Module: InSiteXMLServer.exeselect PRODUCTMATERIALLISTITEMID From orderedmateriallist where bomid = :SYS_B_0 order

4、 by DisplayPriorityo Hash_Value=2784903626 SQL İ CPU Time oİ 2 888.67/3 878 =74.5%*İ Logical Reads oİ 14 991 160/(39 103.18*60*15)=42.6%&5 SQL CPU Time / Elapsd Time =2 888.67/3 172.58 = 91.1% ;5 SQL İ6 CPUİxo 91.1%*İ9 CPU oY SELECT * FROM v$sname WHERE NAME = sorts (rows);SISTIC# NAMECLASS247 sorts

5、 (rows)SQL select * from v$mys64where sVALUEistic#=247;SID SISTIC#100424763.5 SQL,SELECT productmateriallistitemid FROM orderedmateriallistWHERE bomid = 0018a68000093231ORDER BY displaypriority;PRODUCTMATERIALLISTITEMID0017c080000b51760017c080000b51770017c080000b51780017c080000b517b0017c080000b517c0

6、017c080000b51720017c080000b517d0017c080000b519d0017c080000b519e0017c080000b519f0017c080000b5171$393$s* .2Kt0017c080000b51730017c080000b51740017c080000b51750017c080000b51790017c080000b517a16 rowectedExecuted in 0.765 secondsdi,.5 16 Y 250 17 +63 = 250 112 &SQL Kt2fSQL o select sql_text2 from v$sqltex

7、t_with_newlines where hash_value =2784903626 order by piece;SQL_TEXTselect PRODUCTMATERIALLISTITEMID From orderedmateriallist where bomid = :SYS_B_0order by DisplayPriority ORDEREDMATERIALLIST .5*bm0,SQL select dbms_metadata.get_ddl(VIEW,ORDEREDMATERIALLIST) VIEW_SQL from dual; VIEW_SQLCREATE OR REP

8、LACE FORCE VIEW MESW.ORDEREDMATERIALLIST (BOMID, PRODUCTMATERIALLISTITEMID, DISPLAYPRIORITY) ASselect distinct pli.BOMID, pli.PRODUCTMATERIALLISTITEMID, pt.DISPLAYPRIORITY from PRODUCTMATERIALLISTITEM pli, PRODUCT p, PRODUCTBASE pb, PRODUCTTYPE pt where (pli.PRODUCTID = p.PRODUCTID and p.PRODUCTTYPE

9、ID = pt.PRODUCTTYPEID)or (pli.PRODUCTBASEID = pb.PRODUCTBASEID and pb.REVOFRCDID = p.PRODUCTID andp.PRODUCTTYPEID = pt.PRODUCTTYPEID)d.0lX,SQL SELECT child_number, plan_hash_value, object_sus2345FROM v$sqlWHERE hash_value = 2784903626 ORDER BY plan_hash_value/CHILD_NUMBLAN_HASH_VALUE OBJECT_SUS$394$

10、select * from v$myswhere sistic#=247; SID SISTIC#VALUE1004247250175Executed in 0.031 secondss* .2Kt0371084111312125960000000006201675262016752620167526201675262016752INVALID_UNAUTH INVALID_UNAUTH INVALID_UNAUTH VALIDINVALID_UNAUTH14 rowected.Go.5l*1l*7w SELECT operation, options, object_name2345FROM

11、 v$sql_planWHERE hash_value = 2784903626 AND child_number = 9/OPERATIONOPTIONSOBJECT_NAMESELECT SVIEW SORTEMENTUNIQUECONCATENATION NESTED LOOPS NESTED LOOPSNESTED LOOPSTABLE AC INDEX TABLE AC INDEX TABLE AC INDEX TABLE AC INDEX HASH JOINTABLE ACSBY INDEX ROWID RANGE SCANBY INDEX ROWID UNIQUE SCANBY

12、INDEX ROWID UNIQUE SCANBY INDEX ROWIDUNIQUE SCANPRODUCTMATERIALLISTITEMPRODMLISTITEM1SPRODUCTBASE PRODUCTBASE310 PRODUCT PRODUCT502 PRODUCTTYPEPRODUCTTYPE347SSSFULLPRODUCTTYPENESTED LOOPSNESTED LOOPSTABLE AC INDEX TABLE AC INDEXTABLE ACSBY INDEX ROWID RANGE SCANBY INDEX ROWIDUNIQUE SCAN FULLPRODUCTM

13、ATERIALLISTITEMPRODMPRODUCTLISTITEM1SPRODUCT502PRODUCTBASES24 rowected.$395$s* .2KtG 10046 .*.0cQ,SQL alter sesset events 10046 trace name context forever;Sesaltered.Elapsed: 00:00:00.04SQL SELECT productmateriallistitemid23456789101112131415FROM (SELECT DISTINCT pli.bomidbomid,ductmaterialli

14、stitemid productmateriallistitemid,pt.displaypriorityproductmateriallistitem pli,displaypriorityFROMproduct productbaseproducttypep,pb, ptWHERE(ductid = ductid AND ducttypeid = ducttypeid) OR (ductbaseid = ductbaseid AND pb.revofrcdid = ductid ANDducttyp

15、eid = ducttypeid)WHERE bomid = 0018a68000093231ORDER BY displaypriority;PRODUCTMATERIALL0017c080000b51760017c080000b51770017c080000b51780017c080000b517b0017c080000b517c0017c080000b51720017c080000b517d0017c080000b519d0017c080000b519e0017c080000b519f0017c080000b51710017c080000b51730017c080000b51

16、740017c080000b51750017c080000b51790017c080000b517a16 rowected.Elapsed: 00:00:00.83SQL alter sesset events 10046 trace name context off;Sesaltered.I6*o7X,SELECT productmateriallistitemid FROM (SELECT DISTINCT pli.bomidbomid,ductmateriallistitemid productmateriallistitemid,pt.displaypriorityFRO

17、M productmateriallistitem pli,displaypriorityproductp,$396$s* .2Ktproductbaseproducttypepb,ptWHERE (ductid = ductid AND ducttypeid = ducttypeid) OR (ductbaseid = ductbaseid AND pb.revofrcdid = ductid AND ducttypeid = ducttypeid)WHERE bomid = :SYS_B

18、_0ORDER BY displayprioritycallcountcpuelapseddiskquerycurrentrowsParse ExecuteFetch1130.000.000.640.020.000.631005031480000016total50.640.6513153016Misses in library cache during parse: 1 Optimizer goal: CHOOSEParsing user id: 24RowsRow Source Operation16162510720001616000000251072302510721616161616

19、251072VIEWSORT UNIQUE CONCATENATION NESTED LOOPS NESTED LOOPSNESTED LOOPSTABLE ACS BY INDEX ROWID PRODUCTMATERIALLISTITEMINDEX RANGE SCAN PRODMLISTITEM1 (object id 7800)TABLE ACS BY INDEX ROWID PRODUCTBASEINDEX UNIQUE SCAN PRODUCTBASE310 (object id 7790)TABLE ACS BY INDEX ROWID PRODUCTINDEX UNIQUE S

20、CAN PRODUCT502 (object id 7788)TABLE ACS BY INDEX ROWID PRODUCTTYPEINDEX UNIQUE SCAN PRODUCTTYPE347 (object id 7804)HASH JOINTABLE ACS FULL PRODUCTTYPENESTED LOOPSNESTED LOOPSTABLE ACS BY INDEX ROWID PRODUCTMATERIALLISTITEMINDEX RANGE SCAN PRODMLISTITEM1 (object id 7800)TABLE ACS BY INDEX ROWID PROD

21、UCTINDEX UNIQUE SCAN PRODUCT502 (object id 7788)TABLE ACS FULL PRODUCTBASE*alter sesset events 10046 trace name context offSQL _ 0.65 *b6 CPU S. sort unique*7 251 072 E* DBMS_XPLAN Cursor l*biX,$397$s* .2KtSQL select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT| Id |Operation| Name|Rows |Bytes

22、|Cost|SELECT S| VIEWEMENT|18 | 88218 | 88218 | 2790| 2489| 2489| 2489|2 |3 |4 |5 |6 |7 |SORT UNIQUE CONCATENATION NESTED LOOPSNESTED LOOPSNESTED LOOPS| 191K | 28M|180171735313|12 | 1212| 191K |24M816TABLE ACS BY INDEX ROWID |PRODUCTMATERIALLISTITEM |12 |12 |* 8 | 9 |* 10 | 11 |* 12 | 13 |* 14 |* 15

23、|INDEX RANGE SCAN|PRODMLISTITEM1|TABLE ACS BY INDEX ROWID |PRODUCTBASE|15611 |518KINDEX UNIQUE SCAN|PRODUCTBASE310|PRODUCT|PRODUCT502|PRODUCTTYPE|PRODUCTTYPE347|PRODUCTTYPE|1 |1 |30 |1 |TABLE ACS BY INDEX ROWID331INDEX UNIQUE SCANTABLE ACS BY INDEX ROWID6005INDEX UNIQUE SCANHASH JOIN| 191K |28M60024

24、M180517317531|16 |17 |18 |19 |TABLE ACS FULL|30 |NESTED LOOPSNESTED LOOPS| 191K |12 | 1212TABLE ACS BY INDEX ROWID |PRODUCTMATERIALLISTITEM |12 |12 |1 |1 |816|* 20 | 21 |* 22 |* 23 |INDEX RANGE SCAN|PRODMLISTITEM1|TABLE ACS BY INDEX ROWID |PRODUCT33INDEX UNIQUE SCAN|PRODUCT502|PRODUCTBASETABLE ACS F

25、ULL|15611 |518K13Predicate Information (identified by operation id):8 - ac10 - ac12 - ac- ac- ac20 - ac22 - acs(PLI.BOMID=:Z) s(PLI.PRODUCTBASEID=PB.PRODUCTBASEID) s(PB.REVOFRCDID=P.PRODUCTID) s(P.PRODUCTTYPEID=PT.PRODUCTTYPEID) s(P.PRODUCTTYPEID=PT.PRODUCTTYPEID) s(PLI.BOMID=:Z)s(PLI.PRODUCTID=P.PR

26、ODUCTID)23 - filter(LNNVL(PLI.PRODUCTBASEID=PB.PRODUCTBASEID) ORLNNVL(PB.REVOFRCDID=P.PRODUCTID)Note: cpu costing is off 10046 ilX*Fo&l 23 0,q SQL filter PRODUCTBASE 45I*PRODUCTBASE 15.6k q16 S nested loops*Li 1 %15.6k=251k *9İ CPU L& union e,SELECT productmateriallistitemid FROM (SELECT DISTINCT pl

27、i.bomid,ductmateriallistitemid,pt.displaypriorityFROM productmateriallistitem product-productbaseproducttype ptpli, p,pb,WHERE (ductid = ductid AND ducttypeid = ducttypeid)-OR-(ductbaseid = ductbaseid AND pb.revofrcdid = ductid AND ducttypeid = pt

28、.producttypeid)UNIONSELECT DISTINCT pli.bomid,ductmateriallistitemid, pt.displaypriorityFROM productmateriallistitem productproductbase producttypeWHERE (pli, p,pb,pt-(ductid = ductid AND ducttypeid = ducttypeid)-OR(ductbaseid = ductbaseid AND pb.revofrcdid

29、 = ductid AND ducttypeid = ducttypeid)WHERE bomid = :SYS_B_0ORDER BY displayprioritycallcountcpuelapseddiskquerycurrentrowsParse ExecuteFetch1130.010.000.010.000.000.0000000800000016$399$WHERE (ductid = ductid AND ducttypeid = ducttypeid)s* .2Kttotal50.020.0008

30、0016Misses inlibrary cache during parse: 1Optimizer goal: CHOOSEParsinguser id: 24RowsRow Source Operation1616161616161616161616160001616000000SORT ORDER BY VIEWSORT UNIQUE UNION-ALL NESTED LOOPSNESTED LOOPSTABLE ACS BY INDEX ROWID PRODUCTMATERIALLISTITEMINDEX RANGE SCAN PRODMLISTITEM1 (object id 78

31、00)TABLE ACS BY INDEX ROWID PRODUCTINDEX UNIQUE SCAN PRODUCT502 (object id 7788)TABLE ACS BY INDEX ROWID PRODUCTTYPEINDEX UNIQUE SCAN PRODUCTTYPE347 (object id 7804) NESTED LOOPSNESTED LOOPSNESTED LOOPSTABLE ACS BY INDEX ROWID PRODUCTMATERIALLISTITEMINDEX RANGE SCAN PRODMLISTITEM1 (object id 7800)TABLE ACS BY INDEX ROWID PRODUCTBASEINDEX UNIQUE SCAN PRODUCTBASE310 (object id 7790)TABLE ACS BY INDEX ROWID PRODUCTINDEX UNIQUE SCAN PRODUCT502 (object id 7788)TABLE ACS BY INDEX ROWID PRODUCTTYPEINDEX UNIQU