Script - Trasponer_filas_a_columna.sql

Función plsql para transponer los resultados de una consultas (varias filas) al valor de una fila en una columna indicando un carácter separador:

create or replace FUNCTION transponer(vSQL VARCHAR2, vSEP varchar2)
RETURN VARCHAR2 IS
-- vSQL sql dinámico a lanzar cuyo resultado queremos separados por la cadena vSEP
-- vSQL debe devolver una sola columna tipo varchar2 y el total de bytes retornados no debe superar los 32000 y 4000 por registro.
TYPE cv_type IS REF CURSOR;
CV CV_TYPE;
V_RES_VALOR VARCHAR2(32000);
VALOR VARCHAR2(4000);
BEGIN
V_RES_VALOR:='XX';
OPEN CV FOR VSQL;
LOOP
    FETCH CV INTO VALOR;
    EXIT WHEN CV%NOTFOUND;
    IF V_RES_VALOR='XX' THEN
        V_RES_VALOR:=VALOR;
    ELSE
        V_RES_VALOR:=V_RES_VALOR||VSEP||VALOR;
    END IF;
END LOOP;
CLOSE CV;
RETURN V_RES_VALOR;
EXCEPTION
    WHEN OTHERS THEN
        IF SQL%NOTFOUND THEN             VALOR:='NO ENCONTRADO';
        ELSE
            VALOR:='ERROR EN LA CONSULTA, CONSULTE CON EL ADMINISTRADOR';
        END IF;
        IF nvl(length(V_RES_VALOR),0)=0  THEN
         V_RES_VALOR:=VALOR;
        ELSE
         V_RES_VALOR:=V_RES_VALOR||' '||VSEP||' '||VALOR;
        END IF;
    RETURN 'ERROR --> '||V_RES_VALOR;
END TRANSPONER;
/

Ejemplo de uso:

select transponer('SELECT table_name FROM user_tables WHERE ROWNUM <=100', '|') FROM DUAL;

TABLA1|TABLA2|TABLA_EJEMPLO|PRUEBA

Script - Snaps AWR con más accesos a disco

Detectamos el snap_id con mayor número de lecturas y escrituras físicas de las estadísticas AWR almacenadas.

select
b.snap_id, sum(b.ios-a.ios)
from
    (select snap_id, file#, phyrds+phywrts ios
    from
        dba_hist_filestatxs dhf) a,
    (select snap_id, file#, phyrds+phywrts ios
    from
        dba_hist_filestatxs dhf) b
where
    a.snap_id+1=b.snap_id        and
    a.file#=b.file#
group by a.snap_id order by 2   


select * from dba_hist_snapshot where snap_id=....

Comando - Table_lock

alter table '||owner||'.'||table_name||' disable table lock;

Con este comando evitamos un nivel de enqueues o bloqueos en el motor oracle, los tipo TM, permitiendo agilizar estos procesos de gestión de bloqueos, las estadísticas del AWR que se mejoran son:

enqueue releases
enqueue requests

las cuales bajan muchísimo.

Con esta opción en tablas críticas OLTP, bajamos los requerimientos de CPU en sistemas cargados entre un 5-10%.

Hay que tener en cuenta que desabilitar el "table_lock" sobre una tabla implica no poder realizar operaciones DDL sobre ellas (truncate, alter ...) así hay que tenerlo en cuenta para sistemas que requieran este tipo de bloqueos, aunque también es una medida extra de seguridad.

Comando - Commit_write

Pruebas parámetro commit_write desde Oracle-base

http://www.oracle-base.com/articles/10g/Commit_10gR2.php


create table commit_test (id number, description varchar2(100));

SET SERVEROUTPUT ON
DECLARE
  PROCEDURE do_loop (p_type  IN  VARCHAR2) AS
    l_start  NUMBER;
    l_loops  NUMBER := 10000;
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';

    l_start := DBMS_UTILITY.get_time;
    FOR i IN 1 .. l_loops LOOP
      INSERT INTO commit_test (id, description)
      VALUES (i, 'Description for ' || i);
     
      CASE p_type
          WHEN 'NORMAL' THEN COMMIT;
        WHEN 'WAIT' THEN COMMIT WRITE WAIT;
        WHEN 'NOWAIT' THEN COMMIT WRITE NOWAIT;
        WHEN 'BATCH' THEN COMMIT WRITE BATCH NOWAIT;
        WHEN 'IMMEDIATE' THEN COMMIT WRITE IMMEDIATE;
      END CASE;
    END LOOP;
    DBMS_OUTPUT.put_line(RPAD('COMMIT WRITE ' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start));
  END;
BEGIN
    DO_LOOP('NORMAL');
  --do_loop('WAIT');
  --do_loop('NOWAIT');
  --do_loop('BATCH');
  --do_loop('IMMEDIATE');
END;
/

Script - Get_csv.sql

Script para generar ficheros csv, xls directamente a partir de una consulta dada, se pasa como parámetros la consulta, el fichero a generar y la ubicación en el cliente para generar el fichero.

set wrap on
REM SET TERMOUT OFF
set serveroutput on size 1000000
set verify off
set linesize 434
set trimspool on
SET ECHO OFF
SET DOCUMENT OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET NEWPAGE 0
Set pages 999;
SET LINESIZE 15000 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000

PROMPT Introduzca la consulta para generar la salida: (select * from user_tables, ...)
accept cons
PROMPT
PROMPT Introduzca el nombre del fichero a generar: (Listado_20101202.csv, Indicadores_20101202.csv, ...)
accept nom
PROMPT
PROMPT Introduzca la ubicación de la salida: (c:\frank25\marzo\06\, <vacio> para generar en dir. SQL)
accept ubi

spool &&ubi&&nom
declare
procedure genera_subs_dump_to_csv( l_query in varchar2)
    is
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(4000);
        l_status        integer;
       l_colCnt        number := 0;
       l_separator     varchar2(1);
       l_descTbl       dbms_sql.desc_tab;
   begin
       execute immediate 'alter session set nls_date_format=''dd/mm/yyyy hh24:mi:ss'' ';
       dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
       for i in 1 .. l_colCnt loop
           dbms_output.put(l_separator || '"' || l_descTbl(i).col_name || '"' );
           dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
           l_separator := ';';
       end loop;
       dbms_output.new_line;
 
       l_status := dbms_sql.execute(l_theCursor);
 
       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               dbms_output.put(l_separator || l_columnValue );
               l_separator := ';';
           end loop;
                dbms_output.new_line;          
       end loop;
       dbms_sql.close_cursor(l_theCursor);
   exception
       when others then
         raise;
end;
begin
    genera_subs_dump_to_csv( '&&cons');
end;
/   
spool off;

Script - Memoria PGA por session

Script para comprobar la memoria utilizada por una sesion

select name, sum(value/1024) "Value - KB"
 from v$statname n,
 v$session s,
 v$sesstat t
 where s.sid=t.sid
and s.sid=&&1
  and n.statistic# = t.statistic#
 and s.type = 'USER'
 and s.username is not NULL
 and n.name in ('session pga memory', 'session pga memory max', 'session uga memory', 'session uga memory max')
 group by name
/

Script - Monitorizacion recuperaciones SMON

--- Para estudiar el proceso SMON en recuperaciones costosas de transacciones fallidas o rolled-bak

SELECT * FROM V$fast_starT_transactions;

      USN        SLT        SEQ STATE            UNDOBLOCKSDONE UNDOBLOCKSTOTAL       
--------- ---------- ---------- ---------------- -------------- --------------- --
       42         56      12352 RECOVERING                   25           12525       


--- Parámetro con indicencia en los tiempos de recuperación (también requiere del ajuste de otros como parallel_server) nos marcará si las recuperaciones son seriales o paralelas.

fast_start_parallel_rollback