WHEN USING THE MENU ITEM TOOLS ->
AVERAGE REPORTS -> AVERAGE BY WEEK DAY AND SAVING THE CHART TO HTML,
WE ARE NOT SEEING THE LEGEND AT THE BOTTOM OF THE .JPG FILE. IT WILL
SHOW ON THE SCREEN WHEN YOU EXPAND THE CHART BUT NOT WHEN SAVING. THE
LEGEND WILL SHOW IF WE USE SNAPSHOTS AS THE LEGEND ITEMS, BUT NOT
You just need to decrease the size of
legend font using Chart Properties -> Legend -> Text Tab -> Font
Button. Set it to 8 pixels and you see your legend.
CAN YOU EXPLAIN HOW TO SCHEDULE THE
CHARTS TO BE CREATED AUTOMATICALLY ON A WEEKLY SCHEDULE?
First, you need to configure your
Statspack Viewer Service (SVS):
1. Select Configure Services and click New link in the Service/Tasl
2. Enter any service name you like. For example, MyService.
Optionalyy, enter your SMTP server address, user name, e-mail.
3. Specify the full path to SVS log file.
4. Click Save link to save changes to the repository.
5. Click New Database Task link.
6. Select Task Type as "Schedule Task".
7. Select your database in the Database/Instance.
8. Choose chart picture destination folder where charts will be
stored. Check on the checkbox Store To File System?. If you want also
to store generated charts to the viewer's central repository, check on
the checkbox Store To Database Repository?. Optionally, adjust the
chart appearance properties. Click Save to save changes.
Now, you go to the folder where your Statspack Viewer Enterprise is
installed. Open the config file spvesvs.ini. You see there the
Repository=<Repository Connect string>
Set the Name parameter to your SVS name (MyService as in the example)
and the Repository parameter to the full connect string of your
Statspack Viewer Enterprise repository. For example,
Save the changes to the spvesvs.ini file.
Create SVS service using "Install Statspack Viewer Service" shortcut
in the Statspack Viewer Enterprise program group or run in the command
line: spvesvs.exe /Install
Start the SVS service "Statspack Viewer Enterprise Service" using
Control Panel -> Services.
Now, you are ready to schedule your statistics:
1. Connect to the target database and select the required report. For
example, "Total Database I/O Activity".
2. Choose the numeric statistic in the grid you want to schedule chart
3. Click "Statistic Properties" link and immediately click Save link
to save this statistic to the repository.
4. Then choose Schedule link to actually schedule your statistic.
Select next date, interval between executions (for example, if you
want to execute the chart once a week, choose SYSDATE+7 interval like
in Oracle jobs). Then specify what time period in the past you want to
build chart for. If you select Snapshot Interval Type to "Interval
Based on Day Count", then SVS will generate a chart for time period
containing Last Day Count days in the past. The same behavior but for
weeks is expected, if you choose Snapshot Interval Type to "Interval
Based on Week Day and Week Count".
Optionally, specify a printer if you want to automatically print the
chart. Check on the checkbox Save To File Enabled? Click Save to save
Repeat the above four steps to schedule another statistics if you
I AM TRYING TO PLOT A CHART FOR A
STATISTIC "Redo Size/Sec" IN THE REPORT "Instance Load Profile" BY
CLICKING "Add To Chart" LINK BUT NOTHING HAPPENS. WHAT SHOULD I DO?
People are often asking this question.
After they select the required report and its data is shown in Report
Data Grid, they select a statistic's name in the grid for example a
cell with text "Redo Size/Sec" and click "Add To Chart" link. But
nothing happened after that. This is because you need to select a
concrete NUMBER statistic in the grid but not the statistic's name.
Suppose that we open a report called "Tablespace I/O" that shows
various I/O metrics for database tablespaces. For every tablespace you
may plot a set of statistics like "Reads", "Reads/Second", "Avg Reads"
and so on. Therefore, you must SELECT these particular NUMBER
statistics for the given tablespace in the grid. The viewer does not
know what particular statistic you intend to plot chart for, if you
select only the tablespace's name.
HAVING PROBLEMS GETTING THE MENU ITEM - (TOOLS ->
AVERAGE REPORTS -> AVERAGE BY WEEK DAY) TO WORK? HOW DO I CREATE A
CHART (EXAMPLE INSTANCE LOAD) WITH DAYS OF THE WEEK ON THE BOTTOM
AXIS INSTEAD OF SNAPSHOTS.
This is very easy. First, you select
your Instance Load report in the Report List. Then select the required
snapshot interval for the time period for which you intend to compute
week day averages. (You do this using Start and Finish Snapshot combo
boxes.) It is recommended to choose a quite long time period in order
week day averages to be more precise. Then you just select in the
Report Data Grid a NUMBER statistic value you want to get average for
(Note that you must select exactly NUMBER statistic cell but not the
statistic name cell like "Redo Size/Sec". This is because particular
reports contain several statistics per a single parameter. For
example, for a tablespace you might want to view physical reads or
physical writes and so on. ) After you select a required statistic in
the grid, just choose "Tools ->Average Reports -> Average by Week Day"
and that's it!
I WANT TO USE STATSPACK VIEWER
ENTERPRISE TO ACCESS AWR REPOSITORY DATA UNDER DBA USER THAT IS NOT
SYS USER. BUT WHEN I CONNECT TO AWR REPOSITORY WITH THIS DBA USER
USING STATSPACK VIEWER ENTERPRISE, I GET AN ERROR "ORA-04063: package
body "AWR101" has errors". MOST OF THE REPORTS DO NOT WORK. HOW TO FIX
is very often problem of customers who want to connect using Statspack
Viewer Enterprise to Oracle10g AWR repository under non SYS database
account that has DBA role granted. This DBA user is able to query all
DBA_HIST views but AWR101 package body delivered with Statspack Viewer
Enterprise to access AWR is compiled with errors. As a result,
customers are unable to use most of the reports for AWR with this DBA
user. If you look at the errors in the AWR101 package body, you will
see that all the errors are "table or view does not exist" for all
DBA_HIST views referenced in the AWR101 package. This is because your
DBA user is not granted directly (only through DBA role) to select
from these DBA_HIST views. You just need to grant SELECT
privilege for all DBA_HIST views to your user directly using following
select on DBA_HIST_DATABASE_INSTANCE to &&usr;
grant select on DBA_HIST_SNAPSHOT to &&usr;
grant select on DBA_HIST_SNAP_ERROR to &&usr;
grant select on DBA_HIST_BASELINE to &&usr;
grant select on DBA_HIST_WR_CONTROL to &&usr;
grant select on DBA_HIST_DATAFILE to &&usr;
grant select on DBA_HIST_FILESTATXS to &&usr;
grant select on DBA_HIST_TEMPFILE to &&usr;
grant select on DBA_HIST_TEMPSTATXS to &&usr;
grant select on DBA_HIST_SQLSTAT to &&usr;
grant select on DBA_HIST_SQLTEXT to &&usr;
grant select on DBA_HIST_SQL_SUMMARY to &&usr;
grant select on DBA_HIST_SQL_PLAN to &&usr;
grant select on DBA_HIST_SQLBIND to &&usr;
grant select on DBA_HIST_OPTIMIZER_ENV to &&usr;
grant select on DBA_HIST_EVENT_NAME to &&usr;
grant select on DBA_HIST_SYSTEM_EVENT to &&usr;
grant select on DBA_HIST_BG_EVENT_SUMMARY to &&usr;
grant select on DBA_HIST_WAITSTAT to &&usr;
grant select on DBA_HIST_ENQUEUE_STAT to &&usr;
grant select on DBA_HIST_LATCH_NAME to &&usr;
grant select on DBA_HIST_LATCH to &&usr;
grant select on DBA_HIST_LATCH_CHILDREN to &&usr;
grant select on DBA_HIST_LATCH_PARENT to &&usr;
grant select on DBA_HIST_LATCH_MISSES_SUMMARY to &&usr;
grant select on DBA_HIST_LIBRARYCACHE to &&usr;
grant select on DBA_HIST_DB_CACHE_ADVICE to &&usr;
grant select on DBA_HIST_BUFFER_POOL_STAT to &&usr;
grant select on DBA_HIST_ROWCACHE_SUMMARY to &&usr;
grant select on DBA_HIST_SGA to &&usr;
grant select on DBA_HIST_SGASTAT to &&usr;
grant select on DBA_HIST_PGASTAT to &&usr;
grant select on DBA_HIST_RESOURCE_LIMIT to &&usr;
grant select on DBA_HIST_SHARED_POOL_ADVICE to &&usr;
grant select on DBA_HIST_SQL_WORKAREA_HSTGRM to &&usr;
grant select on DBA_HIST_PGA_TARGET_ADVICE to &&usr;
grant select on DBA_HIST_INSTANCE_RECOVERY to &&usr;
grant select on DBA_HIST_JAVA_POOL_ADVICE to &&usr;
grant select on DBA_HIST_THREAD to &&usr;
grant select on DBA_HIST_STAT_NAME to &&usr;
grant select on DBA_HIST_SYSSTAT to &&usr;
grant select on DBA_HIST_SYS_TIME_MODEL to &&usr;
grant select on DBA_HIST_OSSTAT_NAME to &&usr;
grant select on DBA_HIST_OSSTAT to &&usr;
grant select on DBA_HIST_PARAMETER_NAME to &&usr;
grant select on DBA_HIST_PARAMETER to &&usr;
grant select on DBA_HIST_UNDOSTAT to &&usr;
grant select on DBA_HIST_SEG_STAT to &&usr;
grant select on DBA_HIST_SEG_STAT_OBJ to &&usr;
grant select on DBA_HIST_METRIC_NAME to &&usr;
grant select on DBA_HIST_SYSMETRIC_HISTORY to &&usr;
grant select on DBA_HIST_SYSMETRIC_SUMMARY to &&usr;
grant select on DBA_HIST_SESSMETRIC_HISTORY to &&usr;
grant select on DBA_HIST_FILEMETRIC_HISTORY to &&usr;
grant select on DBA_HIST_WAITCLASSMET_HISTORY to &&usr;
grant select on DBA_HIST_DLM_MISC to &&usr;
grant select on DBA_HIST_CR_BLOCK_SERVER to &&usr;
grant select on DBA_HIST_CURRENT_BLOCK_SERVER to &&usr;
grant select on DBA_HIST_CLASS_CACHE_TRANSFER to &&usr;
grant select on DBA_HIST_ACTIVE_SESS_HISTORY to &&usr;
grant select on DBA_HIST_TABLESPACE_STAT to &&usr;
grant select on DBA_HIST_LOG to &&usr;
grant select on DBA_HIST_MTTR_TARGET_ADVICE to &&usr;
grant select on DBA_HIST_TBSPC_SPACE_USAGE to &&usr;
grant select on DBA_HIST_SERVICE_NAME to &&usr;
grant select on DBA_HIST_SERVICE_STAT to &&usr;
grant select on DBA_HIST_SERVICE_WAIT_CLASS to &&usr;
I WANT TO USE STATSPACK VIEWER
PROFESSIONAL UNDER ANOTHER ACCOUNT WHICH IS DIFFERENT FROM STATSPACK
OWNER SCHEMA PERFSTAT BECAUSE OF SECURITY ISSUES. HOW TO ACCOMPLISH
became possible in Statspack Viewer Professional version 188.8.131.52
and later and Web Statspack Viewer 2.0.69 and later. In order
to use above packages under another user account different from
PERFSTAT schema, you must ensure the following things:
at least SELECT privilege for all STATSPACK tables and views
(note that some views belong to SYS schema).
SYNONYMS for all STATSPACK tables and views (note that some
views belong to SYS schema).
EXECUTE privilege for STATSPACK package.
I WANT TO ANALYZE MY STATSPACK REPORT
FILE IN WEB STATSPACK VIEWER. BUT WHEN I CLICK "ANALYZE" BUTTON,
APPLICATION SAYS THAT IT IS UNABLE TO PROCESS FILE. HOW TO FIX THIS
Viewer Pro and Web Statspack Viewer packages use spAnalyzer tool to
process STATSPACK reports. This error is usually encountered, when you
do not activate properly spAnalyzer utility. Enter your trial
activation or license codes to spAnlz.ini file or use
spAnalyzer dialogs to perform software activation.
I SCHEDULED SOME REPORTS TO BE PRINTED
AUTOMATICALLY USING STATSPACK VIEWER SERVICE. ALL REPORTS ARE PRINTED
BUT HAVE VERY SMALL SIZE ON PAPER. HOW TO INCREASE THE SIZE OF PRINTED
your scheduled reports are printed by SVS service with too small size.
The cause of this problem is that SVS service uses default chart
template file SpChartTemplate.ctf located in the installation
folder. The chart template file is determined by ChartTemplateFile
parameter in SpService.ini SVS configuration file. By default, this
parameter is commented out and SVS uses default SpChartTemplate.ctf
template file. This file is supplied with the software distribution
media as an example of using chart templates. This file has settings
for chart print margins which cause small chart size when printing. In
order to increase chart size, try to rename this file
SpChartTemplate.ctf to some other name and restart the SVS service. Or
you could generate your own chart template file with appropriate
appearance (including print margins) settings using
Statspack Viewer Professional. Then set ChartTemplateFile parameter to
the full path name of this new template file.
Note that chart settings in the chart template file set by
ChartTemplateFile parameter always overwrite chart appearance settings
specified in SpService.ini file.
I OPEN "INSTANCE EFFICIENCY PERCENTAGES
" REPORT, DOUBLE CLICK DBHR, GRAPH IS DRAWN, NOW CLICK "STATSPACK
TREND ANALYSIS" TAB, AND GRAPH DISAPPEARS FROM CHART ? WHAT IS WRONG?
is normal behavior. When you switch from simple STATSPACK to TREND
report mode, X axis could change because of different types of
reports. For example, trend chart could contain a single X point that
represents a whole snapshot interval between 100 and 200 snapshots. In
order to provide chart consistency all old graphs are removed.
I HAVE INSTALLED STATSPACK VIEWER
SERVICE (SVS) AND SUCCESSFULLY CONFIGURED IT. ALSO I HAVE SETUP SOME
REPORTS TO BE PRINTED AUTOMATICALLY BY SVS. HOWEVER SVS DOES NOT PRINT
ANY REPORTS TO PRINTER. HOW TO SOLVE THIS?
this happens when service for Statspack Viewer Service is created
under LocalSystem nt system account which has no any printers
installed. It is recommended to configure SVS service as follows:
Control Panel -> Services dialog.
SPViewer Service and open its properties dialog.
Log On tab.
This Account radio button in the Logon As property.
Browse button to select the account under which you have installed
Statspack Viewer Service.
valid password for this account.Click OK button.
for this account all the required printers.
Statspack Viewer Service.
HOW CAN I INSTALL ORACLE STATSPACK
STATSPACK scripts are located at the server-side oracle home in the
catalog $ORACLE_HOME/rdbms/admin. The install script is called
statscre.sql (8.1.6) or spcreate.sql (8.1.7 or later). In
order to install STATSPACK, you must run the installation script at
server side as SYS user. The STATSPACK owner schema PERFSTAT will
be created with all the required database objects.
STATSPACK VIEWER STANDARD RETURNS AN
ERROR "ORACLE STATSPACK UTILITY %VERSION% IS NOT INSTALLED IN THIS
SCHEMA!" WHAT SHOULD I DO?
must verify the following things:
running the correct version of Statspack Viewer Standard against
your PERFSTAT schema. For example, you may run Statspack Viewer
Standard 8.1.6 against your 8.1.7 STATSPACK. You should download and
install the correct version of viewer.
already installed the correct version of Oracle STATSPACK utility by
running installation scripts. For example, you may get this error,
if you try to connect to any another database schema not containing
THE SOFTWARE RETURNS AN ERROR "CANNOT
FIND OCI.DLL LIBRARY". HOW CAN I RESOLVE THIS PROBLEM?
Viewer utilities use the OCI.DLL library from oracle home that
is specified by the registry key
key's value to the oracle home name where you have Oracle client
software installed version 8.1.6 or later.Verify also that the folder
%ORACLE_HOME%\bin is set in the %PATH% environment
I HAVE SUCCESSFULLY INSTALLED STATSPACK
VIEWER PROFESSIONAL AND LOADED ALL THE STANDARD REPORTS. HOWEVER, SOME
REPORTS HAVE TRUNCATED SQL STATEMENTS TO 2000 CHARACTERS AND THUS
CAN'T BE RUN CORRECTLY. HOW TO SOLVE THIS PROBLEM?
problem is usually caused by old version of Oracle client installed.
The old versions support only datatype VARCHAR2(2000). But the viewer
uses datatype VARCHAR2(4000) to store its reports SQL statements.
Therefore, you have to install Oracle client software version 8.1.6 or
later and reload all the pre-defined reports.
I HAVE SETUP SOME STATISTIC FOR ALERT
MONITORING AND E-MAIL NOTIFICATION. BUT I DID NOT GET ANY ALERTS WHEN
THE STATISTIC VIOLATES ITS THRESHOLD. WHY IS IT?
order to monitor any statistics and generate alerts using Statspack
Viewer Pro software, you have to configure and run Statspack Viewer
Service which performs such the tasks. Check the on-line documentation
and its configuration file SPService.ini for details
located in the folder where Statspack Viewer Pro is installed. You
have also to create the Statspack Viewer Service in the system by
clicking the appropriate icon in the Statspack Viewer's program files
I HAVE SETUP STATSPACK VIEWER SERVICE TO
MONITOR MY DATABASE'S ALERT.LOG FILE. HOWEVER, WHEN I TRY TO START UP
THE SERVICE, IT RETURNS TO ITS LOG FILE "SPVIEWER.LOG" AN ERROR
MESSAGE: "FAILED TO LOAD ALERT.LOG FILE" FOR MY DATABASE. HOW CAN WE
SOLVE THIS PROBLEM? THE LOG FILE PATH IS CORRECT AND ALL THE
PARAMETERS OF THE STATSPACK VIEWER SERVICE ARE LOADED SUCCESSFULLY.
this problem can be resolved by granting "CREATE ANY DIRECTORY"
privilege to user PERFSTAT. Login as SYS user and issue the command:
GRANT CREATE ANY DIRECTORY TO PERFSTAT; After that restart the
statspack viewer service.
I WANT TO RETRIEVE THE COMPLETE SQL
STATEMENT TEXT IN THE "TOP SQLWITH HIGH BUFFER GETS" REPORT IN ORDER
TO GET EXPLAIN PLAN FOR IT BUT I CAN SEE ONLY THE FIRST ROW OF THE SQL
STATEMENT. WHAT SHOULD I DO?
order to retrieve the whole sql statement text you should adjust
parameter list of getSQLText function. For example, the default sql
statement for the "Top SQL with High Buffer Gets" report looks like
(this report is valid for STATSPACK 8.1.7 ):
"Gets / Execution"
"Percent of Total"
You need to
increase the second parameter of the packaged function
SP817.getSQLText . For example, SP817.getSQLText ( e.hash_value
, 20 ). The actual value of this parameter depends on the size of
the actual sql statement. The same change can be done for other
similar TOP SQL reports.
I WANT TO MONITOR ALERT.LOG FILES OF MY
REMOTE ORACLE DATABASES BASED ON UNIX AND NT OS USING STATSPACK VIEWER
SERVICE (SVS). DO I NEED TO CREATE FOR SVS NFS CONNECTIONS TO THE
REMOTE FILE SYSTEMS WHERE THESE ALERT LOGS ARE AVAILABLE?
Viewer Service monitors remote alert logs using only Oracle
technologies built-in into Oracle RDBMS. Therefore, you do not need to
have nfs connections to the remote file systems of machines where
Oracle servers are running from the machine hosting Statspack Viewer
Service. SVS uses the standard Net8 connection in order to monitor
remote (UNIX or NT based) alert.log files.
I WANT TO WRITE MY OWN REPORT WHICH IS
BASED ON STATSPACK STANDARD TABLES. I WANT TO MONITOR USER COMMITS
COUNT FROM THE TABLE STATS$SYSSTAT AND TO BUILD AUTOMATICALLY CHARTS
FOR THIS STATISTIC USING STATSPACK VIEWER SERVICE. HOW CAN I SET UP
recommend you to rewrite your report statement the following way:
report and set its type to "STATSPACK Based Report". This report will
return you a single row representing the sum of user commits during
the specified interval of snapshots. If you double-click on the
num_commits cell, you will see a detailed chart showing user commits
In order to
have SVS to automatically generate charts for this statistic, set up
its "Auto Chart" properties. Also, make sure that you have correctly
configured SVS by setting up the appropriate init parameters:
ChartToFileInterval or %TNSALIAS%_ChartToFileInterval - set up
interval for automatic chart building;
Chart Picture Folders] - section sets up database specific destination
folders for charts;
ChartFileDir - parameter sets up the default destination folder for
all the databases.
ShowSnapTime - parameter defines whether to Show Snapshot Timestamp?
(0 - false; 1 - true). Default is 0.
wish also to adjust another parameters which set up charts appearance.
THE OPTION "TOOLS -> COPY STATSPACK DATA
BETWEEN DATABASES" IS DISABLED. WHAT CAN BE WRONG?
option becomes available when you have two or more connections
established in Statspack Viewer Pro to different databases. This is
because this feature copies STATSPACK data on the fly between two
DIFFERENT databases: source and target. For example, you may establish
one database as a central repository of STATSPACK data and copy
information to this repository from all another databases. When you
have only one active connection in Statspack Viewer Pro, it does not
make sense to activate this option.
HOW CAN I DISPLAY SNAPSHOT TIME INSTEAD
OF ITS NUMBER ON X-AXIS OF CHARTS?
should check on "Snapshot Timestamp?" check box in the report
properties inspector and refresh chart. The report properties
inspector is located on "Report SQL/Properties" tab of report window.
CAN I QUICKLY BUILD TREND CHART REPORTS BY DAYS, WEEKS, MONTHS?
example, you can produce the historical cpu usage reports from the
report "Instance Activity Statistics" ("CPU used by this session"
statistic) using "Statspack Trend Analysis" tab. For example, in order
to produce an instance cpu usage graph for a month in day intervals,
perform the following steps:
1. Open report "Instance Activity Statistics".
2. Click "Statspack Trend Analysis" tab.
3. Check radio button "Build Trend Chart By Selected Snapshot Ranges".
4. Click "Quick Snapshot Ranges Select". In the "Quick Snapshot Ranges
Select" dialog choose start date, interval type as "Day Intervals",
and interval count to generate as 30. Click OK button. You will see
snapshot ranges generated in the "Snapshot Ranges" list.
5. Select in the data grid statistic cell "CPU used by this session -
6. Click "Add Chart" button.
WHEN I OPEN EITHER THE SESSION ACTIVITY
STATISTICS OR THE SESSION WAIT
EVENTS REPORTS, NO DATA IS SHOWN. THE REPORT DESCRIPTIONS STATE THAT
THESE ARE FOR PARTICULAR SESSIONS. HOW DO I EITHER SELECT OR INPUT A
SESSION FOR THE REPORT?
order to see statistics for particular session in "Session Activity
Statistics" and "Session Wait Events" reports, you should tell to
STATSPACK what particular session to gather statistics for. To do
this, you can open "Setup -> Setup STATSPACK" dialog box and specify
I_SESSION_ID parameter. Valid value is sid column from v$session.
Default is 0 (no session). After you specify the session id you are
interested in you can click OK button to save parameters for future
snapshots or press "Run STATSPACK.SNAP ..." procedure to immediately
take a snapshot for the specified session. After multiple snapshots
are taken with the particular session id, you could run the reports
"Session Activity Statistics" and "Session Wait Events" to see
statistics for this session. Also note that you must specify snapshots
ranges for those session reports only within the snapshots which are
taken for this session id. Otherwise, you could get invalid or empty