Home » RDBMS Server » Performance Tuning » SQL - very slow response
SQL - very slow response [message #150818] Sat, 10 December 2005 05:34 Go to next message
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 #150836 is a reply to message #150818] Sat, 10 December 2005 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
BY setting the undocumented initSID.ora parameter
_make_query_faster=TRUE

If you really expect any useful answer(s), post the following
1) actual SQL
2) a description of the three tables
3) EXPLAIN_PLAN for the query in #1 above

Are statistics current for all 3 tables?
Re: SQL - very slow response [message #151056 is a reply to message #150836] Mon, 12 December 2005 22:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Interesting predicate found in explain plan
Next Topic: oracle 10g cbo slower than rbo
Goto Forum:
  


Current Time: Mon Nov 28 05:10:37 CST 2022