Oracle database performance

Lets explore Oracle database performance aspects on a high level..

some of the key terms to know about:

SGA: Shared Global Area - is basically a collection of memory units or structures used by all the processes on a db instance.
PGA: Program Global Area- is a memory region specific to a process (server process or bg process).
Buffer Cache: is basically a buffer to keep data blocks read from data files. The buffer cache is shared across the users.
Shared pool: basically contains the program data like parsed SQLs, PL/SQL code, data dictionaries etc., and this is accessed almost in every DB operation.

And lets see some of the interesting views
gv$process contains details on the currently active processes which are either on CPU or on latchwait or in spinning on a latch.. also contains PGA details for this process
gv$sgastat contains details on the system global area (SGA). For each pool - shared/large/java/stream pools
gv$session contains details for each current session. data from this view is sampled every sec and put into V$ACTIVE_SESSION_HISTORY. From 11G Rel 2 onwards, each individual req can be traced with the help of ECID.
gv$pgastat contains details on PGA usage

we can take a periodic snapshots on the above tables to analyze further..
example: CREATE SNAPSHOT snapshotonprocess AS SELECT * FROM gv$process
and then the processes pga can be calculated.. like select inst_id, count(*) cnt, Round(sum(PGA_USED_MEM) / 1024 / 1024 / 1024, 2) from snapshotonprocess group by inst_id order by inst_id
- adding a criteria like 'background is not null' will give pga stats for background processes

similarly, we can query sga stats, shared pools, shared pool usage, buffer cache usage and active sessions etc.,

Another good place to look at for session performance and to point out the slow SQLs or event waits is by querying V$ACTIVE_SESSION_HISTORY.
V$ACTIVE_SESSION_HISTORY contains sampled session activity in the database. samples are taken every sec. So, it is possible to calculate howmuch time spent on DB  and on which queries for a end user request by tracing the ECIDs in this view.

Lets look at awesome reports by Oracle database on performance of the database.

AWRs- A great performance report on DB workloads. It contains information about DB time, system resource usage, waits or other events that could impact performance, SQL statistics like long running SQLs or resource intensive sqls or
SQLs with high buffer gets.
we can check how the buffer cache and shared pool size changed from the beginning to the end of the snapshots.
logical reads(the more the better), physical reads(the less the better), hardparses(the more over a warmedup system indicates the plans are not good perhaps and might need to gather stats or something attached to the queries in runtime), rollbacks etc.,
latches(a short lived serialization methods that oracle uses on shared structures) efficiency metrics, top 5 foreground events, CPU and memory stats are good place to start with if there is a overall db performance hit.
If the performance is specific to SQLs then other areas to look at in AWRs are related SQL performance - # of execs, time taken per exec, cpu used, buffer gets, hard parses etc., And, up on identifying the SQLs (best way is to match the ECIDs for the frontend initiated SQLs),
SQLHC is next step to analyze further on the historical performance and the to analyze the SQL execution plan, indexes, bind variables/conditions etc.,

To have a better insight, keep a good baseline AWR in the system so that any future snapshot can be compared against it..

To create a awr snap:
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
select snap_id from dba_hist_snapshot order by snap_id asc;

To generate AWR: select output from table(dbms_workload_repository.awr_report_html(,,,));

To declare the basline:
BEGIN   DBMS_WORKLOAD_REPOSITORY.create_baseline (     start_snap_id => <###>,     end_snap_id   => <###>,   baseline_name => 'baseline'); END;
 /

To compare AWRs:
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql
or
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(,,,,,,,));

Other good reports to look at:
ADDM report - an Automatic Db Disgnostic Monitoring report.. helps identifying the issues in an Active Workload Repository