Home » RDBMS Server » Performance Tuning » Query tuning
Query tuning [message #495594] Mon, 21 February 2011 05:22 Go to next message
preethavinay
Messages: 5
Registered: February 2011
Junior Member
Please tune the below query since it is running slowly:

select /*+ CHOOSE */ accession, patientkey, max(testreqkey) as testreqkey from testreq where accession || ' ' || patientkey in (select distinct accession || ' ' || patientkey from testreq) group by accession, patientkey;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=789995 Card=60164844 Bytes=2226099
228)


1 0
SORT (GROUP BY) (Cost=789995 Card=60164844 Bytes=2226099228)


2 1
HASH JOIN (Cost=1246 Card=60164844 Bytes=2226099228)


3 2
VIEW OF 'index$_join$_002' (VIEW) (Cost=322 Card=77566 Bytes=1163490)


4 3
HASH JOIN


5 4
INDEX (FAST FULL SCAN) OF 'AK_TESTREQ_ACCESSIONS' (INDEX (UNIQUE)) (Co
st=57 Card=77566 Bytes=1163490)


6 4
INDEX (FAST FULL SCAN) OF 'IX_TESTREQ_PATIENT2' (INDEX) (Cost=46 Card=
77566 Bytes=1163490)


7 2
VIEW OF 'index$_join$_001' (VIEW) (Cost=697 Card=77566 Bytes=1706452)


8 7
HASH JOIN


9 8
HASH JOIN


10 9
INDEX (FAST FULL SCAN) OF 'AK_TESTREQ_ACCESSIONS' (INDEX (UNIQUE)) (
Cost=57 Card=77566 Bytes=1706452)


11 9
INDEX (FAST FULL SCAN) OF 'IX_TESTREQ_PATIENT2' (INDEX) (Cost=46 Car
d=77566 Bytes=1706452)


12 8
INDEX (FAST FULL SCAN) OF 'PK_TESTREQ' (INDEX (UNIQUE)) (Cost=35 Card=
77566 Bytes=1706452)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
131 consistent gets
0 physical reads
172 redo size
3542 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
213 rows processed

------------------


SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4066303761

-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | HASH JOIN | |
| 3 | VIEW | index$_join$_002 |
| 4 | HASH JOIN | |
| 5 | INDEX FAST FULL SCAN | AK_TESTREQ_ACCESSIONS |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | INDEX FAST FULL SCAN | IX_TESTREQ_PATIENT2 |
| 7 | VIEW | index$_join$_001 |
| 8 | HASH JOIN | |
| 9 | HASH JOIN | |
| 10 | INDEX FAST FULL SCAN| AK_TESTREQ_ACCESSIONS |
| 11 | INDEX FAST FULL SCAN| IX_TESTREQ_PATIENT2 |
| 12 | INDEX FAST FULL SCAN | PK_TESTREQ |
-----------------------------------------------------------

19 rows selected.

Report message to a moderator

Re: Query tuning [message #495596 is a reply to message #495594] Mon, 21 February 2011 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Query tuning [message #495598 is a reply to message #495594] Mon, 21 February 2011 05:30 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello preethavinay

Do you really need "distinct" in the subquery? Is it making any difference if you remove the distinct clause?

Regards,
OraKaran

Report message to a moderator

Re: Query tuning [message #495600 is a reply to message #495596] Mon, 21 February 2011 05:31 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
 
select /*+ CHOOSE */ accession, patientkey, max(testreqkey) as testreqkey 
from testreq 
where accession || ' ' || patientkey in (select distinct accession || ' ' || patientkey from testreq) 
group by accession, patientkey;

What exactly do you think that where clause is doing? Cause as far as I can see the only thing it's doing is slowing the query down.

Report message to a moderator

Re: Query tuning [message #495676 is a reply to message #495600] Mon, 21 February 2011 23:23 Go to previous messageGo to next message
preethavinay
Messages: 5
Registered: February 2011
Junior Member
The query is preventing duplicate accessions across patients, that is why ||''|| is used in the where clause, so that the patientkey and accession can be taken together.

Do you have any other suggestions that can implement the same?

Report message to a moderator

Re: Query tuning [message #495733 is a reply to message #495676] Tue, 22 February 2011 02:36 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Can you define 'slowly'? Those stats say there were 0 pio and 131 lio....that's got to be screaming fast, or those numbers are wrong.

Reason I ask is, there comes a point where its just not going to get much faster no matter what you do and I've never seen a query with those stats be "slow".

Report message to a moderator

Re: Query tuning [message #495770 is a reply to message #495676] Tue, 22 February 2011 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
preethavinay wrote on Tue, 22 February 2011 05:23
The query is preventing duplicate accessions across patients, that is why ||''|| is used in the where clause, so that the patientkey and accession can be taken together.

No it doesn't. The where clause you have gives exactly the same rows back as 
where 1=1

Only does it slower.
Try it.

preethavinay wrote on Tue, 22 February 2011 05:23

Do you have any other suggestions that can implement the same?

Not without a test case to show us what you're really trying to achieve. We don't know anything about your tables or data other than what you've shown us.

Report message to a moderator

Re: Query tuning [message #498598 is a reply to message #495770] Thu, 10 March 2011 07:36 Go to previous message
marcodba
Messages: 5
Registered: August 2006
Junior Member
"Max" function and "Group By" will give you the max(testreqkey) for each accession and patientkey pair (no duplicate here).

You can simplify your query with:

select 	/*+ CHOOSE */
	accession,
	patientkey,
	max(testreqkey) as testreqkey
from 	testreq
) group by 
	accession,
	patientkey;


Do you have a reason for using CHOOSE hint?

Report message to a moderator

Previous Topic: Stored Procedure is taking too long time to Execute. (2 Merged)
Next Topic: Slow Performance
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 15:47:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.