Home » RDBMS Server » Performance Tuning » Performance problem when using Analytic Functions. Need Help !!!
icon9.gif  Performance problem when using Analytic Functions. Need Help !!! [message #256971] Tue, 07 August 2007 05:19 Go to next message
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

Here is SQL.

/* Formatted on 2007/08/07 10:48 (Formatter Plus v4.8.6) */
  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)  */
                                                  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.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 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)
                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)
Re: Performance problem when using Analytic Functions. Need Help !!! [message #257035 is a reply to message #256971] Tue, 07 August 2007 07:41 Go to previous messageGo to next message
Messages: 41
Registered: January 2007
Hi v@to

Do you know if it's the same plan as 2 weeks ago ?
Could be that the statistics has changed and the optimizer now finds another plan.
Remember that on 10g there is an automatic update of the statistics being done as default.
You could try to use the new 10g features of the package dbms_sqltune.

best regards
Carl Bruhn
Re: Performance problem when using Analytic Functions. Need Help !!! [message #257039 is a reply to message #257035] Tue, 07 August 2007 07:50 Go to previous message
Messages: 5
Registered: August 2007
Junior Member
Hi cbruhn2,

thank you for great advice.

Yes, I think explain plan was different before. I don't know why it has changed. Maybe some joined tables are changed. Some new rows are inserted.

Is there any way to get previous explain plan?

I Gonna take a look on dbms_sqltune. Never heard before.

Thank you,


[Updated on: Tue, 07 August 2007 07:51]

Report message to a moderator

Previous Topic: stripping in oracle 10g dba?
Next Topic: Performance tunning
Goto Forum:

Current Time: Tue Jan 17 09:38:42 CST 2017

Total time taken to generate the page: 0.12520 seconds