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  |
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 #353916 is a reply to message #353777] |
Wed, 15 October 2008 10:43  |
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 |
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 09:27:18 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
