soft pare versus hard parse

[learning_bee]

based on the result below:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4697 5.01 7.46 0 0 0 0
Execute 3897 12.96 276.84 457 1490 15037 704
Fetch 3092 2.00 1.95 0 18592 0 921
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11686 19.98 286.25 457 20082 15037 1625

Misses in library cache during parse: 23


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1407 0.04 0.07 0 0 0 0
Execute 1407 0.21 0.29 0 1404 1409 702
Fetch 705 5.26 120.24 0 2113 702 704
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3519 5.53 120.60 0 3517 2111 1406

Misses in library cache during parse: 1

shared pool was aged out and it had to parse from disk which is bad.
I have too many hard parse, I have been reading some of the post in asktom and he has mentioned that try to increase session_cached_cursor to some positive number, right now, it's =0 on my database.

Any inputs???

as always, thanks for everything

[JMac]

Shoot the developers for not using bind variables.

[davey23uk]

Originally posted by learning_bee
based on the result below:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4697 5.01 7.46 0 0 0 0
Execute 3897 12.96 276.84 457 1490 15037 704
Fetch 3092 2.00 1.95 0 18592 0 921
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11686 19.98 286.25 457 20082 15037 1625

Misses in library cache during parse: 2539


Any inputs???

as always, thanks for everything

parsing more than you execute - fantastic

[simply_dba]

you may want to pin frequently used procs

[learning_bee]

I thought the same thing and the users disagree and they said it's not the bind variable issues. beside Oracle doesn't use the same execution plan so that it has to parse the new one, what is the other reasons that could make the high parse??? could it be locking issues???

How can I prove to these stubbron users.

[slimdave]

Originally posted by learning_bee
... beside Oracle doesn't use the same execution plan so that it has to parse the new one...

What do you/they mean by this?

[davey23uk]

Originally posted by learning_bee
I thought the same thing and the users disagree and they said it's not the bind variable issues. beside Oracle doesn't use the same execution plan so that it has to parse the new one, what is the other reasons that could make the high parse??? could it be locking issues???

How can I prove to these stubbron users.

high parse = lack of bind variables

[gandolf989]

Originally posted by davey23uk
high parse = lack of bind variables

I would add to that:

high parse = dynamic sql with concatenation

And if that is the case, then I will agree with the phrase, "Shoot the developer!!!"

[DaPi]

Hang on! According to T.Kyte 1-on-1, the PARSE count doesn't distinguish between hard & soft parses - total 4967.

"Misses in library cache during parse: 23" says that 4944 of them WERE in the cache - i.e. soft parses. Sounds good to me.

[gandolf989]

Originally posted by DaPi
Hang on! According to T.Kyte 1-on-1, the PARSE count doesn't distinguish between hard & soft parses - total 4967.

"Misses in library cache during parse: 23" says that 4944 of them WERE in the cache - i.e. soft parses. Sounds good to me.

Well read.