Home » RDBMS Server » Performance Tuning » need help to tune the query (solaris 10 oracle 10.2.0.3)
need help to tune the query [message #353742] Tue, 14 October 2008 23:37 Go to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
Hi,

i am facing performance issue with one query. it is taking full table scan on large table. then i gave index hint to use this this hint while fetching the data, index hint won't helpful to me.

please find the query.

i am attaching the file which contain execution plan ,with index hint and without index hint.

i was tried index_combine, index_hint and parallel 10 as index hints ... but i am not able to get the best plan
can any one help me

SELECT  /*+ INDEX(B DEVX_EVENT_F1) INDEX(D MANUFACTURER_BB_PK)   */ DEVX_EVENT_SEQ.NEXTVAL,         --objid
                         'QC00064068-101064-STAGE-Verizon-200804',       --event2part
                          a.eventid,                                   --event_id
                          a.sid,                                       --sid
                          '43D131264EE969CF6EE3742A494EA0F6',                         --alias_sid.
                          'Y',                                         --md5_gen_flag
                          DECODE(a.type, 'TA', a.adjustment_dap, a.dap),             --dev_price.
                          0,                                           --amt.
                          0,                                           --net_amt
                          a.adjustment_eventid,                        --adjustment_event_id
                          a.adjustment_reason,                         --adjustment_rsn
                          TRUNC(a.bds_date_created),                   --bds_date_created
                          a.gmt,                                       --gmt
                          a.ext_count,                                 --ext_cnt
                          a.type,                                      --type.
                          '',                                          --exclusion_flag
                          '',                                          --exclusion_reason
                          'TB',                                        --table_src
                          a.eventid,                                   --txn_orig_event_id. Verify what this thing is.
                          a.primary_carrier_name,                      --pc_name
                          a.manufacturer,                              --mfr_name
                          TO_CHAR(a.bds_date_created, 'YYYYMM'),       --year_month
                          a.mfg_partno,                                --mfg_partno
                          a.mfg_partname,                              --mfg_partname. Select Partname from Boomers on.
                          '',                                          --bill_type
                          '',                                          --mpi_rule_id
                          a.bill_flag,                                 --bill_flag
                          a.method,                                    --method
                          a.value,                                     --value
                          a.dap_currency,                              --currency
  	                    a.basis,                                     --basis
                          a.network_flag,                              --network_flag
  	                    a.gps_flag,                                  --gps_flag
  	                    a.platformid,                                --platform_id
  	                    a.item_list_name,                            --application_name
  	                    a.vendordata,                                --vendordata
                          'NON-INVOICE',                               --stage_type
                          0,                                           --stage_id
                          a.apptype,                                   --apptype
                          a.billing_level,                             --billing_level
                          a.subtype,                                   --subtype
                          a.source_type,                               --source_type
                          a.substart_billdate,                         --substart_billdate
                          a.subend_billdate,                           --subend_billdate
                          a.ITEMID,
                          a.ITEM_NAME,
                          a.API,
                          a.LANGUAGE,
                          a.PRICE_TYPE,
                          a.PRICE_TYPE_NAME,
                          a.mime_type
                  from txn_bb PARTITION(TXN_BB_0508) a, devx_event b, manufacturer_bb d
                  where a.bds_date_created >= to_date('05-Apr-2008')
                  and a.bds_date_created < to_date('06-Apr-2008')
                  and NOT ((a.type in ('DA', 'TR', 'CA', 'ER')) OR
  				         (a.type = 'DL' and a.method = '3') OR
  				         (a.restrict_flag = 'Y' and NVL(a.ext_count, 0) = 0))
                  and a.primary_carrier_name = 'Verizon'
                  and a.manufacturer = d.mfr_name
                  and a.primary_carrier_name = b.pc_name(+)
                  --Original: commented by Sandeep Maini for CR85205 Improve performance of the PKG_DEV_EXTRACT
                  --and a.eventid = b.event_id(+)
                  --changed by smaini for CR85205 Improve performance of the PKG_DEV_EXTRACT
                  and to_char(a.eventid) = b.event_id(+)
                  and b.event_id is null;

Re: need help to tune the query [message #353777 is a reply to message #353742] Wed, 15 October 2008 01:45 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
It seems you have parallel query enabled at the object level from these xplans.

I would try removing all other hints and adding /*+ FULL(b) */



Re: need help to tune the query [message #353916 is a reply to message #353777] Wed, 15 October 2008 10:43 Go to previous messageGo to next message
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
Member
index hint is not helpful.

actually this query is in the package. is there any way to capture
package performance. means i would like to capture the information
where oracle is spending more time on the package.

can any one guide me.
Re: need help to tune the query [message #354078 is a reply to message #353742] Thu, 16 October 2008 06:36 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do you have indexes on:
- (a.primary_carrier_name, a.bds_date_created)
- (b.event_id, b.pc_name)
- d.mfr_name

If so, have you gathered statistics with DBMS_STATS.GATHER_TABLE_STATS()?

Ross Leishman
Re: need help to tune the query [message #354107 is a reply to message #354078] Thu, 16 October 2008 07:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
These two lines here look suspect to me:
and to_char(a.eventid) = b.event_id(+)
                  and b.event_id is null


I strongly suspect that they are a bug, but if they are correct, they mean that you will only ever select the Outer Join created rows from table b - which means that you can get rid of it entirely, as it will contribute nothing to the number of rows returned.
Previous Topic: Which is faster
Next Topic: Very Strange and Interesting Oracle Issue
Goto Forum:
  


Current Time: Fri Dec 06 01:08:21 CST 2024