On Jul 14, 1:29 pm, groups.brob...@gmail.com wrote:
> Has anyone seen this before? It seems new to me:
>
> We're running Oracle 10.2.0.3.0 on Solaris. We had statspack turned
> on; now we wanted to turn it off. I removed the job, and then I
> wanted to clean out the statspack tables. After doing so by simply
> running "delete from perfstat.stats$snapshot" (and having restarted
> the database many times since), I wanted to see which tables were now
> zero-sized. Running count(*) against the table led to some strange
> results, performance-wise.
>
> Specifically, this statement:
>
> select count(*) from perfstat.STATS$BG_EVENT_SUMMARY ;
>
> COUNT(*)
> ----------
> 0
>
> Elapsed: 00:01:09.72
>
> produced these stats:
>
> Statistics
> ----------------------------------------------------------
> 799 recursive calls
> 12 db block gets
> 4149 consistent gets
> 3954 physical reads
> 0 redo size
> 619 bytes sent via SQL*Net to client
> 488 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 17 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> What's up with that? 1 minute, and 3954 physical reads to find out I
> have 0 rows in the table? And this is AFTER I ran a "analyze table
> perfstat.STATS$BG_EVENT_SUMMARY estimate statistics".
>
> Since when do tables with 0 rows require so much physical i/o to scan?

Solved problem:

SQL> @?/rdbms/admin/sptrunc;

-bdbafh

On Jul 15, 10:33*am, bdbafh <bdb...@gmail.com> wrote:
> On Jul 14, 1:29 pm, groups.brob...@gmail.com wrote:
>
>
>
>
>
> > Has anyone seen this before? *It seems new to me:
>
> > We're running Oracle 10.2.0.3.0 on Solaris. *We had statspack turned
> > on; now we wanted to turn it off. *I removed the job, and then I
> > wanted to clean out the statspack tables. *After doing so by simply
> > running "delete from perfstat.stats$snapshot" (and having restarted
> > the database many times since), I wanted to see which tables were now
> > zero-sized. *Running count(*) against the table led to some strange
> > results, performance-wise.
>
> > Specifically, this statement:
>
> > select count(*) from perfstat.STATS$BG_EVENT_SUMMARY ;
>
> > COUNT(*)
> > ----------
> > * * * * * * * * 0
>
> > Elapsed: 00:01:09.72
>
> > produced these stats:
>
> > Statistics
> > ----------------------------------------------------------
> > * * * * 799 *recursive calls
> > * * * * *12 *db block gets
> > * * * *4149 *consistent gets
> > * * * *3954 *physical reads
> > * * * * * 0 *redo size
> > * * * * 619 *bytes sent via SQL*Net to client
> > * * * * 488 *bytes received via SQL*Net from client
> > * * * * * 2 *SQL*Net roundtrips to/from client
> > * * * * *17 *sorts (memory)
> > * * * * * 0 *sorts (disk)
> > * * * * * 1 *rows processed
>
> > What's up with that? *1 minute, and 3954 physical reads to find out I
> > have 0 rows in the table? *And this is AFTER I ran a "analyze table
> > perfstat.STATS$BG_EVENT_SUMMARY estimate statistics".
>
> > Since when do tables with 0 rows require so much physical i/o to scan?
>
> Solved problem:
>
> SQL> @?/rdbms/admin/sptrunc;
>
> -bdbafh-

Take a second look at that - do you see some statements that are not
truncates?

I did see a system once that had statspack left going for a year or
something, my memory is hazy on the details (9.2, probably), but it
did require dropping and recreating the user, things were just too big
to get the reports or anything.

jg
--
@home.com is bogus.
http://www.net-security.org/secworld.php?id=6325

On 7¿ù15ÀÏ, ¿ÀÈÄ10½Ã53ºÐ, groups.brob...@gmail.com wrote:
> On Jul 14, 8:53 pm, Dion Cho <ukja.d...@gmail.com> wrote:
>
> > It's interesting that "index full scan" is being used here, but it's
> > quite possible.
>
> > This is not a degenerate case, actually very common and natural case.
> > The simple fact is that delete never deallocates the free space.
> > Only truncate or rebuild does it.
>
> > Dion Cho
>
> Well, I call it a degenerate case as it would be something that would
> only occur in:
>
> 1. An unconstrained query (or one that didn't constrain against
> indexed values)
> 2. against a table with a large number number of rows
> 3. that had had a large percentage of those rows deleted
>
> From what has been said so far, all three of these conditions need to
> be true in order for you to see poor performance like this. It is is
> unusual for any app we write to do unconstrained queries against very
> large tables; it is also unusual for us to dramatically reduce the
> size of a particular table once it is in production via a massive
> delete. The only common real-world case where I can see something
> like this happening is in batch-processing situation where
> intermediate staging tables are loaded up with production data and
> subsequently queried, then cleaned out; however, in this case, I would
> expect that you would use global temporary tables for the
> intermediaries.
>
> Can you think of other examples where this type of problem could
> arise? Our application (which has been running 24/7 since 1998 and
> has been upgraded many times; we've always handled our data reaping by
> running nightly jobs to remove the oldest day's worth of data, and we
> haven't run into issues with this strategy. We'd like to eventually
> move to using partitioned tables to make the reaping a little more
> elegant, but the nature of the data is such it is rather tricky to do
> so without a significant amount of work & migration (as new data has
> references to older data, so it's not a simple matter of partitioning
> by create date). We've had no real issues, presumably, because (1)
> our queries all go against indexes and (2) the daily volume on the
> system grows at a very slow rate (maybe 100% per year), so there isn't
> really an issue with "shrinkage", even though the tables probably have
> a fair amount of wasted space (due to holes not filling up until pages
> completely free up).

Think about your execution plan.
It's index full scan, not index fast full scan or table full scan.
If you had added some beautiful predicates(like where xxx > 0), it
would have followed index range scan.

And index has almost zero keys and leaf blocks!!!
Even in that case, you still have performance problem.

It's because, the leaf node chain is not broken by deletion.
Only table truncation, index rebuilding or coalescing do that.

Dion Cho