This content has been marked as final. Show 7 replies
I looked at the note on the OTN link, along with the other bug/note referenced by Osama_mustafa, but they seem to be related to partitioning.
In our case, the table I am gathering stats on is not partitioned.
I use granularity=ALL as my standard command, even if the table is not partitioned, but this didn't make any difference in the test environment.
If the table is not partitioned, then this option is ignored.
As it turned out, I let it run overnight and it finally finished in a little more than 8 hours (I think).
This was done in a new test environment with no one else in the database.
This still seems excessive, but I also found out that this table has many lobs, which may be contributing to the problem.
I also found out that this table has fine grained access control, but again, i don't think that would make that much of a difference.
I beg to differ. Fine grained access control can make all the differences in the world. It is also quite possible that the stats job is slow because of the LOBs. But there is no point in guessing. If you have the diagnostic pack license then you may find the answer in ASH. Otherwise you can trace the stats gathering process(es). That will tell you which statements are slow, and from that you should be able to figure out which parameters may help speed the process up.
Be sure to enable waits when tracing, and also include system queries into tkprof report.