« PREVIOUS « |
MAIN INDEX 19C HOL | » UPGRADE » |
In this part of the Hands-On Lab you’ll capture and preserve SQL statements and information as well as the AWR. We’ll use this collection later on following a performance stability method guideline.
Index
Collect Statements from AWR
In order to collect SQL Statements directly from AWR (Automatic Workload Repository) you’ll call a SQL script which:
- Creates a SQL Tuning Set (STS)
- Populates the STS with SQL information stored in AWR
The script is stored in /home/oracle/scripts
:
capture_awr.sql
In your open SQL*plus session connected to UPGR
run:
@/home/oracle/scripts/capture_awr.sql
The number of statements in SQL Tuning Set “STS_CaptureAWR
” will be displayed.
Collect Statements from Cursor Cache
You can also collect statements directly from the Cursor Cache. This is more resource intense but helpful in case of OLTP applications. Be careful when you poll the cursor cache too frequently.
This procedure:
- Creates a SQL Tuning Set (STS)
- Populates the STS with SQL statements/information from the cursor cache
- It will poll the cursor cache for 240 seconds every 10 seconds
The script is stored in /home/oracle/scripts
:
capture_cc.sql
You have used it already when you ran HammerDB before.
Hence, no need to run it again.
@/home/oracle/scripts/capture_cc.sql —don’t run it again!!!
The number of statements in SQL Tuning Set “STS_CaptureCursorCache
” will be displayed.
But now check, how many statements you’ve collected in each SQL Tuning Set:
select name, owner, statement_count from dba_sqlset;
Export AWR
Especially when you migrate databases, exporting and preserving the AWR is important. When you upgrade, the AWR will stay in the database. This exercise is only done for protection but not necessary for the flow of the lab.
Export the AWR with:
@?/rdbms/admin/awrextr.sql
Databases in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id DB Name Host ------------ ------------ ------------ * 72245725 UPGR localhost.lo caldomain The default database id is the local one: '72245725'. To use this database id, press to continue, otherwise enter an alternative. Enter value for dbid:
Hit
RETURN.
Using 72245725 for Database ID Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will 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:
Type:
2
Hit
RETURN.
Enter value for num_days: 2 Listing the last 2 days of Completed Snapshots DB Name Snap Id Snap Started ------------ --------- ------------------ UPGR 110 20 Feb 2020 22:12 111 20 Feb 2020 22:39 112 20 Feb 2020 22:40 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:
Type: 110
<= Your snapshot number may be different
Hit
RETURN.
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 110 Begin Snapshot Id specified: 110 Enter value for end_snap:
Type: 112
<= Your snapshot number may be different
Hit
RETURN.
End Snapshot Id specified: 112 Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- DATA_PUMP_DIR /u01/app/oracle/admin/UPGR/dpdump/ ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0.4/ccr/hosts/localhost.localdomain/state ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0.4/ccr/state PREUPGRADE_DIR /u01/app/oracle/cfgtoollogs/UPGR/preupgrade XMLDIR /u01/app/oracle/product/11.2.0.4/rdbms/xml Choose a Directory Name from the above list (case-sensitive). Enter value for directory_name:
Type: DATA_PUMP_DIR
Hit
RETURN
Enter value for directory_name: DATA_PUMP_DIR Using the dump directory: DATA_PUMP_DIR Specify the Name of the Extract Dump File ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The prefix for the default dump file name is awrdat_64_71. To use this name, press to continue, otherwise enter an alternative. Enter value for file_name:
Hit RETURN
Using the dump file prefix: awrdat_64_71 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file will be located | in the following directory/file: | /u01/app/oracle/product/UPGR/dpdump/ | awrdat_110_112.dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | *** AWR Extract Started ... | | This operation will take a few moments. The | progress of the AWR extract operation can be | monitored in the following directory/file: | /u01/app/oracle/product/UPGR/dpdump/ | awrdat_110_112.log | End of AWR Extract
This will take now a few minutes.
Exit from SQL*Plus:
exit
« PREVIOUS « |
MAIN INDEX 19C HOL | » UPGRADE » |