Créer sql profile à partir du sql id

create_sql_profile.txt

Télécharger et installer SQLT

http://www.br8dba.com/how-to-install-sqlt/

 

 

All About the SQLT Diagnostic Tool (Doc ID 215187.1)

Créer un sql profile à partir d'un plan d'exéuction

Scripts to deal with SQL Plan Baselines, SQL Profiles and SQL Patches

script à lancer sous SQLT : 

coe_xfr_sql_profile.sql

PLan d'exécution d'une requête Oracle

Méthode classique : 

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=>'');

Récupérer les infos de stats en base

Pour connaître les infos des stats dans la base Oracle.

Les plans d'exécution sont historisés

Désolé J-F tu avais raison :

 

select SQL_ID,COST,TIME from DBA_HIST_SQL_PLAN

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/