The olap_dba_scripts.sql file contains several SQL scripts for use by DBAs in managing resources when the database is used for OLAP applications. Following are brief descriptions and sample output from the scripts. These examples were tested using Oracle Database 10g Release 10.2.0.1 and Release 10.2.0.3.
aw_objects_in_cache
Identifies the objects in the buffer cache that are related to analytic workspaces.
Displays the amount of disk space used by each analytic workspace.
SQL> @aw_size
Analytic Workspace On Disk MB Tablespace
----------------------------------- --------------- --------------------
GLOBAL.GLOBAL 67.25 GLOBAL
GLOBAL_AW.GLOBAL 60.31 GLOBAL
SYS.AWCREATE 2.00 SYSAUX
SYS.AWCREATE10G 0.75 SYSAUX
SYS.AWMD 2.00 SYSAUX
SYS.AWREPORT 0.81 SYSAUX
SYS.AWXML 8.00 SYSAUX
SYS.EXPRESS 2.00 SYSAUX
---------------
Total Disk: 143.13
aw_tablespaces
Provides extensive information about the tablespaces used by analytic workspaces. The report produced by this script contains the following columns:
Column
Description
Tablespace
Name of tablespace
TYPE
U: undo
T: temporary P: permanent
AUTO
Y if the tablespace auto extensible; otherwise N
SEGM
Tablespace segment space management:
A: auto
M: manual
PLUG
Y if the tablespace is plugged in; otherwise N
LOGG
Y if tablespace logging is set; otherwise N
LIVE
Y if the tablespace is online, or N if it is offline
Users
Number of users who own objects in the tablespace
Size(MB)
Total size of the tablespace
Used(MB)
Total space occupied by objects in the tablespace
AW(MB)
The space consumed by analytic workspaces in the tablespace
AW#
Number of analytic workspaces in the tablespace
AWPts
Number of partitions for analytic workspaces in the tablespace
SQL> @aw_tablespaces
T A S P L L
Y U E L O I
P T G U G V
Tablespace E O M G G E Users Size(MB) Used(MB) AW(MB) AW# AWPts
------------------ - - - - - - ----- ---------- ---------- ---------- ---- -----
GLOBAL P Y M N Y Y 2 175.0 146.3 127.6 1 32
SH_AW P Y A N Y Y 0 224.0 0.3 0.0 0 0
SYSAUX P Y A N Y Y 7 88.6 88.6 15.6 6 6
SYSTEM P Y M N Y Y 10 450.0 147.6 0.0 0 0
SYSTEM P N M N Y Y 10 230.0 -72.4 0.0 0 0
TBS_1 P Y M N Y Y 0 100.0 0.0 0.0 0 0
GLOBAL_TEMP T Y M N N Y 0 175.0 80.0 0.0 0 0
SH_AWTEMP T Y M N N Y 0 224.0 0.0 0.0 0 0
TEMP T Y M N N Y 0 40.0 2.0 0.0 0 0
OLAPUNDO U Y M N Y Y 1 29.0 4.7 0.0 0 0
TBS_9 U Y M N Y Y 1 10.0 1.3 0.0 0 0
UD1 U Y M N Y Y 1 10.0 2.4 0.0 0 0
---------- ---------- ---------- ---- -----
total 1,834.6 400.8 143.1 7 38
aw_users
Identifies the users of analytic workspaces.
SQL> @aw_users
User SID Serial# AW Name Mode
------------ ------ ------- ------------------------- -----
GLOBAL_AW 46 94 SYS.AWXML RO
GLOBAL_AW 46 94 SYS.EXPRESS RO
GLOBAL_AW 46 94 GLOBAL_AW.GLOBAL RW
aw_wait_events
Describes the wait events that occurred over the previous hour for users connected to analytic workspaces.
Bookmark