oracle dba手记1chapter3-12sql优化与调整实践

1、T= T= . ! #! $!L 0 9, İ-3F0 zH W6 İ0SQLI SQL HOracleYİ0SQLI SQL HOracleY SQL 0g SQLCREATETABLET1(IDNUMBER,NAMEVARCHAR2(30),TYPESQLCREATETABLET2(IDNUMBER,NAMEVARCHAR2(30),TYPESQLCREATEINDEXIND_T1_NAMEONT1SQLCREATEINDEXIND_T2_NAMEONT2SQLOT1SELECTROWNUM,OBJECT_NAME,2 FROMDBA_OBJECTSWHEREOBJECT_TYPE=205

2、8SQLOT2SELECTROWNUM,OBJECT_NAME,2 FROMDBA_OBJECTSWHEREOBJECT_TYPE=2058SQLSQLCREATEVIEWV_TECT*FROMT1UNIONALLSELECT*FROMG3SETAUTOTONEXP SQL SELECT *2 FROMV_T WHERENAMELIKE18İ0SQLPlanhashvalue:|Name|Rows|Bytes|Cost(%CPU)|01234|SELECT|5555|99|22|00:00:01|00:00:01|UNION-ALLPARTITION|TABLETABLESFULLİ0SQLP

3、lanhashvalue:|Name|Rows|Bytes|Cost(%CPU)|01234|SELECT|5555|99|22|00:00:01|00:00:01|UNION-ALLPARTITION|TABLETABLESFULL |SFULL |55|00:00:01|00:00:01PredicateInformation(identifiedbyoperation-filter(NAMELIKE-filter(NAMELIKEB%) -dynamiclingusedforthisOracle g0aK SQL 0 T1 T2 K0 SQLSELECT/*+INDEX(T1IND_T1

4、_NAME)*/2 FROMV_T WHERENAMELIKEPlanhashvalue:|Name|Rows|Bytes|Cost(%CPU)|01234|SELECT|5555|99|22|00:00:01|00:00:01|UNION-ALLPARTITION|TABLETABLESFULL |SFULL |55|00:00:01|00:00:01PredicateInformation(identifiedbyoperation-filter(NAMELIKE-filter(NAMELIKEg80 VIEW SQL SELECT/*+INDEX(V_T.T1IND_T1_NAME)*/

5、2FROMV_T WHERENAMELIKEPlanhashvalue:|Id |Rows|Bytes |Cost(%CPU)| 0 |SELECT|55| 2585 2 (0)|00:00:01İ0SQL|1345|UNION-ALL|999|(0)(0)(0)TABLESBYINDEXROWID|625INDEXRANGE|IND_T1_NAMETABLES|Information(identifiedbyoperation4-s(NAMELIKEfilter(NAMELIKE5-filter(NAMELIKEK T2 SQL SELECT/*+INDEX(V_T.T1IND_T1_NAM

6、E)INDEX(V_T.T2IND_T2_NAME)*/İ0SQL|1345|UNION-ALL|999|(0)(0)(0)TABLESBYINDEXROWID|625INDEXRANGE|IND_T1_NAMETABLES|Information(identifiedbyoperation4-s(NAMELIKEfilter(NAMELIKE5-filter(NAMELIKEK T2 SQL SELECT/*+INDEX(V_T.T1IND_T1_NAME)INDEX(V_T.T2IND_T2_NAME)*/2FROMV_T WHERENAMELIKE18Planhash|Rows| |01

7、23456|SELECT|5555|9999|22(0)(0)|(0)(0)(0)(0)|UNION-ALLTABLESBYINDEXROWID|6262INDEXRANGE|IND_T1_NAMETABLESBYINDEXROWID|INDEXRANGE|IND_T2_NAMEInformation(identifiedbyoperation4-s(NAMELIKEfilter(NAMELIKE6-s(NAMELIKEfilter(NAMELIKE=gG0 SQL CREATEORREPLACEVIEWV_T2SELECT*FROMT1A UNIONALL SELECT*FROMT2SQL

8、SELECT/*+INDEX(V_T.T1IND_T1_NAME)IND_T2_NAME)*/2FROMV_T WHERENAMELIKE18Planhashvalue:|Name|Rows|Bytes|Cost(%CPU)|01234|SELECT|5555|99|22|00:00:01UNION-ALLPARTITION|TABLETABLESFULL |SFULL |55|00:00:01|00:00:01İ0SQLPredicateInformation(identifiedbyoperation-filter(NAMELIKE-filter(NAMELIKESQL SELECT/*+

9、INDEX(V_T.AIND_T1_NAME)INDEX(V_T.BIND_T2_NAME)*/2FROMV_T WHERENAMELIKE18Planhash|Rows|0123456|SELECT|İ0SQLPredicateInformation(identifiedbyoperation-filter(NAMELIKE-filter(NAMELIKESQL SELECT/*+INDEX(V_T.AIND_T1_NAME)INDEX(V_T.BIND_T2_NAME)*/2FROMV_T WHERENAMELIKE18Planhash|Rows|0123456|SELECT|5555|9

10、999|22(0)|00:00:01(0)|00:00:01|UNION-ALL|TABLESBYINDEXROWID|6262(0)|00:00:01(0)|00:00:01(0)|00:00:01(0)|00:00:01INDEXRANGE|IND_T1_NAMETABLESBYINDEXROWID|INDEXRANGE|IND_T2_NAMEInformation(identifiedbyoperation4-s(NAMELIKEfilter(NAMELIKE6-s(NAMELIKEfilter(NAMELIKEgYİ=B= Oracle Oracle Y0B8 SQL 2SETAUTO

11、TONEXP SELECT *FROM (SELECT*FROMT1 UNIONALL SELECT*FROMT2)AWHERENAMELIKE18Planhashvalue:|Name|Rows|Bytes|Cost(%CPU)|01234|SELECT|5555|99|22|00:00:01|00:00:01|UNION-ALLPARTITION|TABLETABLESFULL |SFULL |55|00:00:01|00:00:01PredicateInformation(identifiedbyoperation-filter(T1.NAMELIKE-filter(T2.NAMELIK

12、Eİ0SQL3 Oracle SQLSELECT/*+INDEX(T1IND_T1_NAME)INDEX(T2IND_T2_NAME)*/2 FROM (SELECT*FROMT1 UNIONALL SELECT*FROMT2)AWHERENAMELIKE18Planhashvalue:|Name|Rows|Bytes|Cost(%CPU)|İ0SQL3 Oracle SQLSELECT/*+INDEX(T1IND_T1_NAME)INDEX(T2IND_T2_NAME)*/2 FROM (SELECT*FROMT1 UNIONALL SELECT*FROMT2)AWHERENAMELIKE1

13、8Planhashvalue:|Name|Rows|Bytes|Cost(%CPU)|01234|SELECT|5555|99|22|00:00:01|00:00:01|UNION-ALLPARTITION|TABLETABLESFULL |SFULL |55|00:00:01|00:00:01 SQLSELECT/*+INDEX(A.T1IND_T1_NAME)INDEX(A.T2IND_T2_NAME)*/2 FROM(SELECT*FROM18UNIONALL SELECT*FROMT2 )AWHERENAMELIKEPlanhashvalue:|Rows|0123456|SELECT|

14、5555|9999|22(0)|00:00:01(0)|00:00:01|UNION-ALL|TABLESBYINDEXROWID|6262(0)|00:00:01(0)|00:00:01(0)|00:00:01(0)|00:00:01INDEXRANGE|IND_T1_NAMETABLESBYINDEXROWID|INDEXRANGE|IND_T2_NAMEInformation(identifiedbyoperation4-s(T1.NAMELIKEfilter(T1.NAMELIKE6-s(T2.NAMELIKEfilter(T2.NAMELIKE|8UNION ALLGROUP BYD

15、STNCSQLSELECT2 FROM (SELECTNAME,COUNT(*) FROMT1 GROUPBYTYPE,NAME )A WHERENAMELIKE9Planhashvalue:İ0SQL|Name|Rows|Bytes|Cost(%CPU)|012|SELECT|999|665|00:00:01|00:00:01İ0SQL|Name|Rows|Bytes|Cost(%CPU)|012|SELECT|999|665|00:00:01|00:00:01|00:00:01| HASHGROUP| TABLESSQLSELECT/*+INDEX(A.T1*/2 FROM (SELECT

16、NAME,COUNT(*)FROMT1GROUPBY9NAME )WHERENAMELIKEPlanhash|Rows|0123|SELECT|ROWID |T1|9999|7762(15)| 00:00:01(15)| 00:00:01(0)|00:00:01(0)|00:00:01|HASHGROUPTABLESBYINDEXRANGE|IND_T1_NAME6h0K0eg SQLSETAUTOTSQLSELECT/*MARKUP*/2 FROM(SELECT*FROMUNIONALLSELECT*FROMT2)WHERENAMELIKE18Yv3 SQL0tkKİ=COLOBJECT_A

17、LIASFORMATSQLSELECTOBJECT_ALIAS,DEPTHFROM2 WHERESQL_IDIN(SELECTSQL_IDFROMV$SQLWHERESQL_TEXTLIKESELECT/*MARKUP*/İ0SQL01233Oracle VIEW + =gSETAUTOTONSQLSELECT/*+İ0SQL01233Oracle VIEW + =gSETAUTOTONSQLSELECT/*+INDEX(from$_subquery$_001.T1IND_T1_NAME)*/2 FROM(SELECT*FROMT1UNIONALLSELECT*FROMT2)WHERENAME

18、LIKE18Planhash|Rows|012345|SELECT|5555|999|22(0)|00:00:01(0)|00:00:01|UNION-ALL|TABLESBYINDEXROWID|625(0)|00:00:01(0)|00:00:01(0)|00:00:01INDEXRANGE|IND_T1_NAMETABLES|Gİ= SQL SQL G3İ000G3 SQL KSETAUTOTTRACE SQL SET TIMING ONSQLSELECT_ROWS23456789SELECTROWNUMROW_NUM,A.* (SELECTA.PRODUCT_ID,C.DRUG_NAM

19、E,C.MODE_NAME,A.MIDDLE_PACK_RATE FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG CWHERE B.PLAT_ID=59 ANDA.ENABLE_FLAG=1ANDA.PRODUCT_ID=İ0SQLANDA.DRUG_ID=AND (INSTR(UPPER(C.DRUG_NAME), ) OR INSTR(UPPER(C.ENGLISH_NAME), ) 0 OR INSTR(UPPER(C.WUBI_CODE), ) 0OR INSTR(UPPER(C.PINYIN_CODE), ) 0 OR INS

20、TR(UPPER(A.PRODUCT_NAME), ) 0 OR INSTR(UPPER(A.PINYIN_CODE), ) 0 OR INSTR(UPPER(A.WUBI_CODE), ) 0)WHEREROWNUM=;10İ0SQLANDA.DRUG_ID=AND (INSTR(UPPER(C.DRUG_NAME), ) OR INSTR(UPPER(C.ENGLISH_NAME), ) 0 OR INSTR(UPPER(C.WUBI_CODE), ) 0OR INSTR(UPPER(C.PINYIN_CODE), ) 0 OR INSTR(UPPER(A.PRODUCT_NAME), )

21、 0 OR INSTR(UPPER(A.PINYIN_CODE), ) 0 OR INSTR(UPPER(A.WUBI_CODE), ) 0)WHEREROWNUM=;10Elapsed:ExecutionPlanhashvalue:|Rows|01234567|SELECT| | |39393932|(1)|00:00:02(1)|00:00:02|COUNTSTOPKEY NESTEDNESTED|9211(1)|00:00:02(2)|00:00:02TABLETABLESS|58535|00:00:01|00:00:01|00:00:01|11INDEXRANGEInformation

22、(identifiedbyoperation-6-filter(A.ENABLE_FLAG=1AND(INSTR(UPPER(C.DRUG_NAME),)0 OR OR INSTR(UPPER(C.WUBI_CODE),)0 OR INSTR(UPPER(C.PINYIN_CODE),)0 OR INSTR(UPPER(A.PRODUCT_NAME),)0 OR INSTR(UPPER(A.PINYIN_CODE),)0 OR INSTR(UPPER(A.WUBI_CODE),)0)7-s(B.PLAT_ID=59ANDS835381consistent10rows3 3 0G 20 0SEL

23、ECTCOUNT(*)FROMINF_PRODUCT; İ0SQL=6 40Oracle INF_PRODUCT f?0:g+K=INF_PRODUCT 06G3uİ0SQL=6 40Oracle INF_PRODUCT f?0:g+K=INF_PRODUCT 06G3SETAUTOTSQLSELECT_ROWS23456789SELECTROWNUMROW_NUM,A.* (FROM INF_PRODUCT A, INF_PRODUCT_PROPERTY B, INF_DRUG CWHERE B.PLAT_ID=59 ANDA.ENABLE_FLAG=1ANDA.PRODUCT_ID=B.P

24、RODUCT_ID AND A.DRUG_ID = C.DRUG_IDAND (INSTR(UPPER(C.DRUG_NAME), ) OR INSTR(UPPER(C.ENGLISH_NAME), ) 0 OR INSTR(UPPER(C.WUBI_CODE), ) 0OR INSTR(UPPER(C.PINYIN_CODE), ) 0 OR INSTR(UPPER(A.PRODUCT_NAME), ) 0 OR INSTR(UPPER(A.PINYIN_CODE), ) 0 OR INSTR(UPPER(A.WUBI_CODE), ) 0)ROWNUM=;10Elapsed:Executi

25、onPlanhashvalue:|0123456789|SELECT|40404032|(0)(0)|(0)(0)(0)(0)(0)(0)(0)COUNTSTOPKEY NESTEDNESTED31101|52835|52835INDEXRANGE|111INDEXUNIQUEINDEXRANGE9İ0SQLPredicateInformation(identifiedbyoperation-6-s(B.PLAT_ID=59ANDSİ0SQLPredicateInformation(identifiedbyoperation-6-s(B.PLAT_ID=59ANDSconsistentrows

26、g 0SQL1970.1083472500g SQLSELECT_NAME,_TYPEFROMS2 WHERETABLE_NAMEIN(INF_PRODUCT,INF_DRUG,3 AND_TYPEIN(P,noSQLSELECTDISTINCT_TYPEFROMS2 WHERETABLE_NAMEIN(INF_PRODUCT,INF_DRUG,CSQLSELECTTABLE_NAME,INDEX_NAME,COLUMN_NAME,2 FROM 3 WHERETABLE_NAMEIN(INF_PRODUCT,INF_DRUG,11111111111121300a08G+3 BTREE İ0SQ

27、LSQLSELECTINDEX_NAME,INDEX_TYPE,DISTINCT_KEYSFROM2 WHEREINDEX_NAMEIN(INF_DRUG_HAOCAI_FLAG,32N636Oracle Pİ0SQLSQLSELECTINDEX_NAME,INDEX_TYPE,DISTINCT_KEYSFROM2 WHEREINDEX_NAMEIN(INF_DRUG_HAOCAI_FLAG,32N636Oracle PaN8ggG8mE8+g Oracle E8KCG2Oracle m0m+SQLEXPLAINPLAN2 SELECT_ROWSSQLSELECT*FROMPlanhashva

28、lue:|Bytes|Cost(%CPU)|01234567|SELECT|(1)|(1)|(1)|COUNT STOPKEY NESTED LOOPS NESTED LOOPS211|TABLETABLESS|3544K22 9 |1 1 INDEXRANGE|INF_PRODUCT_PLATInformation(identifiedbyoperation- filter(A.ENABLE_FLAG=1 AND A.DRUG_ID=C.DRUG_ID AND OR INSTR(UPPER(A.PRODUCT_NAME),)0 OR -s(B.PLAT_ID=59AND26İ 6 0? IN

29、F_PRODUCT yXS SQLSELECTTABLE_NAME,NUM_ROWS,BLOCKSFROMİ0SQL2 WHERETABLE_NAMEIN(INF_PRODUCT,INF_PRODUCT_PROPERTY,OracleCBO?Gk0P3 0 Pİ10203İ0SQL2 WHERETABLE_NAMEIN(INF_PRODUCT,INF_PRODUCT_PROPERTY,OracleCBO?Gk0P3 0 Pİ102030v10203 SQLSQLSELECT*FROMOracleDatabase10griseEdi

30、tionRelease.0-PL/SQLRelease.0-TNSfor32-bitWindows:.0-NLSRTL.0-SQLEXPLAINPLAN2 SELECT_ROWSSQLSELECT*FROMPlanhashvalue:|Bytes|01234567|SELECT|COUNTSTOPKEY NESTEDNESTED201TABLETABLESS|3544K22 9 |1 1 INDEXRANGE|INF_PRODUCT_PLATInformation(identifiedbyoperation-6-filter(A.

31、ENABLE_FLAG=1AND OR INSTR(UPPER(A.PRODUCT_NAME),)0 OR 7-26s(B.PLAT_ID=59ANDİ0SQLSQLEXPLAINPLAN2İ0SQLSQLEXPLAINPLAN2345678SELECT/*+ (_ROWSSELECTROWNUMROW_NUM,A.* (SELECT/*+ORD_ORDEREDNO_EXPANDUSE_NL(AC)USE_NL(B)FULL(C)INDEX(AINF_DRUG_ID)SQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY); Planhash| |* |* |4720|

32、249 |NESTEDLOOPS NESTEDLOOPSS INDEX RANGE SCAN|345678|2 3 1 1 |121|9Information(identifiedbyoperation-6 - filter(A.ENABLE_FLAG=1 AND (INSTR(UPPER(C.DRUG_NAME),)0 OR -27s(B.PLAT_ID=59AND0gX0V3NESTED LOOPX377SQLSQLSQLSELECT*FROMOracleDatabase10gSQLEXPLAINPLANriseEditionRelease.0-2 SELE

33、CT_ROWSİ0SQLSQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY); Planhashvalue:| | |* |* |İ0SQLSQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY); Planhashvalue:| | |* |* |(0)|(0)|(0)|(0)|(0)|NESTEDLOOPS NESTEDLOOPS INDEX RANGE SCAN INDEX UNIQUE SCAN|345678931101 |111|62|9 |Information(identifiedbyoperation-6-29s(B.PLAT_I

34、D=59AND =0Oracle G8:8wG LOOP SV30G+B g10201 = 10203 0EXPLAINPLAN23456789SELECT/*+ (_ROWSSELECTROWNUMROW_NUM,A.* (SELECT/*+ORD_ORDEREDNO_EXPANDUSE_NL(AC)USE_NL(B)FULL(C)FULL(A)*/ A.PRODUCT_ID, C.DRUG_NAME, C.MODE_NAME, A.MIDDLE_PACK_RATESQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY); Planhashvalue:

35、İ0SQL|Rows |Bytes|Cost(%CPU)|01234567|SELECT|40İ0SQL|Rows |Bytes|Cost(%CPU)|01234567|SELECT|4040|4032|(8)|(8)|(8)|(8)|(0)|(7)|(0)|COUNTSTOPKEY NESTEDNESTEDTABLETABLESS|585353544K22 9 21|11INDEXRANGE|INF_PRODUCT_PLATInformation(identifiedbyoperation-6 - filter(A.ENABLE_FLAG=1 AND A.DRUG_ID=C.DRUG_ID

36、AND OR INSTR(UPPER(A.PRODUCT_NAME),)0 OR 7-26s(B.PLAT_ID=59ANDSQLEXPLAINPLANFORSELECT*FROMSQLSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY); Planhashvalue:|Cost(%CPU)| | |SELECT| TABLE| 120 (7) |00:00:02| 120 (7) |00:00:02SFULL |INF_PRODUCT|8=X3r0 10201 10203 10203 = CBO 0 NESTED LOOP İ60Metalink=GBugm+Bug?10

37、104102030 Oracle=GgINDEX +BugOracle Bug 0E_x SQL | SQL Yg SQL İ0SQLCBO=İ0SQLCBO=_SQLCrK SQL 0650aCBO J?3CBO8g_a53g8s6h=COL 6065yf05y_İ=K1 2 0Oracle 3fK3 0Oracle r3createtabletect*fromSQLotselect*from6273SQL12546SQL25092SQL50184SQLotselect*fromotselect*fromotselect*fromotselect*from10

38、0368SQLotselect*from200736SQLSQLselectcount(*)fromKDBA_OBJECTS 03selecttrunc(created),count(*)fromtgroupby2 havingcount(*)COL=COL=500+COL500=İ0SQL2004-06-292004-11-15İ0SQL2004-06-292004-11-15?Gwİ0 SQL ;0J 10ESQL set timing on SQLselectcount(*)2345fromwherecreated=to_date(2004-06-2900:00:00,yyyy-mm-d

39、dhh24:mi:ss) and created selectfromwhere created = to_date(to_char(to_date(2004-06-29 00:00:00, yyyy-mm-dd -123.456,yyyy-mm-ddhh24:mi:ss),yyyy-mm-ddhh24:mi:ss)+andcreatedto_date(to_char(to_date(2004-06-3000:00:00,yyyy-mm-ddhh24:mi:ss)+ 1000, yyyy-mm-dd hh24:mi:ss), yyyy-mm-dd hh24:mi:ss) - 1000group

40、by: 00:00: 1Q3SQL 0603select234fromwhereto_char(created,yyyy-mm-dd)=2004-06-29 group by owner;İ0SQL: İ0SQL: 00:00:C0Kselect2345fromwhereto_char(created,yyyy-mm-ddhh24:mi:ss)=2004-06-29andto_char(created,yyyy-mm-ddhh24:mi:ss)=2004-06-2923:59:59 group by owner;: 00:00:4 3 0G+KSQL G;0YK0 CREATED ;0 SQL

41、 E;oh SQL _=0 SQL = ID =Y 00gSQLPL/SQLK;3K SQL PL/SQL 323Kİ? 92040 +30C3P REDO0aK _xP REDO İ0SQLK SQL SQLSQLCREATETABLET1ECTROWNUMİ0SQLK SQL SQLSQLCREATETABLET1ECTROWNUMID,A.*FROMDBA_OBJECTSSQLALTERTABLET1ADDPRIMARYKEYSQLCREATETABLET2ECTROWNUMID,B.*FROMDBA_SYNONYMSSQLCREATEINDEXIND_T2_IDONSQLALTERTA

42、BLET2MODIFYIDNOTSQLCREATETABLET3ECTROWNUMID,C.OWNER,C.TABLE_NAME,2 FROMDBA_TAB_COLUMNSSQLALTERTABLET3ADDPRIMARYKEYSQLEXECS(USER,PL/SQLSQLEXECS(USER,PL/SQLSQLEXECS(USER,PL/SQLSQLSQLCREATETABLETECTROWNUMID,OBJECT_NAME,MOD(ROWNUM,2)TYPEFROMDBA_OBJECTSSQLALTERTABLETADDPRIMARYKEYSQLEXECS(USER,PL/SQLSQLCR

43、EATEDATABASELINKYTK102CONNECTTKIDENTIFIEDTKUSINGSETTIMINGON SQL BEGIN23456789UPDATETSETTYPE=1 WHERE TYPE = 0ANDIDIN SELECTFROMT1YTK102T1,T2YTK102T2,T3YTK102T3 WHERE T1.ID = T2.IDANDT2.ID=İ0SQLİ0SQLUPDATETSETTYPE=0 WHERE TYPE = 1ANDNOTEXISTS SELECTFROMT1YTK102T1,T2YTK102T2,T3YTK102T3 WHERE T1.ID = T2

44、.IDANDT2.ID=T3.ID AND T.ID = T1.ID/PL/SQL: 00:00:44.28 SQLROLLBACK;: 00:00:0aPL/SQL :;UPDATE 0c1K h040 446Y3UPDATETSETTYPE23456789(SELECTTYPE (SELECTT.ID,DECODE(T1.ID,NULL,0,1)TYPE FROM T,(SELECTFROMT1YTK102T1,T2YTK102T2,T3YTK102T3 WHERE T1.ID = T2.IDANDT2.ID=)WHERET.ID=ANDT.TYPE!=DECODE(T1.ID,NULL,0,)WHERET.ID=)WHEREEXISTS SELECT1 (