statistics_level='ALL' with 10046, level 8 : Cost/Benefit Analysis

From: Hemant K Chitale <>
Date: Wed, 24 Sep 2008 00:16:21 +0800
Message-Id: <>

I have a vendor that insists on running a batch job with statistics_level='ALL' and event 10046, level 8 ,both set at the session level in *production* only to diagnose performance issues. Furthermore, it would seem that the vendor will rely on AWR reports and tkprof's of the 10046 trace files sent to him post-facto (note : not the trace file, but the tkprof of it).

I have run a few tests with my own dummy tables and SQLs to show the impact of 'ALL' and level 8 to the job execution time.

I have proposed a Cost/Benefit analysis -- the "Cost" is the increase in job runtime (for a batch job that can take 8-12 hours normally) and the "Benefit" being the additional performance diagnostics that is done from the tkprof's and/or AWRs.

Do we have a documented / evidenced list of benefits in tkprof when statistics_level is set to 'ALL' ?
What additional information can I see in an AWR report (generated after the fact) ?

Hemant K Chitale

Received on Tue Sep 23 2008 - 11:16:21 CDT

Original text of this message