Matlab 2007a Licence File Mac

Analysis of global REDO space consumption for each session and for each statement

space consumption redo (redo consumption) is inevitable, but you can minimize it in some cases and with certain operations can not be canceled completely. The redo is necessary to ensure that before an unexpected fall of the base, the modified data blocks, commit, and not yet persisted to disk (dirty blocks), can be recovered by raising the base again with an automated process called "rolling forward" .

If the database is in archivelog mode, each redo log is separate copy to not overwrite and thus permit, when required, for example, to make base backup with online, go to a previous state of the database, retrieve a base using the latest full backup and using the archives, etc.

redo If consumption is important, the I / O is going to be compromised and could affect the overall performance of the base. Remember that the redo is sequential write, other than writing to datafiles. Always accommodate the redo on raid 1 or similar and separated from datafiles. In addition, we also note that with each commit must be written to redo synchronously. This means serialized, and therefore must be as efficient as possible. For

measurable redo consumption, and therefore number of files generated, at their bases, I pass a series of methods and queries commonly use. Queries can obtain the following:

• consumption Redo the last minute
• consumption per session
• Redo Redo for Consultations Consumption (*)

(*) The consumption decision is not obtain directly, so I set up a procedure to go sqlid space saving. This form may not be very accurate at times. Must be used have certain requirements and considerations. It can be very useful to test the use of redo an application before deployment.

For redo consumption overall last time (since the last snapshot of AWR) (redo Consumption by DB)

Run the following query which gives the use of the database since the last snapshot, ie from the last exact time. Ie if you run at 16:30, will give the consumer from the entire base 16hs

 
 select round ((t1.value-t2.value) / 1024/1024, 2) "Consumo_Redo (MB) "
 
 from (select value from v $ sysstat WHERE name = 'redo size') t1, 
 (select value from dba_hist_sysstat 
 WHERE stat_name = 'redo size' 
 and snap_id = (select max (snap_id) from dba_hist_sysstat)) t2 
 

For redo consumption per session (redo Consumption by session)

Run the following query, which gives redo consumption per session. Consider the cumulative is since the meeting opened

 
 select se.INST_ID, 
 se.SID, 
 se.USERNAME, 
 se.OSUSER, 
 se.TERMINAL, 
 se.PROGRAM, 
 round (ss.value / 1024/1024, 2) "Redo Size (Mb)" 
 from gv $ session se, gv $ sesstat 
 ss, v $ statname 
 
 WHERE sn = ss.SID se.SID 
 and ss.STATISTIC # = sn. STATISTIC # 
 and sn.NAME = 'redo size' and 
 
 username is not null order by "Redo Size (Mb) "desc 
 

For consumption by a court redo (redo Consumption by sqlid / sentence)

Start by copying setup below (copy the contents to a file. Sql and run it from sqlplus on the user selected as the repository):

 
 
 
 Rem Rem setup_redo_usage.sql (Redo Usage by Sentence / sqlid) 
 
 Rem Rem Rem NAME 
 setup_redo_usage.sql 
 
 DESCRIPTION Rem Rem Rem 
 Sets programming a job to collect information 
 Rem space usage by sqlid redo. 
 
 Rem Rem NOTES - Rem 
 INSTALLATION REQUIREMENTS The user running the script must have sufficient quota on a Rem 
 auxiliary tablespace (eg TOOLS) Rem 
 to store the monitoring information generated every 5 
 Rem. 
 Rem is necessary to grant privileges Rem 
 SELECT on dynamic views: 
 Rem Rem 
 sys Connected to: 
 Rem Rem 
 grant select on gv_ $ sesstat to ; 
 Rem grant select on v_ $ statname to ; 
 Rem grant select on gv_ $ session to ; 
 Rem grant select on gv_ $ sqlarea to ; 
 
 Rem Rem Rem MODIFIED 
 (DD / MM / YY) Rem 
 
 Paul A. 
 Rem Rem Roveda 06/01/1911 - Created Rem 
 v 1.0 
 
 - Delete the table if there 
 TBL_REDO_USAGE 
 drop table tmp $ redo_usage 
 / 
 
 - Create the repository table TBL_REDO_USAGE 
 
 create table tmp $ redo_usage 
 (
 USERNAME VARCHAR2 (30), 
 Osus VARCHAR2 (30), 
 TERMINAL VARCHAR2 (30), 
 PROGRAM VARCHAR2 (48), 
 SQL_ID VARCHAR2 (13), 
 SQL_FULLTEXT CLOB, 
 REDO_SIZE NUMBER, DATE SNAP 
, 
 INST_ID NUMBER (1) 
) 
 
 PCTFREE 0 NOLOGGING 
 / 
 
 
 - Create the procedure that collects information  Alocación de 
 -- espacio de redo 
  
 create or replace procedure p_get_redo_usage 
 is 
 begin 
 insert /*+ append */ into tmp$redo_usage 
 select s.USERNAME, 
        s.OSUSER, 
        s.terminal, 
        s.PROGRAM, 
        s.SQL_ID, 
        sa.SQL_FULLTEXT, 
        round(ss.VALUE/1024/1024) redo_size, 
        sysdate, 
        s.inst_id 
 from gv$sesstat ss, 
      v$statname sn, 
      gv$session s, 
      gv$sqlarea sa 
 where s.sid = ss.sid 
   and s.inst_id = ss.inst_id 
   and sn.STATISTIC# = ss.STATISTIC# 
   and s.sql_id = sa.SQL_ID 
   and s.inst_id = sa.inst_id 
 and sn.name = 'redo size' 
 and s.username NOT IN ('SYS', 'SYSTEM'); 
 commit; 
 end; 
 / 
 
 - Delete the job if it already exists 
 begin 
 dbms_scheduler 
 . drop_job (job_name => 'J_SAVE_REDO_USAGE' 
 force => true); 
 end; 
 / 
 
 - Create a new job begin 
 
 
 dbms_scheduler.create_job (
 job_name => 'J_SAVE_REDO_USAGE' 
, job_type => 'PLSQL_BLOCK' 
, job_action => 'begin p_get_redo_usage; end;' 
, start_date => sysdate 
, end_date => sysdate +1 / 24 - Gathering for 1 hour 
, repeat_interval => 'FREQ = secondly; BYSECOND = 0,5,10,15,20,25,30,35,40,45,50,55' 
, enabled => TRUE 
, comments => 'Store Allocation Information redo space '); 
 end; 
 / 
     
 
 

After completion of the collection (in the script
defined monitoring interval of 1 hour) you can run the following query to see
the Results:

 
 
 select sql_id, sum (redo_size) "redo_size (Mb)" 
 from (select unique sql_id, 
 redo_size-lead (redo_size) over (partition by order by snap sql_id desc) from 
 redo_size $ tmp redo_usage) WHERE 
 
 redo_size is not null group by sql_id 
 order by "redo_size (Mb)" desc