Prev: what characterset to use?
Next: ORA-04031: unable to allocate 2097152 bytes of shared memory("shared pool","unknown object","hash-join subh","QERHJ Hash Table Entries")
From: Mladen Gogala on 29 Aug 2007 11:01
In article <1188388608.487786.253110(a)w3g2000hsg.googlegroups.com>,
johnbhurley(a)sbcglobal.net says...
> Hacking up some triggers and putting some low level traces on is
> another way since you are just testing.
>
>
No need for triggers. DBMS_SCHEDULER.RUN_JOB has two arguments: job name
and "run in current session". Simply, set up 10046 trace, level 12 and
run the job with the second argument set to TRUE. Of course, nothing
will show up, as the package text is encrypted, so the arguments to the
DBMS_STATS will not be visible.
From: Mladen Gogala on 29 Aug 2007 12:08
In article <1188394506.879960.34390(a)o80g2000hse.googlegroups.com>,
Mark.Powell(a)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(a)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(a)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

Copyright (c) 1982, 2007, Oracle. All rights reserved.

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

Copyright (c) 1982, 2007, Oracle. All rights reserved.

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.
First  |  Prev  | 
Pages: 1 2
Prev: what characterset to use?
Next: ORA-04031: unable to allocate 2097152 bytes of shared memory("shared pool","unknown object","hash-join subh","QERHJ Hash Table Entries")