On a side note, it's not necessary to alter a table to invalidate all statements for that object. If you want to flush a single statement in 10g you can do so by creating an outline on the statement using the DBMS_OUTLN.CREATE_OUTLINE procedure, and them immediately dropping it. This has the side effective of flushing the statement from the shared pool (most of the time). Of course this is possible in 11g (and back ported to 10.2.0.4) with the dbms_shared_pool.purge procedure. I wrote a little script that I use all the time that prompts for a sql_id, creates the outline, and then drops it. I posted it on my blog a while back if anyone is interested. Here's a direct link to the script: http://www.oracle-guy.com/scripts/flush_sql10.sql
Kerry Osborne Enkitec blog: kerryosborne.oracle-guy.com On Mar 31, 2009, at 4:40 AM, Martin Klier wrote:
Hi, J. Dex schrieb:(specifically 10.2.0.3) or is there still something that needs to be setup to get it to regularly run? What needs to be set up? How do you set it up? In the past, we have had a cron job that runs weekly that does the following:exec dbms_stats.gather_database_stats(estimate_percent=>20,cascade=>true);How is everybody else handling this?Yes, Oracle gathers statitics on its own, with some default values described by John in the parallel post. But IMO the major disadvantage of this nightly operations is the time period they do it in by default. For your very special case, you will have to look up carefully, if statistics/histograms collected in the middle of the night are fitting your needs. Especially with OLTP databases, the contrary may be true: Imagine a table of orders. The company tries to finish all open orders before quitting time. So only a minority of order statuses will be in "order-is-still-open" mode when the stats collector comes and gathersstatistics. He might be mislead to create even histograms based on thatstatus field contents. (For example: OPEN means 1, closed means 1000. some single OPEN lines will be considered as massive data skew, as far-outs.) If you are now inserting lots of open orders (or reopen oldones) in the morning, all stats and histograms for the column are crap,and your query plans will honor that. :) A far better approach for this kind of usage profile might be, to kick out the auto gather jobs, and create statistics/histograms one time while the data set is in a realistic state, maybe at average plantworkload. Yes, the performance will degrade in this time period, and youmay experience lots of reparsing at the time, and there may be severalhybrid parses as well, but at least the stats are ok one time. Then lock the stats, and recreate them only on demand or in a wider interval (eachmonday morning, etc.) Hybrid parses (no clue if the term is useful or correct) are a specialside effect of bind variable peeking: Imagine a join between two tables. The statement is parsed during your gather stats job. One table alreadyhas the new stats, and the other table still has its old stats. A newquery plan will be created based on this information. Now the statementis used so frequently (with honored bind variables in service) that itwill not age out of the library cache. So you have proper stats now, allshould be well, but you see a sad query plan anyway all over the day! My quick and dirty solution prior to 11g: Alter the comment of all (affected) tables in a superfast loop after gather stats job ran. :) This hack will invalidate all execution plans operating with the table in question, because the table "has changed". HTH Martin Klier -- Usn's IT Blog for Linux, Oracle, Asterisk http://www.usn-it.de -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
