statspack report shows low execute to parse%

These 2 are way below the 100% target. All other readings are between
97 to 100%.
What could be the problem that is causing such low value. As
i am new to DB ADMIN i request group's help.
My SGA Config is:
shared pool size: 85mb
db_block_size :8192 db_block_buffers :14000
log buffer:524288

RAM size:512MB. ONE PENTIUM III Processor.
thks
clement

Not using bind variables, not using bind variables, and not using bind
variables.
Read *any* article by Thomas Kyte, and you will find out all about
them. It is his personal hobby horse and rightly so.
In the mean time peak in v$sql to see whether you have any statements
like
select * from emp where empno = 10
instead of
select * from emp where empno = :b1
in the former case your code is causing the behavior you observe.

--
Sybrand Bakker, Senior Oracle DBA

Applications that generate dynamic SQL require parsing. Substitue
static SQL for dyamic SQL wherever possible.

HTH -- Mark D Powell --

I trust the last sentence should read
'Substitute dynamic sql for static sql wherever possible'
and you do know the difference between the two ; -)

Sybrand I am confused.

How can you say that he is not using bind variables from the given
information.
The information says that Soft Parse % is more that 97%.

If bind variables were not used then wont' Soft Parse % would be far
less than 97% ?

The only thing execute to parse ratio being too low tells us that the
system is parsing a lot, that could be normal for a web application
with stateless connection for e.g mod_plsql or a j2ee environment which
does not use statement caching provided by jdbc 3.0.

I am not saying that this ratio too low is not a problem what I am
saying is that we would need more information to determine whether it
is a problem or not for example latch contention information on
library cache latch and shared pool latch.

thanks
amit

Doesn't this tell us that while parsing
for every 68 seconds (over 1 minute) only 1 second was used for parsing
?

If thats true then rest of the time must have been spent while waiting
on latches

library cache(in case of soft parses)
shared_pool latch (in case of hard parses)

even then it is not sufficient to say whether it is hard parsing a lot
or over soft parsing

So as u see the issues raised are all in the 99% level. Also i am not
working in web environment.
It is a client server env at the branches and connected to the remote
server(Compaq TRU64 RAC) at main office(MO) working on distributed
database concept. Branch transactions are transferred to MO on an
hourly basis.
SGA breakdown difference for DB: ORCSAW Instance: orcsaw Snaps: 2 -3:
Pool Name Begin value End value
Difference
----------- ------------------------ --------------
-------------- -----------
shared pool free memory 40,492,116 40,456,720
-35,396
shared pool library cache 11,763,564 11,778,732
15,168
shared pool dictionary cache 3,136,892 3,136,892
0
Also the queries we are using seems to be fine(with proper bind
variables).

rgds
clement

Statspack some time may not display the right value.

anysql from china
http://www.anysql.net/en/

Could you post the wait event part of statspack also ?

amit

Hi amit
I am indicating the top 5 wait events:
Event Waits WaitTime(cs) %total wait
time
Sql*Net message from dblink 1,134 4,058 90.72
db file scattered read 510 141 3.15
db file sequential read 513 126 2.82
Sql*Net break/reset to client 44 67 1.5
Direct Path Read 247 20 .45

I dont the complete listing as of now, as the next 2 days are off. In
the meanwhile i hope this info will help.
thks and regds
clement

Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ------
------
SQL*Net message from dblink 1,134 0 4,058 36
9.9
db file scattered read 510 0 141 3
4.4
db file sequential read 513 0 126 2
4.5
SQL*Net break/reset to clien 44 0 67 15
0.4
direct path read 247 0 20 1
2.1
log file sync 111 0 17 2
1.0
latch free 40 25 14 4
0.3
direct path write 79 0 13 2
0.7
control file sequential read 37 0 10 3
0.3
refresh controlfile command 6 0 4 7
0.1
log file parallel write 160 0 3 0
1.4
SQL*Net message to dblink 1,133 0 0 0
9.9
SQL*Net more data to client 465 0 0 0
4.0
control file parallel write 233 0 0 0
2.0
db file parallel write 66 0 0 0
0.6
file open 5 0 0 0
0.0
SQL*Net message from client 5,472 0 1,138,103 2080
47.6
SQL*Net message to client 5,471 0 2 0
47.6
-------------------------------------------------------------

thks and regards
clement

Hi,

Are you sure you really have a performance issue with the database. Or
you are just trying to eliminate all the wait event ?

amit

what is the interval of the statapack.

Could you email me you complete statspack at my email address
podd...@gmail.com

amit