Modificar el valor de STALE_PERCENT

STALE_PERCENT indica el porcentaje de cambios que debe alcanzar una table para que Oracle pase las estadísticas de forma automática sobre las mismas. En la mayoría de los casos, el valor predeterminado, suele ser suficiente y no hay que tocar este valor que por defecto es 10%. En algunos casos, para mejor el rendimiento, es necesario que en ciertas tablas, las estadísticas se pasen  por ejemplo  con más frecuencia y debemos disminuir el valor de STALE_PERCENT. 

A continuación muestro como consultar el valor que tienen las tablas y como cambiarlo al valor que queremos.

1.-Consultamos el valor para todas las tablas del esquema donde queremos modificar STALE_PERCENT. También  podemos filtrar la consulta y consultar solamente sobre las tablas que nos interesa.

SELECT owner, table_name,

DBMS_STATS.get_prefs(ownname=>'NOMBRE_ESQUEMA',tabname=>table_name,

pname='STALE_PERCENT') 

FROM dba_tables 

WHERE owner = 'NOMBRE_ESQUEMA';

2.-Modificamos  el STALE_PERCENT a 5% para algunas tablas 

--modificamos para la Tabla1  

exec dbms_stats.set_table_prefs(ownname=>'NOMBRE_ESQUEMA',tabname=>'Tabla1',pname=>'STALE_PERCENT',pvalue=>5);

--modificamos para la Tabla 2

exec dbms_stats.set_table_prefs(ownname=>'NOMBRE_ESQUEMA',tabname=>'Tabla2',pname=>'STALE_PERCENT',pvalue=>5); 

Si en vez de ir cambiando tabla por tabla, quisiéramos cambiar el valor de las tablas que tengan más de 1000000 filas, lo haríamos de este modo:

BEGIN

FOR c IN (SELECT TABLE_NAME, T.NUM_ROWS FROM DBA_TABLES T WHERE T.OWNER = 'NOMBRE_ESQUEMA'  and T.NUM_ROWS > 1000000) 

LOOP

    DBMS_STATS.SET_TABLE_PREFS('NOMBRE_ESQUEMA', c.TABLE_NAME, 'STALE_PERCENT', '5');

END LOOP;

END;

3.- Y por último, comprobamos los nuevos valores y en vez de 10% deberían de salir 5%, lo que implica que Oracle pasará las estadísticas sobre esas tablas con más frecuencia.

SELECT owner, table_name,

DBMS_STATS.get_prefs(ownname=>'NOMBRE_ESQUEMA',tabname=>table_name,

pname='STALE_PERCENT') 

FROM dba_tables 

WHERE owner = 'NOMBRE_ESQUEMA';