La verdad este año tengo el propósito de escribir un poco más a menudo (Siempre digo lo mismo, jejeje). Bueno, empiezo con algo muy rapidito. Buscando y buscando y buscando en google, encontré la descripción de la columna flag.x$bh. Interesante, y encontré que:
FLAG
KCBBHFSQ 0x80000 sequential scan only flag
KCB = Kernel Cache Buffer.
El objetivo es conocer todos los objetos que están en la buffer cache que se suben de forma secuencial, esto "PUEDE" implicar que si son tablas, se un FULL SCAN forma de acceso.
¿Para qué sirve?
Pues para conocer las POSIBLES tablas que están en la Buffer Cache con FULL SCAN, importante por temas de rendimiento y esas cosas :D. Por aquello de "menos CR mejor", aunque como todo "depende", pero en general o porque no, menos I/O mejor.
¿Cómo puedo entonces sacar la lista de esas tablas?
Aquí está una consulta, ya después es a gusto del consumidor lo que hagan con ella. Yo excluyo los las tablas de los objetos, propios de algunos componentes de Oracle.
SELECT distinct o.owner,o.object_name,o.object_type,o.owner
FROM dba_objects o,x$bh x
WHERE x.obj=o.object_id
AND o.object_type='TABLE'
AND standard.bitand(x.flag,524288)>0
AND o.owner not in ('SYS','PATROL','FLASHBACKSTATS','INVENTORY', 'SCOTT'
,'OUTLN','TSMSYS','EXFSYS','SYSMAN','SYSTEM', 'MDSYS', 'DMSYS', 'CTXSYS'
,'DBSNMP','WMSYS','SMSEXP', 'ORDPLUGINS', 'ORDSYS', 'PUBLIC', 'OLAPSYS');
El 524288 es el decimal del hexadecimal 80000, por tanto, la columna FLAG contiene muchísima información. Hasta la 9i se de 38 datos sobre el estado del bloque en la buffer cache.
Nota:
Según un compi mío, del cual no quiero dar el nombre porque se cabrea :). Él me dijo que si la cantidad de bloques de la consulta son mayores que el 10% del tamaño de la buffer cache (se puede contralar por parámetro oculto, creo) entonces Oracle protege la SGA y no va a memoria esta cantidad de bloques sino directamente a la UGA. Así bien para server processes dedicados, sería su PGA, pero cuando usan Shared Server, en teoría debería ir a la Large Pool, pero esto todavía lo tendría que confirmar :), si alguien ya lo ha probado me gustaría verlo.
Dije un 2% del B.C. y ya depende. En Exadata esto es especialmente relevante para "activar" el Smart Scan de las celdas. La filosofía cambia completamente, mientras que en no-Exadata puede ser interesante tener cacheados bloques, en Exadata puede llegar a ser contraproducente ya que normalmente una de las pretensiones es dejar que sea cellsrv el proceso que entregue sólo las filas y la proyección estrictamente necesarias a la pga. Esto quiere decir que en Exadata puede no llegar a ser una mala idea en disminuir el tamaño del B.C. y sí aumentar la pga.
ResponderEliminarAquí adjunto un ejemplo de una sentencia ejecutada sin Storage Indexes y, por tanto, sin Smart Scan y posteriormente con su activación. En la parte final se puede apreciar como, aún haciendo varios flush del B.C., los tiempos de respuesta son inmediatos al usarse el Smart Scan, lo que demuestra la escasa necesidad de tener un B.C. grande en Exadata. No es que no sea necesario, sino que si se logran activar los mecanismos de Offloading de las celdas (lo que implica consideraciones sobre num. de bloques cacheados, tipo de sentencia, bloques de estadísticas del segmento si los hay ...), puede llegar a ser realmente útil este tipo de mecanismo en ausencia de índices. El valor "cell physical IO bytes saved by storage index " es el realmente interesante, permanece en 0 hasta que se activan el Offloading cuando se dispara indicando que se se han descartado gran cantidad de datos que no hace falta leer porque las celdas saben que "ahí" seguro que no está el dato que se busca. Atención también al predicado "filter + storage" que sólo puede aparecer si el Offloading está activo mientras que en caso de no usarse Offloading, el predicado es sólo de tipo filter.
ResponderEliminarNAME VALUE
ResponderEliminar---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 0
cell blocks processed by cache layer 0
cell blocks processed by txn layer 0
cell blocks processed by data layer 0
cell blocks processed by index layer 0
cell IO uncompressed bytes 0
8 rows selected.
Elapsed: 00:00:00.01
SQL> @trunc t
Table truncated.
Elapsed: 00:00:00.29
SQL> @analizar t
'Estadisticas de tabla t '
Elapsed: 00:00:01.22
SQL> @stats t
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN COMPRESS_FOR
-------------------- ------------------------------ ---------- ---------- ----------- ------------
SYS T 0 0 0 NO COMPRESS
Elapsed: 00:00:00.06
SQL> @insertar t 4e6
'Insertando en tabla t 4e6 filas'
OWNER SEGMENT_NAME MB
------------------------------ ------------------------------ ----------
SYS T 88
SQL> @analizar t
'Estadisticas de tabla t '
SQL> @stats t
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN COMPRESS_FOR
-------------------- ------------------------------ ---------- ---------- ----------- ------------
SYS T 4000000 10361 14 NO COMPRESS
SQL>
SQL> @stat
NAME VALUE
---------------------------------------------------------------- ----------
cell scans 2
Elapsed: 00:00:00.01
NAME VALUE
ResponderEliminar---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload 167346176
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 121589072
cell blocks processed by cache layer 27680
cell blocks processed by txn layer 27680
cell blocks processed by data layer 20542
cell blocks processed by index layer 0
cell IO uncompressed bytes 168280064
Elapsed: 00:00:00.02
SQL>
SQL> update t set c=null where n between 2500 and 3000;
Elapsed: 00:00:00.68
SQL> commit;
Elapsed: 00:00:00.00
SQL> @stat
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
NAME VALUE
---------------------------------------------------------------- ----------
cell scans 3
Elapsed: 00:00:00.00
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload 251019264
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 121610736
cell blocks processed by cache layer 37894
cell blocks processed by txn layer 37894
cell blocks processed by data layer 30756
cell blocks processed by index layer 0
cell IO uncompressed bytes 251953152
Elapsed: 00:00:00.02
SQL>
SQL> @si_off
Elapsed: 00:00:00.02
"Storage Indexes desactivados ..."
SQL>
SQL>
SQL> select count(*) from t where c is null;
COUNT(*)
----------
501
1 row selected.
Elapsed: 00:00:05.71
SQL> select count(*) from t where c is null;
COUNT(*)
----------
501
1 row selected.
Elapsed: 00:00:05.32
SQL> select count(*) from t where c is null;
COUNT(*)
----------
501
1 row selected.
Elapsed: 00:00:05.26
SQL> select count(*) from t where c is null;
COUNT(*)
----------
501
1 row selected.
Elapsed: 00:00:10.97
SQL> select count(*) from t where c is null;
COUNT(*)
----------
501
1 row selected.
Elapsed: 00:00:05.71
SQL> @plan
ResponderEliminarSQL_ID 2ubctxp51bkc1, child number 0
-------------------------------------
select count(*) from t where c is null
Plan hash value: 2966233522
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.70 | 10217 | 10214 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.70 | 10217 | 10214 |
|* 2 | TABLE ACCESS STORAGE FULL| T | 1 | 1 | 501 |00:00:05.70 | 10217 | 10214 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C" IS NULL)
SQL>
SQL> @stat
NAME VALUE
---------------------------------------------------------------- ----------
cell scans 4
1 row selected.
Elapsed: 00:00:00.01
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload 251019264
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 121610736
cell blocks processed by cache layer 37894
cell blocks processed by txn layer 37894
cell blocks processed by data layer 30756
cell blocks processed by index layer 0
cell IO uncompressed bytes 251953152
8 rows selected.
Elapsed: 00:00:00.03
"Storage Indexes activados ..."
SQL>
SQL> select count(*) from t where c is null;
COUNT(*)
----------
501
Elapsed: 00:00:00.04
SQL> @stat
SQL> @plan
SQL_ID 0ddytv9pcua5s, child number 0
-------------------------------------
select count(1) from t where c is null
Plan hash value: 2966233522
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 10217 | 10215 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 10217 | 10215 |
|* 2 | TABLE ACCESS STORAGE FULL| T | 1 | 1 | 501 |00:00:00.04 | 10217 | 10215 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("C" IS NULL)
filter("C" IS NULL)
NAME VALUE
ResponderEliminar---------------------------------------------------------------- ----------
cell scans 5
Elapsed: 00:00:00.00
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload 334692352
cell physical IO bytes saved by storage index 330825728
cell physical IO interconnect bytes returned by smart scan 28416
cell blocks processed by cache layer 472
cell blocks processed by txn layer 472
cell blocks processed by data layer 472
cell blocks processed by index layer 0
cell IO uncompressed bytes 3866624
8 rows selected.
Elapsed: 00:00:00.02
SQL>
Elapsed: 00:00:00.03
"Storage Indexes activados ..."
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.00
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.00
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.00
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.01
SQL> select count(*) from t where c is null;
COUNT(*)
----------
501
1 row selected.
Elapsed: 00:00:00.06
SQL> @stat
System altered.
Elapsed: 00:00:00.01
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
Session altered.
Elapsed: 00:00:00.00
NAME VALUE
---------------------------------------------------------------- ----------
cell scans 6
1 row selected.
Elapsed: 00:00:00.01
NAME VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload 251019264
cell physical IO bytes saved by storage index 248119296
cell physical IO interconnect bytes returned by smart scan 21312
cell blocks processed by cache layer 354
cell blocks processed by txn layer 354
cell blocks processed by data layer 354
cell blocks processed by index layer 0
cell IO uncompressed bytes 2899968
8 rows selected.
Elapsed: 00:00:00.02
SQL>