ReUse flag in PeopleSoft AE program – Bind variable issue and its impact on Performance

It has been long since I blogged as I got busy. Personally I became a Father and officially, I moved to PeopleSoft Performance and Benchmark(P&B) team. Now my time is dedicated to handling escalated performance issues from customer and internal PeopleSoft tuning projects.
Recently I got an escalation from dev team that a customer is having an performance issue in Fusion – PeopleSoft co-exist module and they were about to go alive in next couple of days. Asked customer to provide us AE trace (level 135), process scheduler configuration file and few other application architecture details. From the AE trace I found FT_COMP_CHNG taking lot of time(Close to 6 hours).

AE Program: FT_COMP_CHNG

                               C o m p i l e    E x e c u t e    F e t c h        Total          

SQL Statement                  Count   Time     Count   Time     Count   Time     Time   

------------------------------ ------- -------- ------- -------- ------- -------- --------

MAIN.Clear.S                         1      0.0       1    119.9       0      0.0    119.9

MAIN.Extract.S                       1      0.0       1     99.7       0      0.0     99.7

MAIN.Filter.D                        1      0.0       1     11.5   37596      0.5     12.0

xxxxxx.Step01.S                  37595     24.7   37595   5254.7       0      0.0   5279.4

xxxxxx.Step02.S                  37595      3.1   37595   5809.9       0      0.0   5813.0

xxxxxx.Step03.S                  37595      3.1   37595   5099.4       0      0.0   5102.5

xxxxxx.Step01.S                  37595     26.2   37595   5208.1       0      0.0   5234.4

xxxxxx.Step04.S                  37595      3.4   37595     24.7       0      0.0     28.1

xxxxxx.Step01.S                  37595      2.4   37595     27.4       0      0.0     29.9

                                                                                  --------

                                                                                   21718.8

The moment I saw compile count equal to execution count, I did a quick check on the SQL in the associated steps and found they are static with just. Recommended the dev team to set ReUse flag on the steps. Also from the architecture, I found DB server and process scheduler are on same box. So we have recommended customer to set UseLocalOracleDB to 1 instead of 0.

When the recommendation was send to the customer and customer has shared the latest trace as follows:

AE Program: FT_COMP_CHNG

                               C o m p i l e    E x e c u t e    F e t c h        Total          

SQL Statement                  Count   Time     Count   Time     Count   Time     Time   

------------------------------ ------- -------- ------- -------- ------- -------- --------

xxxxxx.Clear.S                         1      0.0       1    142.3       0      0.0    142.3

xxxxxx.Extract.S                       1      0.0       1     93.1       0      0.0     93.1

xxxxxx.Filter.D                        1      0.0       1      7.2   37440     10.8     18.0

xxxxxx.Step01.S                        1      0.0   37439      8.7       0      0.0      8.7

xxxxxx.Step02.S                        1      0.0   37439      5.6       0      0.0      5.6

xxxxxx.Step01.S                        1      0.0   37439     18.0       0      0.0     18.0

xxxxxx.Step03.S                        1      0.0   37439     61.2       0      0.0     61.2

xxxxxx.Step01.S                        1      0.0   37439     11.2       0      0.0     11.2

                                                                                    --------

                                                                                       358.1

Now the program got finished in less than 6 minutes (earlier it was more than 6 hours).

Technicalities behind the scene:

ReUse Flag – Quote from PeopleBook:

One of the key performance features of Application Engine is the ability to reuse SQL statements by dedicating a persistent cursor to that statement.

Unless you select the ReUse property for a SQL action, %BIND fields are substituted with literal values in the SQL statement. The database has to recompile the statement every time it is executed.

However, selecting ReUse converts any %BIND fields into real bind variables (:1, :2, and so on), enabling Application Engine to compile the statement once, dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance.

Impact of ReUse Flag on Oracle database:

 If we don’t the ReUse flag for the static SQLs executing in loop in the AE program, literal values are passed as such to the Oracle database and it will cause the database to hard parse each and every time. Following quote was taken from Expert Oracle database Architecture 2^nd edition written by Thomas Kyte.

If I were to write a book about how to build non-scalable Oracle applications, “Don’t Use Bind Variables” would be the first and last chapter. Not using bind variables is a major cause of performance issues and a major inhibitor of scalability—not to mention a security risk of huge proportions. The way the Oracle shared pool (a very important shared-memory data structure) operates is predicated on developers using bind variables in most cases. If you want to make Oracle run slowly, even grind to a total halt, just refuse to use them.

Links:

ReUse Flag

http://docs.oracle.com/cd/E26239_01/pt851h3/eng/psbooks/tape/book.htm?File=tape/htm/tape05.htm#H3009

UseLocalOracleDB

http://docs.oracle.com/cd/E26239_01/pt851h3/eng/psbooks/tsvt/book.htm?File=tsvt/htm/tsvt07.htm#H4016

As always, your comments are always welcome. Hope this will be good performance tuning hints for atleast few.

Thanks,

Thiru

Oracle Column Selectivity – An Performance Impact

Lot of times I hear developer saying what is the big difference it is going to make when we execute “select * from tab1 where c1=1” instead of “select c2 from tab1 where c1=1”. (Even though they need only c2).  I bet, performance impact will  be big.

Benefits of selecting only required columns are listed below (Below list may not exclusive):

1. Network round trip between application server and database server.

2. Application server’s record set size will consume more memory which adds overhead to JVM’s GC engine.

3. Database server IO (Quite obvious IO will be having big impact on OLTP systems).

First 2 points are simple, straight and it is easy to understand. Regarding 3rd point, see my example below which shows IO impact it has because of bad query(selecting all columns).

SQL> create table tab1 as select * from all_objects;

Table created.

SQL> desc tab1

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)

 OBJECT_NAME                               NOT NULL VARCHAR2(30)

 SUBOBJECT_NAME                                     VARCHAR2(30)

 OBJECT_ID                                 NOT NULL NUMBER

 DATA_OBJECT_ID                                     NUMBER

 OBJECT_TYPE                                        VARCHAR2(19)

 CREATED                                   NOT NULL DATE

 LAST_DDL_TIME                             NOT NULL DATE

 TIMESTAMP                                          VARCHAR2(19)

 STATUS                                             VARCHAR2(7)

 TEMPORARY                                          VARCHAR2(1)

 GENERATED                                          VARCHAR2(1)

 SECONDARY                                          VARCHAR2(1)

 NAMESPACE                                 NOT NULL NUMBER

 EDITION_NAME                                       VARCHAR2(30)

SQL> create index ind1 on tab1(object_id,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'TAB1');

PL/SQL procedure successfully completed.

SQL> select * from tab1;

53762 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2211052296

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 53762 |  4882K|   228   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| TAB1 | 53762 |  4882K|   228   (1)| 00:00:03 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       4352  consistent gets

          0  physical reads

          0  redo size

    6269625  bytes sent via SQL*Net to client

      39840  bytes received via SQL*Net from client

       3586  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      53762  rows processed

SQL> select object_id,object_name from tab1;

53762 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4126477464

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      | 53762 |  1522K|    87   (0)| 00:00:02 |

|   1 |  INDEX FAST FULL SCAN| IND1 | 53762 |  1522K|    87   (0)| 00:00:02 |

-----------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       3887  consistent gets

          0  physical reads

          0  redo size

    1845080  bytes sent via SQL*Net to client

      39840  bytes received via SQL*Net from client

       3586  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      53762  rows processed

SQL> select * from tab1 where object_id=2001

  2  ;

Execution Plan

----------------------------------------------------------

Plan hash value: 2086140937

------------------------------------------------------------------------------------

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time   |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |      |     1 |    93 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1 |     1 |    93 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND1 |     1 |       |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=2001)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1349  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> select object_name from tab1 where object_id=2001;

Execution Plan

----------------------------------------------------------

Plan hash value: 3743434205

-------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |      |     1 |    29 |     2   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IND1 |     1 |    29 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("OBJECT_ID"=2001)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        428  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> 

If we carefully look in our above example, query "select * from tab1" requires 4882K bytes whereas "select object_id,object_name from tab1" requires only 1522K bytes.

Likewise "select * from tab1 where object_id=2001" requires 93bytes whereas "select object_name from tab1 where object_id=2001" requires just 29 bytes.

All of this IO optimization is because of 

• "INDEX FAST FULL SCAN" ==>Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized
  
• "INDEX RANGE SCAN" ==> An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
  

During any index access, if index has all column required to satisfy an SQL, table will not be accessed at all(avoids extra IO/buffer gets).

Note: Bytes in above examples means "Estimate by the cost-based approach of the number of bytes accessed by the operation".

As a single user, we might not see much performance impact; but at high load this will have huge impact. Whoever developing code, please mark this valuable performance tuning hints.

As usual, your comments are always welcome.

-Thiru

Improving performance with Result Cache in Oracle 11g

Initially in previous versions(before 11g), Oracle Database started off with caching data block in memory, which inturn is used to construct results for user’s request. Even though data blocks are cached in memory, it should construct results from cache which takes from time, resource, locks (latches & enqueues) etc., Oracle has gone to next level of caching in 11g database. Effective from Oracle 11g, Oracle can cache result sets. Oracle can cache the results of both SQL & PL/SQL. In this article, I am going to discuss basics of SQL result cache and I am planning to write about PL/SQL result cache in future articles. Quite obvious, at the end of article, most people will feel that this is the most interesting performance tuning hints while working in Oracle 11g database.

Few basics about result cache

Oracle introduced the memory structure called Result Cache to store the results of SQLs & PL/SQL. The database serves the results for the executed SQL queries and PL/SQL functions from the result cache instead of re-executing the actual query, quite obvious this feature will increase the performance. Memory allocated(can be controlled using RESULT_CACHE_MAX_SIZE parameter) for the result cache is taken from the shared pool. By now one might come to the conclusion that result cache will improve the performance of the repeatly used PL/SQL & SQL. RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. If the value of this parameter is 0, then the result cache feature is disabled.

Here are some of the parameters that control the behaviour of the result cache.

SQL> col name format a30

SQL> col value format a25

SQL> select name,value from v$parameter where name like 'result_cache%';

NAME                           VALUE

------------------------------ -------------------------

result_cache_mode              MANUAL

result_cache_max_size          5373952

result_cache_max_result        5

result_cache_remote_expiration 0

Brief description about the above parameters:

RESULT_CACHE_MODE: RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan. If set to FORCE, all the queries are cached if they are qualifed and fit in cache. The default is MANUAL, which indicates that only queries with the hint will be cached. 

RESULT_CACHE_MAX_SIZE: Specified maximum size of the result cache. Remember, result cache is part of shared pool and maximum can be of 75% of shared pool size. Also query result.

RESULT_CACHE_MAX_RESULT: Specifies percentage of RESULT_CACHE_MAX_SIZE that any single query result set can occupy. (Just to prevent the big result set from throwing out all other small resultset).

RESULT_CACHE_REMOTE_EXPIRATION: Specifies the number of minutes that a cached result that accesses a remote object will remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value may produce stale answers (for example, if the remote table used by a result is modified at the remote database). I personally think it should be set to positive value if remote database is read only database.

From the below query result, it is evident that result cache is part of shared pool.

SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL         NAME                            BYTES

------------ -------------------------- ----------

shared pool  Result Cache: Memory Mgr          200

shared pool  Result Cache: Bloom Fltr         2048

shared pool  Result Cache: Cache Mgr           152

Like almost all memory structure in oracle instance, Result cache is also protected by latches. Latches, being serialization devices, are scalability inhibitors. Don’t just to come to conclusion that Latches are evil; afterall we expect database to maintain ACID property.

SQL> select * from v$latchname where name like 'Result Cache%';

    LATCH# NAME                                 HASH

---------- ------------------------------ ----------

       377 Result Cache: Latch            1545889529

       378 Result Cache: SO Latch          986859868

Oracle 11g provides couple of views to see the behavious/statistics of result cache. Click on the below view names to see the details in Oracle documentation.

V$RESULT_CACHE_STATISTICS 

V$RESULT_CACHE_MEMORY

V$RESULT_CACHE_OBJECTS

V$RESULT_CACHE_DEPENDENCY

APIs for the result cache management is available in PL/SQL package DBMS_RESULT_CACHE.

Couple of examples on how to use APIs on DBMS_RESULT_CACHE package.

SQL> set serveroutput on size 99999

SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")

R e s u l t   C a c h e   M e m o r y   R e p o r t

[Parameters]

Block Size          = 1K bytes

Maximum Cache Size  = 5248K bytes (5248 blocks)

Maximum Result Size = 262K bytes (262 blocks)

[Memory]

Total Memory = 2400 bytes [0.001% of the Shared Pool]

... Fixed Memory = 2400 bytes [0.001% of the Shared Pool]

....... Cache Mgr  = 152 bytes

....... Memory Mgr = 200 bytes

....... Bloom Fltr = 2K bytes

....... State Objs =  bytes

... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

DBMS_RESULT_CACHE package has lot more functions/procedures and if you are looking for more, login into 11g using SQL* Plus and execute “desc DBMS_RESULT_CACHE” and the documentation for the same can be found here.

Background Process

SQL> select description from V$BGPROCESS where name like 'RC%';

DESCRIPTION

----------------------------------------------------------------

Result Cache: Background

Watch my blog to see result set in action.

As usual, comments are always welcome.

-Thiru

Monitoring Oracle database using Perfmon

Performance monitoring is the essential part of performance tuning exercise. Oracle database monitoring is no exception to this rule. As such Oracle database provides various ways to monitor such as statspack, AWR, tracing etc., Some people might be quite interested in seeing numerical values such as hit ratios, DBWR statistics etc., so that they can plot graphs against load and see how the trend is. Oracle running on Windows based Operating System provides features which help to monitor the system via perfmon (click here to know more about perfmon).

By default Oracle database related counters are not exposed to perfmon. But there are couple of ways to make it appear on perfmon. 

1. By using custom installation

2. By using operfcfg utility

3. By using administrative Assist on Windows

4. By adding couple of values in registry

First three ways indirectly adds the values to registry (hides complexity from end user). Oracle recommends user to user operfcfg utility. I am going to explain how to use operfcfg to add Oracle database counters in perfmon.

operfcfg is an Oracle Database tool that you run from the command prompt. Use this syntax:

operfcfg [-U username] [-D TNS_Alias_for_database] [-P password]

username is the username registry parameter value that Oracle Counters for Windows Performance Monitor uses to log in to the database. You must have a DBA privilege on this database.

TNS_Alias_for_database is the net service name that Oracle Counters for Windows Performance Monitor uses to connect to the database. It affects the Hostname registry parameter. The net service name corresponds to the SID of the database that you want to monitor. The -D command can be specified without providing a database name value.

password is the password registry parameter value for username.

If we omit –D & -P, then utility will take the default database on that system and it will interactive asks for password to connect to that user.

Debugging: 

If you are not able to see Oracle database counter after running through operfcfg utility, then

1. If you are using TNS alias then try to ping the database using tnsping utility and check whether you are able to reach the database. Click here to know more about tnsping utility

2. Try to connect to the Oracle database using sqlplus with the username, password provided for operfcfg utility.

3. Make sure the username provided has DBA privilege.

Once you got the counters, then enjoy the performance monitoring & tuning excerise. As usual, you comments are always welcome regarding my performance tuning hints.

Cheers,

Thiru

High Performance Oracle Database Export using Data Pump

Oracle offers high performance database not only in DDL and DML. Not only this that, but also in data backup and recovery. Oracle 10g offers new export/import utilities called data dump. The biggest advance of using data dump is it can push the system’s hardware capabilities to roof to achieve performance.

Oracle Data Pump is the replacement for the original Export and Import utilities. Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed movement of data and metadata from one database to another.

The Data Pump Export and Import utilities have a similar look and feel to the original utilities, but they are much more efficient and give you greater control and management of your import and export jobs.

Unlike original export/import utilities, data pump runs as jobs primarily on the server using server processes and stores the data in database server itself avoiding network latency(Except db link).

Data pump utilities uses direct file manipulation on the directory objects which helps us to achieve the speed. Since directory object can be managed from Oracle, security can be controlled by DBA.

Sample Practical example for export using data pump (expdp)

1. Creating directory 

Data pump works on direct file manipulation on database object directory. So before invoking data pump utilities, we need to create directory object and grant required permission. See the sample below.

create directory hr_backup_dir as '/u02/dbbackup';

grant read, write on directory hr_backup_dir to scott;

2. decide on parallelism

If you the whole system resource is dedicated to database backup, then PARALLEL can be set to number of processor available in that system. If you want to assign only limited processor for backup, then set PARALLEL count accordingly. Based on the PARALLEL count, data dump will span that many number of child Oracle processes.

Parallelism also depends on the number of DUMPFILE specified in expdp. Data pump thread needs exclusive access to DUMPFILE. So if you specify PARALLEL to 8 and give only 1 DUMPFILE, then only 1 data pump process will do export and all other processes will wait for first process to finish and release the dump file. So plan for both PARALLEL and DUMPFILE parameter.

3. run expdp

Here we go

expdp hr/hrworks SCHEMAS=HR DIRECTORY= hr_backup_dir DUMPFILE=hr.thiru.10-feb-2009%U.dmp LOGFILE=hr.thiru.10-feb-2009.log PARALLEL=8 JOB_NAME=HRDataPumpByThiru

If you specify %U in DUMPFILE, data pump will automatically create required number of files (most probably number of files created will be equal to PARALLEL).

4. check log files

Once export got completed, we can check the details in hr.thiru.10-feb-2009.log file.

Interactive Mode

While data pump export operation is going, user can press ctrl + c; it will stop displaying message in screen, but it will not stop the export operation. Instead it display Export prompt which is interactive. From here user can query to see the status of export operation and also alter data pump parameters such as parallel and so on.

Database Monitoring

DBA or any other user have permission can view the status of the export operation from the database views, too. The main view to monitor the jobs is DBA_DATAPUMP_JOBS, which tells how many worker processes (column DEGREE) are working on the job. The other view that is important is DBA_DATAPUMP_SESSIONS, which when joined with the previous view and V$SESSION gives the SID of the session of the main foreground process.

select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

For more information refer Oracle documentation here

Post your valuable feedback.

Statspack

I have worked in databases including IBM's DB2 (infact I am at IBM certified DB2 DBA for version 8.1), Open source MYSQL, Oracle (From Version 8). As a performance tester, I used to wonder how Oracle along can provide such wide range of monitoring facility that no other vendor is able to match. From System wide monitoring using statspack/AWR to session level trace, Oracle's monitoring capability is amazing. This article I am going to cover couple of basic things about Oracle’s very own monitoring utility Statspack. Statspack is the build in tool and installation script comes along with the database itself. (No need to pay even an extra penny from your pocket). All you need is just to install the same and start using the same.

Quote from Oracle Database Documentation about statspack:

"The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters."

Installation:

To invoke the Statspack setup, all you need to do is call the spcreate.sql script which is available in ORACLE_HOME/rdbms/admin. PERFSTAT user own all PL/SQL code, database objects including tables, sequence, constrains etc., In windows, you can login into Oracle user (using SQL* Plus) which has enough privilege to install and run the following 

%ORACLE_HOME%\rdbms\admin\spcreate

In Linux/Unix OS, run the following

$ORACLE_HOME/rdbms/admin/spcreate

During installation it will ask for PERFSTAT schema’s password (usually people use perfstat as password), permanent tables and temporary tablespace. SPCREATE.SQL install script in turn automatically calls the following scripts.

SPCUSR.SQL: Creates the PERFSTAT user and grants privileges required to collect the performance data from V$ tables.

SPCTAB.SQL: Creates the tables which are going to stores performance data.

SPCPKG.SQL: Creates the package required for monitoring, data purging, reporting

Installation script dumps errors (if any) into SPCUSR.LIS, SPCTAB.LIS, and SPCPKG.LIS output files. 

How it works:

Snapshot of database’s performance was taken, stored in the PERFSTAT tables and will be assigned a unique SNAP_ID for the INSTANCE. Typically we can take snapshots for every pre-defined interval and between snapshots, we can generate performance report. If instance is restarted between snapshots, then reports will be meaning less. Snapshot can be taken at various levels depending up level of monitoring data required. Snapshot level can range from 1 to 10. Default level will be 5. More the level, more performance data (consumes bit high resource also).

Taking snapshot

Login as PERFSTAT user or user which can has execute privilege on statspack package and call statspack.snap function. Few examples.

exec statspack.snap

exec statspack.snap(I_SNAP_LEVEL=>7)

Statspack report

Statspack report will give instance-wide statistics between two snapshots. Between snapshots, we can generate reports (but if instance is restarted between snapshot, report will not be meaningful). Just call SPREPORT.SQL and provide begin SNAP_ID, end SNAP_ID and report file name. 

Oracle 10g and above has come up with something (feature which combines Statspack & ADDM) called Automatic Workload Repository(AWR). But statspack is still supported.