oracle. sql tuning,Oracle SQL TUNING(sqlid文本方式)

1.给权限grant ADVISOR  to system;(system应该有)2.查看原来SQL的执行计划alter session set statistics_level=all;set serveroutput offset linesize 222set pagesize 1000set long 99999select * from table(dbms_xplan.display_awr('7ysgr9qfhfgtr')); (7ysgr9qfhfgtr 是sql_id) (awr里面的执行计划)SQL>  select * from table(dbms_xplan.display_awr('7ysgr9qfhfgtr'));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID 7ysgr9qfhfgtr--------------------INSERT INTO di_te_purstockbill (PURORG, plant,purchasereqno, itemno,purstockbillid,   ordtype, client, purdocno,  createddate, vendorno,purdocitemno, mtlno, ordpriceunit,  netvalue, vendordesc, purgrp,itemdeliverydate, quantity,  price,purgrpdesc,buyliststrdes,storelocatioPlan hash value: 3278049608-----------------------------------------------------------------------------------------------------------------------------| Id  | Operation                                   | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------------------------------------|  4111 |   132K|       |    33   (0)| 00:00:01 ||  55 |             NESTED LOOPS OUTER            |                       |  3265 |  1138K|       | 24734   (1)| 00:04:57 ||  56 |              NESTED LOOPS                 |                       |  3265 |  1042K|       | 24442   (1)| 00:04:54 ||* 57 |               HASH JOIN                   |                       |   934 |   222K|       | 21638   (1)| 00:04:20 ||* 58 |                HASH JOIN RIGHT OUTER      |                       |  1097 |   206K|       | 21608   (1)| 00:04:20 ||* 59 |                 TABLE ACCESS FULL         | PURGROUPTRL           |   249 |  6723 |       |     4   (0)| 00:00:01 ||* 60 |                 HASH JOIN OUTER           |                       |  1097 |   177K|       | 21603   (1)| 00:04:20 ||* 61 |                  HASH JOIN                |                       |  1097 |   142K|       | 21570   (1)| 00:04:19 ||  62 |                   VIEW                    | index$_join$_027      |   249 |  3237 |       |     3  (34)| 00:00:01 ||* 63 |                    HASH JOIN              |                       |       |       |       |            |          ||* 64 |                     INDEX FAST FULL SCAN  | PURGROUP_U            |   249 |  3237 |       |     1   (0)| 00:00:01 ||  65 |                     INDEX FAST FULL SCAN  | SYS_C0023218          |   249 |  3237 |       |     1   (0)| 00:00:01 ||* 66 |                   HASH JOIN RIGHT OUTER   |                       |  1099 |   128K|       | 21567   (1)| 00:04:19 ||* 67 |                    TABLE ACCESS FULL      | PURDOCTYPETRL         |    47 |  1598 |       |     4   (0)| 00:00:01 ||* 68 |                    HASH JOIN              |                       |  1099 | 94514 |       | 21562   (1)| 00:04:19 ||  69 |                     VIEW                  | index$_join$_029      |    21 |   315 |       |     3  (34)| 00:00:01 ||* 70 |                      HASH JOIN            |                       |       |       |       |            |          ||* 71 |                       INDEX FAST FULL SCAN| PURDOCTYPE_U          |    21 |   315 |       |     1   (0)| 00:00:01 ||  72 |                       INDEX FAST FULL SCAN| SYS_C0023208          |    21 |   315 |       |     1   (0)| 00:00:01 ||* 73 |                     HASH JOIN             |                       |  1577 |   109K|       | 21559   (1)| 00:04:19 ||* 74 |                      INDEX RANGE SCAN     | PUROUTSTOBILLVOUTYP_U |     2 |    14 |       |     1   (0)| 00:00:01 ||* 75 |                      TABLE ACCESS FULL    | PURDOCHEADER          |  2490K|   152M|       | 21546   (1)| 00:04:19 ||* 76 |                  TABLE ACCESS FULL        | ADUSER                |  4110 |   132K|       |    33   (0)| 00:00:01 ||* 77 |                TABLE ACCESS FULL          | PURVENDORMASTER       |  4241 |   211K|       |    29   (0)| 00:00:01 ||* 78 |               TABLE ACCESS BY INDEX ROWID | PURDOCITEM            |     3 |   249 |       |     3   (0)| 00:00:01 ||* 79 |                INDEX RANGE SCAN           | PURDOCITEM_U          |     4 |       |       |     2   (0)| 00:00:01 ||  80 |              TABLE ACCESS BY INDEX ROWID  | PURREQ                |     1 |    30 |       |     2   (0)| 00:00:01 ||* 81 |               INDEX UNIQUE SCAN           | PURREQ_U              |     1 |       |       |     1   (0)| 00:00:01 ||  82 |            TABLE ACCESS BY INDEX ROWID    | PURSASL               |     1 |    29 |       |     3   (0)| 00:00:01 ||* 83 |             INDEX RANGE SCAN              | PURSASL_U             |     1 |       |       |     2   (0)| 00:00:01 ||* 84 |           TABLE ACCESS FULL               | MMRGENMTL             |   195K|    22M|       |  3555   (2)| 00:00:43 |---------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------9 - access("C"."CLIENT"="M"."CLIENT"(+) AND "C"."MTLNO"="M"."MTLNO"(+))11 - access("J"."CREATEDBY"="K"."NAME"(+) AND "J"."CLIENT"="K"."CLIENT"(+))12 - filter("K"."LANGUAGE"(+)=:3)15 - access("B"."VENDORNO"="D"."VENDORNO" AND "B"."CLIENT"="D"."CLIENT")16 - access("F"."PURGROUPID"="G"."PURGROUPID"(+))17 - filter("G"."LANGUAGE"(+)=:3)18 - access("B"."CLIENT"="L"."CLIENT"(+) AND "B"."CREATEDBY"="L"."NAME"(+))19 - access("B"."PURGRP"="F"."PURGROUP" AND "B"."CLIENT"="F"."CLIENT")21 - access(ROWID=ROWID)22 - filter("F"."CLIENT"=:2)24 - access("H"."PURDOCTYPEID"="I"."PURDOCTYPEID"(+))25 - filter("I"."LANGUAGE"(+)=:3)26 - access("B"."ORDTYPE"="H"."PURDOCTYPE" AND "B"."CLIENT"="H"."CLIENT" AND"B"."PURCHASINGDOCCTG"="H"."PURDOCCTG")28 - access(ROWID=ROWID)29 - filter("H"."CLIENT"=:2 AND "H"."PURDOCCTG"='F')31 - access("B"."ORDTYPE"="A"."ORDTYPE" AND "B"."PURCHASINGDOCCTG"="A"."PURDOCCTG" AND "B"."CLIENT"="A"."CLIENT")32 - access("A"."CLIENT"=:2 AND "A"."PURDOCCTG"='F')33 - filter("B"."PURCHASINGDOCCTG"='F' AND "B"."CLIENT"=:2)34 - filter("L"."LANGUAGE"(+)=:3 AND "L"."CLIENT"(+)=:2)35 - filter("D"."CLIENT"=:2)36 - filter(NVL("C"."PURDOCDELETIONINDIC",'N')='N')37 - access("C"."PURDOCNO"="B"."PURDOCNO" AND "C"."CLIENT"=:2)filter("C"."CLIENT"=:2)39 - access("C"."PURCHASEREQNO"="J"."PURCHASEREQNO"(+) AND "C"."ITEMNO"="J"."ITEMNO"(+) AND "J"."CLIENT"(+)=:2)41 - access("E"."PURDOCNO"(+)="C"."PURDOCNO" AND "E"."PURDOCLINENO"(+)="C"."PURDOCLINENO" AND "E"."CLIENT"(+)=:2)filter("E"."CLIENT"(+)=:2)42 - filter("M"."CLIENT"(+)=:2)44 - filter("DI_OPERTYPE"<>'D')48 - filter("DI_OPERTYPE"<>'D')51 - access("C"."CLIENT"="M"."CLIENT"(+) AND "C"."MTLNO"="M"."MTLNO"(+))53 - access("J"."CREATEDBY"="K"."NAME"(+) AND "J"."CLIENT"="K"."CLIENT"(+))54 - filter("K"."LANGUAGE"(+)=:3)57 - access("B"."VENDORNO"="D"."VENDORNO" AND "B"."CLIENT"="D"."CLIENT")58 - access("F"."PURGROUPID"="G"."PURGROUPID"(+))59 - filter("G"."LANGUAGE"(+)=:3)60 - access("B"."CLIENT"="L"."CLIENT"(+) AND "B"."CREATEDBY"="L"."NAME"(+))61 - access("B"."PURGRP"="F"."PURGROUP" AND "B"."CLIENT"="F"."CLIENT")63 - access(ROWID=ROWID)64 - filter("F"."CLIENT"=:2)66 - access("H"."PURDOCTYPEID"="I"."PURDOCTYPEID"(+))67 - filter("I"."LANGUAGE"(+)=:3)68 - access("B"."ORDTYPE"="H"."PURDOCTYPE" AND "B"."CLIENT"="H"."CLIENT" AND"B"."PURCHASINGDOCCTG"="H"."PURDOCCTG")70 - access(ROWID=ROWID)71 - filter("H"."CLIENT"=:2 AND "H"."PURDOCCTG"='F')73 - access("B"."ORDTYPE"="A"."ORDTYPE" AND "B"."PURCHASINGDOCCTG"="A"."PURDOCCTG" AND "B"."CLIENT"="A"."CLIENT")74 - access("A"."CLIENT"=:2 AND "A"."PURDOCCTG"='F')75 - filter("B"."PURCHASINGDOCCTG"='F' AND "B"."CLIENT"=:2)76 - filter("L"."LANGUAGE"(+)=:3 AND "L"."CLIENT"(+)=:2)77 - filter("D"."CLIENT"=:2)78 - filter(NVL("C"."PURDOCDELETIONINDIC",'N')='N')79 - access("C"."PURDOCNO"="B"."PURDOCNO" AND "C"."CLIENT"=:2)filter("C"."CLIENT"=:2)81 - access("C"."PURCHASEREQNO"="J"."PURCHASEREQNO"(+) AND "C"."ITEMNO"="J"."ITEMNO"(+) AND "J"."CLIENT"(+)=:2)83 - access("E"."PURDOCNO"(+)="C"."PURDOCNO" AND "E"."PURDOCLINENO"(+)="C"."PURDOCLINENO" AND "E"."CLIENT"(+)=:2)filter("E"."CLIENT"(+)=:2)84 - filter("M"."CLIENT"(+)=:2)-------------------------------------------------------------------------------看到有3个建议1.收集表信息2.固定执行计划3.考虑用UNION ALL 代替 UNIONSQL TUNNING 是否可以在生产环境中发挥重要作用，还需要进一步测试