Measure parsing calls...

From: Jonas A Wetterberg <jonas.a.wetterberg_at_manpower.se>
Date: Wed, 03 Oct 2001 08:13:46 -0700
Message-ID: <F001.003A07F8.20011003075133@fatcity.com>

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). Received on Wed Oct 03 2001 - 10:13:46 CDT