Database Reference
In-Depth Information
To show you what the original formatting of the example statement was, I have captured it
with SQL trace.
*** SERVICE NAME:(SYS$USERS) 2007-07-28 13:10:44.703
*** SESSION ID:(158.3407) 2007-07-28 13:10:44.703
…
*** ACTION NAME:(EMPLIST) 2007-07-28 13:10:57.406
*** MODULE NAME:(HR) 2007-07-28 13:10:57.406
…
PARSING IN CURSOR #8 len=416 dep=0 uid=0 oct=3 lid=0 tim=79821013130 hv=3786124882
ad='2d8f5f1c'
SELECT emp.last_name, emp.first_name, j.job_title, d.department_name, l.city,
l.state_province, l.postal_code, l.street_address, emp.email,
emp.phone_number, emp.hire_date, emp.salary, mgr.last_name
FROM hr.employees emp, hr.employees mgr, hr.departments d, hr.locations l, hr.jobs j
WHERE emp.manager_id=mgr.employee_id
AND emp.department_id=d.department_id
AND d.location_id=l.location_id
AND emp.job_id=j.job_id
END OF STMT
PARSE #8:c=46875,e=42575,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=79821013122
The developer nicely formatted the statement, placing each clause of the
SELECT
statement
on a separate line and indenting the long select list with tab stops. As we just saw, all of this is lost
in the Statspack report. Active Workload Repository (AWR) HTML formatted reports do contain
the complete text of SQL statements (see Figure 25-1). By default, AWR text reports contain up to
four lines of SQL statement text. AWR is bundled with the Database Diagnostics Pack, which
has a price tag of $3,000 per processor or $60 per named user (see
http://oraclestore.
oracle.com
). Some DBAs mistakenly assume that AWR may be used at no additional charge,
since it is installed and enabled (
STATISTICS_LEVEL=TYPICAL
) by default.
The Database Diagnostics Pack includes AWR, the
DBMS_WORKLOAD_REPOSITORY
package for
taking AWR snapshots and AWR administration, AWR reports (
awrrpt.sql
), the
DBA_HIST_*
and
DBA_ADVISOR_*
views, Active Session History (
V$ACTIVE_SESSION_HISTORY
), and ADDM (Automatic
Database Diagnostic Monitor). Of course, without proper licensing, these features may not be
accessed through Enterprise Manager Grid Control or Database Control either. Speaking of
AWR and Statspack, AWR snapshots cannot include session-specific data in the same way that
Statspack snapshots can (see
i_session_id
in Figure 25-1). Active Session History samples metrics of
all sessions, whereas a session-level Statspack snapshot saves accurate, non-sampled metrics
of a single session.
