Función para la generación dinámica de fechas/horas con parámetros: fecha_inicio, fecha_fin, intervalo (en minutos):
CREATE OR REPLACE TYPE "SYSTEM"."DATE_LIST" is table of date;
CREATE OR REPLACE FUNCTION "SYSTEM"."PIPE_DATE_INTERVAL" (p_start date,p_end date, p_inc number)
return date_list pipelined is
v_intervalos number;
begin
v_intervalos:=(p_end-p_start)*24*60/p_inc;
for i in 0 ..v_intervalos-1 loop
pipe row (p_start +p_inc*i/(24*60));
end loop;
return;
end;
-- Ejemplo:
select column_value from table(system.pipe_date_interval(trunc(sysdate), sysdate,30));
Generaría una lista como la siguiente:
COLUMN_VALUE
-------------------
25/06/2012 00:00:00
25/06/2012 00:30:00
25/06/2012 01:00:00
25/06/2012 01:30:00
25/06/2012 02:00:00
25/06/2012 02:30:00
25/06/2012 03:00:00
25/06/2012 03:30:00
25/06/2012 04:00:00
25/06/2012 04:30:00
25/06/2012 05:00:00
25/06/2012 05:30:00
25/06/2012 06:00:00
25/06/2012 06:30:00
25/06/2012 07:00:00
25/06/2012 07:30:00
lunes, 25 de junio de 2012
lunes, 18 de junio de 2012
Script - Traspaso de estadísticas Oracle entre esquemas
Script para el traspaso de estadísticas Oralce entre dos esquemas de distintas bases de datos, se supone existe una tabla intermedia llamadas "CONEXIONES" ubicada en una base de datos central "DB_CENTRAL" donde se almacenan todas los usuarios y passwords de nuestro pool de bases de datos.
El script va pidiendo el esquema de origen y destino, base de datos de origen y destino.
column FECHA new_value FECHA
select
TO_CHAR(SYSDATE,'YYYYMMDD') FECHA
from
DUAL
/
host mkdir &FECHA
WHENEVER SQLERROR CONTINUE
WHENEVER OSERROR CONTINUE
set serveroutput on size 1000000
set termout on
set verify off
set feedback off
set echo off
set heading off
set pagesize 0
set pause off
set wrap on
set line 500
column lanza format a1000
conn usuario/"password"@DB_CENTRAL
define conexion_orig = ""
define conexion_dest = ""
column conexion_orig format a60 new_value Conexion_orig
column conexion_dest format a60 new_value Conexion_dest
COLUMN cadena_conexion format a60
COLUMN comentarios format a50
-- Esquemas a capturar/volcar estadísticas
accept esquema_orig_stats char prompt 'Filtro de esquema ORIGEN a capturar estadísticas: '
accept esquema_dest_stats char prompt 'Filtro de esquema DESTINO a capturar estadísticas: '
-- Mostramos las conexiones origen
accept cadena_orig char prompt 'Filtro de cadena de conexión ORIGEN (usuario SYSTEM): '
select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion , SUBSTR(COMENTARIOS,1,50) comentarios
from
CONEXIONES
where
upper(usuario) like '%'||UPPER('SYSTEM')||'%' and
upper(cadena) like '%'||UPPER('&cadena_orig')||'%' and
(UPPER(comentarios) not like 'NO DISPONIBLE%' AND
UPPER(comentarios) not like 'ELIMINA%' OR
COMENTARIOS IS NULL)
order by comentarios, trim(cadena), trim(usuario)
/
accept conn_id_orig number prompt 'Introduzca el id de la conexión ORIGEN: ';
-- Mostramos las conexiones destino
accept cadena_dest char prompt 'Filtro de cadena de conexión DESTINO (usuario SYSTEM): '
select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion , SUBSTR(COMENTARIOS,1,50) comentarios
from
CONEXIONES
where
upper(usuario) like '%'||UPPER('SYSTEM')||'%' and
upper(cadena) like '%'||UPPER('&cadena_dest')||'%' and
(UPPER(comentarios) not like 'NO DISPONIBLE%' AND
UPPER(comentarios) not like 'ELIMINA%' OR
COMENTARIOS IS NULL)
order by comentarios, trim(cadena), trim(usuario)
/
accept conn_id_dest number prompt 'Introduzca el id de la conexión DESTINO: ';
spool &FECHA\copy_stats.sql
select 'copy from '||v_from.cadena_conexion||' to '||v_to.cadena_conexion||' append &esquema_dest_stats..STATS_TABLE using SELECT * from &esquema_orig_stats..STATS_TABLE'
from
(select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion
from
CONEXIONES
where
conn_id=&conn_id_orig
order by conn_id) v_from,
(select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion
from
CONEXIONES
where
conn_id=&conn_id_dest
order by conn_id) v_to
/
spool off;
-- Generamos las cadenas de conexión a origen y destino
select trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as conexion_orig
from CONEXIONES
where
conn_id=decode(nvl(&conn_id_orig,98),0,98,nvl(&conn_id_orig,98))
/
select trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as conexion_dest
from CONEXIONES
where
conn_id=decode(nvl(&conn_id_dest,98),0,98,nvl(&conn_id_dest,98))
/
-- Conexion a ORIGEN
conn &conexion_orig
-- Borrado, creación y captura de stadisticas
exec DBMS_STATS.DROP_STAT_TABLE('&esquema_orig_stats','STATS_TABLE');
EXEC DBMS_STATS.CREATE_STAT_TABLE('&esquema_orig_stats' , 'STATS_TABLE');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('&esquema_orig_stats', 'STATS_TABLE', NULL, '&esquema_orig_stats');
-- Conexion a DESTINO
conn &conexion_dest
-- Borrado, creación de tabla de estadísticas
exec DBMS_STATS.DROP_STAT_TABLE('&esquema_dest_stats','STATS_TABLE');
EXEC DBMS_STATS.CREATE_STAT_TABLE('&esquema_dest_stats' , 'STATS_TABLE');
-- Copia de estadísticas
@&FECHA\copy_stats.sql
-- Depuramos las estadísticas cargadas
DELETE FROM &esquema_dest_stats..STATS_TABLE WHERE (C1,C4,C5) IN
(SELECT A.C1, A.C4, A.C5
FROM
&esquema_dest_stats..STATS_TABLE A,
DBA_TAB_COLUMNS B
WHERE
A.C1=B.TABLE_NAME(+) AND
A.C4=B.COLUMN_NAME(+) AND
A.C5=B.OWNER(+) AND
B.TABLE_NAME IS NULL);
COMMIT;
exec DBMS_STATS.IMPORT_SCHEMA_STATS('&esquema_dest_stats', 'STATS_TABLE', NULL, '&esquema_dest_stats');
El script va pidiendo el esquema de origen y destino, base de datos de origen y destino.
column FECHA new_value FECHA
select
TO_CHAR(SYSDATE,'YYYYMMDD') FECHA
from
DUAL
/
host mkdir &FECHA
WHENEVER SQLERROR CONTINUE
WHENEVER OSERROR CONTINUE
set serveroutput on size 1000000
set termout on
set verify off
set feedback off
set echo off
set heading off
set pagesize 0
set pause off
set wrap on
set line 500
column lanza format a1000
conn usuario/"password"@DB_CENTRAL
define conexion_orig = ""
define conexion_dest = ""
column conexion_orig format a60 new_value Conexion_orig
column conexion_dest format a60 new_value Conexion_dest
COLUMN cadena_conexion format a60
COLUMN comentarios format a50
-- Esquemas a capturar/volcar estadísticas
accept esquema_orig_stats char prompt 'Filtro de esquema ORIGEN a capturar estadísticas: '
accept esquema_dest_stats char prompt 'Filtro de esquema DESTINO a capturar estadísticas: '
-- Mostramos las conexiones origen
accept cadena_orig char prompt 'Filtro de cadena de conexión ORIGEN (usuario SYSTEM): '
select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion , SUBSTR(COMENTARIOS,1,50) comentarios
from
CONEXIONES
where
upper(usuario) like '%'||UPPER('SYSTEM')||'%' and
upper(cadena) like '%'||UPPER('&cadena_orig')||'%' and
(UPPER(comentarios) not like 'NO DISPONIBLE%' AND
UPPER(comentarios) not like 'ELIMINA%' OR
COMENTARIOS IS NULL)
order by comentarios, trim(cadena), trim(usuario)
/
accept conn_id_orig number prompt 'Introduzca el id de la conexión ORIGEN: ';
-- Mostramos las conexiones destino
accept cadena_dest char prompt 'Filtro de cadena de conexión DESTINO (usuario SYSTEM): '
select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion , SUBSTR(COMENTARIOS,1,50) comentarios
from
CONEXIONES
where
upper(usuario) like '%'||UPPER('SYSTEM')||'%' and
upper(cadena) like '%'||UPPER('&cadena_dest')||'%' and
(UPPER(comentarios) not like 'NO DISPONIBLE%' AND
UPPER(comentarios) not like 'ELIMINA%' OR
COMENTARIOS IS NULL)
order by comentarios, trim(cadena), trim(usuario)
/
accept conn_id_dest number prompt 'Introduzca el id de la conexión DESTINO: ';
spool &FECHA\copy_stats.sql
select 'copy from '||v_from.cadena_conexion||' to '||v_to.cadena_conexion||' append &esquema_dest_stats..STATS_TABLE using SELECT * from &esquema_orig_stats..STATS_TABLE'
from
(select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion
from
CONEXIONES
where
conn_id=&conn_id_orig
order by conn_id) v_from,
(select conn_id, trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as cadena_conexion
from
CONEXIONES
where
conn_id=&conn_id_dest
order by conn_id) v_to
/
spool off;
-- Generamos las cadenas de conexión a origen y destino
select trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as conexion_orig
from CONEXIONES
where
conn_id=decode(nvl(&conn_id_orig,98),0,98,nvl(&conn_id_orig,98))
/
select trim(usuario)||'/"'||trim(passw)||'"@'||trim(cadena) as conexion_dest
from CONEXIONES
where
conn_id=decode(nvl(&conn_id_dest,98),0,98,nvl(&conn_id_dest,98))
/
-- Conexion a ORIGEN
conn &conexion_orig
-- Borrado, creación y captura de stadisticas
exec DBMS_STATS.DROP_STAT_TABLE('&esquema_orig_stats','STATS_TABLE');
EXEC DBMS_STATS.CREATE_STAT_TABLE('&esquema_orig_stats' , 'STATS_TABLE');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('&esquema_orig_stats', 'STATS_TABLE', NULL, '&esquema_orig_stats');
-- Conexion a DESTINO
conn &conexion_dest
-- Borrado, creación de tabla de estadísticas
exec DBMS_STATS.DROP_STAT_TABLE('&esquema_dest_stats','STATS_TABLE');
EXEC DBMS_STATS.CREATE_STAT_TABLE('&esquema_dest_stats' , 'STATS_TABLE');
-- Copia de estadísticas
@&FECHA\copy_stats.sql
-- Depuramos las estadísticas cargadas
DELETE FROM &esquema_dest_stats..STATS_TABLE WHERE (C1,C4,C5) IN
(SELECT A.C1, A.C4, A.C5
FROM
&esquema_dest_stats..STATS_TABLE A,
DBA_TAB_COLUMNS B
WHERE
A.C1=B.TABLE_NAME(+) AND
A.C4=B.COLUMN_NAME(+) AND
A.C5=B.OWNER(+) AND
B.TABLE_NAME IS NULL);
COMMIT;
exec DBMS_STATS.IMPORT_SCHEMA_STATS('&esquema_dest_stats', 'STATS_TABLE', NULL, '&esquema_dest_stats');
martes, 5 de junio de 2012
Script - Control de Sesiones en base a Planes de Ejecución
Script para el control de sesiones activas en base a métodos de acceso en sus planes de ejecución.
En el ejemplo: Sesiones activas por más de 1 hora con MERGE JOIN CARTESIAN, el control en este caso consiste en cerrar la sesión mediante un kill immediate.
declare
cursor sql_id is
select l.sql_id from V$session l, v$sql s where SUBSTR(l.STATUS,1,10) LIKE 'ACTIV%' and
l.sql_id is not null and
l.username is not null and
l.sql_id=s.sql_id and
-- CPU_TIME > 1 hora
s.cpu_time > (3600)*1000000 ;
v_ind number;
v_mata varchar2(1000);
begin
v_ind:=0;
for v_reg in sql_id
loop
selecT COUNT(1) INTO v_ind
FROM TABLE(DBMS_XPLAN.DISPLAY_cursor(v_reg.sql_id,null)) where plan_table_output like '%MERGE JOIN CARTESIAN%';
if v_ind > 0 then
select 'ALTER SYSTEM KILL SESSION '''|| L.SID||','||SUBSTR(TO_CHAR(L.SERIAL#),1,8)||''' immediate' into v_mata FROM V$SESSION L WHERE L.sql_id=v_reg.sql_id and l.username is not null;
execute immediate v_mata;
end if;
v_ind:=0;
end loop;
exception
when others then null;
end;
/
En el ejemplo: Sesiones activas por más de 1 hora con MERGE JOIN CARTESIAN, el control en este caso consiste en cerrar la sesión mediante un kill immediate.
declare
cursor sql_id is
select l.sql_id from V$session l, v$sql s where SUBSTR(l.STATUS,1,10) LIKE 'ACTIV%' and
l.sql_id is not null and
l.username is not null and
l.sql_id=s.sql_id and
-- CPU_TIME > 1 hora
s.cpu_time > (3600)*1000000 ;
v_ind number;
v_mata varchar2(1000);
begin
v_ind:=0;
for v_reg in sql_id
loop
selecT COUNT(1) INTO v_ind
FROM TABLE(DBMS_XPLAN.DISPLAY_cursor(v_reg.sql_id,null)) where plan_table_output like '%MERGE JOIN CARTESIAN%';
if v_ind > 0 then
select 'ALTER SYSTEM KILL SESSION '''|| L.SID||','||SUBSTR(TO_CHAR(L.SERIAL#),1,8)||''' immediate' into v_mata FROM V$SESSION L WHERE L.sql_id=v_reg.sql_id and l.username is not null;
execute immediate v_mata;
end if;
v_ind:=0;
end loop;
exception
when others then null;
end;
/
lunes, 4 de junio de 2012
Script - Reescritura de Sentencias
Script para reescribir sentencias mediante el uso del paquete DBMS_ADVANCED_REWRITE.
exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence('ejemplo');
exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence('ejemplo');
exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence ( -
name => 'ejemplo', -
source_stmt=> q'[select columna1, columna2 from tablaA where columna1 like 'ABCD%' ]', -
destination_stmt=> q'[select columna1, columna2 from tablaB where columna3 = 'ABCD' ]', -
validate => false, -
rewrite_mode=>'TEXT_MATCH');
Vista donde podemos ver la equivalencia creada: DBA_REWRITE_EQUIVALENCES
ALTER SESSION SET QUERY_REWRITE_ENABLED=FORCE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
Así cuando ejecutemos :
select columna1, columna2 from tablaA where columna1 like 'ABCD%'
Realmente se ejecutará:
select columna1, columna2 from tablaB where columna3 = 'ABCD'
Útil para situaciones en las que no se puede modificar el código de la aplicación, y el resto de opciones no fuciona (SQL_profiles, hints, parámetros, etc).
IMPORTANTE: No admite el uso de variables BIND, por lo que su ámbito de actuación está limitado a sentencias que no hacen uso de variables bind.
name => 'ejemplo', -
source_stmt=> q'[select columna1, columna2 from tablaA where columna1 like 'ABCD%' ]', -
destination_stmt=> q'[select columna1, columna2 from tablaB where columna3 = 'ABCD' ]', -
validate => false, -
rewrite_mode=>'TEXT_MATCH');
Vista donde podemos ver la equivalencia creada: DBA_REWRITE_EQUIVALENCES
ALTER SESSION SET QUERY_REWRITE_ENABLED=FORCE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
Así cuando ejecutemos :
select columna1, columna2 from tablaA where columna1 like 'ABCD%'
Realmente se ejecutará:
select columna1, columna2 from tablaB where columna3 = 'ABCD'
Útil para situaciones en las que no se puede modificar el código de la aplicación, y el resto de opciones no fuciona (SQL_profiles, hints, parámetros, etc).
IMPORTANTE: No admite el uso de variables BIND, por lo que su ámbito de actuación está limitado a sentencias que no hacen uso de variables bind.
lunes, 14 de mayo de 2012
Scrip - Analiza_AWR
Script para generar estadísticas de sentencias (conociendo su sql_id o alguna parte del texto de la sql).
Genera los planes almacenados para la sentencia elegida y propuestas ADDM.
Tiene como parámetros el txt o sql_id de la consulta, usuario de parseo, snap_id inicial si se quiere indicar, snap_id final si se quiere indicar y fechas fec_ini y fec_fin de lanzamiento de la sentencia, además solicita el min_snap y max_snap para sentencias que han tenido varios planes a lo largo de distintos awr.
Cliente de lanzamiento : sqlplus.
set linesize 4500
define v_sql =""
column v_sql format a400 new_value V_sql
column fecha_Max_plan format a20
column min_snap format 999999
column max_snap format 999999
PROMPT
PROMPT =================================================================
PROMPT
PROMPT LISTA DE CONSULTAS Y SQL_IDS
PROMPT
PROMPT =================================================================
PROMPT
accept sqltxt char prompt 'Introduzca una cadena SQL a buscar o SQL_ID: '
PROMPT
accept snap_id_ini char DEFAULT 0 prompt 'Introduzca el SNAP_ID Inicial de localización de las sentencias: '
PROMPT
accept snap_id_fin char DEFAULT 999999999 prompt 'Introduzca el SNAP_ID Final de localización de las sentencias: '
PROMPT
accept fec_ini char DEFAULT sysdate-4000 prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
accept fec_fin char DEFAULT sysdate prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
select plan_hash_value,to_date(MAX(cast(END_INTERVAL_TIME as DATE)),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan,
min(q.snap_id) Min_Snap, max(q.snap_id) Max_Snap,
T.SQL_ID, SUM(EXECUTIONS_DELTA) EJECUCIONES, ROUND(SUM(ELAPSED_TIME_DELTA)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS_DELTA) "Physical Reads",
ROUND(SUM(ELAPSED_TIME_DELTA)/(1000000*SUM(EXECUTIONS_DELTA)),2) "Elap per Exec(s)/EJEC",
ROUND(SUM(DISK_READS_DELTA)/SUM(EXECUTIONS_DELTA),2) "Reads per Exec",
--replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)) v_sql
from
dba_hist_sqlstat q,
dba_hist_snapshot s,
(select SQL_ID, SUBSTR(SQL_TEXT,1,4000) SQL_TEXT from dba_hist_sqltext where UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt')) T
where q.sql_id = T.SQL_ID
and q.snap_id = s.snap_id
and q.snap_id BETWEEN (case
when &snap_id_ini=&snap_id_fin then &snap_id_ini
else &snap_id_ini +1 end) AND &snap_id_fin
and s.begin_interval_time between &fec_ini and &fec_fin
and executions_delta > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID,
--replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))
TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000))
/
accept sqlid char prompt 'Introduzca el SQL_ID: '
accept min_snap char prompt 'Introduzca el Min_Snap: '
accept max_snap char prompt 'Introduzca el Max_Snap: '
PROMPT
PROMPT =================================================================
PROMPT
PROMPT CONSULTA ELEGIDA PARA ANALISIS
PROMPT
PROMPT =================================================================
PROMPT
SELECT replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
FROM dba_hist_sqltext T WHERE SQL_ID='&sqlid'
/
PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLANES ALMACENADOS EN AWR
PROMPT
PROMPT =================================================================
PROMPT
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
exec dbms_sqltune.drop_tuning_task('sql_tuning_task');
DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := '&v_sql';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap=> &min_snap,
end_snap=> &max_snap,
sql_id => '&sqlid',
--bind_list => sql_binds(anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('10')),
--user_name => '&usuario',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task');
END;
/
exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task');
PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLAN DE EJECUCION ACTUAL Y PROPUESTAS
PROMPT
PROMPT =================================================================
PROMPT
COLUMN RESULTADO FORMAT A1000
select dbms_sqltune.report_tuning_task('sql_tuning_task') AS RESULTADO from dual;
Genera los planes almacenados para la sentencia elegida y propuestas ADDM.
Tiene como parámetros el txt o sql_id de la consulta, usuario de parseo, snap_id inicial si se quiere indicar, snap_id final si se quiere indicar y fechas fec_ini y fec_fin de lanzamiento de la sentencia, además solicita el min_snap y max_snap para sentencias que han tenido varios planes a lo largo de distintos awr.
Cliente de lanzamiento : sqlplus.
set linesize 4500
define v_sql =""
column v_sql format a400 new_value V_sql
column fecha_Max_plan format a20
column min_snap format 999999
column max_snap format 999999
PROMPT
PROMPT =================================================================
PROMPT
PROMPT LISTA DE CONSULTAS Y SQL_IDS
PROMPT
PROMPT =================================================================
PROMPT
accept sqltxt char prompt 'Introduzca una cadena SQL a buscar o SQL_ID: '
PROMPT
accept snap_id_ini char DEFAULT 0 prompt 'Introduzca el SNAP_ID Inicial de localización de las sentencias: '
PROMPT
accept snap_id_fin char DEFAULT 999999999 prompt 'Introduzca el SNAP_ID Final de localización de las sentencias: '
PROMPT
accept fec_ini char DEFAULT sysdate-4000 prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
accept fec_fin char DEFAULT sysdate prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
select plan_hash_value,to_date(MAX(cast(END_INTERVAL_TIME as DATE)),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan,
min(q.snap_id) Min_Snap, max(q.snap_id) Max_Snap,
T.SQL_ID, SUM(EXECUTIONS_DELTA) EJECUCIONES, ROUND(SUM(ELAPSED_TIME_DELTA)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS_DELTA) "Physical Reads",
ROUND(SUM(ELAPSED_TIME_DELTA)/(1000000*SUM(EXECUTIONS_DELTA)),2) "Elap per Exec(s)/EJEC",
ROUND(SUM(DISK_READS_DELTA)/SUM(EXECUTIONS_DELTA),2) "Reads per Exec",
--replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)) v_sql
from
dba_hist_sqlstat q,
dba_hist_snapshot s,
(select SQL_ID, SUBSTR(SQL_TEXT,1,4000) SQL_TEXT from dba_hist_sqltext where UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt')) T
where q.sql_id = T.SQL_ID
and q.snap_id = s.snap_id
and q.snap_id BETWEEN (case
when &snap_id_ini=&snap_id_fin then &snap_id_ini
else &snap_id_ini +1 end) AND &snap_id_fin
and s.begin_interval_time between &fec_ini and &fec_fin
and executions_delta > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID,
--replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))
TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000))
/
accept sqlid char prompt 'Introduzca el SQL_ID: '
accept min_snap char prompt 'Introduzca el Min_Snap: '
accept max_snap char prompt 'Introduzca el Max_Snap: '
PROMPT
PROMPT =================================================================
PROMPT
PROMPT CONSULTA ELEGIDA PARA ANALISIS
PROMPT
PROMPT =================================================================
PROMPT
SELECT replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
FROM dba_hist_sqltext T WHERE SQL_ID='&sqlid'
/
PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLANES ALMACENADOS EN AWR
PROMPT
PROMPT =================================================================
PROMPT
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
exec dbms_sqltune.drop_tuning_task('sql_tuning_task');
DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := '&v_sql';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap=> &min_snap,
end_snap=> &max_snap,
sql_id => '&sqlid',
--bind_list => sql_binds(anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('10')),
--user_name => '&usuario',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task');
END;
/
exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task');
PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLAN DE EJECUCION ACTUAL Y PROPUESTAS
PROMPT
PROMPT =================================================================
PROMPT
COLUMN RESULTADO FORMAT A1000
select dbms_sqltune.report_tuning_task('sql_tuning_task') AS RESULTADO from dual;
Script - Analiza_SGA
Script para generar estadísticas de sentencias (conociendo su sql_id o alguna parte del texto de la sql), utilizando como variables bind las máximas utilizadas en el último snap_id almacenado.
Genera los planes almacenados para la sentencia elegida y posteriormente muestra el análisis actual (con la sustitución de las variables bind) y propuestas ADDM.
Tiene como parámetros el txt o sql_id de la consulta, usuario de parseo, snap_id inicial si se quiere indicar, snap_id final si se quiere indicar y fechas fec_ini y fec_fin de lanzamiento de la sentencia:
Cliente de lanzamiento : sqlplus.
set linesize 4500
define v_sql =""
column v_sql format a4000 new_value V_sql
column fecha_Max_plan format a20
PROMPT
PROMPT =================================================================
PROMPT
PROMPT LISTA DE CONSULTAS Y SQL_IDS
PROMPT
PROMPT =================================================================
PROMPT
accept sqltxt char prompt 'Introduzca una cadena SQL a buscar o SQL_ID (en AWR y SGA): '
PROMPT
accept usuario char DEFAULT SYSTEM prompt 'Introduzca el usuario de Parseo de la sentencia: '
PROMPT
accept snap_id_ini char DEFAULT 0 prompt 'Introduzca el SNAP_ID Inicial de localización de las sentencias: '
PROMPT
accept snap_id_fin char DEFAULT 999999999 prompt 'Introduzca el SNAP_ID Final de localización de las sentencias: '
PROMPT
accept fec_ini char DEFAULT sysdate-4000 prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
accept fec_fin char DEFAULT sysdate prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
select plan_hash_value,to_date(MAX(cast(END_INTERVAL_TIME as DATE)),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan, T.SQL_ID, SUM(EXECUTIONS_DELTA) EJECUCIONES, ROUND(SUM(ELAPSED_TIME_DELTA)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS_DELTA) "Physical Reads",
ROUND(SUM(ELAPSED_TIME_DELTA)/(1000000*SUM(EXECUTIONS_DELTA)),2) "Elap per Exec(s)/EJEC",
ROUND(SUM(DISK_READS_DELTA)/SUM(EXECUTIONS_DELTA),2) "Reads per Exec",
replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
from
dba_hist_sqlstat q,
dba_hist_snapshot s,
(select SQL_ID, SUBSTR(SQL_TEXT,1,4000) SQL_TEXT from dba_hist_sqltext where UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt')) T
where q.sql_id = T.SQL_ID
and q.snap_id = s.snap_id
and q.snap_id BETWEEN (case
when &snap_id_ini=&snap_id_fin then &snap_id_ini
else &snap_id_ini +1 end) AND &snap_id_fin
and s.begin_interval_time between &fec_ini and &fec_fin
and executions_delta > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID,
replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))
union
select plan_hash_value, TO_DATE(to_date(LAST_LOAD_TIME,'YYYY-MM-DD/hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan, T.SQL_ID, sum(executions) ejecucions, ROUND(SUM(ELAPSED_TIME)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS) "Physical Reads",
ROUND(SUM(ELAPSED_TIME)/(1000000*SUM(EXECUTIONS)),2) "Elap per Exec(s)/EJEC",
ROUND(SUM(DISK_READS)/SUM(EXECUTIONS),2) "Reads per Exec",
replace(TO_CHAR(SUBSTR(T.SQL_fullTEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
from
v$sql T
where
(UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt') )
AND EXECUTIONS > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID, TO_DATE(to_date(LAST_LOAD_TIME,'YYYY-MM-DD/hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss'),
replace(TO_CHAR(SUBSTR(T.SQL_FULLTEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))
/
accept sqlid char prompt 'Introduzca el SQL_ID: '
PROMPT
PROMPT =================================================================
PROMPT
PROMPT CONSULTA ELEGIDA PARA ANALISIS
PROMPT
PROMPT =================================================================
PROMPT
SELECT replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
FROM dba_hist_sqltext T WHERE SQL_ID='&sqlid'
/
PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLANES ALMACENADOS EN AWR
PROMPT
PROMPT =================================================================
PROMPT
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
-- AHORA NOS QUEDAMOS CON LOS ÚLTIMOS VALORES INFORMADOS SEGÚN DBA_HIST_SQLBIND
var sqlidd varchar2(4000);
begin
:sqlidd:='&v_sql';
FOR rec in (select name, value_string from DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid' and snap_id =(select max(snap_id) from DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid'))
LOOP
:sqlidd := substr(replace('&v_sql',rec.name,CHR(39)||rec.value_string||CHR(39)),1,4000);
END LOOP;
end;
/
column sqlid2 format a4000 new_value Sqlid
select replace(:sqlidd,chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql from dual;
exec dbms_sqltune.drop_tuning_task('sql_tuning_task');
DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := '&v_sql';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
--bind_list => sql_binds(anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('10')),
user_name => '&usuario',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task');
END;
/
exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task');
PROMPT
PROMPT =========================================================================
PROMPT
PROMPT PLAN DE EJECUCION ACTUAL(CON SUSTITUCIÓN DE VARIABLES BIND) Y PROPUESTAS
PROMPT
PROMPT =========================================================================
PROMPT
COLUMN RESULTADO FORMAT A1000
select dbms_sqltune.report_tuning_task('sql_tuning_task') AS RESULTADO from dual;
Genera los planes almacenados para la sentencia elegida y posteriormente muestra el análisis actual (con la sustitución de las variables bind) y propuestas ADDM.
Tiene como parámetros el txt o sql_id de la consulta, usuario de parseo, snap_id inicial si se quiere indicar, snap_id final si se quiere indicar y fechas fec_ini y fec_fin de lanzamiento de la sentencia:
Cliente de lanzamiento : sqlplus.
set linesize 4500
define v_sql =""
column v_sql format a4000 new_value V_sql
column fecha_Max_plan format a20
PROMPT
PROMPT =================================================================
PROMPT
PROMPT LISTA DE CONSULTAS Y SQL_IDS
PROMPT
PROMPT =================================================================
PROMPT
accept sqltxt char prompt 'Introduzca una cadena SQL a buscar o SQL_ID (en AWR y SGA): '
PROMPT
accept usuario char DEFAULT SYSTEM prompt 'Introduzca el usuario de Parseo de la sentencia: '
PROMPT
accept snap_id_ini char DEFAULT 0 prompt 'Introduzca el SNAP_ID Inicial de localización de las sentencias: '
PROMPT
accept snap_id_fin char DEFAULT 999999999 prompt 'Introduzca el SNAP_ID Final de localización de las sentencias: '
PROMPT
accept fec_ini char DEFAULT sysdate-4000 prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
accept fec_fin char DEFAULT sysdate prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
select plan_hash_value,to_date(MAX(cast(END_INTERVAL_TIME as DATE)),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan, T.SQL_ID, SUM(EXECUTIONS_DELTA) EJECUCIONES, ROUND(SUM(ELAPSED_TIME_DELTA)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS_DELTA) "Physical Reads",
ROUND(SUM(ELAPSED_TIME_DELTA)/(1000000*SUM(EXECUTIONS_DELTA)),2) "Elap per Exec(s)/EJEC",
ROUND(SUM(DISK_READS_DELTA)/SUM(EXECUTIONS_DELTA),2) "Reads per Exec",
replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
from
dba_hist_sqlstat q,
dba_hist_snapshot s,
(select SQL_ID, SUBSTR(SQL_TEXT,1,4000) SQL_TEXT from dba_hist_sqltext where UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt')) T
where q.sql_id = T.SQL_ID
and q.snap_id = s.snap_id
and q.snap_id BETWEEN (case
when &snap_id_ini=&snap_id_fin then &snap_id_ini
else &snap_id_ini +1 end) AND &snap_id_fin
and s.begin_interval_time between &fec_ini and &fec_fin
and executions_delta > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID,
replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))
union
select plan_hash_value, TO_DATE(to_date(LAST_LOAD_TIME,'YYYY-MM-DD/hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan, T.SQL_ID, sum(executions) ejecucions, ROUND(SUM(ELAPSED_TIME)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS) "Physical Reads",
ROUND(SUM(ELAPSED_TIME)/(1000000*SUM(EXECUTIONS)),2) "Elap per Exec(s)/EJEC",
ROUND(SUM(DISK_READS)/SUM(EXECUTIONS),2) "Reads per Exec",
replace(TO_CHAR(SUBSTR(T.SQL_fullTEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
from
v$sql T
where
(UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt') )
AND EXECUTIONS > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID, TO_DATE(to_date(LAST_LOAD_TIME,'YYYY-MM-DD/hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss'),
replace(TO_CHAR(SUBSTR(T.SQL_FULLTEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))
/
accept sqlid char prompt 'Introduzca el SQL_ID: '
PROMPT
PROMPT =================================================================
PROMPT
PROMPT CONSULTA ELEGIDA PARA ANALISIS
PROMPT
PROMPT =================================================================
PROMPT
SELECT replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
FROM dba_hist_sqltext T WHERE SQL_ID='&sqlid'
/
PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLANES ALMACENADOS EN AWR
PROMPT
PROMPT =================================================================
PROMPT
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
-- AHORA NOS QUEDAMOS CON LOS ÚLTIMOS VALORES INFORMADOS SEGÚN DBA_HIST_SQLBIND
var sqlidd varchar2(4000);
begin
:sqlidd:='&v_sql';
FOR rec in (select name, value_string from DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid' and snap_id =(select max(snap_id) from DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid'))
LOOP
:sqlidd := substr(replace('&v_sql',rec.name,CHR(39)||rec.value_string||CHR(39)),1,4000);
END LOOP;
end;
/
column sqlid2 format a4000 new_value Sqlid
select replace(:sqlidd,chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql from dual;
exec dbms_sqltune.drop_tuning_task('sql_tuning_task');
DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := '&v_sql';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
--bind_list => sql_binds(anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('10')),
user_name => '&usuario',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task');
END;
/
exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task');
PROMPT
PROMPT =========================================================================
PROMPT
PROMPT PLAN DE EJECUCION ACTUAL(CON SUSTITUCIÓN DE VARIABLES BIND) Y PROPUESTAS
PROMPT
PROMPT =========================================================================
PROMPT
COLUMN RESULTADO FORMAT A1000
select dbms_sqltune.report_tuning_task('sql_tuning_task') AS RESULTADO from dual;
martes, 24 de abril de 2012
Script - Restore Datapump por Red
Procedimiento para exportación/importación mediante Datapump via dblink, que debe ser creado en la base de datos Destino:
CREATE OR REPLACE PROCEDURE RECUPERA
( source_schema in varchar2,
destination_schema in varchar2,
network_link in varchar2 default 'DBLINK')
as
JobHandle number;
js varchar2(9);
q varchar2(1) := chr(39);
BEGIN
JobHandle := dbms_datapump.open ('IMPORT','SCHEMA',network_link);
dbms_datapump.metadata_filter ( JobHandle,'SCHEMA_LIST',q||source_schema||q);
dbms_datapump.metadata_remap ( JobHandle,'REMAP_SCHEMA',source_schema,destination_schema);
dbms_datapump.set_parameter ( JobHandle,'TABLE_EXISTS_ACTION','REPLACE' );
dbms_datapump.start_job( JobHandle);
dbms_datapump.wait_for_job( JobHandle, js);
end;
/
Ejecución:
exec recupera('ESQUEMA_ORIGEN', 'ESQUEMA_DESTINO', 'DB_LINK_A_ORIGEN');
CREATE OR REPLACE PROCEDURE RECUPERA
( source_schema in varchar2,
destination_schema in varchar2,
network_link in varchar2 default 'DBLINK')
as
JobHandle number;
js varchar2(9);
q varchar2(1) := chr(39);
BEGIN
JobHandle := dbms_datapump.open ('IMPORT','SCHEMA',network_link);
dbms_datapump.metadata_filter ( JobHandle,'SCHEMA_LIST',q||source_schema||q);
dbms_datapump.metadata_remap ( JobHandle,'REMAP_SCHEMA',source_schema,destination_schema);
dbms_datapump.set_parameter ( JobHandle,'TABLE_EXISTS_ACTION','REPLACE' );
dbms_datapump.start_job( JobHandle);
dbms_datapump.wait_for_job( JobHandle, js);
end;
/
Ejecución:
exec recupera('ESQUEMA_ORIGEN', 'ESQUEMA_DESTINO', 'DB_LINK_A_ORIGEN');
Suscribirse a:
Entradas (Atom)