From cspence@FuelSpot.com Wed, 03 Oct 2001 13:17:46 -0700 From: Christopher Spence Date: Wed, 03 Oct 2001 13:17:46 -0700 Subject: RE: Measure parsing calls... In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: text/plain Without using TIMED_STATISTICS it is hard to weigh the costs of the parses. Also, you want to get the parse count (total) as well, this will allow you to find what percentage is hard parses and which are not. That query you did, as is really doesn't tell you much. 582593 hard parses could be perfectly fine if you have 1000000000 parses. If parsing only takes up a few minutes over the life of the instance of a few months, it may not be a problem even if they are re-parsing. Once you have that information, then you can weigh it and determine if it is a problem. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -----Original Message----- Sent: Wednesday, October 03, 2001 11:52 AM To: Multiple recipients of list ORACLE-L Hello folks I'm trying to improve performance in our database, and the Oracle manual gave me some queries and tips, but I don't realy know to interpet the results. The query SELECT * FROM V$SYSSTAT WHERE NAME IN('parse time cpu', 'parse time elapsed', 'parse count (hard)') gives me the following values: STATISTIC# NAME CLASS VALUE 177 parse time cpu 64 0 178 parse time elapsed 64 0 180 parse count (hard) 64 582593 So my problem should be parsing of sql-statements To get the most paresed sql statements, I run the following query: SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA ORDER BY PARSE_CALLS desc and get the following result: SQL_TEXT PARSE_CALLS EXECUTIONS ---------------------------------------------------------------------------- - savepoint S4 2573193 2573193 ---------------------------------------------------------------------------- - BEGIN .Print_Queue_API.Get_Job(:a0,:a1,:a2); END; 2537668 2537668 ---------------------------------------------------------------------------- - update seq$ set increment$=:2,minvalue=:3,maxvalue=:4, cycle#=:5,order$=:6,cache=:7, highwater=:8,audit$=:9 where obj#=:1 693313 693313 ---------------------------------------------------------------------------- -- select sysdate + 30/86400 from dual 534042 534042 If I get everything right I should focus on the statement 'savepoint S4' (the client applikations sets this befora a transaction is made). But how do I do that? The other call is a external C program checking for printjobs in the database. How can I can I improve this. These two statements are by far the 'worst'. If anyone managed to get to this point of the mail, I will supply the technical details for our dear little db: OS: WIN 2000 Advanced Server DB: 8.1.7.1.3 Standard Edition time_statistics is not on in production environment Thankful for any input Regards /Jonas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonas A Wetterberg INET: jonas.a.wetterberg@manpower.se 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@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: cspence@FuelSpot.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@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).