Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> (RESOLUTION): AWR report showing ###### ... PLUS stats pack viewer

(RESOLUTION): AWR report showing ###### ... PLUS stats pack viewer

From: Tim Onions <>
Date: Fri, 06 Apr 2007 13:19:39 +0100
Message-ID: <BAY137-F111A2D274FB8FE7A53B82499640@phx.gbl>


Just a quick update on a post I made week for anybody that may be intested. My AWR report shows ###### for certain values suggesting that the number is too large to display. I have not found a "fix" so far and have raised a SR. I did get one excelent suggestion back form the list which was to do a 10046 trace and use the SQL from that so I could run the queries that are giving ###### and get the data I need. I did this, and although a bit of a pain to locate the right SQL have had some success. In doing this I used an option of TKPROF I did not know existed - insert=filename. This creates a series of insert statements which include the SQL. I found it useful as that way I had just the SQL and none of the other stuff in a TKPROF formatted output (if there is a simpler way to get TKPROF to list just the SQL and none of the others stats then from RTFM I could not see it)

So the process goes like this:

alter session set max_dump_file_size=unlimited ; alter session set timed_statistics=true; alter session set tracefile_identifier='TIMTRACE'; alter session set events '10046 trace name context forever, level 12'; start $ORACLE_HOME/rdbms/admin/awrrpt.sql ...
alter session set events '10046 trace name context off';

tkprof /u01/app/oracle/admin/PT1D4TP/udump/pt1d4tp2_ora_nnnn_TIMTRACE.trc awrtrace.txt sys=no aggregate=yes insert=awrsql.txt

But then...

Having done this, yesterday's post about stats pack viewer set some bells ringing. I contacted Alexey Danchenkov and got hold of the latest trial version of this and the other sp products (there were some website issues initially, maybe that was the cause of the poster's issues yesterday, although I did see the press release that was referred too also - yet Alexey Danchenkov was certainly clearly still maintaining and selling spViewer et al - and stated that new versions were coming out soon). spReporter can read the AWR views directly and create its own version of an AWR report. So this is another option to consider if Oracle does not come up with a prompt fix. spReporter is not free but not expensive either. I've yet to fully compare its output with the AWR report but I get a "warm" feeling.

Hope this is of some interest to some members of the list. At least this information will get into the archives as a result of this posting and so maybe it will prove useful to others in the future too.

Tim Onions

Get Hotmail, News, Sport and Entertainment from MSN on your mobile.
Received on Fri Apr 06 2007 - 07:19:39 CDT

Original text of this message