RE: high parse/execute ratio

From: Kieran Murray <kieran.murray_at_norkom.com>
Date: Tue, 24 Oct 2000 21:47:49 +0100
Message-Id: <10659.120098@fatcity.com>

Tony,

you don't seem to have any limiting condition in your WHERE clause so this statement is probably doing at least one full table scan. If you're executing it over 500000 times then you should be passing some kind of variable into the statement (eg. member_id or login). That way your code will be recycled many times but should only be parsed once.

Regards,
Kieran

-----Original Message-----
From: tony_guo_at_iname.com
To: Multiple recipients of list ORACLE-L Sent: 10/24/00 7:11 PM
Subject: high parse/execute ratio

I have a select statement called by java as follows: SELECT
  b.login,
  a.user_id,
  (sum(a.score)+100) as highscore
FROM
  score_table a,
  member_table b
WHERE
  b.member_id(+)=a.user_id
group by a.user_id,b.login order by highscore desc

It was executed for 563895 times, parsed for 563895, buffer gets 438919180, disk reads 105716809, first loaded on 10/19. How bad is this? What can I do about it?

TIA
tony

Get free email from CMP at http://www.cmpnetmail.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: tony_guo_at_iname.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

The information contained in this e-mail transmission is confidential and may be privileged. It is intended only for the addressee(s) stated above. If you are not an addressee, any use, dissemination, distribution, publication, or copying of the information contained in this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately notify our IT Department by telephone at 353-1-6769333 or e-mail mail.sweeper_at_norkom.com and Received on Tue Oct 24 2000 - 15:47:49 CDT