Nada foi alterado no banco e ficou lento

Uma grande reclamação dos clientes do Oracle, muito conhecida pelos DBAs, é que nada foi alterado e o tempo de resposta de um comando SQL piorou.

Bem, primeiramente, em um banco de dados, sempre algo é alterado, quase a todo momento. Mesmo que não haja uma nova versão da aplicação, um banco de dados por definição é utilizado a todo momento, como SELECTs, DMLs, DDLs, DCLs, tanto das aplicações quanto das estruturas internas do sistema gerenciador.

Em segundo lugar, os planos de execução, por definição, não são perfeitos, pois para torna-los perfeitos, o Oracle Database precisaria levantar todas as hipóteses possíveis para execução de determinado comando SQL, e isto poderia levar um tempo longo demais. É o mesmo conceito dos programas jogadores de xadrez: eles não levantam todas as possibilidades, ou passariam séculos computando o próximo movimento. A solução é levantar o máximo de possibilidades (baseadas nas estatísticas dos objetos) no menor tempo possível.

O Oracle Database tem se tornado cada vez melhor e mais esperto com os planos de execução gerados, mas este problema original persiste. E junto com isto, cada vez que alguma alteração em algum objeto utilizado por um comando SQL é feita, todo o plano de execução é reciclado e recalculado. Um mero GRANT a um objeto invalida todos os planos de execução que fazem referência a ele.

Para evitar a situação que uma alteração corriqueira do banco de dados, como o GRANT citado, ou mesmo a atualização das estatísticas do objeto alterem o plano de execução de uma forma que torne-o mais lento, o Oracle Database tem a funcionalidade de SQL Plan Baselines.

Esta funcionalidade faz com que um plano de execução, após ser gerado, nunca sofra uma regressão. Se uma alteração no banco de dados requerer uma recompilação deste plano, esta recompilação não será adotada se o custo estimado for maior do que o plano anterior.

Esta funcionalidade não é habilitada por padrão. Para habilita-la, basta alterar o parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES para TRUE.

Obviamente, para utilizar esta funcionalidade seus SQL precisam ser repetitivos, ou seja, utilizar BIND VARIABLES.

O SQL Plan Baselines possui outras opções, como criar uma Baseline baseada em um ou mais SQLs já existentes na SHARED POOL.

Documentação: Oracle Database 11gR2: Overview of SQL Plan Baselines

No exemplo abaixo, veja que um novo plano de execução foi feito após a adição do índice, mas não foi aceito, e o plano de execução utilizado continuou o mesmo.

[oracle@CentOS ~]$ sqlplus SCOTT/TIGER
 SQL*Plus: Release 11.2.0.2.0 Production on Mon May 23 10:52:31 2011
 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, Oracle Label Security, OLAP, Data Mining,
 Oracle Database Vault and Real Application Testing options
 SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;
 Table created.
 
 SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;
 COUNT(DISTINCT(OWNER))
 ----------------------
 15
 
 SQL> SELECT SQL_TEXT, SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES;
 SQL_TEXT                                    SQL_HANDLE         PLAN_NAME              ENA ACC FIX
 -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- --- ---
 delete from sdo_geor_ddl__table$                        SQL_02a86218930bbb20       SQL_PLAN_05a32329hrft07347ab53 YES YES NO
 SELECT COUNT(DISTINCT(OWNER)) FROM T                         SQL_0c20446867a16450      SQL_PLAN_0s824d1mu2t2hb2385278 YES YES NO
 SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELIN SQL_1fbaaec7dfd53f8e      SQL_PLAN_1zfpfszgxagwfddf8b2e2 YES YES NO
 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE SQL_2bdf77bedbcdea50      SQL_PLAN_2rrvrrvdwvukhed88afee YES YES NO
 SELECT topology   FROM  SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers SQL_6720b1ddecdbc6c1       SQL_PLAN_6f85jvrqdrjq1d583a8e3 YES YES NO
 select column_name from dba_tab_columns where owner=:1 and table_name=:2    SQL_88b892888182a6dc       SQL_PLAN_8jf4kj20s59qwd2ac7573 YES YES NO
 select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAM SQL_aea38aa7dc216335      SQL_PLAN_ax8wanzf22stp6397e3b7 YES YES NO
 delete from sdo_geor_ddl__table$ where id=2                     SQL_b999cdcccb07a248       SQL_PLAN_bm6fdtm5hg8k87347ab53 YES YES NO
 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P SQL_bff897b9dbcabe27      SQL_PLAN_bzy4rr7dwpgj7ed88afee YES YES NO
 9 rows selected.
 
 SQL> CREATE BITMAP INDEX T_INDEX_01 ON T(OWNER);
 Index created.
 
 SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;
 COUNT(DISTINCT(OWNER))
 ----------------------
 15
 
 SQL>  SELECT SQL_TEXT, SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES;
 SQL_TEXT                                    SQL_HANDLE         PLAN_NAME              ENA ACC FIX
 -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- --- ---
 delete from sdo_geor_ddl__table$                        SQL_02a86218930bbb20       SQL_PLAN_05a32329hrft07347ab53 YES YES NO
 SELECT COUNT(DISTINCT(OWNER)) FROM T                         SQL_0c20446867a16450      SQL_PLAN_0s824d1mu2t2h99a1fd19 YES NO  NO
 SELECT COUNT(DISTINCT(OWNER)) FROM T                         SQL_0c20446867a16450      SQL_PLAN_0s824d1mu2t2hb2385278 YES YES NO
 SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELIN SQL_1fbaaec7dfd53f8e      SQL_PLAN_1zfpfszgxagwfddf8b2e2 YES YES NO
 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('SQL*Plus') LIKE UPPE SQL_2bdf77bedbcdea50      SQL_PLAN_2rrvrrvdwvukhed88afee YES YES NO
 SELECT topology   FROM  SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers SQL_6720b1ddecdbc6c1       SQL_PLAN_6f85jvrqdrjq1d583a8e3 YES YES NO
 select column_name from dba_tab_columns where owner=:1 and table_name=:2    SQL_88b892888182a6dc       SQL_PLAN_8jf4kj20s59qwd2ac7573 YES YES NO
 select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAM SQL_aea38aa7dc216335      SQL_PLAN_ax8wanzf22stp6397e3b7 YES YES NO
 delete from sdo_geor_ddl__table$ where id=2                     SQL_b999cdcccb07a248       SQL_PLAN_bm6fdtm5hg8k87347ab53 YES YES NO
 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P SQL_bff897b9dbcabe27      SQL_PLAN_bzy4rr7dwpgj7ed88afee YES YES NO
 SELECT SQL_TEXT, SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PL SQL_d76b3eba2d10a549      SQL_PLAN_dfutyr8qj19a9ddf8b2e2 YES YES NO
 11 rows selected.