Tuning Oracle

Pour ceux qui mettent en place des solutions maisons à base de base de données Oracle, un casse tête est celui du tuning de ladite base. Une solution simple est d’interroger les tables système pour identifier les requêtes couteuses.

La requête suivante permet d’identifier le top 10 des requêtes couteuses en buffer_gets. Elle est bien entendu adaptable pour obtenir le top 10 des exécutions ou des optimizer_cost. À noter qu’elle filtre les requêtes issues d’un serveur apache et d’applications PHP. À adapter donc.

SELECT  To_Char(buffer_gets, '999G999G999G990')       AS buffer_gets,
        To_Char(executions, '999G999G999G990')        AS executions,
        To_Char(ratio, '999G999G999G990D0')           AS ratio,
        To_Char(optimizer_cost, '999G999G999G990')    AS optimizer_cost,
        To_Char(total_cost, '999G999G999G990')        AS total_cost,
        module                                        AS module,
        optimizer_mode                                AS optimizer_mode,
        action                                        AS action,
        sql_fulltext                                  AS sql_fulltext
FROM    (
           SELECT  buffer_gets                   AS buffer_gets,
                   executions                    AS executions,
                   Decode(executions,
                     0, 0,
                     buffer_gets/executions
                   )                             AS ratio,
                   module                        AS module,
                   optimizer_mode                AS optimizer_mode,
                   optimizer_cost                AS optimizer_cost,
                   optimizer_cost * executions   AS total_cost,
                   action                        AS action,
                   sql_fulltext                  AS sql_fulltext
           FROM    V$SQLAREA
           WHERE   executions > 0                AND
                   buffer_gets IS NOT NULL       AND
                   module in (
                     'httpd.exe',
                     'java.exe',
                     'JDBC Thin Client',
                     'php-cgi.exe',
                     'php.exe'
                   )                             AND
                   optimizer_cost is not null    AND
                   optimizer_cost > 0
           ORDER BY buffer_gets DESC
       )
WHERE ROWNUM <=10