Monthly Archives: mai 2008
Comment utiliser dbms_profiler
select p.unit_name, p.occured, p.tot_time, p.line# line, substr(s.text, 1,75) text from (select u.unit_name, d.TOTAL_OCCUR occured, (d.TOTAL_TIME/1000000000) tot_time, d.line# from plsql_profiler_units u, plsql_profiler_data d where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number and d.TOTAL_OCCUR >0 and u.runid= &runid) p, user_source s where p.unit_name = … Continue reading
Activer la trace 10053
SQL>ALTER SESSION SET EVENTS ’10053 trace name context forever, level 1′; SQL>explain plan for select * from SCOTT.EMP; SQL>Explained SQL>ALTER SESSION SET EVENTS ’10053 trace name context off’; SQL>select value from v$parameter where name = ‘user_dump_dest’; u01/app/oracle/diag/rdbms/orcl/orcl/trace Aller au répertoire … Continue reading
Principes du « Cost based optimiser »
L’optimiseur Oracle disposait jusque la de deux modes de fonctionnement : – Par règles (RBO ; Rule Based Optimiser) – Par couts (CBO : Cost Based Optimiser) Le mode par règle est pratiquement abandonné depuis la version 10 G, tous … Continue reading
Requetes diagnostics tuning
Remonter le mauvais SQL SELECT disk_reads, executions, disk_reads/executions, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads SELECT buffer_gets, executions, buffer_gets/executions, sql_text FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY buffer_gets Remonter les statistiques sur l’usage des colonnes select … Continue reading