soft pare versus hard parse

[DaPi]

We're waiting on you to verify the data and post the query.

[learning_bee]

Dapi,

I agree with you that there is only 31 million in a year but she said there is no typo which makes not to understand either.
sorry, I forgot to mention that to you the other day, I did veify the data and it's correct.


what do you think why that elasped time is so huge, could it be a lock somewhere??? but at the same time, it's only a "SELECT" statement

[slimdave]

Originally posted by learning_bee
Dapi,

I agree with you that there is only 31 million in a year but she said there is no typo which makes not to understand either.
sorry, I forgot to mention that to you the other day, I did veify the data and it's correct.


what do you think why that elasped time is so huge, could it be a lock somewhere??? but at the same time, it's only a "SELECT" statement

Isn't it obvious that it's a bug? The query didn't really take that long to run. How long did it really take?

[DaPi]

The trace file should give some hint as to what you're waiting on.

I'd suggest to use the Trace Analyser: http://metalink.oracle.com/metalink/...&p_id=224270.1

[learning_bee]

the job took about 3 days to complete and other dba think it's a lock somewhere on other processes but I disagree with them but I can't prove it.

[davey23uk]

Originally posted by learning_bee
the job took about 3 days to complete and other dba think it's a lock somewhere on other processes but I disagree with them but I can't prove it.

ask them to prove it is a lock then

neither of you can what it is or isnt without the proof

[pando]

can someone post the query or whatever is lol

[DaPi]

Originally posted by DaPi
I'd suggest to use the Trace Analyser: http://metalink.oracle.com/metalink/...&p_id=224270.1

This might not be such a bright idea after all - my guess is that the trace file is VAST - the analyser might take three days to read it!

You'll need to open the trace file with something that doesn't try to read it all into memory - how about "type" or "more" or . . ? - then see what it says about waiting.

[gandolf989]

Originally posted by DaPi
This might not be such a bright idea after all - my guess is that the trace file is VAST - the analyser might take three days to read it!

You'll need to open the trace file with something that doesn't try to read it all into memory - how about "type" or "more" or . . ? - then see what it says about waiting.

You might be able to grep it if you are using unix.

[learning_bee]

Sorry, I was trying to use the code so it's easier to read but I couldn't do it, could someone tell me how so I can use it next time.


Any way, come back to my problemI was suspecting the elapsed time was wrong so I request my colleague to take another trace. anyway, below is the new update elapsed time and gain the number is so huge:

/code:


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 585 42.054156502209.87 0 18073 0 0
Execute 469 0.78 56.87 175 733 307 60
Fetch 345 0.21 21.65 19 4720 0 51
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1399 43.054156502209.39 194 23526 307 111


/code


Also she mentioned about couple trace file that were start with p and s so I guess it had something to do with pmon and smon. Also there were errors on one of those s file: ora-00604. below is what I think and please tell me if my approach is wrong:


they currently set process=150 and it's cluster env so I think this number is too low. that could possible give an error and the new processes were created and that could caus the lock.

anyone had any other suugestion???