problem with slow parse of simple insert query

From: <nordby_at_channelpoint.com>
Date: Thu, 14 Jan 1999 01:12:54 GMT
Message-ID: <77jgae$qj6$1@nnrp1.dejanews.com>

Wondering if anyone has insight into this problem. I have a simple insert query that I am executing while running tkprof and sql_trace=true, timed_statistics=true in my init.ora file.

When running tkprof, ordering by elapsed times (tkprof73 oratracefile.trc TkprofElap.prf sort=(exeela,fchela,prsela) ), it shows very slow elapsed times on the parse of the query. The subsequent 10 parses or so get gradually faster, until finally it gets to where it should be. Why would it take so long to parse the first 10, then finally get where it should be.

Thanks in advance! Jon.

Dictionary cache hits are 98%.

shared_pool_size=27000000
shared_pool_reserved_min_alloc=5000
shared_pool_reserved_size=13000000

insert into Member (memberID, firstName, aclID, lastName) values (5497576188618, 'John', 2, 'Doe')

call  count  cpu  elapsed  disk  query	current  rows ------- ------ 


-------- ---------- ---------- ---------- ----------  ---------- Parse	1 


0.02  0.40  0  0  0  0 Execute	1  0.00  0.00  0  1  3	1 Fetch  0  0.00 
0.00  0  0  0  0 ------- ------  -------- ---------- ---------- ----------
----------  ---------- total  2  0.02  0.40  0	1  3  1

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 17 (TWO)

Rows Execution Plan

-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE

We run the query 1000 times with incremental id's. The next several parse times are:

call  count  cpu  elapsed  disk  query	current  rows ------- ------ 


-------- ---------- ---------- ---------- ----------  ---------- Parse	1 


0.02  0.38  0  0  0  0 Parse  1  0.02  0.34  0	0  0  0 Parse  1  0.02	0.34 
0  0  0  0 Parse  1  0.03  0.32  0  0  0  0 Parse  1  0.03  0.32  0  0	0  0
Parse  1  0.03	0.29  0  0  0  0 Parse	1  0.02  0.28  0  0  0	0 Parse  1 
0.02  0.26  0  0  0  0 Parse  1  0.02  0.22  0	0  0  0 Parse  1  0.01	0.14 

0 0 0 0 Parse 1 0.01 0.02 0 0 0 0

The rest of the 1000 executions are at .02!

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 13 1999 - 19:12:54 CST