Home » RDBMS Server » Performance Tuning » query is taking index sacn,even though it is taking long time and it is not completing (oracle 10.2.0.2 solaris 10 64 bit)
query is taking index sacn,even though it is taking long time and it is not completing [message #325007] Wed, 04 June 2008 15:09 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
experts,

i am facing problem with this query. query is taking long time and it is not ending up. before it used to work fine. please find query and explain plan.

even it is going for index scan it is taking long time. can any provide some hints.

SELECT  /*+ INDEX (A TXN_BB1_N3)INDEX(B DEVX_EVENT_F1) INDEX(D MANUFACTURER_BB_PK) */ --DEVX_EVENT_SEQ.NEXTVAL,         --objid
a.eventid,                                   --event_id
a.sid,                                       --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 
BILLING.txn_bb a, 
BILLING.devx_event b,
BILLING.manufacturer_bb d
where a.bds_date_created >= '29-May-2008'
and a.bds_date_created < '30-May-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
/

--------------------------------------------
explain plan is

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2873170473

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |   658K|   341M|  3050K  (1)| 10:10:03 |       |       |
|*  1 |  FILTER                               |                    |       |       |            |          |       |       |
|   2 |   NESTED LOOPS OUTER                  |                    |   658K|   341M|  3050K  (1)| 10:10:03 |       |       |
|*  3 |    HASH JOIN                          |                    |   527K|   263M|  1465K  (1)| 04:53:12 |       |       |
|   4 |     INDEX FULL SCAN                   | MANUFACTURER_BB_PK |  4090 | 40900 |    13   (0)| 00:00:01 |       |       |
|   5 |     PARTITION RANGE SINGLE            |                    |   566K|   277M|  1465K  (1)| 04:53:12 |    29 |    29 |
|*  6 |      TABLE ACCESS BY LOCAL INDEX ROWID| TXN_BB             |   566K|   277M|  1465K  (1)| 04:53:12 |    29 |    29 |
|*  7 |       INDEX RANGE SCAN                | TXN_BB1_N3         |   259K|       | 30795   (1)| 00:06:10 |    29 |    29 |
|*  8 |    INDEX RANGE SCAN                   | DEVX_EVENT_F1      |     1 |    20 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / D@SEL$1
   6 - SEL$1 / A@SEL$1
   7 - SEL$1 / A@SEL$1
   8 - SEL$1 / B@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------


[Updated on: Wed, 04 June 2008 15:11]

Report message to a moderator

Re: query is taking index sacn,even though it is taking long time and it is not completing [message #325027 is a reply to message #325007] Wed, 04 June 2008 17:14 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
before when it used to work fine?
before an upgrade to the database, hardware, large data movement, change to the query, what were the condition when it used to work fine?

I notice your query isnt giving you any OR expansion in the explain plan although you have or's in your query. This may be ok, but you might want to see what happens when you use the USE_CONCAT hint.

Also, make sure you gather stats on all the tables in the query.
Re: query is taking index sacn,even though it is taking long time and it is not completing [message #325029 is a reply to message #325007] Wed, 04 June 2008 17:18 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Also, go back to basics, take the hints off, analyze the tables and see what happens. At least you will get a benchmark to start tuning against.
Previous Topic: Performance issue with Oracle Cluster
Next Topic: How to improve this query
Goto Forum:
  


Current Time: Fri Dec 09 03:42:30 CST 2016

Total time taken to generate the page: 0.09900 seconds