Database Stats - Part 1

Introduced in Oracle 9iR1, the GATHER_SYSTEM_STATS procedure gathers statistics relating to the performance of your systems I/O and CPU. Giving the optimizer this information makes its choice of execution plan more accurate, since it is able to weigh the relative costs of operations using both the CPU and I/O profiles of the system.

There are two possible types of system statistics:

Noworkload: All databases come bundled with a default set of noworkload statistics, but they can be replaced with more accurate information. When gathering noworkload stats, the database issues a series of random I/Os and tests the speed of the CPU. As you can imagine, this puts a load on your system during the gathering phase.

EXEC DBMS_STATS.gather_system_stats;

Workload: When initiated using the start/stop or interval parameters, the database uses counters to keep track of all system operations, giving it an accurate idea of the performance of the system. If workload statistics are present, they will be used in preference to noworkload statistics.

-- Manually start and stop to sample a representative time (several hours) of system activity.

EXEC DBMS_STATS.gather_system_stats('start');
EXEC DBMS_STATS.gather_system_stats('stop');

Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS or adjusting SREADTIM and MREADTIM using DBMS_STATS.SET_SYSTEM_STATS

The current system statistics can be displayed by querying the AUX_STATS$ table.

SELECT pname, pval1 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

OPTIMIZER_ENV:

SQL Area references 1 distinct CBO Environments for this one SQL. Distinct CBO Environments may produce different Plans.

GV$SQL references 1 distinct CBO Environments for this one SQL. Distinct CBO Environments may produce different Plans.

AWR references 1 distinct CBO Environments for this one SQL. Distinct CBO Environments may produce different Plans.

set lines 155
	col execs for 999,999,999
	col avg_etime for 999,999.999
	col avg_lio for 999,999,999.9
	col begin_interval_time for a30
	col node for 99999
	break on plan_hash_value on startup_time skip 1
	select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
	nvl(executions_delta,0) execs,(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
	from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
	where sql_id = nvl('&sql_id','')
	and ss.snap_id = S.snap_id
	and ss.instance_number = S.instance_number
	and executions_delta > 0
	order by 1, 2, 3;	
SQL>   2    3    4    5    6    7    8    9   10   11
Enter value for sql_id: 79x5cc5jp837c
old   6: where sql_id = nvl('&sql_id','79x5cc5jp837c')
new   6: where sql_id = nvl('79x5cc5jp837c','79x5cc5jp837c')

Flushing a Single SQL Statement out of the Object Library Cache, Oracle Support Document ID 457309.1 "How To Flush an Object out the Library Cache [SGA]"

select address, hash_value, executions, loads, version_count, invalidations, 
parse_calls from v$sqlarea
where sql_id = '&1';

exec dbms_shared_pool.purge ('00000000B91FA518,1666452716', 'C');

Otherwise for all buffer cache as follow sentence.

ALTER SYSTEM FLUSH BUFFER_CACHE;

ElPatoDBA