Database Reference
In-Depth Information
This change does not harm the appearance of the Statspack report. The section with our
problem statement now becomes this:
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
-------- ---------- -------- ------ -------- ----------- ----------
10.34 11 0.94 12.3 31.65 162,064 1455318379
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.l
ast_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.loc
ation_id
AND emp.job_id=j.job_id
Undocumented Statspack Report Parameters
As we saw in the previous sections, an important undocumented Statspack report parameter
in Oracle9 i is num_rows_per_hash . This parameter is documented in Oracle10 g ; however, there
are still a number of old and new undocumented report parameters in Oracle10 g . I consider
none of them as important or useful as num_rows_per_hash . Perhaps top_n_events , which is
undocumented in both releases, is the most interesting. It controls how many lines are shown
in the “Top Timed Events” section near the beginning of the report. Often the lowest contribu-
tion to total elapsed time (Oracle9 i : “Total Ela Time”; Oracle10 g : “Total Call Time”) in this section
is less than 2-3 percent and thus marginally relevant. It's only worth increasing top_n_events if
the lowest contributor has consumed 5 percent or more of the total elapsed time. Of course,
any such contributor would also appear in the “Wait Events” section of the report, 8 but the
percentage of total elapsed time is only reported in the “Top Timed Events” section. Following
is an example “Top Timed Events” section from an Oracle10 g Statspack report:
8.
Except if the contribution of CPU time had been so low that it was not reported in the “Top Timed
Events” section, which is unlikely.
 
Search WWH ::




Custom Search