« Oracle technet en Spanish!!!
Tablespace Size en SQL Developer »

DBA_FREE_SPACE

Esta semana me encontré con un problema muy raro, cuando hacia un query sobre la vista dba_free_space éste se tardaba mucho para regresar la respuesta cuando en otras bases de datos el resultados lo regresaba en cuestión de milisegundos.

Después de obtener el explain plan me di cuenta que a partir de la versión 10g esta vista incluye también el recycle_bin. A continuación voy a explicar el problema y como resolverlo (después de calcular estadísticas de sistema, modificar parámetros del init,etc).

Recycle Bin

Básicamente el recycle bin es similar al de windows, es un lugar dentro de la base de datos que sirve para guardar todos los objectos borrados. El recycle bin esta habilitado por default; así que si nosotros hacemos algo como:

drop table emp;

la tabla es “borrada” y nosotros podemos volver a crear otra tabla emp (igualita) y trabajar con ella,etc. Pero en realidad la tabla no fue borrada, lo que sucedió fue que oracle nada mas renombro la tabla con el prefijo BIN$$ y una secuencia de caracteres, la tabla (la estructura, indices, constraints, registros) siguen dentro del mismo tablespace ocupando espacio y no son borrados hasta que se utiliza el comando PURGE.

La razón de esta funcionalidad es variada pero principalmente es para poder recuperar información borrada de manera accidental.

Mas información sobre el recycle bin: What is the recycle bin?

Y que tiene que ver el recycle bin con dba_free_space?

Pues a partir de la versión 10g la vista accesa tambien la tabla sys.recyclebin$ para saber cuanto espacio queda libre en un tablespace, como habia mecionado anteriormente los objetos borrados no son eliminados completamente y en esta tabla se guarda la información(nombre, tamaño,etc) respecto a estos objetos.
Tiene sentido que la vista ahora incluya el recycle_bin (RBin) ya que hay que tomar en cuenta ahora cuanto espacio ocupan los objetos “borrados”.

Bueno, ahora mejor muestro un ejemplo sobre lo que puede suceder y como resolverlo.

Primero hay que ejecutar un query que contenga la vista:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> set lines 130
SQL> set pages 10000
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.29
SQL> l
  1  select tablespace_name TS2,
  2         sum(bytes) SUM_B2,
  3         max(bytes) MAX_B2,
  4         count(bytes) CNT_B2
  5    from dba_free_space
  6*  group by tablespace_name

SQL> /

TS2                                SUM_B2     MAX_B2     CNT_B2
------------------------------ ---------- ---------- ----------
PSAPPW1                        3.7242E+10 2990538752      21213
PSAPPW1640                     1.6777E+10 4160749568          8
PSAPPW1700                     3194159104 1581252608          7
PSAPPW1DB                       671744000  670957568          2
PSAPPW1DIM                     6167658496 3471835136        305
PSAPPW1FACT                    2222653440  900661248         64
PSAPPW1ODS                     6.9220E+11 3220176896      17456
PSAPPW1USR                     1730150400  455475200       3486
PSAPUNDO                       5.5332E+10 1736441856       1242
PSAPZFCT0                      2.6376E+10 1534066688      10457
PSAPZFCT1                      9236316160  443547648        523
PSAPZODS0                      7.2968E+10 2434793472        452
PSAPZODS1                      2.5842E+10 3226468352       1011
SYSAUX                          892534784  834600960         96
SYSTEM                         2040774656 2040479744          7

15 rows selected.

Elapsed: 00:02:50.81

Execution Plan
----------------------------------------------------------
Plan hash value: 2686115433

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   649 | 19470 |       |   126K|
|   1 |  SORT GROUP BY               |                  |   649 | 19470 |       |   126K|
|   2 |   VIEW                       | DBA_FREE_SPACE   |   649 | 19470 |       |   126K|
|   3 |    UNION-ALL                 |                  |       |       |       |       |
|   4 |     NESTED LOOPS             |                  |     7 |   259 |       |     5 |
|   5 |      NESTED LOOPS            |                  |     7 |   210 |       |     5 |
|*  6 |       TABLE ACCESS FULL      | TS$              |     2 |    40 |       |     3 |
|*  7 |       TABLE ACCESS CLUSTER   | FET$             |     4 |    40 |       |     1 |
|*  8 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|   9 |     NESTED LOOPS             |                  |    74 |  5328 |       |    90 |
|  10 |      NESTED LOOPS            |                  |    74 |  4810 |       |    90 |
|* 11 |       TABLE ACCESS FULL      | TS$              |    14 |   364 |       |     3 |
|* 12 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |     5 |   195 |       |     7 |
|* 13 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  14 |     NESTED LOOPS             |                  |     1 |    97 |       |   125K|
|  15 |      NESTED LOOPS            |                  |     1 |    90 |       |   125K|
|* 16 |       HASH JOIN              |                  | 20579 |   763K|   488K|    42 |
|  17 |        TABLE ACCESS FULL     | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 18 |        TABLE ACCESS FULL     | TS$              |    14 |   364 |       |     3 |
|* 19 |       FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |     1 |    52 |       |     7 |
|* 20 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  21 |     NESTED LOOPS             |                  |   567 | 29484 |       |   462 |
|* 22 |      HASH JOIN               |                  |   567 | 25515 |       |   462 |
|* 23 |       HASH JOIN              |                  |  7475 |   240K|       |   430 |
|* 24 |        TABLE ACCESS FULL     | TS$              |     2 |    40 |       |     3 |
|  25 |        TABLE ACCESS FULL     | UET$             |  7475 | 97175 |       |   426 |
|  26 |       TABLE ACCESS FULL      | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 27 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("TS"."BITMAPPED"=0)
   7 - filter("TS"."TS#"="F"."TS#")
   8 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  11 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  12 - filter("TS"."TS#"="F"."KTFBFETSN")
  13 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  16 - access("TS"."TS#"="RB"."TS#")
  18 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  19 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#"
              AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  20 - access("RB"."TS#"="FI"."TS#" AND "RB"."FILE#"="FI"."RELFILE#")
  22 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
              "U"."SEGBLOCK#"="RB"."BLOCK#")
  23 - access("TS"."TS#"="U"."TS#")
  24 - filter("TS"."BITMAPPED"=0)
  27 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
     520872  recursive calls
       1992  db block gets
     123081  consistent gets
      31977  physical reads
          0  redo size
       1235  bytes sent via SQL*Net to client
        465  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
         15  rows processed

Del query podemos observar lo siguiente:
Tiempo de ejecución:

Elapsed: 00:02:50.81

Del explain plan podemos ver que accesa el recycle bin:

TABLE ACCESS FULL      | RECYCLEBIN$      | 20791 |   243K|

Y también podemos ver que las estadísticas son algo altas para esta simple vista.

Ok, ahora hacemos un Purge y volvemos a ejecutar el query y revisamos el resultado.

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Elapsed: 00:28:58.65

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.10
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:01.51
SQL> set timing on;
SQL> set autotrace on;
SQL> select tablespace_name TS2,
  2  sum(bytes) SUM_B2,
  3  max(bytes) MAX_B2,
  4  count(bytes) CNT_B2
  5  from dba_free_space
  6  group by tablespace_name;

TS2                                SUM_B2     MAX_B2     CNT_B2
------------------------------ ---------- ---------- ----------
PSAPPW1                        3.7242E+10 2990538752       3370
PSAPPW1640                     1.6777E+10 4160749568          8
PSAPPW1700                     3194159104 1581252608          7
PSAPPW1DB                       671744000  670957568          2
PSAPPW1DIM                     6167658496 3471835136        288
PSAPPW1FACT                    2222653440  900661248         64
PSAPPW1ODS                     6.9220E+11 3220176896      17456
PSAPPW1USR                     1730150400 1233649664         12
PSAPUNDO                       5.4988E+10 1736441856       1234
PSAPZFCT0                      2.6376E+10 1534066688       3412
PSAPZFCT1                      9236316160  443547648        523
PSAPZODS0                      7.2968E+10 2434793472        452
PSAPZODS1                      2.5842E+10 3226468352       1011
SYSAUX                          892534784  834600960         96
SYSTEM                         2040774656 2040479744          7

15 rows selected.

Elapsed: 00:00:06.21

Execution Plan
----------------------------------------------------------
Plan hash value: 2686115433

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   649 | 19470 |       |   126K|
|   1 |  SORT GROUP BY               |                  |   649 | 19470 |       |   126K|
|   2 |   VIEW                       | DBA_FREE_SPACE   |   649 | 19470 |       |   126K|
|   3 |    UNION-ALL                 |                  |       |       |       |       |
|   4 |     NESTED LOOPS             |                  |     7 |   259 |       |     5 |
|   5 |      NESTED LOOPS            |                  |     7 |   210 |       |     5 |
|*  6 |       TABLE ACCESS FULL      | TS$              |     2 |    40 |       |     3 |
|*  7 |       TABLE ACCESS CLUSTER   | FET$             |     4 |    40 |       |     1 |
|*  8 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|   9 |     NESTED LOOPS             |                  |    74 |  5328 |       |    90 |
|  10 |      NESTED LOOPS            |                  |    74 |  4810 |       |    90 |
|* 11 |       TABLE ACCESS FULL      | TS$              |    14 |   364 |       |     3 |
|* 12 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |     5 |   195 |       |     7 |
|* 13 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  14 |     NESTED LOOPS             |                  |     1 |    97 |       |   125K|
|  15 |      NESTED LOOPS            |                  |     1 |    90 |       |   125K|
|* 16 |       HASH JOIN              |                  | 20579 |   763K|   488K|    42 |
|  17 |        TABLE ACCESS FULL     | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 18 |        TABLE ACCESS FULL     | TS$              |    14 |   364 |       |     3 |
|* 19 |       FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |     1 |    52 |       |     7 |
|* 20 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  21 |     NESTED LOOPS             |                  |   567 | 29484 |       |   462 |
|* 22 |      HASH JOIN               |                  |   567 | 25515 |       |   462 |
|* 23 |       HASH JOIN              |                  |  7475 |   240K|       |   430 |
|* 24 |        TABLE ACCESS FULL     | TS$              |     2 |    40 |       |     3 |
|  25 |        TABLE ACCESS FULL     | UET$             |  7475 | 97175 |       |   426 |
|  26 |       TABLE ACCESS FULL      | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 27 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("TS"."BITMAPPED"=0)
   7 - filter("TS"."TS#"="F"."TS#")
   8 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  11 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  12 - filter("TS"."TS#"="F"."KTFBFETSN")
  13 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  16 - access("TS"."TS#"="RB"."TS#")
  18 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  19 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#"
              AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  20 - access("RB"."TS#"="FI"."TS#" AND "RB"."FILE#"="FI"."RELFILE#")
  22 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
              "U"."SEGBLOCK#"="RB"."BLOCK#")
  23 - access("TS"."TS#"="U"."TS#")
  24 - filter("TS"."BITMAPPED"=0)
  27 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
       1411  recursive calls
       1992  db block gets
       8777  consistent gets
       8820  physical reads
          0  redo size
       1233  bytes sent via SQL*Net to client
        465  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
         15  rows processed

Inmediatamente podemos ver varias cosas:

  1. El tiempo se redujo considerablemente: de 00:02:50.81 a 00:00:06.21.
  2. La estadísticas cambiaron muchísimo.

Con este ejemplo quería mostrar que el recycle bin puede afectar el performance de algunos queries.
Después de buscar un poco en metalink encontre las siguientes notas en las cuales mencionan el problema, en algunas dice que es un bug y en otras dice que es un comportamiento “normal” y que podemos recrear la vista agregandole un hint para mejorar el performance.

Cuando se haga el purge se debe realizar a dba_recyclebin con un usuario que tenga el rol sysdba ya que si solo hacemos purge recyclebin solo se eliminaran los objetos del usuario actual y no de toda la base de datos.

Si se cree que no es necesario estar utilizando el recycle bin entonces podemos deshabilitarlo usando: alter system set recyclebin = off scope=both; cuidado ya que aunque deshabilitemos el recycle bin los objetos que se encontraban antes de esta acción continuaran ocupando espacio (por lo cual debemos hacer primero un purge).

Metalink: Note:302333.1, Note:271169.1,Note:3492682.8

Etiquetas:

Esta entrada fué posteada el 17 Abril, 2008 a las 3:20 pm y está archivado bajo Oracle. Puedes seguir las respuestas de esta entrada a través de sindicación RSS 2.0. Puedes dejar una respuesta, o trackback desde tu propio sitio.

Escribe un comentario