db tech showcase Tokyo 2014 - L36 - JPOUG : SQLチューニング総合診療所 ケースファイルX
db tech showcase Tokyo 2013 - A35...
117
潮溜まりでジャブジャブ、 SQL チューニング Rock Pool / Michael Hiroshi Sekiguchi ( @discus_hamburg ) 13年11月16日土曜日
-
Upload
hiroshi-sekiguchi -
Category
Technology
-
view
4.612 -
download
3
Transcript of db tech showcase Tokyo 2013 - A35...
- 1. SQLRock Pool / MichaelHiroshi Sekiguchi ( @discus_hamburg ) 131116
2. @discus_hamburg Stargrass Software : Software Engineer < DB Engineer JPOUG Oracle ACE (2012) Blog : Mac De Oracle / http://discus-hamburg.cocolog-nifty.com131116 3. Deep diveDeep dive Oracle Core SQLShallow Dive 131116 4. SQL... ...131116 5. SQL 131116 6. 131116 7. 131116 8. 131116 9. SQL ... ... 131116 10. OLTP(DB OLTP SQL ()131116 11. 131116 12. 131116 13. 131116 14. SQL SELECT /*+USE_NL(tab1tab2tab3tab4)*/ tab4.* FROM tab1 INNERJOINtab2 ON tab1.unique#=tab2.unique# ANDtab1.unique#IN(1,2) INNERJOINtab3 ON tab2.unique#=tab3.unique# ANDtab2.branch#=tab3.branch# INNERJOINtab4 ON tab3.unique#=tab4.unique# ANDtab3.branch#=tab4.branch# ANDtab3.loc#=tab4.loc# ; 131116 15. SQL () -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20000|11M|603(0)|00:00:01||| |1|NESTEDLOOPS|||||||| |2|NESTEDLOOPS||20000|11M|603(0)|00:00:01||| |3|NESTEDLOOPS||200|15600|3(0)|00:00:01||| |4|NESTEDLOOPS||200|13000|3(0)|00:00:01||| |5|REMOTE|TAB3|200|7800|3(0)|00:00:01|PDBOR~|R->S| |6|REMOTE|TAB2|1|26|0(0)|00:00:01|PDBOR~|R->S| |7|REMOTE|TAB1|1|13|0(0)|00:00:01|PDBOR~|R->S| |*8|INDEXRANGESCAN|PK_TAB4|1||2(0)|00:00:01||| |9|TABLEACCESSBYINDEXROWID|TAB4|100|51400|3(0)|00:00:01||| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 8-access("TAB3"."UNIQUE#"="TAB4"."UNIQUE#"AND"TAB3"."BRANCH#"="TAB4"."BRANCH#"AND "TAB3"."LOC#"="TAB4"."LOC#") filter("TAB4"."UNIQUE#"=1OR"TAB4"."UNIQUE#"=2)131116 16. SQL () -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20000|11M|603(0)|00:00:01||| |1|NESTEDLOOPS|||||||| |2|NESTEDLOOPS||20000|11M|603(0)|00:00:01||| |3|NESTEDLOOPS||200|15600|3(0)|00:00:01||| |4|NESTEDLOOPS||200|13000|3(0)|00:00:01||| |5|REMOTE|TAB3|200|7800|3(0)|00:00:01|PDBOR~|R->S| |6|REMOTE|TAB2|1|26|0(0)|00:00:01|PDBOR~|R->S| |7|REMOTE|TAB1|1|13|0(0)|00:00:01|PDBOR~|R->S| |*8|INDEXRANGESCAN|PK_TAB4|1||2(0)|00:00:01||| |9|TABLEACCESSBYINDEXROWID|TAB4|100|51400|3(0)|00:00:01||| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 8-access("TAB3"."UNIQUE#"="TAB4"."UNIQUE#"AND"TAB3"."BRANCH#"="TAB4"."BRANCH#"AND "TAB3"."LOC#"="TAB4"."LOC#") filter("TAB4"."UNIQUE#"=1OR"TAB4"."UNIQUE#"=2)131116 17. SQL (DB Link..) USER_DB_LINKS DB_LINKUSERNAMEHOST ------------------------------ PDBORCL_RSCOTTPDBORCL_R USER_SYNONYMS SYNONYM_NAMETABLE_NAMEDB_LINK ------------------------------------------ TAB1TAB1PDBORCL_R TAB2TAB2PDBORCL_R TAB3TAB3PDBORCL_R131116 18. SQL (DB Link..) USER_DB_LINKS DB_LINKUSERNAMEHOST ------------------------------ PDBORCL_RSCOTTPDBORCL_RDB Link USER_SYNONYMS SYNONYM_NAMETABLE_NAMEDB_LINK ------------------------------------------ TAB1TAB1PDBORCL_R TAB2TAB2PDBORCL_R TAB3TAB3PDBORCL_R131116 19. SQL ()131116 20. SQL () SQL 131116 21. SQL () -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20000|11M|603(0)|00:00:01||| |1|NESTEDLOOPS|||||||| |2|NESTEDLOOPS||20000|11M|603(0)|00:00:01||| |3|NESTEDLOOPS||200|15600|3(0)|00:00:01||| |4|NESTEDLOOPS||200|13000|3(0)|00:00:01||| |5|REMOTE|TAB3|200|7800|3(0)|00:00:01|PDBOR~|R->S| |6|REMOTE|TAB2|1|26|0(0)|00:00:01|PDBOR~|R->S| |7|REMOTE|TAB1|1|13|0(0)|00:00:01|PDBOR~|R->S| |*8|INDEXRANGESCAN|PK_TAB4|1||2(0)|00:00:01||| |9|TABLEACCESSBYINDEXROWID|TAB4|100|51400|3(0)|00:00:01||| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 8-access("TAB3"."UNIQUE#"="TAB4"."UNIQUE#"AND"TAB3"."BRANCH#"="TAB4"."BRANCH#"AND "TAB3"."LOC#"="TAB4"."LOC#") filter("TAB4"."UNIQUE#"=1OR"TAB4"."UNIQUE#"=2)131116 22. SQL () -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20000|11M|603(0)|00:00:01||| |1|NESTEDLOOPS|||||||| |2|NESTEDLOOPS||20000|11M|603(0)|00:00:01||| |3|NESTEDLOOPS||200|15600|3(0)|00:00:01||| |4|NESTEDLOOPS||200|13000|3(0)|00:00:01||| |5|REMOTE|TAB3|200|7800|3(0)|00:00:01|PDBOR~|R->S| |6|REMOTE|TAB2|1|26|0(0)|00:00:01|PDBOR~|R->S| |7|REMOTE|TAB1|1|13|0(0)|00:00:01|PDBOR~|R->S| |*8|INDEXRANGESCAN|PK_TAB4|1||2(0)|00:00:01||| |9|TABLEACCESSBYINDEXROWID|TAB4|100|51400|3(0)|00:00:01||| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): ---------------------------------------------------8-access("TAB3"."UNIQUE#"="TAB4"."UNIQUE#"AND"TAB3"."BRANCH#"="TAB4"."BRANCH#"AND "TAB3"."LOC#"="TAB4"."LOC#") filter("TAB4"."UNIQUE#"=1OR"TAB4"."UNIQUE#"=2)131116 23. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 5-SELECT/*+USE_NL("TAB3")*/"UNIQUE#","BRANCH#" ,"LOC#"FROM"TAB3""TAB3"WHERE "UNIQUE#"=1OR"UNIQUE#"=2(accessing'PDBORCL_R') 6-SELECT/*+USE_NL("TAB2")*/"UNIQUE#","BRANCH#" FROM"TAB2""TAB2"WHERE"BRANCH#"=:1 AND"UNIQUE#"=:2AND("UNIQUE#"=1OR"UNIQUE#"=2) (accessing'PDBORCL_R') 7-SELECT/*+USE_NL("TAB1")*/"UNIQUE#" FROM"TAB1""TAB1"WHERE"UNIQUE#"=:1AND ("UNIQUE#"=1OR"UNIQUE#"=2)(accessing'PDBORCL_R')explain plan 131116 24. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 5-SELECT/*+USE_NL("TAB3")*/"UNIQUE#","BRANCH#" ,"LOC#"FROM"TAB3""TAB3"WHERE "UNIQUE#"=1OR"UNIQUE#"=2(accessing'PDBORCL_R') 6-SELECT/*+USE_NL("TAB2")*/"UNIQUE#","BRANCH#" FROM"TAB2""TAB2"WHERE"BRANCH#"=:1 AND"UNIQUE#"=:2AND("UNIQUE#"=1OR"UNIQUE#"=2) (accessing'PDBORCL_R') 7-SELECT/*+USE_NL("TAB1")*/"UNIQUE#" FROM"TAB1""TAB1"WHERE"UNIQUE#"=:1AND ("UNIQUE#"=1OR"UNIQUE#"=2)(accessing'PDBORCL_R')SQL 131116 25. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 5-SELECT/*+USE_NL("TAB3")*/"UNIQUE#","BRANCH#" ,"LOC#"FROM"TAB3""TAB3"WHERE "UNIQUE#"=1OR"UNIQUE#"=2(accessing'PDBORCL_R')----------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ----------------------------------------------------------------------------- |0|SELECTSTATEMENT||200|2000|3(0)|00:00:01| |1|INLISTITERATOR|||||| |*2|INDEXRANGESCAN|PK_TAB3|200|2000|3(0)|00:00:01| -----------------------------------------------------------------------------131116 26. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 5-SELECT/*+USE_NL("TAB3")*/"UNIQUE#","BRANCH#" ,"LOC#"FROM"TAB3""TAB3"WHERE "UNIQUE#"=1OR"UNIQUE#"=2(accessing'PDBORCL_R')----------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ----------------------------------------------------------------------------- |0|SELECTSTATEMENT||200|2000|3(0)|00:00:01| |1|INLISTITERATOR|||||| |*2|INDEXRANGESCAN|PK_TAB3|200|2000|3(0)|00:00:01| ----------------------------------------------------------------------------- ...131116 27. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 6-SELECT/*+USE_NL("TAB2")*/"UNIQUE#","BRANCH#" FROM"TAB2""TAB2"WHERE"BRANCH#"=:1 AND"UNIQUE#"=:2AND("UNIQUE#"=1OR"UNIQUE#"=2) (accessing'PDBORCL_R') ------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1|7|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB2|1|7|1(0)|00:00:01| ------------------------------------------------------------------------------131116 28. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 6-SELECT/*+USE_NL("TAB2")*/"UNIQUE#","BRANCH#" FROM"TAB2""TAB2"WHERE"BRANCH#"=:1 AND"UNIQUE#"=:2AND("UNIQUE#"=1OR"UNIQUE#"=2) (accessing'PDBORCL_R') ------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1|7|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB2|1|7|1(0)|00:00:01| ------------------------------------------------------------------------------ ...131116 29. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 7-SELECT/*+USE_NL("TAB1")*/"UNIQUE#" FROM"TAB1""TAB1"WHERE"UNIQUE#"=:1AND ("UNIQUE#"=1OR"UNIQUE#"=2)(accessing'PDBORCL_R')------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1|4|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB1|1|4|1(0)|00:00:01| ------------------------------------------------------------------------------131116 30. RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 7-SELECT/*+USE_NL("TAB1")*/"UNIQUE#" FROM"TAB1""TAB1"WHERE"UNIQUE#"=:1AND ("UNIQUE#"=1OR"UNIQUE#"=2)(accessing'PDBORCL_R')------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1|4|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB1|1|4|1(0)|00:00:01| ------------------------------------------------------------------------------ ...131116 31. ----------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ----------------------------------------------------------------------------- |0|SELECTSTATEMENT||200|2000|3(0)|00:00:01| |1|INLISTITERATOR|||||| |*2|INDEXRANGESCAN|PK_TAB3|200|2000|3(0)|00:00:01| ----------------------------------------------------------------------------- ------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1|7|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB2|1|7|1(0)|00:00:01| ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1|4|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB1|1|4|1(0)|00:00:01| ------------------------------------------------------------------------------131116 32. ----------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ----------------------------------------------------------------------------- |0|SELECTSTATEMENT||200|2000|3(0)|00:00:01| |1|INLISTITERATOR|||||| |*2|INDEXRANGESCAN|PK_TAB3|200|2000|3(0)|00:00:01| ----------------------------------------------------------------------------- ------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ |0|SELECTSTATEMENT||1|7|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB2|1|7|1(0)|00:00:01| ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------ SQL |0|SELECTSTATEMENT||1|4|1(0)|00:00:01| |*1|FILTER|||||| |*2|INDEXUNIQUESCAN|PK_TAB1|1|4|1(0)|00:00:01| .... ------------------------------------------------------------------------------ ....... 131116 33. -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20000|11M|603(0)|00:00:01||| |1|NESTEDLOOPS|||||||| |2|NESTEDLOOPS||20000|11M|603(0)|00:00:01||| |3|NESTEDLOOPS||200|15600|3(0)|00:00:01||| |4|NESTEDLOOPS||200|13000|3(0)|00:00:01||| |5|REMOTE|TAB3|200|7800|3(0)|00:00:01|PDBOR~|R->S| |6|REMOTE|TAB2|1|26|0(0)|00:00:01|PDBOR~|R->S| |7|REMOTE|TAB1|1|13|0(0)|00:00:01|PDBOR~|R->S| |*8|INDEXRANGESCAN|PK_TAB4|1||2(0)|00:00:01||| |9|TABLEACCESSBYINDEXROWID|TAB4|100|51400|3(0)|00:00:01||| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 8-access("TAB3"."UNIQUE#"="TAB4"."UNIQUE#"AND"TAB3"."BRANCH#"="TAB4"."BRANCH#"AND "TAB3"."LOC#"="TAB4"."LOC#") filter("TAB4"."UNIQUE#"=1OR"TAB4"."UNIQUE#"=2)131116 34. -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20000|11M|603(0)|00:00:01||| |1|NESTEDLOOPS|||||||| |2|NESTEDLOOPS||20000|11M|603(0)|00:00:01||| |3|NESTEDLOOPS||200|15600|3(0)|00:00:01||| |4|NESTEDLOOPS||200|13000|3(0)|00:00:01||| |5|REMOTE|TAB3|200|7800|3(0)|00:00:01|PDBOR~|R->S| |6|REMOTE|TAB2|1|26|0(0)|00:00:01|PDBOR~|R->S| |7|REMOTE|TAB1|1|13|0(0)|00:00:01|PDBOR~|R->S| |*8|INDEXRANGESCAN|PK_TAB4|1||2(0)|00:00:01||| |9|TABLEACCESSBYINDEXROWID|TAB4|100|51400|3(0)|00:00:01||| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): ---------------------------------------------------DB Link Nested Loop8-access("TAB3"."UNIQUE#"="TAB4"."UNIQUE#"AND"TAB3"."BRANCH#"="TAB4"."BRANCH#"AND "TAB3"."LOC#"="TAB4"."LOC#") filter("TAB4"."UNIQUE#"=1OR"TAB4"."UNIQUE#"=2) ....131116 35. SELECT /*+USE_NL(t01tab4)*/ tab4.* FROM ( SELECT /*+NO_MERGE*/ tab3.unique# ,tab3.branch# ,tab3.loc# FROM tab1 INNERJOINtab2 ON tab1.unique#=tab2.unique#ANDtab1.unique#IN(1,2) INNERJOINtab3 ON tab2.unique#=tab3.unique#ANDtab2.branch#=tab3.branch# )t01 INNERJOINtab4 ON t01.unique#=tab4.unique#ANDt01.branch#=tab4.branch# ANDt01.loc#=tab4.loc# ;131116 36. DEMO131116 37. () -------------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT| -------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20000|10M|2004(1)|00:00:01||| |1|NESTEDLOOPS|||||||| |2|NESTEDLOOPS||20000|10M|2004(1)|00:00:01||| |3|VIEW||200|7800|3(0)|00:00:01||| |4|REMOTE||||||PDBOR~|R->S| |*5|INDEXRANGESCAN|PK_TAB4|100||2(0)|00:00:01||| |6|TABLEACCESSBYINDEXROWID|TAB4|100|51400|10(0)|00:00:01||| -------------------------------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 5-access("T01"."UNIQUE#"="TAB4"."UNIQUE#"AND"T01"."BRANCH#"="TAB4"."BRANCH#"AND "T01"."LOC#"="TAB4"."LOC#") RemoteSQLInformation(identifiedbyoperationid): ---------------------------------------------------- 4-EXPLAINPLANSETSTATEMENT_ID='PLUS8440309'INTOPLAN_TABLE@!FORSELECT/*+NO_MERGE*/ "A1"."UNIQUE#","A1"."BRANCH#","A1"."LOC#"FROM"TAB1""A3","TAB2""A2","TAB3""A1"WHERE "A2"."UNIQUE#"="A1"."UNIQUE#"AND"A2"."BRANCH#"="A1"."BRANCH#"AND "A3"."UNIQUE#"="A2"."UNIQUE#"AND("A3"."UNIQUE#"=1OR"A3"."UNIQUE#"=2)AND("A2"."UNIQUE#"=1 OR"A2"."UNIQUE#"=2)AND("A1"."UNIQUE#"=1OR"A1"."UNIQUE#"=2)(accessing'PDBORCL_R')131116 38. () ------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time| ------------------------------------------------------------------------------- |0|SELECTSTATEMENT||20|420|3(0)|00:00:01| |1|NESTEDLOOPS||20|420|3(0)|00:00:01| |2|NESTEDLOOPS||20|340|3(0)|00:00:01| |3|INLISTITERATOR|||||| |*4|INDEXRANGESCAN|PK_TAB3|200|2000|3(0)|00:00:01| |*5|INDEXUNIQUESCAN|PK_TAB2|1|7|0(0)|00:00:01| |*6|INDEXUNIQUESCAN|PK_TAB1|1|4|0(0)|00:00:01| ------------------------------------------------------------------------------- PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 4-access("A1"."UNIQUE#"=1OR"A1"."UNIQUE#"=2) 5-access("A2"."UNIQUE#"="A1"."UNIQUE#"AND "A2"."BRANCH#"="A1"."BRANCH#") filter("A2"."UNIQUE#"=1OR"A2"."UNIQUE#"=2) 6-access("A3"."UNIQUE#"="A2"."UNIQUE#") filter("A3"."UNIQUE#"=1OR"A3"."UNIQUE#"=2)131116 39. Elapsed TImeBefore After 131116 40. SQL131116 41. SQL :)131116 42. Oracle Database 12c 1 (12.1) http://docs.oracle.com/cd/E49329_01/server. 121/b71301/ds_appdev.htm#i1007640131116 43. 131116 44. SQL ... User I/O...... 131116 45. UPDATE OracleStandard Edition etc... 131116 46. DECLARE CURSORcs_foobarIS SELECT rowid FROM foobar WHERE unique#BETWEEN1AND400000 ORDERBY unique# FORUPDATE; BEGIN FORrowINcs_foobarLOOP UPDATEfoobar SET foobar_str=LPAD('y',500,'y') WHERE rowid=row.rowid; ENDLOOP; COMMIT; END; /131116 47. DECLARE CURSORcs_foobarIS SELECT rowid FROM foobar WHERE unique#BETWEEN1AND400000 ORDERBY unique# FORUPDATE; BEGIN FORrowINcs_foobarLOOP UPDATEfoobar SET foobar_str=LPAD('y',500,'y') WHERE rowid=row.rowid; ENDLOOP; COMMIT; END; /131116 48. USER_INDEXES TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR -------------- --------------- ---------- ------------- ----------------FOOBAR SYS_C0010235 2070913 2070913 2070899 USER_IND_COLUMNS NDEX_NAME COLUMN_NAME ---------------- -------------SYS_C0010235 UNIQUE# USER_TABLES TABLE_NAME NUM_ROWS AVG_ROW_LEN ------------ ---------- ----------FOOBAR 2000000 506131116 49. USER_INDEXES TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR -------------- --------------- ---------- ------------- ----------------FOOBAR SYS_C0010235 2070913 2070913 2070899 USER_IND_COLUMNS NDEX_NAME COLUMN_NAME ---------------- -------------SYS_C0010235 UNIQUE# 400,000USER_TABLES TABLE_NAME NUM_ROWS AVG_ROW_LEN ------------ ---------- ----------FOOBAR 2000000 506131116.. 50. NAME -------------------db_block_size db_cache_size db_16k_cache_size db_2k_cache_size db_32k_cache_size db_4k_cache_size db_8k_cache_size pga_aggregate_target131116TYPE ----------integer big integer big integer big integer big integer big integer big integer big integerVALUE ------8192 32M 0 0 0 0 0 512M 51. NAME -------------------db_block_size db_cache_size db_16k_cache_size db_2k_cache_size db_32k_cache_size db_4k_cache_size db_8k_cache_size pga_aggregate_target131116TYPE ----------integer big integer big integer big integer big integer big integer big integer big integerVALUE ------8192 32M 0 0 0 0 0 512M .... 52. Block Size = 8192bytes 4096blocks(Buffer Cache) AVG_ROW_LEN = 506bytes PCTFREE10 114 20040 28,572blocks() ....131116 53. DECLARE CURSORcs_foobarIS SELECT rowid FROM foobar WHERE unique#BETWEEM1AND400000 ORDERBY unique# FORUPDATE; BEGIN FORrowINcs_foobarLOOP UPDATEfoobar SET foobar_str=LPAD('y',500,'y') WHERE rowid=row.rowid; ENDLOOP; COMMIT; END; / ....131116 54. DECLARE CURSORcs_foobarIS SELECT rowid FROM foobar WHERE unique#BETWEEM1AND400000 ORDERBY unique# FORUPDATE; BEGIN FORrowINcs_foobarLOOP UPDATEfoobar SET foobar_str=LPAD('y',500,'y') WHERE rowid=row.rowid; ENDLOOP; COMMIT; END; / ....131116 55. ....131116 56. .... Buffer Cache I/OStorage 131116 57. .... 5 4 3 2 Buffer Cache 4 3 2 I/OStorage 131116 58. .... 5 4 3 2 Buffer Cache 4 3 2 I/OStorage 131116 59. ROWID I/O ....131116 60. .... Buffer Cache I/OStorage 131116 61. .... Buffer Cache I/OStorage 131116 62. .... Buffer Cache I/OStorage 131116 63. 131116 64. DECLARE CURSORcs_foobarIS SELECT rowid FROM foobar WHERE unique#BETWEEN1AND400000 ORDERBY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) FORUPDATE; BEGIN FORrowINcs_foobarLOOP UPDATEfoobar SET foobar_str=LPAD('y',500,'y') WHERE rowid=row.rowid; ENDLOOP; COMMIT; END; /131116 65. DEMO131116 66. v$sysstat STATISTICS NAMEBEFOREAFTERDidirty buers inspected785,126298,823-486,303free buer inspected847,091308,384-538,707free buer requested839,961307,981-531,980physical read IO requests791,470273,368-518,102physical read total IO requests794,522274,376-520,146000physical reads802,309273,605-528,704physical reads cache802,140273,410-528,730physical write IO requests774,818246,934-527,884physical write total IO requests779,397248,918-530,479physical writes825,809306,644-519,165physical writes from cache825,631306,479-519,152physical writes non checkpoint818,900303,921-514,979physical read total multi block requests131116 67. v$sysstat STATISTICS NAMEBEFOREAFTERDidirty buers inspected785,126298,823-486,303free buer inspected847,091308,384-538,707free buer requested839,961307,981-531,980physical read IO requests791,470273,368-518,102physical read total IO requests794,522274,376-520,146000physical reads802,309273,605-528,704physical reads cache802,140273,410-528,730physical write IO requests774,818246,934-527,884physical write total IO requests779,397248,918-530,479physical writes825,809306,644-519,165physical writes from cache825,631306,479-519,152physical writes non checkpoint818,900303,921-514,979physical read total multi block requests131116 68. 10 900,000675,000225,000 0free buer requested physical reads cache physical writes from cache BEFORE131116AFTERBlocks450,000 69. SQL131116 70. SQL 131116 71. 131116 72. SQL .... ... 131116 73. ERD...Oracle SQL Developer Data Modeler 4.0.0131116 74. ...131116 75. 131116 76. SQL131116 77. 131116 78. 131116 79. SQL ... (@t_wada :131116 80. ( OK etc....131116 81. SQL SQL etc... 131116 82. SQL SQL etc... 131116 83. ERD ...131116 84. ERD ...131116 85. ...131116 86. ...131116 87. 131116 88. 131116 89. 131116 90. 131116 91. 131116 92. 131116 93. ERD 131116 94. .... SELECT orderitems.order#,COUNT(1) FROM orderitems INNERJOINitems ON orderitems.item#=items.item# INNERJOINorders ON orderitems.order#=orders.order# INNERJOINcustomers ON orders.customer#=customers.customer# GROUPBY orderitems.order# ; 131116 95. 131116 96. ()131116 97. SELECT orderitems.order#,COUNT(1) FROM orderitems GROUP BY orderitems.order# ;131116 98. SELECT orderitems.order#,COUNT(1) FROM orderitems GROUP BY orderitems.order# ;131116 99. 131116 100. SELECT orderitems.order#,COUNT(1) FROM orderitems INNER JOIN items ON orderitems.item# = items.item# INNER JOIN orders ON orderitems.order# = orders.order# INNER JOIN customers ON orders.customer# = customers.customer# GROUP BY orderitems.order# ;131116 101. SELECT orderitems.order#,COUNT(1) FROM orderitems INNER JOIN items ON orderitems.item# = items.item# INNER JOIN orders ON orderitems.order# = orders.order# INNER JOIN customers ON orders.customer# = customers.customer# GROUP BY orderitems.order# ; 131116 102. DEMO131116 103. ---------------------------------------------------------- Planhashvalue:411877633 ------------------------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ------------------------------------------------------------------------------------------------ |0|SELECTSTATEMENT||50000|1464K||1325(3)|00:00:16| |1|SORTGROUPBYNOSORT||50000|1464K||1325(3)|00:00:16| |2|NESTEDLOOPS||246K|7212K||1325(3)|00:00:16| |3|NESTEDLOOPS||246K|6010K||1313(2)|00:00:16| |4|MERGEJOIN||247K|4831K||1301(1)|00:00:16| |5|INDEXFULLSCAN|PK_ORDERITEMS|250K|2441K||1029(1)|00:00:13| |*6|SORTJOIN||50000|488K|1976K|271(1)|00:00:04| |7|TABLEACCESSFULL|ORDERS|50000|488K||68(0)|00:00:01| |*8|INDEXUNIQUESCAN|PK_ITEMS|1|5||0(0)|00:00:01| |*9|INDEXUNIQUESCAN|PK_CUSTOMERS|1|5||0(0)|00:00:01| ------------------------------------------------------------------------------------------------ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 6-access("ORDERITEMS"."ORDER#"="ORDERS"."ORDER#") filter("ORDERITEMS"."ORDER#"="ORDERS"."ORDER#") 8-access("ORDERITEMS"."ITEM#"="ITEMS"."ITEM#") 9-access("ORDERS"."CUSTOMER#"="CUSTOMERS"."CUSTOMER#") ---------------------------------------------------------- 0recursivecalls 0dbblockgets 262017consistentgets 244physicalreads 0redosize 1126677bytessentviaSQL*Nettoclient 37207bytesreceivedviaSQL*Netfromclient 3335SQL*Netroundtripsto/fromclient 1sorts(memory) 0sorts(disk) 50000rowsprocessed131116 104. ---------------------------------------------------------- Planhashvalue:411877633 ------------------------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ------------------------------------------------------------------------------------------------ |0|SELECTSTATEMENT||50000|1464K||1325(3)|00:00:16| |1|SORTGROUPBYNOSORT||50000|1464K||1325(3)|00:00:16| |2|NESTEDLOOPS||246K|7212K||1325(3)|00:00:16| |3|NESTEDLOOPS||246K|6010K||1313(2)|00:00:16| |4|MERGEJOIN||247K|4831K||1301(1)|00:00:16| |5|INDEXFULLSCAN|PK_ORDERITEMS|250K|2441K||1029(1)|00:00:13| |*6|SORTJOIN||50000|488K|1976K|271(1)|00:00:04| |7|TABLEACCESSFULL|ORDERS|50000|488K||68(0)|00:00:01| |*8|INDEXUNIQUESCAN|PK_ITEMS|1|5||0(0)|00:00:01| |*9|INDEXUNIQUESCAN|PK_CUSTOMERS|1|5||0(0)|00:00:01| ------------------------------------------------------------------------------------------------ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 6-access("ORDERITEMS"."ORDER#"="ORDERS"."ORDER#") filter("ORDERITEMS"."ORDER#"="ORDERS"."ORDER#") 8-access("ORDERITEMS"."ITEM#"="ITEMS"."ITEM#") 9-access("ORDERS"."CUSTOMER#"="CUSTOMERS"."CUSTOMER#") ---------------------------------------------------------- 0recursivecalls 0dbblockgets 262017consistentgets 244physicalreads 0redosize 1126677bytessentviaSQL*Nettoclient 37207bytesreceivedviaSQL*Netfromclient 3335SQL*Netroundtripsto/fromclient 1sorts(memory) 0sorts(disk) 50000rowsprocessed131116 105. ---------------------------------------------------------- Planhashvalue:411877633 ------------------------------------------------------------------------------------------------ |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ------------------------------------------------------------------------------------------------ |0|SELECTSTATEMENT||50000|1464K||1325(3)|00:00:16| |1|SORTGROUPBYNOSORT||50000|1464K||1325(3)|00:00:16| |2|NESTEDLOOPS||246K|7212K||1325(3)|00:00:16| |3|NESTEDLOOPS||246K|6010K||1313(2)|00:00:16| |4|MERGEJOIN||247K|4831K||1301(1)|00:00:16| |5|INDEXFULLSCAN|PK_ORDERITEMS|250K|2441K||1029(1)|00:00:13| |*6|SORTJOIN||50000|488K|1976K|271(1)|00:00:04| |7|TABLEACCESSFULL|ORDERS|50000|488K||68(0)|00:00:01| |*8|INDEXUNIQUESCAN|PK_ITEMS|1|5||0(0)|00:00:01| |*9|INDEXUNIQUESCAN|PK_CUSTOMERS|1|5||0(0)|00:00:01| ------------------------------------------------------------------------------------------------ PredicateInformation(identifiedbyoperationid): --------------------------------------------------- 6-access("ORDERITEMS"."ORDER#"="ORDERS"."ORDER#") filter("ORDERITEMS"."ORDER#"="ORDERS"."ORDER#") 8-access("ORDERITEMS"."ITEM#"="ITEMS"."ITEM#") 9-access("ORDERS"."CUSTOMER#"="CUSTOMERS"."CUSTOMER#") ---------------------------------------------------------- 0recursivecalls 0dbblockgets 262017consistentgets 244physicalreads 0redosize 1126677bytessentviaSQL*Nettoclient 37207bytesreceivedviaSQL*Netfromclient 3335SQL*Netroundtripsto/fromclient 1sorts(memory) 0sorts(disk) 50000rowsprocessed131116 106. :) ---------------------------------------------------------- Planhashvalue:3325112419 ------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||50536|246K||516(2)|00:00:07| |1|HASHGROUPBY||50536|246K|2952K|516(2)|00:00:07| |2|INDEXFASTFULLSCAN|IX01_ORDERITEMS|250K|1220K||145(1)|00:00:02| ------------------------------------------------------------------------------------------------- ---------------------------------------------------------- 0recursivecalls 0dbblockgets 589consistentgets 0physicalreads 0redosize 1126677bytessentviaSQL*Nettoclient 37207bytesreceivedviaSQL*Netfromclient 3335SQL*Netroundtripsto/fromclient 0sorts(memory) 0sorts(disk) 50000rowsprocessed131116 107. :) ---------------------------------------------------------- Planhashvalue:3325112419 ------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||50536|246K||516(2)|00:00:07| |1|HASHGROUPBY||50536|246K|2952K|516(2)|00:00:07| |2|INDEXFASTFULLSCAN|IX01_ORDERITEMS|250K|1220K||145(1)|00:00:02| ------------------------------------------------------------------------------------------------- ---------------------------------------------------------- 0recursivecalls 0dbblockgets 589consistentgets 0physicalreads 0redosize 1126677bytessentviaSQL*Nettoclient 37207bytesreceivedviaSQL*Netfromclient 3335SQL*Netroundtripsto/fromclient 0sorts(memory) 0sorts(disk) 50000rowsprocessed131116 108. :) ---------------------------------------------------------- Planhashvalue:3325112419 ------------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time| ------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||50536|246K||516(2)|00:00:07| |1|HASHGROUPBY||50536|246K|2952K|516(2)|00:00:07| |2|INDEXFASTFULLSCAN|IX01_ORDERITEMS|250K|1220K||145(1)|00:00:02| ------------------------------------------------------------------------------------------------- ---------------------------------------------------------- 0recursivecalls 0dbblockgets 589consistentgets 0physicalreads 0redosize 1126677bytessentviaSQL*Nettoclient 37207bytesreceivedviaSQL*Netfromclient 3335SQL*Netroundtripsto/fromclient 0sorts(memory) 0sorts(disk) 50000rowsprocessed131116 109. 131116 110. DWH:)131116 111. Oracle Database 11g2(11.2) / http://docs.oracle.com/cd/E16338_01/server.112/ b56309/constra.htm#i1006284131116 112. 131116 113. 131116 114. 131116 115. 2007214Insight World 2007 / "Oracle Performance Management: Past, Present and Future" Oracle10gOracleDBAOracle.....131116 116. 2007214Insight World 2007 / "Oracle Performance Management: Past, Present and Future" Oracle10gOracleDBAOracle.....131116 117. http://www.jpoug.org131116
