RE: Long Parse Times, Huge In Lists

From: Larry G. Elkins <elkinsl_at_flash.net>
Date: Fri, 23 Oct 2009 13:10:09 -0500
Message-ID: <004d01ca540c$0ea07d20$2be17760$_at_net>

Stats are in place for all objects, none with no stats, various gathering times. Dynamic sampling is the default of 2. No sign of them altering the setting within the sessions, or hints for dynamic sampling in SQL statements. And there none of the typical dynamic sampling type queries in the trace files. So feel pretty sure dynamic sampling isn't kicking in.    

Larry G. Elkins

elkinsl_at_flash.net

Home: 214.954.1781

Cell: 214.695.8605

Work: 817.280.5634  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Friday, October 23, 2009 12:38 PM
To: elkinsl_at_flash.net; oracle-l_at_freelists.org Subject: Re: Long Parse Times, Huge In Lists  

Larry,  

Even though they may not have disabled the auto stats gathering job itself in DBMS_SCHEDULER, they may have locked some tables or disabled table monitoring or otherwise disabled stats gathering by that job? Can you check to see how many application tables do not have stats? Then, check the setting of "optimizer_dynamic_sampling" (default is "2" in 10g). The levels are defined in the Oracle Database "Reference" manual as...  

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 23 2009 - 13:10:09 CDT