Oracle Database Discussions

Hi All,

Following is the TKPROF output of a user_dump_dest trace file, obtained by setting trace to 10046@level 12.

select "CHNNL"."CHNNL_BK" "C0", case "GEOG12"."SUB_STATE_PRVNC_CD" when

*:"SYS_B_000" then :"SYS_B_001" when :"SYS_B_002" then :"SYS_B_003" when*

*:"SYS_B_004" then :"SYS_B_005" when :"SYS_B_006" then :"SYS_B_007" when*

*:"SYS_B_008" then :"SYS_B_009" when :"SYS_B_010" then :"SYS_B_011" when*

*:"SYS_B_012" then :"SYS_B_013" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end*

*"C1", "TIME_PERIOD13"."MONTH_CD" "C2", lpad(to_char(EXTRACT( MONTH*

FROM
*(TIMESTAMP :"SYS_B_014") )), :"SYS_B_015", :"SYS_B_016") "C3", sum(case*

when "TIME_PERIOD13"."DAY_CLNDR_DATE"=TIMESTAMP :"SYS_B_017" then

*"SALES_FACT"."EXTND_NET_SALE_AMNT" else :"SYS_B_018" end ) over (partition*

by "CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_019"

then :"SYS_B_020" when :"SYS_B_021" then :"SYS_B_022" when :"SYS_B_023"

then :"SYS_B_024" when :"SYS_B_025" then :"SYS_B_026" when :"SYS_B_027"

then :"SYS_B_028" when :"SYS_B_029" then :"SYS_B_030" when :"SYS_B_031"

then :"SYS_B_032" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD") "C4", sum(case when*

*"TIME_PERIOD13"."DAY_CLNDR_DATE" between (TIMESTAMP :"SYS_B_033" -*

NUMTODSINTERVAL( EXTRACT( DAY FROM TIMESTAMP :"SYS_B_034" ), :"SYS_B_035" )

+ INTERVAL :"SYS_B_036" DAY) and TIMESTAMP :"SYS_B_037" then

*"SALES_FACT"."EXTND_NET_SALE_AMNT" else :"SYS_B_038" end ) over (partition*

by "CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_039"

then :"SYS_B_040" when :"SYS_B_041" then :"SYS_B_042" when :"SYS_B_043"

then :"SYS_B_044" when :"SYS_B_045" then :"SYS_B_046" when :"SYS_B_047"

then :"SYS_B_048" when :"SYS_B_049" then :"SYS_B_050" when :"SYS_B_051"

then :"SYS_B_052" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD") "C5", sum(case when*

*"TIME_PERIOD13"."DAY_CLNDR_DATE"=TIMESTAMP :"SYS_B_053" then*

*"SALES_FACT"."QTY_SOLD_IN_CASES" else :"SYS_B_054" end ) over (partition by*

*"CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_055"*

then :"SYS_B_056" when :"SYS_B_057" then :"SYS_B_058" when :"SYS_B_059"

then :"SYS_B_060" when :"SYS_B_061" then :"SYS_B_062" when :"SYS_B_063"

then :"SYS_B_064" when :"SYS_B_065" then :"SYS_B_066" when :"SYS_B_067"

then :"SYS_B_068" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD") "C6", sum(case when*

*"TIME_PERIOD13"."DAY_CLNDR_DATE" between (TIMESTAMP :"SYS_B_069" -*

NUMTODSINTERVAL( EXTRACT( DAY FROM TIMESTAMP :"SYS_B_070" ), :"SYS_B_071" )

+ INTERVAL :"SYS_B_072" DAY) and TIMESTAMP :"SYS_B_073" then

*"SALES_FACT"."QTY_SOLD_IN_CASES" else :"SYS_B_074" end ) over (partition by*

*"CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_075"*

then :"SYS_B_076" when :"SYS_B_077" then :"SYS_B_078" when :"SYS_B_079"

then :"SYS_B_080" when :"SYS_B_081" then :"SYS_B_082" when :"SYS_B_083"

then :"SYS_B_084" when :"SYS_B_085" then :"SYS_B_086" when :"SYS_B_087"

then :"SYS_B_088" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD") "C7", sum(case when*

*"TIME_PERIOD13"."DAY_CLNDR_DATE"=TIMESTAMP :"SYS_B_089" then*

*"SALES_FACT"."QTY_SOLD_IN_KLGMS" else :"SYS_B_090" end ) over (partition by*

*"CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_091"*

then :"SYS_B_092" when :"SYS_B_093" then :"SYS_B_094" when :"SYS_B_095"

then :"SYS_B_096" when :"SYS_B_097" then :"SYS_B_098" when :"SYS_B_099"

then :"SYS_B_100" when :"SYS_B_101" then :"SYS_B_102" when :"SYS_B_103"

then :"SYS_B_104" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD") "C8", sum(case when*

*"TIME_PERIOD13"."DAY_CLNDR_DATE" between (TIMESTAMP :"SYS_B_105" -*

NUMTODSINTERVAL( EXTRACT( DAY FROM TIMESTAMP :"SYS_B_106" ), :"SYS_B_107" )

+ INTERVAL :"SYS_B_108" DAY) and TIMESTAMP :"SYS_B_109" then

*"SALES_FACT"."QTY_SOLD_IN_KLGMS" else :"SYS_B_110" end ) over (partition by*

*"CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_111"*

then :"SYS_B_112" when :"SYS_B_113" then :"SYS_B_114" when :"SYS_B_115"

then :"SYS_B_116" when :"SYS_B_117" then :"SYS_B_118" when :"SYS_B_119"

then :"SYS_B_120" when :"SYS_B_121" then :"SYS_B_122" when :"SYS_B_123"

then :"SYS_B_124" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD") "C9", sum("SALES_FACT"."EXTND_GROSS_SALE_AMNT")*

over (partition by "CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD"

when :"SYS_B_125" then :"SYS_B_126" when :"SYS_B_127" then :"SYS_B_128"

when :"SYS_B_129" then :"SYS_B_130" when :"SYS_B_131" then :"SYS_B_132"

when :"SYS_B_133" then :"SYS_B_134" when :"SYS_B_135" then :"SYS_B_136"

when :"SYS_B_137" then :"SYS_B_138" else

*"GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end , "TIME_PERIOD13"."MONTH_CD") "C10",*
*((sum(case when ("TIME_PERIOD13"."DAY_CLNDR_DATE"=TIMESTAMP :"SYS_B_139")*

then "SALES_FACT"."EXTND_NET_SALE_AMNT" else :"SYS_B_140" end ) over

*(partition by "CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when*

*:"SYS_B_141" then :"SYS_B_142" when :"SYS_B_143" then :"SYS_B_144" when*

*:"SYS_B_145" then :"SYS_B_146" when :"SYS_B_147" then :"SYS_B_148" when*

*:"SYS_B_149" then :"SYS_B_150" when :"SYS_B_151" then :"SYS_B_152" when*

*:"SYS_B_153" then :"SYS_B_154" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end*

*, "TIME_PERIOD13"."MONTH_CD")+sum(case when*

*"TIME_PERIOD13"."DAY_CLNDR_DATE" between (TIMESTAMP :"SYS_B_155" -*

NUMTODSINTERVAL( EXTRACT( DAY FROM TIMESTAMP :"SYS_B_156" ), :"SYS_B_157" )

+ INTERVAL :"SYS_B_158" DAY) and TIMESTAMP :"SYS_B_159" then

*"SALES_FACT"."EXTND_NET_SALE_AMNT" else :"SYS_B_160" end ) over (partition*

by "CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_161"

then :"SYS_B_162" when :"SYS_B_163" then :"SYS_B_164" when :"SYS_B_165"

then :"SYS_B_166" when :"SYS_B_167" then :"SYS_B_168" when :"SYS_B_169"

then :"SYS_B_170" when :"SYS_B_171" then :"SYS_B_172" when :"SYS_B_173"

then :"SYS_B_174" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD"))+(sum(case when*

*("TIME_PERIOD13"."DAY_CLNDR_DATE"=TIMESTAMP :"SYS_B_175") then*

*"SALES_FACT"."QTY_SOLD_IN_CASES" else :"SYS_B_176" end ) over (partition by*

*"CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_177"*

then :"SYS_B_178" when :"SYS_B_179" then :"SYS_B_180" when :"SYS_B_181"

then :"SYS_B_182" when :"SYS_B_183" then :"SYS_B_184" when :"SYS_B_185"

then :"SYS_B_186" when :"SYS_B_187" then :"SYS_B_188" when :"SYS_B_189"

then :"SYS_B_190" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD")+sum(case when "TIME_PERIOD13"."DAY_CLNDR_DATE"*

between (TIMESTAMP :"SYS_B_191" - NUMTODSINTERVAL( EXTRACT( DAY FROM

TIMESTAMP :"SYS_B_192" ), :"SYS_B_193" ) + INTERVAL :"SYS_B_194" DAY) and

TIMESTAMP :"SYS_B_195" then "SALES_FACT"."QTY_SOLD_IN_CASES" else

*:"SYS_B_196" end ) over (partition by "CHNNL"."CHNNL_BK", case*

*"GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_197" then :"SYS_B_198" when*

*:"SYS_B_199" then :"SYS_B_200" when :"SYS_B_201" then :"SYS_B_202" when*

*:"SYS_B_203" then :"SYS_B_204" when :"SYS_B_205" then :"SYS_B_206" when*

*:"SYS_B_207" then :"SYS_B_208" when :"SYS_B_209" then :"SYS_B_210" else*

*"GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end , "TIME_PERIOD13"."MONTH_CD")))*
+(sum(case when ("TIME_PERIOD13"."DAY_CLNDR_DATE"=TIMESTAMP :"SYS_B_211")

then "SALES_FACT"."QTY_SOLD_IN_KLGMS" else :"SYS_B_212" end ) over

*(partition by "CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when*

*:"SYS_B_213" then :"SYS_B_214" when :"SYS_B_215" then :"SYS_B_216" when*

*:"SYS_B_217" then :"SYS_B_218" when :"SYS_B_219" then :"SYS_B_220" when*

*:"SYS_B_221" then :"SYS_B_222" when :"SYS_B_223" then :"SYS_B_224" when*

*:"SYS_B_225" then :"SYS_B_226" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end*

*, "TIME_PERIOD13"."MONTH_CD")+sum(case when*

*"TIME_PERIOD13"."DAY_CLNDR_DATE" between (TIMESTAMP :"SYS_B_227" -*

NUMTODSINTERVAL( EXTRACT( DAY FROM TIMESTAMP :"SYS_B_228" ), :"SYS_B_229" )

+ INTERVAL :"SYS_B_230" DAY) and TIMESTAMP :"SYS_B_231" then

*"SALES_FACT"."QTY_SOLD_IN_KLGMS" else :"SYS_B_232" end ) over (partition by*

*"CHNNL"."CHNNL_BK", case "GEOG12"."SUB_STATE_PRVNC_CD" when :"SYS_B_233"*

then :"SYS_B_234" when :"SYS_B_235" then :"SYS_B_236" when :"SYS_B_237"

then :"SYS_B_238" when :"SYS_B_239" then :"SYS_B_240" when :"SYS_B_241"

then :"SYS_B_242" when :"SYS_B_243" then :"SYS_B_244" when :"SYS_B_245"

then :"SYS_B_246" else "GEOG12"."SUB_STATE_PRVNC_NAME_TXT" end ,

*"TIME_PERIOD13"."MONTH_CD")) "C11" from "THA_SALES_DBA"."CHNNL" "CHNNL",*

*(select "GEOG"."GEOG_KEY" "GEOG_KEY", "GEOG"."SUB_STATE_PRVNC_CD"*

*"SUB_STATE_PRVNC_CD", "GEOG"."SUB_STATE_PRVNC_NAME_TXT"*

*"SUB_STATE_PRVNC_NAME_TXT" from "THA_SALES_DBA"."GEOG" "GEOG" where*

*"GEOG"."SUB_STATE_PRVNC_LOAD_DATA_TYPE"=:"SYS_B_247") "GEOG12",*

*"THA_SALES_DBA"."TIME_PERIOD" "TIME_PERIOD13", "THA_SALES_DBA"."SALES_FACT"*

*"SALES_FACT", (select "PROD"."PROD_KEY" "PROD_KEY", "PROD"."PROD_DVSN_CD"*

*"PROD_DVSN_CD", "PROD"."PCKG_SIZE_CD" "PCKG_SIZE_CD" from*

*"THA_SALES_DBA"."PROD" "PROD" where "PROD"."LOAD_DATA_TYPE_CD"=:"SYS_B_248")*
*"PROD14", "THA_SALES_DBA"."DSTRBTR" "DSTRBTR" where*

*"PROD14"."PROD_DVSN_CD"=:"SYS_B_249" and "PROD14"."PCKG_SIZE_CD"=*
*:"SYS_B_250" and "DSTRBTR"."DSTRBTR_CD"<>:"SYS_B_251" and CASE*

WHEN("DSTRBTR"."DSTRBTR_CD" IS NULL) OR ("CHNNL"."CHNNL_BK" IS NULL) THEN

NULL ELSE ("DSTRBTR"."DSTRBTR_CD"||"CHNNL"."CHNNL_BK") END<>:"SYS_B_252"

and "SALES_FACT"."CHNNL_KEY"="CHNNL"."CHNNL_KEY" and

*"SALES_FACT"."DSTRBTR_KEY"="DSTRBTR"."DSTRBTR_KEY" and*

*"SALES_FACT"."GEOG_KEY"="GEOG12"."GEOG_KEY" and "SALES_FACT"."PROD_KEY"=*
*"PROD14"."PROD_KEY" and "SALES_FACT"."TIME_PERIOD_KEY"=*
*"TIME_PERIOD13"."TIME_PERIOD_KEY"*


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 7632 129.92 245.70 406278 12619 35 763042
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7634 129.95 245.73 406278 12619 35 763042

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 68


Rows Row Source Operation
------- ---------------------------------------------------
*763042 WINDOW BUFFER (cr=12619 pr=406278 pw=257613 time=239232878 us)*
*763042 WINDOW BUFFER (cr=12619 pr=372409 pw=237458 time=217248824 us)*
*763042 WINDOW BUFFER (cr=12619 pr=339595 pw=217662 time=194593662 us)*
*763042 WINDOW BUFFER (cr=12619 pr=307255 pw=198149 time=177438908 us)*
*763042 WINDOW BUFFER (cr=12619 pr=275292 pw=178940 time=157976971 us)*
*763042 WINDOW BUFFER (cr=12619 pr=244097 pw=160017 time=139997871 us)*
*763042 WINDOW BUFFER (cr=12619 pr=213275 pw=141404 time=119675383 us)*
*763042 WINDOW BUFFER (cr=12619 pr=184170 pw=123525 time=105714100 us)*
*763042 WINDOW BUFFER (cr=12619 pr=155800 pw=105953 time=88478655 us)*
*763042 WINDOW BUFFER (cr=12619 pr=128025 pw=88650 time=71243641 us)*
*763042 WINDOW BUFFER (cr=12619 pr=100871 pw=71641 time=54047210 us)*
*763042 WINDOW BUFFER (cr=12619 pr=73821 pw=54957 time=35324144 us)*
*763042 WINDOW SORT (cr=12619 pr=48205 pw=38587 time=18865032 us)*
*763042 HASH JOIN (cr=12619 pr=0 pw=0 time=3127703 us)*
*15098 TABLE ACCESS FULL TIME_PERIOD (cr=693 pr=0 pw=0 time=15216 us)*
*763042 HASH JOIN (cr=11926 pr=0 pw=0 time=2344335 us)*
*1845 TABLE ACCESS FULL GEOG (cr=92 pr=0 pw=0 time=1910 us)*
*763194 HASH JOIN (cr=11834 pr=0 pw=0 time=1577327 us)*
*11 TABLE ACCESS FULL CHNNL (cr=7 pr=0 pw=0 time=47 us)*
*765432 HASH JOIN (cr=11827 pr=0 pw=0 time=1580849 us)*
*2 TABLE ACCESS FULL DSTRBTR (cr=7 pr=0 pw=0 time=50 us)*
*765432 HASH JOIN (cr=11820 pr=0 pw=0 time=814835 us)*
*313 TABLE ACCESS FULL PROD (cr=174 pr=0 pw=0 time=1195 us)*
*1045260 TABLE ACCESS FULL SALES_FACT (cr=11646 pr=0 pw=0 time=31 us)*


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQLNet message to client 7633 0.00 0.01*
SQLNet message from client 7633 1826.57 2334.17*
SQLNet more data from client 1 0.00 0.00*
direct path write temp 5332 0.09 6.23
direct path read temp 389239 0.09 115.09

The query is automatically generated by Cognos 8.2. The database is 64-bit Oracle 10.2.0.4 on Windows 2003 Enterprise Edition R2.

I am unable to understand the "*WINDOW BUFFER/WINDOW SORT*" statement under the Row Source Operation. Could one of you please explain this operation to me?

Thanks and Regards,
Sriraman