Re: Oracle11 & statistics calculations

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Wed, 29 Aug 2007 12:08:17 -0400
Message-ID: <MPG.213f63e7dafb31149896ae@nntp.aioe.org>

In article <1188394506.879960.34390_at_o80g2000hse.googlegroups.com>, Mark.Powell_at_eds.com says...
> A trace of the new statistics update process might show you if a call
> to dbms_stats is made.
>
>

It didn't show me that, but it did show me that the "counting" SQL is being executed. Also, in addition to the normal .trc file, the dump directory now contains an additional, much smaller file called .trm: [oracle_at_oracle12 trace]$ ls -l *BSLN*
-rw-r----- 1 oracle oinstall 460056 Aug 29 11:51 11G_ora_18564_BSLN.trc -rw-r----- 1 oracle oinstall 281 Aug 29 11:51 11G_ora_18564_BSLN.trm [oracle_at_oracle12 trace]$

This file is readable by tkprof and produces an output like this: TKPROF: Release 11.1.0.6.0 - Production on Wed Aug 29 12:03:31 2007

Trace file: 11G_ora_18564_BSLN.trm
Sort options: default

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
************************************************************************

Trace file: 11G_ora_18564_BSLN.trm
Trace file compatibility: 10.01.00
Sort options: default

       0  session in tracefile.
       0  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       0  SQL statements in trace file.
       0  unique SQL statements in trace file.
      23  lines in trace file.
       0  elapsed seconds in trace file.

It looks like some kind of advanced session/module tracking mechanism for tkprof/trcsess.

The normal .trc file produces the normal output: KPROF: Release 11.1.0.6.0 - Production on Wed Aug 29 12:06:27 2007

Trace file: 11G_ora_18564_BSLN.trc
Sort options: default

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
************************************************************************

SQL ID : 6743x3tw15hc6
BEGIN dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',TRUE); END;

BTW, it gives you SQL ID with every SQL in the trace file. Oracle10 did not do that. Received on Wed Aug 29 2007 - 11:08:17 CDT