Home » RDBMS Server » Performance Tuning » Performance problem when using Analytic Functions. Need Help !!!
Performance problem when using Analytic Functions. Need Help !!! [message #256971] |
Tue, 07 August 2007 05:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
v@to
Messages: 5 Registered: August 2007
|
Junior Member |
|
|
Hello All,
Although the data is the same, it takes more that 7 hours, six times more than before (two weeks ago). Problem here is big sort operations that are caused by analytic functions. I tried to tune PGA as all sorts are performed in the PGA memory but it didn't help. Also i tried modify TEMP tablespace - no results.
Major wait event during SQL execution is "direct path read temp"
Horrible is that it was working and I don't know what happened.
Please post your advices.
Oracle version is 10.1.0.4.0
Here is SQL.
/* Formatted on 2007/08/07 10:48 (Formatter Plus v4.8.6) */
SELECT *
FROM (SELECT /*+ parallel(c 4)*/
NVL (d.call_type_id, -99) AS db_call_type_id,
NVL (d.service_id, -99) AS db_service_id,
NVL (d.distance_band_id, -99) AS db_distance_band_id,
ROW_NUMBER () OVER (PARTITION BY c.call_id, d.call_type_id ORDER BY LENGTH
(d.check_digits) DESC NULLS LAST)
AS record_order,
c.record_no, c.call_id, c.subscriber_id, c.service_id,
c.tariff_id, c.subscriber_business_type_id,
c.subscriber_marketing_type_id, c.gsm_payment_type_id,
c.distance_band_id, c.time_band_id, c.time_id,
c.duration_band_id, c.cell_id, c.equipment_id, c.roaming_id,
c.gsm_method_id, c.cboss_call_type_id, c.a_number, c.b_number,
c.a_number_original, c.b_number_original, c.call_date,
c.etl_date, c.DURATION, c.rounded_duration, c.imei,
c.call_charge, c.interoper_charge, c.uplink, c.downlink,
c.service_id_2, c.call_type_id_2, c.call_charge_with_vat,
c.direction_type_id, c.account_typ
FROM (SELECT /*+ parallel (e 4) */
k.master_source_system_value1
AS orga_call_type_src_value,
k.detail_dwh_id AS cboss_call_type_id, e.record_no,
e.call_id, e.subscriber_id, e.service_id,
e.tariff_id, e.subscriber_business_type_id,
e.subscriber_marketing_type_id,
e.gsm_payment_type_id, e.distance_band_id,
e.time_band_id, e.time_id, e.duration_band_id,
e.cell_id, e.equipment_id, e.roaming_id,
e.gsm_method_id, e.call_type_id, e.a_number,
e.b_number, e.a_number_original, e.b_number_original,
e.call_date, e.etl_date, e.DURATION,
e.rounded_duration, e.imei, e.call_charge,
e.interoper_charge, e.uplink, e.downlink,
e.service_id_2, e.call_type_id_2,
e.call_charge_with_vat, e.direction_type_id,
e.local_flag, e.account_typ
FROM dwh_adm_key_transformation k,
(SELECT *
FROM dwh_tmp_pre_calldetails_6_1
WHERE local_flag = 'INT') e
WHERE k.master_trans_src_sys_value1 = 374
AND k.source_system_id = 2
AND e.call_type_id_2 = k.master_dwh_id(+)
ORDER BY e.call_id, e.call_type_id, b_number) c,
(SELECT *
FROM dwh_dim_distance_band
WHERE local_flag = 'INT' AND NVL (gsm_method_id, 2) = 2) d
WHERE c.cboss_call_type_id = d.call_type_id(+)
AND c.gsm_method_id = NVL (d.gsm_method_id(+), 2)
AND c.direction_type_id = TO_NUMBER (d.service_direction(+))
AND NVL (c.b_number, -1) LIKE NVL (d.check_digits(+), -1) || '%') a
WHERE a.record_order = 1
Explain Plan
INSERT STATEMENT Optimizer=ALL_ROWS (Cost=46107.3959168591 Card=763992 Bytes=490482864)
PX COORDINATOR
PX SEND* (QC (RANDOM)) OF :TQ10004 (Cost=46107.3959168591 Card=763992 Bytes=490482864)
VIEW* (Cost=46107.3959168591 Card=763992 Bytes=490482864)
WINDOW* (SORT PUSHED RANK) (Cost=46107.3959168591 Card=763992 Bytes=484370928)
PX RECEIVE* (Cost=46107.3959168591 Card=763992 Bytes=484370928)
PX SEND* (HASH) OF :TQ10003 (Cost=46107.3959168591 Card=763992 Bytes=484370928)
VIEW*
WINDOW* (CHILD PUSHED RANK) (Cost=46107.3959168591 Card=763992 Bytes=484370928)
HASH JOIN* (RIGHT OUTER) (Cost=17670.097249308 Card=763992 Bytes=484370928)
BUFFER* (SORT)
PX RECEIVE* (Cost=32.1019571406856 Card=6048 Bytes=187488)
PX SEND* (BROADCAST) OF :TQ10001 (Cost=32.1019571406856 Card=6048 Bytes=187488)
TABLE ACCESS (FULL) OF DWH_DIM_DISTANCE_BAND (TABLE) (Cost=32.1019571406856 Card=6048 Bytes=187488)
VIEW* (Cost=17635.1924274298 Card=763992 Bytes=460687176)
SORT* (ORDER BY) (Cost=17635.1924274298 Card=763992 Bytes=127586664)
PX RECEIVE* (Cost=9824.85769449657 Card=763992 Bytes=127586664)
PX SEND* (RANGE) OF :TQ10002 (Cost=9824.85769449657 Card=763992 Bytes=127586664)
HASH JOIN* (Cost=9824.85769449657 Card=763992 Bytes=127586664)
BUFFER* (SORT)
PX RECEIVE* (Cost=170.804665557204 Card=1 Bytes=21)
PX SEND* (BROADCAST) OF :TQ10000 (Cost=170.804665557204 Card=1 Bytes=21)
TABLE ACCESS (FULL) OF DWH_ADM_KEY_TRANSFORMATION (TABLE) (Cost=170.804665557204 Card=1 Bytes=21)
PX BLOCK* (ITERATOR) (Cost=9629.73513927536 Card=7995580 Bytes=1167354680)
TABLE ACCESS* (FULL) OF DWH_TMP_PRE_CALLDETAILS_6_1 (TABLE) (Cost=9629.73513927536 Card=7995580 Bytes=1167354680)
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 08:37:58 CDT 2024
|