Monitoring--Database AWR report

with Xshell Login:

[Email protected]/]$ su-oracle

[email protected] ~]$ Sqlplus system/[email protected]/ORCL

Sql*plus:release 11.2.0.4.0 Production on Sun Oct 9 14:16:59 2016

Copyright (c) 1982, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the partitioning, Real application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real application testing options

sql> exec dbms_workload_repository.create_snapshot (); (LoadRunner After you start running the stress test script, create the snapshots manually by using this phrase , an ID is produced at this time , roughly remembering the current time, which will be used later )

PL/SQL procedure successfully completed.

sql> exec dbms_workload_repository.create_snapshot (); (LoadRunner After you finish running the stress test script, create the snapshots manually by using this phrase , an ID is produced at this time , roughly remembering the current time, which will be used later )

(The value between these two IDs is taken later as HTML report generation.) )

PL/SQL procedure successfully completed.

Sql> @?/rdbms/admin/awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id db Name Inst Num Instance

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

787750339 Ltdb 1 LTDB1

Specify the report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would the HTML report, or a plain the text report?

Enter ' HTML ' for the HTML report, or ' text ' for plain text

Defaults to ' HTML '

Enter value for Report_type: (carriage return)

Type specified:html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num db Name Instance Host

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

* 787750339 1 Ltdb ltdb1 Node1

787750339 2 Ltdb ltdb2 Node2

Using 787750339 for Database Id

Using 1 For instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) would result in the most recent

(n) days of snapshots being listed. Pressing <return> without

Specifying a number lists all completed snapshots.

Enter value for num_days: (carriage return)

Listing all completed Snapshots

Instance DB Name snap Id snap Started level

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

LTDB1 ltdb 1412 Oct 2016 11:00 1

1429 OCT 2016 13:04 1

1430 OCT 2016 13:35 1

1431 OCT 2016 13:35 1

1432 OCT 2016 13:51 1

1433 OCT 2016 14:07 1

1434 OCT 2016 14:17 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for Begin_snap: (1433 selected from the list above. ) (basically take the last two IDs , as the above is also manually created two times snapshots)

Enter value for end_snap:1434 (1434 selected from the list above. )

End Snapshot Id specified:1434

Specify the report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report, file name, is awrrpt_1_1431_1434.html. To use the this name,

Press <return> to continue, otherwise enter an alternative.

Enter value for Report_name: (the name of the default report.) Path is /home/orace/ awrrpt_1_1431_1434.html) After the return of the production report.

/export/home/oracle

the point to be concerned with in the report is that SQL script Execution Time is the main one, where tuning needs to be developed.

The third column: Indicates the time taken to execute the SQL, and if the time is long it needs to be optimized.

Brief introduction:

-- If necessary , DBAs can manually create, delete, or modify snapshots through the dbms_workload_repository process.

-- prompt to invoke the Dbms_workload_repository package requires DBA authority.

--1. Manually Create Snapshots manually created snapshots through dbms_workload_repository. Create_snapshot Process

- For example

exec dbms_workload_repository.create_snapshot ();

-- You can then view the snapshots information you just created from the Dba_hist_snapshot view .

SELECT * from Dba_hist_snapshot;

--2 Delete snapshots Manually

-- deleting snapshots is another procedure that uses the Dbms_workload_repository package Drop_snapshot_range The process can be executed by specifying snap_id A range of ways to delete multiple Snapshot at once

- For example

Select COUNT (0) from Dba_hist_snapshot where snap_id between 6770 and 6774;

Select Max (snap_id) from Dba_hist_snapshot;

Select dbid from V$database;

exec dbms_workload_repository.drop_snapshot_range (low_snap_id = 6770,high_snap_id = 6774,dbid = 4059638244);

- or

Begin

Dbms_workload_repository.drop_snapshot_range (

low_snap_id = 6770,

high_snap_id = 6774,

dbid = 4059638244);

End

Select COUNT (0) from Dba_hist_snapshot where snap_id between 6770 and 6774;

Monitoring--Database AWR report