| Difference in query performance [message #480401] |
Sat, 23 October 2010 13:39  |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Hello experts,
I have a typical issue with a third party application. The application uses one query which when run on toad or sqlplus hardly takes 1 sec to execute. But in the ADDM report, I get that particular query listed in the "Findings" section under
SQL statements consuming significant database time were found.
Vendor says, everything is fine with his application. How to identify the issue and go about it?
The query is something like this:
Select DISTINCT ( H.reference_id) FROM ReferenceUserView H, ReferenceUser
W1, ObjectData data WHERE H.reference_id = W1.user_id AND
H.is_archived = 'N' AND H.object_type = 'User' AND
H.object_code IS NULL AND H.module = 'PRGM' AND H.reference_id
= data.reference_id AND (( data.attribute_id =
'49lh7a009720009000030ag19l' AND data.value = 'TEST1') OR (
data.attribute_id = '49lh7a72000900009l' AND data.value =
'TEST2') OR ( data.attribute_id =
'49lh7a009720009000030ag19l' AND data.value = 'TEST3'))
AND W1.user_id = '6oristi0972000500'
Regards,
Sandhyaa
|
|
|
|
|
|
| Re: Difference in query performance [message #480405 is a reply to message #480402] |
Sat, 23 October 2010 13:55  |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Hi,
below is the explain plan generated from toad:
SELECT STATEMENT ALL_ROWSCost: 10 Bytes: 297 Cardinality: 1
15 SORT UNIQUE NOSORT Cost: 10 Bytes: 297 Cardinality: 1
14 NESTED LOOPS Cost: 9 Bytes: 297 Cardinality: 1
10 NESTED LOOPS Cost: 6 Bytes: 231 Cardinality: 1
8 NESTED LOOPS Cost: 5 Bytes: 204 Cardinality: 1
6 NESTED LOOPS OUTER Cost: 4 Bytes: 177 Cardinality: 1
4 NESTED LOOPS Cost: 3 Bytes: 150 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) XPKREFERENCEUSER Cost: 1 Bytes: 27 Cardinality: 1
3 TABLE ACCESS BY INDEX ROWID TABLE REFERENCEOBJECT Cost: 2 Bytes: 123 Cardinality: 1
2 INDEX UNIQUE SCAN INDEX (UNIQUE) XPKREFERENCEOBJECT Cost: 1 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) XPKREFERENCEOBJECT Cost: 1 Bytes: 4,548,042 Cardinality: 168,446
7 INDEX UNIQUE SCAN INDEX (UNIQUE) XPKREFERENCEOBJECT Cost: 1 Bytes: 4,548,042 Cardinality: 168,446
9 INDEX UNIQUE SCAN INDEX (UNIQUE) XPKREFERENCEOBJECT Cost: 1 Bytes: 4,548,042 Cardinality: 168,446
13 INLIST ITERATOR
12 TABLE ACCESS BY INDEX ROWID TABLE OBJECTDATA Cost: 3 Bytes: 66 Cardinality: 1
11 INDEX RANGE SCAN INDEX (UNIQUE) XPKOBJECTDATA Cost: 2 Cardinality: 1
|
|
|
|
|
|
| Re: Difference in query performance [message #480407 is a reply to message #480406] |
Sat, 23 October 2010 14:09 |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Unfortunately it is not true with my case. Users starts complaining about the performance and when the tool vendor disables the feature, which uses this particular query, things become right.
I am unable to understand, how to identify the cause of the issue.
|
|
|
|
|
|
|
|
| Re: Difference in query performance [message #480417 is a reply to message #480402] |
Sat, 23 October 2010 19:26 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
BlackSwan wrote on Sun, 24 October 2010 05:44
It could be because what you observe is "First Rows" being returned; as opposed to "ALL ROWS".
A good way to test this is:
SELECT * FROM (
Select DISTINCT ( H.reference_id) FROM ReferenceUserView H, ReferenceUser
W1, ObjectData data WHERE H.reference_id = W1.user_id AND
H.is_archived = 'N' AND H.object_type = 'User' AND
H.object_code IS NULL AND H.module = 'PRGM' AND H.reference_id
= data.reference_id AND (( data.attribute_id =
'49lh7a009720009000030ag19l' AND data.value = 'TEST1') OR (
data.attribute_id = '49lh7a72000900009l' AND data.value =
'TEST2') OR ( data.attribute_id =
'49lh7a009720009000030ag19l' AND data.value = 'TEST3'))
AND W1.user_id = '6oristi0972000500'
) where rownum > 1
Is that fast or slow? It will tell you how long it takes Oracle to find ALL rows without actually returning them across the network.
Another possibility: Did the SQL show in the report with those values like '49lh7a009720009000030ag19l', or were they bind variables - like :b1. Changing bind variables to literals can change the execution plan - most frequently making it faster.
Ross Leishman
|
|
|
|
|
|
| Re: Difference in query performance [message #480486 is a reply to message #480418] |
Mon, 25 October 2010 03:20 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Can you try rewriting the query as follows:
Select data.reference_id
FROM ObjectData data
WHERE
data.reference_id = '6oristi0972000500'
AND (data.attribute_id, data.value) IN (('49lh7a009720009000030ag19l','TEST1'),
('49lh7a72000900009l', 'TEST2'),
('49lh7a009720009000030ag19l','TEST3'))
AND EXISTS (SELECT 1 FROM ReferenceUser W1
WHERE W1.user_id = data.reference_id )
AND EXISTS (SELECT 1 FROM ReferenceUserView H
WHERE H.reference_id = data.reference_id AND
AND H.is_archived = 'N' AND H.object_type = 'User'
AND H.object_code IS NULL AND H.module = 'PRGM')
AND ROWNUM = 1
IMHO it's supposed to return the same data.
However I would recommend to verify an existance of following indexes:
1. Table ObjectData - index on (reference_id, attribute_id, value ) columns.
2. Table ReferenceUser index on ( user_id ) column (probably already exists)
3. For ReferenceUserView view - index on ( reference_id, object_type, module ) for the included table.
HTH.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
