Home » RDBMS Server » Performance Tuning » SQL - very slow response
| SQL - very slow response [message #150818] |
Sat, 10 December 2005 05:34  |
avi123india
Messages: 5
Registered: December 2005
|
Junior Member |
|
|
Hi,
Need ur help
I m using following query based on 3 table table
mem has 2 million,
act has 17 million and
trx has 27 million records
SELECT
MEM.MEMHIP_NO,
ACT.ACTIVITY_DATE,
TRX.TRANSACTION_DATE,
TRX.trx_fact,
.....
FROM
MEM,
ACT,
TRX
WHERE
( ACT.MEMBER_ACTIVITY_ID(+)=TRX.MEMBER_ACTIVITY_ID )
AND ( MEM.MEMBER_ID=TRX.MEMBER_ID )
AND ( TRX.USER_CREATED = ????)
AND TRX.TRx_DATE BETWEEN '1-Jan-2005' AND '31-DEC-2005'
AND
.... some other conditions....
)
this query runs very very slow ..it takes 4-5 hrs to refresh data. all indexes and joins are proper. i m using normal as well as BITmap indexes....
can anyone pls help me how to tune this query and suggetion to improve the performace. earlier i used hints also but in some case it might helpfull but not always...
your early response would be appriciate.
Regards,
Avi.
|
|
|
|
|
|
| Re: SQL - very slow response [message #151056 is a reply to message #150836] |
Mon, 12 December 2005 22:31  |
avi123india
Messages: 5
Registered: December 2005
|
Junior Member |
|
|
Thanks for quick response .
now i m sending the exact query,table def and explain plan. i run the same query it shd take 30 minutes but it tooks almost 4-5 hrs.
would appriciate your response.
1. Query
SELECT DISTINCT
MEMBERS.MEMBERSHIP_NO,
MEMBERS.TITLE,
MEMBERS.FIRST_NAME,
MEMBERS.MIDDLE_NAME,
MEMBERS.FAMILY_NAME,
MEMBERS.TIER_CODE,
MEMBERS.COUNTRY_NATIONALITY,
MEMBERS.JOB_TITLE,
MEMBERS.COMPANY_NAME,
CONTACT_NUMBERS.MOBILE_NUMBER,
CONTACT_NUMBERS.PREF_NUMBER,
MEMBERS.BUILDING,
MEMBERS.STREET,
MEMBERS.LOCALITY,
MEMBERS.TOWN,
MEMBERS.STATE,
MEMBERS.POST_CODE,
MEMBERS.PO_BOX,
MEMBERS.COUNTRY_CODE,
MEMBERS.COUNTRY_NAME,
MEMBERS.PERSON_ID,
MEMBERS.MEMBER_ID
FROM
MEMBERS,
CONTACT_NUMBERS,
ACTIVITIES
WHERE
( ACTIVITIES.MEMBER_ID=MEMBERS.MEMBER_ID )
AND ( MEMBERS.PERSON_ID=CONTACT_NUMBERS.PERSON_ID(+) )
AND (
MEMBERS.COUNTRY_CODE IN ('AE', 'KW', 'QA', 'BH', 'OM')
AND ACTIVITIES.PMA_CANCELLED_STATUS IS NULL
AND ACTIVITIES.ACTIVITY_TYPE_CODE = 'FLT'
AND ACTIVITIES.MEMBER_ACTIVITY_DATE > '01-Nov-2004'
AND ACTIVITIES.MEMBER_ACTIVITY_TYPE = 'EMA'
AND MEMBERS.TIER_CODE IN ('GOLD', 'SILVER')
AND ACTIVITIES.BCP IN ('J', 'Y')
AND MEMBERS.MEDIA_CODE NOT IN ('FAM', 'TEST')
AND MEMBERS.ACTIVE_STATUS IN ('ACT', 'PND', 'INV')
AND MEMBERS.POSTAL_STATUS IS NULL
AND MEMBERS.FAMILY_HEAD_FLAG = 'Y'
)
2. table members has 2 million
activities has 17 million rows and contact_numbers has 2.5 million rows.
3.explain plan
Operation OBJ_NAME COST CRDNLTY
SELECT STATEMENT () 305 212
SORT (UNIQUE) 305 212
TABLE ACCESS (BY INDEX ROWID) ACTIVITIES 6 1
NESTED LOOPS () 303 212
HASH JOIN (OUTER) 170 212
TABLE ACCESS (BY INDEX ROWID) MEMBERS 98 212
BITMAP CONVERSION (TO ROWIDS)
BITMAP AND ()
BITMAP MINUS ()
BITMAP MINUS ()
BITMAP MINUS ()
BITMAP MINUS ()
BITMAP INDEX (SINGLE VALUE) MEM_FAM_HEAD
BITMAP INDEX (SINGLE VALUE) MEM_MEDIA_CODE
BITMAP INDEX (SINGLE VALUE) MEM_MEDIA_CODE
BITMAP INDEX (SINGLE VALUE) MEM_MEDIA_CODE
BITMAP INDEX (SINGLE VALUE) MEM_MEDIA_CODE
BITMAP OR ()
BITMAP INDEX (SINGLE VALUE) MEM_COU_CODE
BITMAP INDEX (SINGLE VALUE) MEM_COU_CODE
BITMAP INDEX (SINGLE VALUE) MEM_COU_CODE
BITMAP INDEX (SINGLE VALUE) MEM_COU_CODE
BITMAP INDEX (SINGLE VALUE) MEM_COU_CODE
BITMAP OR ()
BITMAP INDEX (SINGLE VALUE) MEM_TIER_CODE
BITMAP INDEX (SINGLE VALUE) MEM_TIER_CODE
BITMAP OR ()
BITMAP INDEX (SINGLE VALUE) MEM_ACT_STATUS
BITMAP INDEX (SINGLE VALUE) MEM_ACT_STATUS
BITMAP INDEX (SINGLE VALUE) MEM_ACT_STATUS
TABLE ACCESS (FULL) CONTACTNUMBER 75 1723094
INDEX (RANGE SCAN) ACT_MEMBER_ID 2 9
Thanks and regards,
Avi
|
|
|
|
| Re: SQL - very slow response [message #151161 is a reply to message #150818] |
Tue, 13 December 2005 08:20 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
You should take a few minutes to read the sticky in this forum. Make sure you have proper stats collected as it describes. And use code tags to indent your query.
Also check that your optimizer_mode is all_rows. Is there an index for the foreign key on person_id in contact_numbers?
And do you need the distinct keyword? Are there really duplicates to eliminate or could you leave it off?
And get rid of those extra and not needed parens in the where clause.
In your case, you might also try removing the contact numbers portion of the query to a different level than the members and activities, which serve as your limiting criteria. So you would do something like:
select blah
from
(
select blah
from members, activities
where blah
) temp,
contact_numbers
where temp.person_id = contact_numbers.person_id (+);
|
|
|
|
| Re: SQL - very slow response [message #151291 is a reply to message #151161] |
Wed, 14 December 2005 02:08 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The very last line in your plan is worrying. It is an INDEX RANGE SCAN that is the second step in a NESTED LOOPs join. This means that for every row returned from the join between MEMBERS and CONTACT_NUMBERS, you are performing a non-unique lookup on ACTIVITIES.
This non-unique scan could be returning 2 rows per lookup or 2 million - we cannot tell (although Oracle seems to think 9 rows).
You could try one of two things:
- add one or more of PMA_CANCELLED_STATUS, ACTIVITY_TYPE_CODE, MEMBER_ACTIVITY_DATE, MEMBER_ACTIVITY_TYPE, or BCP to the index. Whichever is most selective. Columns joined with = conditions will work better than those with </>, which in turn will work better than those with IS [NOT] NULL.
- do a hash join instead. Use the /*+ USE_HASH(activities)*/ hint
_____________
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 06:59:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
