John, OK I guess we're saying it is accounted for in the parse time.
The client is complaining about I/O and a 9 hour run at the beginning of each month. I told him he doesn't have an I/O issue as 99% of his response is CPU time. I did look at the "Parsing SQL time" reported by sp_time for 9i and it only showed a .03% value for this run. The rest of the month it does show roughly 4-7% of the response time. I think it's worth looking into but it won't address his main concern and even if we do speed up the rest I doubt if it will really be noticed.
Provided we get them to agree to a change, if we do get some positive results I'll let you know.
I ran the report on another production database (and another app) which took the default. The "Parsing SQL time" was usually 0.0x% for a day the highest being 0.40% of response time.
And like I said in my other email, I don't understand why some people have their test systems have one setting and their production another. And then they complain about not getting the same plan.
Thanks Larry
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of John Kanagaraj Sent: Wednesday, January 11, 2006 4:24 PM To: mark.powell@(protected); oracle-l@(protected) Subject: RE: optimizer time reported as?
Note that in 10g, this parameter is hidden (becomes "_optimizer_max_permutations"), and the value is defaulted to 2000.
Oracle seems to have noticed that the large default value in 8i was probably incorrect. At least for Oracle's E-Biz Suite (Apps 11i), even that based on a 8i database, this value necessarily needs to be set to 2000. I had helped someone (albeit on an Apps database) debug a query that took 10 mins to parse (and less than a min to execute!)- the parameter was incorrectly set to default and the query parsed and performed under a minute.
So go ahead and satisfy our curiousity... What did sp_time (or is it sp_systime_<Version>,sql!) point to?
John Kanagaraj <>< DB Soft Inc Phone: 408-970-7002 (W)
** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **
__ ____ ____ ____ ____ ____ ____
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Powell, Mark D Sent: Wednesday, January 11, 2006 1:11 PM To: oracle-l@(protected) Subject: RE: optimizer time reported as?
I remember the default on 8.1.7 being 80,000 and Oracle support had us lower it to 2,000 while we were working on an ORA-04031 (See ORA-04031.ora-code.com) iTAR.
ora817 > @(protected)/parms 'For all columns: Y = Yes/True N = No/False ' ' D = Deferred, I = Immediate, S = System, U = User session' ' Default indicator is unreliable if col SYS indicates chg ' Enter value for parameter: optimizer_max_permutations
D S S M A e e y o d NAME VALUE f s s d j -- ---- ---- ---- ---- ---- --- -- ---- ---- ---- ---- ---- ---- -- - - - - - DESCRIPTION -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ optimizer_max_permutations 80000 Y Y N N N optimizer maximum join permutations per query block
So how did the performance test turn out?
HTH -- Mark D Powell --
__ ____ ____ ____ ____ ____ ____
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Wolfson Larry - lwolfs Sent: Wednesday, January 11, 2006 4:01 PM To: oracle-l@(protected) Subject: optimizer time reported as?
I got asked about performance on a database that was recently upgraded from 8.1.7.4 to 9.2.0.6.
Looking at the initora I noticed optimizer_max_permutations integer 79999
The DBA doing the upgrade said she only changed the parmaeters relating to the upgrade. I know the default changed from 8's 80000 to 9's 2000 and there was an earlier TAR telling us to change the 80000 to 79999.
I thought this might be an issue and I ran Tim Gorman's sp_time script to see where the overhead was. I just wanted to verify that the optimizer_max_permutations time is accounted for in the Parsing SQL time and not somewhere else.
TIA Larry Wolfson
************************************************************************ * The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
