Home » RDBMS Server » Performance Tuning » Query not executing
Query not executing [message #65708] Sun, 12 December 2004 18:40 Go to next message
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
Re: Query not executing [message #65709 is a reply to message #65708] Sun, 12 December 2004 21:06 Go to previous messageGo to next message
Frank
Messages: 7880
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
Re: Query not executing [message #65710 is a reply to message #65709] Sun, 12 December 2004 22:51 Go to previous messageGo to next message
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
Re: Query not executing [message #65711 is a reply to message #65710] Mon, 13 December 2004 21:24 Go to previous messageGo to next message
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
Re: Query not executing [message #65714 is a reply to message #65711] Tue, 14 December 2004 03:01 Go to previous messageGo to next message
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
Re: Query not executing [message #65717 is a reply to message #65711] Tue, 14 December 2004 21:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why on earth would you need to rebuild the indexes ???
Re: Query not executing [message #65736 is a reply to message #65717] Thu, 16 December 2004 17:28 Go to previous messageGo to next message
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
Re: Query not executing [message #65737 is a reply to message #65714] Thu, 16 December 2004 17:31 Go to previous message
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
Previous Topic: experiences in performance tuning
Next Topic: How much to tune
Goto Forum:
  


Current Time: Tue Sep 29 13:38:54 CDT 2020