ORACLE-BASE Blog Aggregator

[I originally had the first section at the end of the blog post, but then realised I might as well get the bad news out of the way to save you wasting your time if you're not interested]

A small section of the course covers moving AWR data between instances
to run the AWR comparison report against different environments.

The Bad News

As I mentioned before, one of Oracle’s local Pre-Sales Technical guys
was on the course and he approached me at a break and said that he
thought that these scripts were only to be used by Oracle Support. When
I got home I re-ran the awrextr.sql script and, sure enough :-

sys@GP06PROD> @?/rdbms/admin/awrextr ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Disclaimer: This SQL/Plus script should only be called under the guidance of Oracle Support. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

So
an interesting demo, but only for home experimentation or if Oracle
Support are involved and I certainly wouldn’t expect to see the script
converted to use old exp/imp utilities rather than the Data Pump
equivalent, as I was asked during the course. In retrospect, I
imagine Oracle might request you run awrextr.sql and send the output
dump file to them so they can use awrload.sql to load it for further
analysis.

How to move AWR data to another repository

Connect to Source Instance as SYS

@?/rdbms/admin/awrextr

The script will prompt for

1. dbid
2. Number of days worth of snapshots to display for selection (just like awrrpt.sql)
3. Begin Snap
4. End Snap
5. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR)
6. Dump File Name

Connect to Target Instance as SYS

@?/rdbms/admin/awrload

The script will prompt for

1. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR)
2. Dump File Name
3. Schema Name for staging schema that data will be imported into (the default of AWR_STAGE is fine)
4. Default and Temporary tablespaces for the staging schema

@?/rdbms/admin/awrddrpi (Note the ‘i’ which allows you to specify which database/instances to report against)

The script will prompt for

1. HTML or Text report format
2. First dbid and instance number
3. Number of days snapshots to display for selection from first repository
4. Begin and end snapshot for first repository
5. Second dbid and instance number
6. Number of days snapshots to display for selection from second repository
7. Begin and end snapshot for second repository
8. Report file name

When I was working on the course, I noticed this White Paper (PDF) on Oracle 10g Self-Management Framework Internals: Exploring the Automatic Workload Repository. The paper describes the way that MMON selects 1 in 10 of the ASH samples for storage in DBA_HIST_ACTIVE_SESS_HISTORY (I suppose it would be more correct to say WRH$_ACTIVE_SESSION_HISTORY_BL) :-

"The in-memory data that is selected for writing is randomly chosen (sampling of the samples)."

This phrase confused me but, rather than digging around to resolve it (it was late), I took the paper at face value and repeated the phrase during the class the next day. Someone questioned it (as I said before, they were a smart bunch) and I found myself unable to justify it, particularly as it didn’t really make sense to me either. So that evening I sent a mail to a couple of people who would know and, as well as getting a quick answer, was able to sit back and enjoy a debate about whether the selection could be described as ‘random’ or not. The answer first :-

Not random, think MOD(sample_id, 10)

Initially I thought that meant that MMON selects every tenth sample from the ASH buffers. That would be the case if the sample_id was incremented every time there was data in a sample but it’s incremented even if there are no active sessions. For example, here is a selection of ASH samples from an active system. (Note that a ’sample’ includes all of the sessions that were Active at
the sample point, so the volume of data and number of sessions in each
sample will vary.)

SYS@TEST1020> SELECT sample_id, TO_CHAR(sample_time, 'HH24:MI:SS'), COUNT(*)  2  FROM v$active_session_history  3  WHERE sample_id > 1238065  4  GROUP BY sample_id, TO_CHAR(sample_time, 'HH24:MI:SS')  5  ORDER BY 1  6  /

 SAMPLE_ID TO_CHAR(   COUNT(*)---------- -------- ----------   1238073 20:57:17          1   1238074 20:57:18          2

Straightforward so far; there was one active session at 20:57:17 and two at 20:57:18. So let’s look at the next bunch of samples that appear in the output.

   1238162 20:58:47          1   1238242 21:00:08          1   1238245 21:00:11          1   1238266 21:00:32          1   1238278 21:00:45          1   1238286 21:00:53          1   1238358 21:02:06          1   1238371 21:02:19          2   1238483 21:04:12          1   1238510 21:04:40          1   1238527 21:04:57          1   1238554 21:05:24          1   1238560 21:05:31          1   1238563 21:05:34          1   1238575 21:05:46          1   1238667 21:07:19          1

Because there were no active sessions between 20:57:19 and 20:58:46, there’s no ASH data (as expected), but SAMPLE_ID is incremented every second. If we apply MOD(SAMPLE_ID) to determine which samples should be written to the workload repository, we’d end up with something like this.

   1238073 20:57:17          1   1238074 20:57:18          2   1238162 20:58:47          1   1238242 21:00:08          1   1238245 21:00:11          1   1238266 21:00:32          1   1238278 21:00:45          1   1238286 21:00:53          1   1238358 21:02:06          1   1238371 21:02:19          2   1238483 21:04:12          1   1238510 21:04:40          1 << IN AWR      1238527 21:04:57          1   1238554 21:05:24          1   1238560 21:05:31          1 << IN AWR      1238563 21:05:34          1   1238575 21:05:46          1   1238667 21:07:19          1

I can check this by forcing a manual AWR snapshot.

SYS@TEST1020> exec dbms_workload_repository.create_snapshot

PL/SQL procedure successfully completed.

SYS@TEST1020> SELECT ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),  2     DECODE(awr.sample_id, NULL, 'NO', 'YES') in_awr  3  FROM v$active_session_history ash, dba_hist_active_sess_history awr  4  WHERE ash.sample_id=awr.sample_id  (+)  5  AND ash.sample_id > 1238065  6  GROUP BY ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),  7     DECODE(awr.sample_id, NULL, 'NO', 'YES')  8  ORDER BY 2;

 SAMPLE_ID TO_CHAR( IN_---------- -------- ---   1238073 20:57:17 NO   1238074 20:57:18 NO   1238162 20:58:47 NO   1238242 21:00:08 NO   1238245 21:00:11 NO   1238266 21:00:32 NO   1238278 21:00:45 NO   1238286 21:00:53 NO   1238358 21:02:06 NO   1238371 21:02:19 NO   1238483 21:04:12 NO   1238510 21:04:40 YES   1238527 21:04:57 NO   1238554 21:05:24 NO   1238560 21:05:31 YES   1238563 21:05:34 NO   1238575 21:05:46 NO   1238667 21:07:19 NO

Bear in mind that this is on a very quiet instance running on my laptop so it’s possible to get large holes in the AWR data. The reality is that any reasonably busy system is likely to have ASH data for every second and so will have an AWR sample for every ten seconds.

As for the debate, I’m no mathematician, but although the sample time
gaps in DBA_HIST_ACTIVE_SESS_HISTORY might appear random in isolation,
it seems clear to me that they aren’t. I think the term ’sampling of
the samples’ is perfect, though.