[Turn]oracle performance Tuning--oracle 10g AWR Configuration

The story of Ash and awr

1.1 About Ash

As we all know, when a user performs an operation in an Oracle database, it is necessary to create the appropriate connection and session, where all current session information is saved in the dynamic performance view v$session, where the DBA can see what the user is actually doing, or the current wait event. This part of the information is usually the key information in the tuning process, however, once the connection is broken. The session information is purged from the v$session and other related views at the same time, that is, the user finishes the operation, and you (the DBA), if you do not catch him at the moment, after this, do not know what it has done.

In the 10g version, Oracle added a new view (the other is a few, but we still take the session as a primer): V$active_session_history, see the name is known, is the history of the active session, this, even after the user operation is completed, Disconnected is not afraid, because the situation of its session has been recorded, this feature is Ash, the full name is the same as the view, it is: ACTIVE session history.

ASH collects information about the session that is currently in a non-idle wait event, and is stored in the V$active_session_history view, and we (should and must) know that The dynamic performance view is actually a bunch of virtual tables that Oracle itself constructs in the SGA memory area, that is, Ash's data is stored in memory, in fact, the space that Oracle allocates to Ash is not infinite (not to mention that Oracle itself manages the memory space is not infinite), View Ash's available memory space, which can be obtained by using the following SQL:

sql> SELECT Pool, name, bytes/1024/1024 Mb from V$sgastat WHERE name is like ' ASH% ';

To put it bluntly, how much session information can be recorded in v$active_session_history depends on the size of the SGA allocated to Ash buffers on the database, on the other hand, depending on the database startup and shutdown (the SGA memory area will be reconstructed when the database is restarted). These two factors constrain the ability to save session information in V$active_session_history, and as a DBA, we certainly want Ash to retain as much information about the session as possible, but for the moment it relies solely on

V$active_session_history certainly can't achieve this, then what? Don't worry, Oracle also provides the AWR feature, where Ash collects session information as part of the snapshot information in the AWR and is saved to the hard disk.

1.2 About AWR

AWR is a new feature of the Oracle 10g release, called Automatic Workload repository-automatic load repository. When it comes to this feature, I have to mention that Statspack,statspack is the old man in the Oracle world, but after all, the age is big, although the legs are still flexible, but the efficiency is not so high, with fashionable words is unable to adapt to the pace of the times, can not follow the trend of the Times, Can not maintain as a good member of the advanced nature, always adhere to the three table for the DBA service. However, in the final analysis statspack or for the sake of the party state career struggle for a lifetime, in the party or has the power has the authority has the influence of the four old people, and the residual heat can play, although its in the statistical real-time, performance is not enough to meet the needs of DBAs, still can not take it off, so ORACLE adopted a progressive approach to the first awr, said to be auxiliary statspack work, in fact, discerning eye a look at the clear, this is the new designated successor.

AWR and predecessor Statspack are the same in terms of career positioning, are responsible for collecting, processing and maintaining performance statistics, for checking and analyzing performance issues (even the resulting report format is very close), the AWR generated statistics can be viewed through the v$ view and dba_* data dictionary, You can also generate the corresponding report from a script. When it comes to the improvement of the STATSPACK,AWR, the main thing to say is two: Auto + real-time, as for the slight improvement of the function, but because the awr is young and agile.

Second, generate analysis report

AWR generates report data by comparing the statistics collected by two snapshots (snapshot), and the resulting report contains multiple parts, much like the report generated by Statspack. However, when generating reports, AWR can choose to generate reports in TXT or HTML two formats, which, by contrast, are better for reading, while TXT is more adaptable (even on machines that cannot use a browser).

Statspack friends have also remembered that the generated report using the $oracle_home/rdbms/admin/spreport.sql script, to the awr this piece, the operation steps are basically the same, but the script to generate the report many choices, including:

• Awrrpt.sql: Generate a statistical report of the specified snapshot interval;
• Awrrpti.sql: Generates the specified DB instance, and specifies a statistical report of the snapshot interval;
• Awrsqlrpt.sql: Generates a statistical report of the specified snapshot interval, specifying the SQL statement (which is actually specified as the sqlid of the statement);
• Awrsqrpi.sql: Generates a statistical report of the specified SQL statement for the specified DB instance, specifying the snapshot interval;
• Awrddrpt.sql: Specify two different time periods, generate a statistical comparison report of the two periods;
• Awrddrpi.sql: Specify a DB instance and specify two different time periods to generate a statistical comparison report for both periods;

In the following chapters, we will take one by one examples.

Tip: As with Statspack, you must set the value of the initialization parameter statistics_level to typical or all if you want the AWR to collect accurate statistics to generate reliable performance analysis reports.

2.1 Generating standard statistical reports

The process is not complex, and the following operations are bold, where you need to specify a value (note that the user performing the report generation must have a DBA role):

jssweb> @ $ORACLE _home/rdbms/admin/awrrpt.sql

Current Instance

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

DB Id db Name Inst Num Instance

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

3812548755 TEST08 1 test08

Specify the report Type

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

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

Enter¨html¨for an HTML report, Or¨text¨for Plain text

Defaults To¨html¨

Enter value for report_type:html

Here you need to specify the generated report format, with TXT and HTML two choices, by default, HTML format, here for demonstration purposes, enter HTML.

Type specified:html

Instances in this Workload Repository schema

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

DB Id Inst Num db Name Instance Host

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

* 3812548755 1 TEST08 test08 yans1

Using 3812548755 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 without

Specifying a number lists all completed snapshots.

Enter value for Num_days:2

Here you specify how many days you want to read the snapshot information!

Listing the last 2 days of completed snapshots

Snap

Instance DB Name snap Id snap Started level

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

test08 TEST08 7330 20 October 2009 00:00 1

7331 20 October 2009 01:00 1

7332 20 October 2009 02:00 1

7333 20 October 2009 03:00 1

7334 20 October 2009 04:00 1

7335 20 October 2009 05:01 1

7336 20 October 2009 06:00 1

7337 20 October 2009 07:01 1

7338 20 October 2009 08:00 1

7339 20 October 2009 09:00 1

7340 20 October 2009 10:00 1

7341 20 October 2009 11:00 1

7342 20 October 2009 12:00 1

7343 20 October 2009 13:00 1

7344 20 October 2009 14:00 1

7345 20 October 2009 15:00 1

7346 20 October 2009 16:00 1

7347 20 October 2009 17:00 1

7348 20 October 2009 18:00 1

7349 20 October 2009 19:00 1

7350 20 October 2009 20:00 1

7351 20 October 2009 21:00 1

7352 20 October 2009 22:00 1

7353 20 October 2009 23:00 1

7354 21 October 2009 00:00 1

7355 21 October 2009 01:00 1

7356 21 October 2009 02:00 1

7357 21 October 2009 03:00 1

7358 21 October 2009 04:00 1

7359 21 October 2009 05:00 1

7360 21 October 2009 06:00 1

7361 21 October 2009 07:00 1

7362 21 October 2009 08:00 1

7363 21 October 2009 09:00 1

7364 21 October 2009 10:00 1

7365 21 October 2009 11:00 1

7366 21 October 2009 12:00 1

7367 21 October 2009 13:00 1

7368 21 October 2009 14:00 1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap:7331

Begin Snapshot Id specified:7331

Enter value for end_snap:7355

End Snapshot Id specified:7355

Specify the report Name

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

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

Press to continue, otherwise enter an alternative.

Enter value for Report_name:

Here you specify a file name for the report to be generated, by default a file name is generated based on the previously entered snap_id, for example, the default file name generated here is awrrpt_1_7331_7355.html, and of course the DBA can customize the file name according to the actual situation. Here think of stealing a lazy, with the default file name good, directly enter.

Next you don't need the DBA to enter anything, wait for Oracle to generate a report for you.

Using the report name awrrpt_1_7331_7355.html

..................

.......................

End of report

Report written to awrrpt_1_7331_7355.html

Open the resulting statistical report with the following interface:

Tips:

Q: When I was prompted to enter snaps, where did you get so many snapshots?

A: This is all collected by Oracle at 1.1 (hourly).

Q: How long has it been collected?

Answer: From the current record, at least 7 days!

Q: What is snapshot?

Answer: Look down!

2.2 Generating a statistical report for the specified DB instance

This statistical report is generally for multi-instance databases, the script used earlier is to generate a database-level statistical report, for multi-instance database, sometimes the DBA may want to see the performance of an instance, then this script will be useful. In fact, the operation is very similar to the above (both relatively simple, requires the DBA to knock the word multibyte a few), pay attention to the execution of the script is different yo.

sql> @ $ORACLE _home/rdbms/admin/awrrpti.sql

Specify the report Type

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

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

Enter¨html¨for an HTML report, Or¨text¨for Plain text

Defaults To¨html¨

Enter value for Report_type:

Here you need to specify the generated report format, with TXT and HTML two choices, by default, in HTML format.

Type specified:html

Instances in this Workload Repository schema

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

DB Id Inst Num db Name Instance Host

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

948405229 2 Jssdb jssdbn2 jssdbn2

* 948405229 1 Jssdb jssdbn1 jssdbn1

Enter value for dbid:948405229

Using 948405229 for Database Id

Enter value for Inst_num:1

Using 1 For instance number

Compared to the standard statistical report generation, there are two more values to specify, that is, select the dbid to generate the report and the ID of the instance.

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 without

Specifying a number lists all completed snapshots.

Enter value for Num_days:2

Specifies the number of days of snapshot information to be read.

Listing the last 2 days of completed snapshots

Snap

Instance DB Name snap Id snap Started level

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

JSSDBN1 jssdb 235 Oct 2009 00:00 1

236 OCT 2009 01:00 1

237 OCT 2009 02:00 1

238 OCT 2009 03:00 1

239 OCT 2009 04:00 1

OCT 2009 05:00 1

241 OCT 2009 06:00 1

242 OCT 2009 07:00 1

243 OCT 2009 08:00 1

244 OCT 2009 09:00 1

245 OCT 2009 10:00 1

246 OCT 2009 11:00 1

247 OCT 2009 12:00 1

248 OCT 2009 13:00 1

249 OCT 2009 14:00 1

OCT 2009 15:00 1

251 OCT 2009 16:00 1

252 OCT 2009 17:00 1

253 OCT 2009 18:00 1

254 OCT 2009 19:00 1

255 OCT 2009 20:00 1

OCT 2009 21:00 1

257 OCT 2009 22:00 1

258 OCT 2009 23:00 1

259 OCT 2009 00:00 1

260 OCT 2009 01:00 1

261 OCT 2009 02:00 1

262 OCT 2009 03:00 1

263 OCT 2009 04:00 1

OCT 2009 05:00 1

265 OCT 2009 06:00 1

266 OCT 2009 07:00 1

267 OCT 2009 08:00 1

Specify the Begin and End Snapshot Ids

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

Enter value for Begin_snap:

.....................

......................

The rest of the steps are exactly the same as the steps in the standard statistics report, where the demo is not repeated.

2.3 Generating a statistical report for the specified SQL statement

This statistic is specifically used to analyze a specified SQL statement, which, through the Awrsqrpt.sql script, can generate information such as the execution plan of the specified SQL (once executed SQL), the resources consumed, and so on, to help DBAs with SQL tuning.

Here's how to do this first, or execute the build script:

sql> @ $ORACLE _home/rdbms/admin/awrsqrpt.sql

Current Instance

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

DB Id db Name Inst Num Instance

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

3812548755 TEST08 1 test08

Specify the report Type

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

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

Enter¨html¨for an HTML report, Or¨text¨for Plain text

Defaults To¨html¨

Enter value for report_type:html

Choose the generated report format, nothing to say, the default HTML format bar.

Type specified:html

Instances in this Workload Repository schema

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

DB Id Inst Num db Name Instance Host

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

* 3812548755 1 TEST08 test08 yans1

Using 3812548755 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 without

Specifying a number lists all completed snapshots.

Enter value for Num_days:2

Specify the generation interval for the report snapshot!

Listing the last 2 days of completed snapshots

Snap

Instance DB Name snap Id snap Started level

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

test08 TEST08 7450 25 October 2009 00:00 1

7451 25 October 2009 01:00 1

7452 25 October 2009 02:00 1

7453 25 October 2009 03:00 1

7454 25 October 2009 04:00 1

7455 25 October 2009 05:00 1

7456 25 October 2009 06:00 1

7457 25 October 2009 07:00 1

7458 25 October 2009 08:00 1

7459 25 October 2009 09:00 1

7460 25 October 2009 10:00 1

7461 25 October 2009 11:00 1

7462 25 October 2009 12:00 1

7463 25 October 2009 13:00 1

7464 25 October 2009 14:00 1

7465 25 October 2009 15:00 1

7466 25 October 2009 16:00 1

7467 25 October 2009 17:00 1

7468 25 October 2009 18:00 1

7469 25 October 2009 19:00 1

7470 25 October 2009 20:00 1

7471 25 October 2009 21:00 1

7472 25 October 2009 22:00 1

7473 25 October 2009 23:00 1

7474 26 October 2009 00:00 1

7475 26 October 2009 01:00 1

7476 26 October 2009 02:00 1

7477 26 October 2009 03:00 1

7478 26 October 2009 04:00 1

7479 26 October 2009 05:00 1

7480 26 October 2009 06:00 1

7481 26 October 2009 07:00 1

7482 26 October 2009 08:00 1

7483 26 October 2009 09:00 1

7484 26 October 2009 10:00 1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap:7451

Begin Snapshot Id specified:7451

Enter value for end_snap:7475

End Snapshot Id specified:7475

Specify the start and end snapshot IDs:

Specify the SQL Id

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

Enter value for SQL_ID:A51Q9UN8J1KV6

Note that here to specify the sql_id of the analysis, you may want to ask, where does the SQL ID to parse come from? In general, ask this question to indicate that you have not worked on AWR or seen a SQL. Why do you say that, because in general, the way to get the problem SQL is either through V$sql (and other related views), or through tools such as Awr/statspack, and these ways to find the SQL statement, as long as you take a little bit of eye light so sweep, You will find a SQL ID next to the statement that stands silently:)

SQL ID Specified:a51q9un8j1kv6

Specify the report Name

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

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

Press to continue, otherwise enter an alternative.

Enter value for report_name:awr_sqlrpt_1_7451_7475.html

Using the report name awr_sqlrpt_1_7451_7475.html

[Turn]oracle performance Tuning--oracle 10g AWR Configuration