Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Expertise en SGBDR - Page 23

  • récupérer un historique de session

    spool hist_sess.html
    set markup html on
    alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI';
    select * from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time between '2020-03-09 18:00' and '2020-03-12 17:01';
    set markup html off
    spool off

  • AWR génération d'un rapport automatique pour chaque snap

    set serveroutput on size unlimited

    spool awr_8408_8478.txt

    declare

      dbid number := 644588240; -- a remplacer par ton dbid

      instance_id number := 1;

      l_premier number := 8408;

      l_dernier number := 8478;

      end_id number;

    begin

    for start_id in l_premier..l_dernier loop

      end_id := start_id + 1;

      for awrreport in ( select output from table(dbms_workload_repository.awr_report_html(dbid,instance_id,start_id,end_id)) )

      loop

         dbms_output.put_line(awrreport.output);

      end loop;

    end loop;

    end;

    /

    spool off

              

  • Tracer un sql id

    tracer un sql id :

    exec dbms_sqldiag.dump_trace(p_sql_id=>'tamere',p_child_number=>0,p_component=>'Compiler',p_file_id=>'');

  • Tuning Latch Contention: Cache-Buffers-Chain Latches

    Researching Further

    Re-querying v$session_wait, I see that a couple of latches are hot. I will consider one latch children with latch address 19875043200 as an example, and drill down further.

     

    Hang Those Buffers!

    Next, I needed to find buffers protected by these latch children, and then find the buffers causing latch contention. Many such hash buckets (and so, numerous buffers) are protected by a latch children. 

    I don’t favour the above script, since its performance is not optimal. It is much easier to dump the blocks and convert them to object ids. Let’s dump these three blocks.

    Now I can query dba_objects to find the object_names.

    https://blog.pythian.com/tuning-latch-contention-cache-buffers-chain-latches/