Understand and Analyse AWR Report

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. 
Compare the AWR report for the period with bad performance with that of a one taken when performance was good, also a lot depends on type of problem you have, your system, your application, time of day etc.


SNAPSHOTS:

Creating Snapshots From Grid Control(automatically generated every hour), go to:

DB instance-Server tab-Automatic Workload Repository-Run AWR report- By Snapshot(Begin & End)

Creating Snapshots Manually:

You can manually create snapshots with the CREATE_SNAPSHOT procedure if you want to capture statistics at times different than those of the automatically generated snapshots. For example:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

Begin with:
1) Top 5 Timed Foreground Events
2) Operating System Statistics -- how much busy and wait ...
3) SQL statistics -- like top SQL's
4) Instance Activity 
5) I/O stats.
6) look out at advisors (memory-sga,pga, Undo & so on)
7) Buffer waits
8) Instance Efficiency Percentages (Target 100%)

Also check for SQL Statistics:

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Physical Reads (UnOptimized)
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

Reference:
NOTE:1086120.1 - Quick Instructions For Obtaining The Automatic Workload Repository (AWR) Report
FAQ: How to Use AWR reports to Diagnose Database Performance Issues [ID 1359094.1]
Using AWR/Statspack reports to help solve some Portal Performance Problems scenarios [ID 565812.1]

vi cheat sheet

How To Give Grant Select On X$ Objects In Oracle 10g?

Metalink Note:453076.1

1) You cannot grant a select privilege on X$* tables to another user due to they are internally
protected.

- If you try grant select on x$ objects then you will receive the following error:

SQL> grant select on sys.x$bh to scott;
grant select on sys.x$bh to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


2) Alternatively, you can create a view and then grant a select privilege on that new view to
another user as follow:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"
SQL> create user scott identified by tiger default tablespace users temporary tablespace temp;

User created.

SQL> grant resource to scott;

Grant succeeded.

SQL> grant connect to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> show user
USER is "SCOTT"

SQL> select * from sys.x$bh;
select * from sys.x$bh
*
ERROR at line 1:
ORA-00942: table or view does not exist

==================================================================

SQL> show user
USER is "SYS"
SQL> grant select on sys.x$bh to scott;
grant select on sys.x$bh to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


SQL> create view vw_x$bh as select * from sys.x$bh; <(=== This works

View created.

SQL> grant select on sys.vw_x$bh to scott; <(=== This works

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> select * from sys.vw_x$bh; <(=== This works

External Tables Example From Oracle Documentation

test3>create directory ext_tab_dir as '/user1/oracle/test_vinay';

Directory created.

test3>grant read,write on directory ext_tab_dir to osvinay;

Grant succeeded.

test3>conn osvinay@test3
Enter password: *********
Connected.
test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2    ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                           ACCESS PARAMETERS (RECORDS FIXED 20 FIELDS (first_name CHAR(7),
  4                                                                      last_name CHAR(8),
  5                                                                      year_of_birth CHAR(4)))
  6                           LOCATION ('info.dat'));

Table created.

test3>
test3>select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963

test3>drop table emp_load;

Table dropped.

test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2    ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                           ACCESS PARAMETERS (RECORDS VARIABLE 2 FIELDS TERMINATED BY ','
  4                                               (first_name CHAR(7),
  5                                                last_name CHAR(8),
  6                                                year_of_birth CHAR(4)))
  7                           LOCATION ('info.dat'));

Table created.

test3>select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963

test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2   ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                          ACCESS PARAMETERS (RECORDS DELIMITED BY '|' FIELDS TERMINATED BY ','
  4                                               (first_name CHAR(7),
  5                                                last_name CHAR(8),
  6                                                year_of_birth CHAR(4)))
  7                          LOCATION ('info.dat'));

Table created.

test3>select * from emp_load;
select * from emp_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

I got this error when i followed the example from Oracle documentation. After several attempts i finally figured out that it only works if you change the values in info.dat file as follows:

bash-2.05$ more info.dat
Alvin,Tolliver,1976,|Kenneth,Baer,1963,|Mary,Dube,1973,

test3>/

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963
Mary            Dube                 1973