How do we gather optimizer statistics?

How do we gather optimizer statistics?

* By default, Oracle Database uses automatic optimizer statistics collection

* Database automatically runs DBMS_STATS to collect optimizer statistics for all schema objects for which statistics are missing or stale

* You can also update and manage optimizer statistics by manually executing DBMS_STATS

* Also, when optimizer statistics are missing, stale, or insufficient, the database automatically gathers dynamic statistics during a parse (depends on level of dynamic sampling)

Automatic Statistics Gathering

Automatic Statistics Gathering 

What is An Optimizer ?

What is An Optimizer ?

* Generates the optimal execution plan 

* Determines the most efficient method for a SQL statement to access requested data

* The optimizer choose the plan with the lowest cost among all considered candidate plans

* The optimizer uses available statistics to calculate cost

* Cost computation accounts for factors of query execution like I/O , CPU, Network, Memory

Reasons Of Changing Execution Plans ?

Reasons Of Changing Execution Plans ?

* Stale or missing statistics 

* Have gathered fresh statistics 

* Optimizer engine has changed after a database upgrade 

* Index has been added or dropped or Table structure has changed

* Database optimizer related  init.ora parameters have been changed 

* Parsed representation of the SQL statement is not in the Library Cache – statements are aged out

* Reparsing the same SQL statement now leads to generation of a new plan 

Difference Between Execution plan & Explain plan ?

Difference Between Execution plan & Explain plan ?

Execution Plan?

* The execution plan for a SQL statement is a set of instructions.

* Tell the database how to access the data and join it together.

Explain Plan?

* An explain plan predicts how Oracle will process your query

* An execution plan describes the steps it actually took

Script to Track The RMAN Backup Status

Script to Track The RMAN Backup Status  

set lines 1000
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
col "SESSION RECID" for a15
col "OUTPUT|MBYTES" for a10
col start_time for a20
col end_time for a20
col input_type for a10
col dow for a15
col "ELAPSED|SECONDS" for a10
col "TIME|TAKEN" for a10

select j.session_recid, j.session_stamp, to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time, (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type, decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',3, 'Tuesday', 4, 'Wednesday', 5, 'Thursday', 6, 'Friday',7, 'Saturday') dow, j.elapsed_seconds, j.time_taken_display, x.cf, x.df, x.i0, x.i1, x.l, ro.inst_id output_instance from V$RMAN_BACKUP_JOB_DETAILS j left outer join (select d.session_recid, d.session_stamp, sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF, sum(case when d.controlfile_included = 'NO' and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF, sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0, sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1, sum(case when d.backup_type = 'L' then d.pieces else 0 end) L from V$BACKUP_SET_DETAILS d join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count where s.input_file_scan_only = 'NO' group by d.session_recid, d.session_stamp) x on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id from GV$RMAN_OUTPUT o group by o.session_recid, o.session_stamp) ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS order by j.start_time;