RE: Measure parsing calls...

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 03 Oct 2001 13:17:46 -0700
Message-ID: <F001.003A117D.20011003132543@fatcity.com>

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
<schema>.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_at_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_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).
--

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

Author: Christopher Spence
INET: cspence_at_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