STATS PACK

•Statspack collects more data, including high-resource SQL.
•Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. (Many of these ratios must be calculated manually when using BSTAT/ESTAT).
•Permanent tables owned by PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes it easier to compare historical data.
•Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.
•Data collection is easy to automate using either DBMS_JOB or an operating system utility.
•Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.
The installation steps for STATSPACK are relatively simple and straightforward. This chapter will walk you, step by step, through the installation and configuration of STATSPACK. We will be including a complete description of the following steps:
•Creating the perfstat tablespace
•Creating the perfstat user
•Creating the tables
•Installing the packages
•Collecting data
•Scheduling data collection snapshots with dbms_job
•Testing your installation
Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:
•spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:
•spcsr.sql This script creates a user called PERFSTAT with the required permissions.
•spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.
•spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.
•spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.
•spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:
•spdusr.sql This script drops the PERFSTAT user.
•spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.
•spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.
•sprepins.sql This is the actual SQL that produces the STATSPACK report.
•sppurge.sql This is a script to delete older unwanted snapshots.
•spuexp.par This is a export parameter file to export all of the STATSPACK data.
•sptrunc.sql This is a script to truncate all STATSPACK tables.
•spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.
•spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6

How to run the report
sql >variable snapno number; (eg :run the script at 6 clk )
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 1 if you are executing the
statspack procedure in the database for the first time..)

runit again as per time required time ( run it at 7 clk)
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 2 )

Now we have two snap numbers (these are called as begin and end snap numbers
which you will be entering when you are running the spreport.sql.)

sql >oracle_home/ora90/rdbms/admin/spreport.sql;

this will ask you to enter the begin and end snap numbers.(enter as 1 and 2
for the begin and end snap numbers..)

How does one determine the table and column name for a field displayed in a form

1. Use Help > Record History
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Record History
- The name of the base view used displays as Table Name:
- Go to the Sql*Plus
- Type desc , for example, desc mtl_system_items_fvl
- The columns of the table are displayed
- Review the columns and see if one matches the field that you are reviewing

2. Use Help > Diagnostics > Examine
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Diagnostics > Examine
- The block and field name that contain the data in the form are displayed as
well as the value in the field.
- Many times the block name is similar to the table name in the database
- Many times the field name is similar to the column name in the database
- Also you can go back to the view in #1 and see if the column can be found

3. Use the eTRM
- Find the table name from the record history in #1
- You may still require additional information about the columns
- Goto http://metalink.oracle.com/ > Top Tech Docs > Applications Electronic
Technical Reference Manuals
- Choose your version (ie., 11.5.9)
- Type the view or table name as the search criteria
- Press search
- The view or table appears
- Press the link and get more details and descriptions about the columns in the table

4. Research the view
- Sometimes the steps in #1 and #2 lead you to a view
- Get more details about the view as well as the underlying tables and columns
with the following SQL:
set long 100000
col text format a70
set pages 100
spool view.lst
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';
spool off

5. Use SYSTEM.LAST_QUERY
- Query the records in a given form
- Choose Help > Diagnostics > Examine > Block = SYSTEM, Field= LAST_QUERY
- The query run to get the data shown is displayed.
- You can look at the various columns and tables used and research further

6. Run a trace
- Open the form that you are interested in but do not run a query
- Choose Help > Diagnostics > Trace > Trace with binds
- The trace file name and location are listed. Note them down
- Query the records
- Immediately shut trace off with Help > Diagnostics > Trace > No Trace
- The trace file name and location are listed
- Go to the database machine and get the trace file
- Create a tkprof of the trace file with the command, tkprof sys=no
- The various queries run are displayed.
- You can look at the various columns and tables used and research further

7. Open the form in Oracle Developer
- Open the form that you are interested in the Oracle Applications
- Choose Help > About
- Note the form name
- You have to first configure Oracle Developer to open forms with their
associated libraries
- Once this is done Open Developer
- Open the form in question
- Review the block, pl/sql procedures and libraries associated to the form

Profile option for change of settings like color etc

Change the "Corporate Branding Image for Oracle Applications / FND_CORPORATE_BRANDING_IMAGE" profile option to achieve this.

Refer to the following notes for more details:

Note: 551795.1 - How to change the default branding on the homepage which shown as "E-Business Suite" ?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=551795.1

Note: 395445.1 - Oracle Application Framework Profile Options Release 12
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=395445.1

Note: 421636.1 - How to replace the default Oracle Logo with a Customized Logo?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=421636.1

Note: 459350.1 - R12 - Image Size Problem On Login Page
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=459350.1