OraFAQ Forum: Performance Tuning » Query not executing

Home » RDBMS Server » Performance Tuning » Query not executing

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query not executing [message #65708]

Sun, 12 December 2004 18:40

Supraja
Messages: 18
Registered: August 2004

Junior Member

Hi,

I am joining 3 tables. Each have millions of records. When I am running that query, DB is not giving any response till 30 mins also.
I wrote it in optimized way. Still I don't know the reason.
Can I get any help on this please.

I am using ORacle 9i version

My query and tables are

SELECT

A.PRODUCT_CATEGORY,
C.PRODUCT_TYPE,
COUNT(DISTINCT C.CDM_POS_HEADER_ID) AS CDM_POS_HEADER_ID_COUNT

FROM

EDWD_PRODUCT_ITEM A,
EDWD_PRODUCT_TYPE_SUMMARY B,
EDWD_PRODUCT_TYPE_DETAIL_CKM C

WHERE

A.CDM_PROD_ID = B.CDM_PROD_ID AND
B.CDM_POS_HEADER_ID = C.CDM_POS_HEADER_ID AND
A.PRODUCT_CATEGORY = 'Hamburger'

GROUP BY A.PRODUCT_CATEGORY,C.PRODUCT_TYPE

Index created by me are
CDM_PROD_ID_IDX -- This is on created on table column cdm_prod_id
CDM_POS_HEADER_ID_IDX - This is created EDWD_PRODUCT_TYPE_DETAIL_CKM on cdm_pos_header_id column.
CDM_POS_HEADER_ID_INDEX -- This is creatde on EDWD_PRODUCT_TYPE_SUMMARY and column is cdm_pos_header_id.
CDM_PROD_ID_INDEX ---- This is creatde on EDWD_PRODUCT_TYPE_SUMMARY and column is cdm_prod_id

script for table creation are

EDWD_PRODUCT_TYPE_SUMMARY
(
CDM_POS_HEADER_ID NUMBER(15),
PRODUCT_TYPE VARCHAR2(255 BYTE),
TNX_DATE DATE,
CDM_PROD_ID NUMBER(15),
PROD_COUNT NUMBER,
BA_INDICATOR VARCHAR2(1 BYTE)
)

EDWD_PRODUCT_TYPE_DETAIL_CKM
(
CDM_POS_HEADER_ID NUMBER(15),
PRODUCT_TYPE VARCHAR2(30 BYTE),
PRODUCT_TYPE_NO NUMBER,
CDM_POS_TYPE_ID NUMBER(15),
CDM_STORE_ID NUMBER(15),
CDM_TIME_ID NUMBER(10),
CDM_TIME_DAY_PART_ID VARCHAR2(2 BYTE),
TNX_AMOUNT NUMBER(15,5)
)

EDWD_PRODUCT_ITEM
(
ID NUMBER(10) NOT NULL,
MAIN_CATEGORY VARCHAR2(255 BYTE),
MAIN_CATEGORY1 VARCHAR2(255 BYTE),
PRODUCT_TYPE VARCHAR2(255 BYTE),
PRODUCT_CATEGORY VARCHAR2(255 BYTE),
PRODUCT_CODE VARCHAR2(20 BYTE),
CDM_PROD_ID NUMBER(15)
)

Thanks in advance.

Regards,
Supraja

Report message to a moderator

Re: Query not executing [message #65709 is a reply to message #65708]

Sun, 12 December 2004 21:06

Frank
Messages: 7901
Registered: March 2000

Senior Member

Could you give us an execution plan ?
And details, like how many rows have product_category='Hamburger', how are the join-columns distributed etc.

Did you analyze your tables and indexes ?

hth

Report message to a moderator

Re: Query not executing [message #65710 is a reply to message #65709]

Sun, 12 December 2004 22:51

Supraja
Messages: 18
Registered: August 2004

Junior Member

Today I tried to ger explain plan, So it is not executing to get explain plan. for Hamburger I shd get 2222110 records. Actully I wrote this query to get data for 120 products in a single shot.
But that is not happening

Some explanation:

CDM_PROD_ID = productId given to each particular product.

I created 4 indexes on all these 3 tables.

I wrote this query after complete analyzatio

Report message to a moderator

Re: Query not executing [message #65711 is a reply to message #65710]

Mon, 13 December 2004 21:24

prakash
Messages: 24
Registered: October 2001

Junior Member

As this query is fetching around 2 million records, It needs to study the execution plan for the query.
You gave the structure of the tables and the list of indexes...but without data we can't get the exact execution plan for the query.
Anyways, you better rebuild all the four indexes and Analyze those tables.
I hope this will work.

Best regards

Report message to a moderator

Re: Query not executing [message #65714 is a reply to message #65711]

Tue, 14 December 2004 03:01

Alan
Messages: 68
Registered: October 1999

Member

Hi
from sql have you set autotrace on
and then run the sql.

put the output onto the forum

Alan

Report message to a moderator

Re: Query not executing [message #65717 is a reply to message #65711]

Tue, 14 December 2004 21:27

Frank
Messages: 7901
Registered: March 2000

Senior Member

Why on earth would you need to rebuild the indexes ???

Report message to a moderator

Re: Query not executing [message #65736 is a reply to message #65717]

Thu, 16 December 2004 17:28

Supraja
Messages: 18
Registered: August 2004

Junior Member

Just to increase the speed. But later I deleted b.coz they wewre occupying much of the memory

Report message to a moderator

Re: Query not executing [message #65737 is a reply to message #65714]

Thu, 16 December 2004 17:31

Supraja
Messages: 18
Registered: August 2004

Junior Member

This i s explain plan for one product, i.e Hamburger like that I need to run for 120 pdts

this is query plan for one pdt category ,where as i 120 pdts

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID DW.EDWD_PRODUCT_ITEM
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL DW.EDWD_PRODUCT_TYPE_DETAIL_CKM
TABLE ACCESS BY INDEX ROWID DW.EDWD_PRODUCT_TYPE_SUMMARY
INDEX RANGE SCAN DW.CDM_POS_HEADER_ID_INDEX
INDEX RANGE SCAN DW.CDM_PROD_ID_IDX

Supraja

Report message to a moderator

Previous Topic:	experiences in performance tuning
Next Topic:	How much to tune

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 07:03:22 CDT 2025