 |
|
Monitoring Events
Oracle Tips by Burleson Consulting
|
As you know, Oracle is an event-driven system.
Sessions wait for calls; locks and latches spin; processes wake up and
go back to sleep—all based on events. The V$SESSION_ EVENT DPT tracks
all current events by session. The report in Source 11.40 will
generate a report on current events (Oracle-wise anyway). An example
of the event report is shown in Listing 11.34.
SOURCE 11.40 Script to generate an event
report.
rem
rem FUNCTION: Generate a report on session events by user
rem
rem NAME:events.sql
rem HISTORY: MRA 6/15/97 Created
rem MRA 5/22/99 Verified on 8i
rem
COLUMN sid HEADING Sid
COLUMN event HEADING Event FORMAT a40
COLUMN total_waits HEADING Total|Waits
COLUMN total_timeouts HEADING Total|Timeouts
COLUMN time_waited HEADING Time|Waited
COLUMN average_wait HEADING Average|Wait
COLUMN username HEADING User
BREAK ON username
START title132 "Session Events By User"
SPOOL rep_out\&db\events
SET LINES 132 PAGES 59 VERIFY OFF FEEDBACK OFF
SELECT
username, event,total_waits,total_timeouts,
time_waited,average_wait
FROM
sys.v_$session_event a,
sys.v_$session b
WHERE
a.sid= b.sid
ORDER BY 1;
SPOOL OFF
PAUSE Press Enter to continue
CLEAR COLUMNS
CLEAR BREAKS
SET LINES 80 PAGES 22 VERIFY ON FEEDBACK ON
TTITLE OFF
LISTING 11.34 Example of output from the
session events report.
Date:
10/14/01
Page: 1
Time: 05:37 PM Session Events By
User SYS
galinux1 database
Total Total
Time Average
User Event Waits Timeouts
Waited Wait
---------- ------------------------------ --------- ---------
--------- ---------
SYSTEM enqueue 149 149
44425 298
control file sequential read 214 0
14 0
log file sync 61 0
65 1
SQL*Net message to client 462 0
1 0
single-task message 2 0
5 2
SQL*Net break/reset to client 4 0
0 0
SQL*Net message from client 800 0
735778 920
SQL*Net message to client 19 0
0 0
db file sequential read 24987 0
10986 0
db file sequential read 16 0
10 1
pmon timer 167133 167130
49031711 293
rdbms ipc message 163764 163681
49050112 300
control file sequential read 20 0
2 0
control file parallel write 163663 0
75411 0
direct path read 14 0
0 0
db file parallel write 3300 3300
8 0
db file scattered read 596 0
750 1
db file sequential read 248 0
232 1
log file parallel write 3760 0
1168 0
log file single write 4 0
0 0
log file sequential read 4 0
2 1
library cache load lock 1 1
299 299
smon timer 1640 1634
47313312 28850
direct path write 12 0
0 0
direct path read 14 0
0 0
control file parallel write 8 0
4 0
control file sequential read 49110 0
1069 0
async disk IO 1 0
0 0
rdbms ipc message 166085 163858
49051891 295
Press Enter to continue
Workspaces in Oracle9i
Orcacle9i introduced the concept of a database
workspace. A workspace is an environment for a long-term transaction
that allows versioning of objects. A workspace can be shared among
multiple users. The concept of workspace management involves a series
of short transactions and multiple data versions to implement a
complete long-transaction event that maintains atomicity and
concurrency.
The Workspace Manager (WKS) is installed by
default in all seed and DBCA databases. (If you need it in a manually
created database you must install it according with the installation
guide in the Oracle9i Application Developers Guide--Workspace Manager,
Release 1 9.0.1, PART# A88806-01, Oracle Corporation, June 2001.)
Workspaces are monitored using the DBA_ view
cluster associated with workspaces shown in Figure 11.9. OEM also
provides a Workspace Manager interface accessible from the database
listing of the main GUI.
Figure 11.9 DBA_ view cluster for workspaces.
An example report to show workspace status is
shown in Source 11.41. An example of the workspace status script
output is shown in Listing 11.35.
SOURCE 11.41 Example workspace status script.
rem
workspace_status.sql
rem Function: Show status of workspaces in database
rem
rem History: MRA 10/14/2001 Initial Creation
rem
COLUMN WORKSPACE FORMAT a10 HEADING 'Workspace'
COLUMN owner FORMAT a10 HEADING 'Owner'
COLUMN freeze_status FORMAT a8 HEADING 'Freeze|Status'
COLUMN resolve_status FORMAT a8 HEADING 'Resolve|Status'
COLUMN parent_workspace FORMAT a10 HEADING 'Parent|Workspace'
COLUMN freeze_mode FORMAT a8 HEADING 'Freeze|Mode'
start title80 'Workspace Status'
spool rep_out\&db\workspace_status
select
workspace,
NVL(parent_workspace,'NONE') parent_workspace,
owner,
freeze_status,
NVL(freeze_mode,'NONE') freeze_mode,
resolve_status
from
DBA_workspaces
/
spool off
ttitle off
LISTING 11.35 Example output from workspace status report.
Date:
10/14/01 Page: 1
Time: 06:25 PM Workspace Status SYS
galinux1 database
Parent Freeze Freeze Resolve
Workspace Workspace Owner Status Mode Status
---------- ---------- ---------- -------- -------- --------
LIVE NONE SYS UNFROZEN NONE INACTIVE
Other items, such as workspace privileges and
savepoints, can also be monitored using the DBA_ series of views.
Monitoring Other Database Objects
Generally speaking, the “other” database
objects consist of stored objects such as packages, procedures, and
functions. About the only thing for which these stored objects can be
monitored (other than tuning stats, which we cover in the next
chapter) is status, that is, whether they are either valid or invalid.
Monitoring for Invalid Objects
The script in Source 11.42 generates a listing
of invalid database objects. If the object is valid, you don’t need to
monitor it! Sample output from the object-monitoring script is shown
in Listing 11.36.
SOURCE 11.42 Example of script to report
object status.
rem Name:
inv_obj.sql
rem Purpose: Show all invalid objects in database
rem Mike Ault 7/2/96
rem Mike Ault 10/14/01 verified for Oracle9i
rem
COLUMN object_name FORMAT A20 HEADING 'Object|Name'
COLUMN owner FORMAT a10 HEADING 'Object|Owner'
COLUMN last_time FORMAT a18 HEADING 'Last Change|Date'
COLUMN object_type FORMAT a15 Heading 'Object|Type'
SET LINES 80 FEEDBACK OFF PAGES 0 VERIFY OFF
START title80 'Invalid Database Objects'
SPOOL rep_out/&db/inv_obj
SELECT
owner,
object_name,
object_type,
TO_CHAR(last_ddl_time,'DD-MON-YY hh:mi:ss') Last_time
FROM
DBA_objects
WHERE
status='INVALID'
/
PAUSE Press Enter to continue
SET LINES 80 FEEDBACK ON PAGES 22 VERIFY ON
CLEAR COLUMNS
TTITLE OFF
LISTING 11.36 Example of output from invalid
objects script.
Date:
10/14/01 Page: 1
Time: 06:39 PM Invalid Database Objects SYS
galinux1 databa
Object
Object Object Last Change
Owner Name Type Date
---------- -------------------- --------------- ------------------
SYS RECREATE_DB PROCEDURE 23-AUG-01 01:40:59
WKSYS WK_CRW PACKAGE BODY 06-JUN-01 12:54:24
Press Enter
to continue
If your search reveals that you have invalid
objects, then a script similar to that in Source 11.43 can be executed
to recompile the objects. The utlrp.sql script, found in the $ORACLE_HOME/rdbms/admin
directory, can be used to recompile all invalid PL/SQL objects at any
time; there is also the dbms_utility.compile_schema procedure.
SOURCE 11.43 Example of dynamic SQL script to
recompile invalid objects.
rem Name:
com_proc.sql
rem Function: Create a compile list for invalid procedures
rem
rem MRA 5/1/96
rem
DEFINE cr='chr(10)'
SET HEADING OFF PAGES 0 ECHO OFF TERMOUT OFF FEEDBACK OFF VERIFY OFF
SPOOL recompile.sql
SELECT 'ALTER '||object_type||' '||object_name||' COMPILE;'||&&cr||
'SHOW ERROR'
FROM DBA_objects WHERE status='INVALID'
/
SPOOL OFF
SET HEADING ON TERMOUT ON FEEDBACK ON VERIFY ON
UNDEF cr
Monitoring Multiple Databases
Frequently today, DBAs are called upon to
manage multiple instances. In this era of corporate downsizing, what
was a manageable job a few years ago has rapidly descended into the
chaos of a Doom episode gone wrong, as more responsibility is being
placed on fewer and fewer people. Thus, it’s imperative for DBAs to
automate the management of multiple databases, especially if they are
physically remote.
To this end, a DBA has several options. If
your budget permits (the tools seem to get more expensive as the
hardware decreases in cost), purchase a good monitoring tool, such as
Patrol by BMC, or Eco-Tools, as well as a “point-and-shoot” monitor,
such as Q by Savant Corporation, Platinum Technologies’ offerings, or
any of the plethora of new tools that seem to spring up each year. If
the budget is not there for purchasing tools or, like me, you are a
bit of a masochist, you may want to develop your own tools based on
the scripts in this book.
To develop your own tools, you must first
decide what you want to monitor. Once you have, in a central
monitoring database, design and install a small set of database tables
to store the data, with an identifier and date stamp for each entry.
On each of the remote databases, establish a monitoring user that has
select privileges against all required tables. Next, establish a
database link from the remote databases to the central monitoring
instance. On the central monitoring instance, establish a set of
monitoring users with a set of synonyms that point to the remote
database tables. Finally, set up a group of monitoring jobs that
execute the monitoring scripts via the database links and synonyms
against the remote databases, and store the results in the results
tables. Once the tables are loaded, you can report against them.
Procedurized, the steps are:
1. Establish a user on the remote database
that has select privilege on the DBA and V$ views and tables you want
to monitor.
2. On the central or monitoring database,
establish a corresponding monitoring user that has a database link to
the remote database monitoring user.
3. Create synonyms that hide the required
database link syntax for a select from the monitoring remote user; for
example:
CREATE SYNONYM galinux_sessions FOR v$session@galinux;
4. Once the synonyms are in place in the
monitoring database, create a central repository user that can be used
to store statistics (this will depend on what you want to trend;
look at the DBA_RUNNING_STATS definition in the DBA_UTILITIES zip file
on the Wiley Web site).
5. Use procedures to collect and store
information in the central repository for each monitored instance.
6. Generate reports from of the central
repository.
The Oracle Enterprise Manager enables
remote monitoring for multiple instances.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
