Oracle sort merge join

Posted in 2003

Yoon-Jae Hwnag — Thu, 07 Aug 2003 07:19:54 +0000 — source: Usenet: comp.databases.informix

Greetings all, below script is a part of 10053 trace. could you tell me the meaning of " Sort width","Blocks to Sort", "Initial runs", "Merge passes" and formula for :Total IO sort cost"? Best regards, Thanks. SM Join Outer table: resc: 54062 cdn: 14610000 rcz: 46 deg: 1 resp: 54062 Inner table: COSTS resc: 256 cdn: 787766 rcz: 14 deg: 1 resp: 256 using join:1 distribution:2 #groups:1 SORT resource Sort statistics Sort width: 2 Area size: 131072 Max Area size: 1257472 Degree: 1 Blocks to Sort: 109110 Row size: 61 Rows: 14610000 Initial runs: 709 Merge passes: 10 IO Cost / pass: 163665 Total IO sort cost: 872880 Total CPU sort cost: 0 Total Temp space used: 1650852000 SORT resource Sort statistics Sort width: 2 Area size: 131072 Max Area size: 1257472 Degree: 1 Blocks to Sort: 2508 Row size: 26 Rows: 787766 Initial runs: 17 Merge passes: 5 IO Cost / pass: 3762 Total IO sort cost: 10659 Total CPU sort cost: 0 Total Temp space used: 37970000 Merge join Cost: 937857 Resp: 937857 -- Posted via http://dbforums.com