Re: stats collection question

  • From: Gints Plivna <gints.plivna@xxxxxxxxx>
  • To: mschmitt@xxxxxxxxxxxx
  • Date: Thu, 29 Apr 2010 17:56:22 +0300

I'm not sure I can explain the reasons why stats collection by
different users leads to different plans, but as a side note I have
another question - does the data populated are very different each
If they are similar or slowly evolving probably you can gather stats
once and then just export and import already precalculated stats? If
the data are more or less the same (amount and distribution) this
could give at least 2 positive aspects:
1) predictive performance
2) decrease import time, because importing stats is muuuuch less
resource eager process than stats calculation or estimation

Of course if the loaded data are very volatile the process described
above won't help.

We have used similar mechanism for a search mechanism described here
Setting and deleting stats on alternating pair of materialized views
gave us possibility to periodcally update them without interruption.
And - yes I know - one can use only one MV to achieve the same, but
then full refresh process uses delete and this was too much compared
to truncate.

Gints Plivna

2010/4/29 Michael Schmitt <mschmitt@xxxxxxxxxxxx>:
> Hi All,
> I have a quick question related to stats collection.  This is for a 10203
> database on Linux.
> We have application (that is not designed very well), that drops all its
> tables, loads data into the tables, collects stats via (exec
> dbms_stats.gather_schema_stats(‘<userA>’,30,CASCADE=true)), and then
> compiles a number of views.  This process runs as UserA.  After the process
> completes, I can run a query that joins view+view+view and it takes about
> 2minutes.  However, right after that query takes 2 minutes if I run the same
> stats collection procedure as SYS ( exec
> dbms_stats.gather_schema_stats(‘<userA>’,30,CASCADE=true)) the query now
> returns in under 10 seconds (and selects a different plan) until the nightly
> processing runs again and it goes back to taking 2 minutes again.
> Therefore, is there a difference in stats collection based on who executes
> dbms_stats?  What am I missing here?  The only other thing I can think of is
> that it is related to the stats being generated prior to UserA compiling
> their views.  I have verified that UserA’s command seems to be working by
> checking the last_analyzed date on dba_tables and dba_indexes.  The explain
> plans are all pretty ugly and 200 lines long, so they have not been much
> help to me.
> Any thoughts?

Other related posts: