This is an old revision of the document!
Table of Contents
THIS document is basic_oracle_sql_querys but with the parts splitted removed
[DOC][OLD] Oracle basic queries
SECCIÓN : QUERYS
longest querys
Las querys con duración de más de 10 segundos:
COL SQL_TEXT FORMAT A130 WORD WRAPPED SELECT * FROM ( SELECT SQL_ID, CPU_TIME/NVL(EXECUTIONS,1) TIME_PER_EXECUTION, CPU_TIME, EXECUTIONS, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>10 ORDER BY EXECUTIONS DESC, TIME_PER_EXECUTION DESC, CPU_TIME DESC ) WHERE ROWNUM <31;
Ver también:
- V$SQL
- V$SESSION
Long operations
La vista LONGOPS nos da información de las operaciones largas/costosas.
- Burleson info.
COL UNITS FORMAT A12 COL opname FORMAT A30 COL target FORMAT A30 COL message FORMAT A70 COL SID_SERIAL FORMAT A15 SELECT * FROM ( SELECT opname, start_time, target, sofar, totalwork, units, elapsed_seconds, message FROM v$session_longops ORDER BY start_time DESC ) WHERE rownum <=1;
Bind variables de querys
COL SQL_ID FORMAT A15 COL NAME FORMAT A30 COL POSITION FORMAT 999 COL DATATYPE_STRING FORMAT A20 COL VALUE_STRING FORMAT A100 SELECT SQL_ID, LAST_CAPTURED, HASH_VALUE, NAME, POSITION, DUP_POSITION, DATATYPE_STRING, VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE sql_id='SQL_ID' ORDER BY 1,2,4 ;
SECCIÓN : Execution PLANS
Hidden options/formatting
The display_cursor funcion has the following undocumented format options:
- ALL
- ADVANCED
- OUTLINE
- PROJECTION
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ADVANCED');
Ver un plan de ejecución
De una query
Capturar la query:
EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename='benoit';
y
SET LINESIZE 280 SET PAGESIZE 999 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
De un SQL_ID
SELECT * FROM TABLE ( DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
Eliminar un Plan de ejecución
Primero, hay que saber el SQLID y el PLANHASHVALUE, podemos verlos con la siguiente query al SQLAREA
:
<code sql>
select sqlid, sqltext, hashvalue, planhashvalue
from v$sql
where sql_text like '%SOMETHING HERE%'
/
</code>
Después, ejecutar el siguiente proceseo:
vari rc NUMBER EXEC :rc:=dbms_spm.load_plans_from_cursor_cache(sql_id=> '<SQL_ID>',plan_hash_value=> <PLAN_HASH_VALUE>) ;
Substituir:
<SQLID>
*
<PLANHASH_VALUE>
Habitualmente con eso vale, auqnue a lo mejor hay que hacer “algo más”.
Obtener los valores necesarios para borrarlo (los hashes):
COL SQL_HANDLE FORMAT A50 COL PLAN_NAME FORMAT A50 SELECT sql_handle, plan_name, enabled, accepted,fixed,origin FROM dba_sql_plan_baselines ;
Ejecutar el siguiente procedimiento para borarrlo:
SET SERVEROUTPUT ON DECLARE l_plans_dropped PLS_INTEGER; BEGIN l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline ( sql_handle => '<SQL_HANDLE>', plan_name => '<SQL_PLAN_NAME>'); DBMS_OUTPUT.put_line(l_plans_dropped); END; /
Links
-
SECCIÓN : Performance
Hard parse
- How to Identify Hard Parse Failures (Doc ID 1353015.1)
-
Top 100 querys
Esta query se basa en las estadísticas guardadas en el repositorio awr.
COL CPU_RANK FORMAT 999 ; COL TOTAL_CPU_TIME FORMAT 999999999 ; COL SQLID FORMAT A23 ; SELECT CPU_RANK, SQLID, TOTAL_CPU_TIME, MODULE FROM ( SELECT s.sql_id SQLID, RANK() OVER (ORDER BY (MAX(s.CPU_TIME_TOTAL)) DESC) cpu_rank, ROUND(MAX(S. CPU_TIME_TOTAL)/1000000) TOTAL_CPU_TIME, MODULE FROM dba_hist_sqlstat s, dba_hist_snapshot sn WHERE sn.snap_id=s.snap_id GROUP BY s.sql_id, s.MODULE ) WHERE cpu_rank <=100 ;
SGA stats
Full:
SELECT POOL, NAME, BYTES/1024/1024 FROM v$sgastat ;
Únicamente lo libre:
SELECT POOL, NAME, BYTES/1024/1024 FROM v$sgastat WHERE name LIKE '%free memory%' ;
Shared pool histogram
COL BEGIN_INTERVAL_TIME FORMAT A50 COL END_INTERVAL_TIME FORMAT A50 COL NAME FORMAT A50 SELECT sn.BEGIN_INTERVAL_TIME, SN.END_INTERVAL_TIME, sg.NAME, sg.POOL, sg.BYTES FROM dba_hist_snapshot sn, DBA_HIST_SGASTAT sg WHERE sn.SNAP_ID = sg.SNAP_ID AND POOL = 'shared pool' AND NAME LIKE '%free memory%' ORDER BY BEGIN_INTERVAL_TIME ;
Library cache Hit ratio
SELECT 'Buffer Cache' NAME, ROUND ( (congets.VALUE + dbgets.VALUE - physreads.VALUE) * 100 / (congets.VALUE + dbgets.VALUE), 2) VALUE FROM v$sysstat congets, v$sysstat dbgets, v$sysstat physreads WHERE congets.NAME = 'consistent gets' AND dbgets.NAME = 'db block gets' AND physreads.NAME = 'physical reads' UNION ALL SELECT 'Execute/NoParse', DECODE ( SIGN ( ROUND ( (ec.VALUE - pc.VALUE) * 100 / DECODE (ec.VALUE, 0, 1, ec.VALUE), 2)), -1, 0, ROUND ( (ec.VALUE - pc.VALUE) * 100 / DECODE (ec.VALUE, 0, 1, ec.VALUE), 2)) FROM v$sysstat ec, v$sysstat pc WHERE ec.NAME = 'execute count' AND pc.NAME IN ('parse count', 'parse count (total)') UNION ALL SELECT 'Memory Sort', ROUND ( ms.VALUE / DECODE ( (ds.VALUE + ms.VALUE), 0, 1, (ds.VALUE + ms.VALUE)) * 100, 2) FROM v$sysstat ds, v$sysstat ms WHERE ms.NAME = 'sorts (memory)' AND ds.NAME = 'sorts (disk)' UNION ALL SELECT 'SQL Area get hitrate', ROUND (gethitratio * 100, 2) FROM v$librarycache WHERE namespace = 'SQL AREA' UNION ALL SELECT 'Avg Latch Hit (No Miss)', ROUND ( (SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2) FROM v$latch UNION ALL SELECT 'Avg Latch Hit (No Sleep)', ROUND ( (SUM (gets) - SUM (sleeps)) * 100 / SUM (gets), 2) FROM v$latch;
Espacio usado dentro de la SGA
SET pagesize 132 COLUMN owner format a16 COLUMN name format a36 COLUMN sharable_mem format 999,999,999 COLUMN executions format 999,999,999
Memory Usage of Shared Pool Order - Biggest First
COLUMN name format 45 SELECT owner, name||' - '||TYPE name, sharable_mem FROM v$db_object_cache WHERE sharable_mem > 10000 AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') ORDER BY sharable_mem DESC /
Loads into Shared Pool - Most Loads First
SELECT owner, name||' - '||TYPE name, loads , sharable_mem FROM v$db_object_cache WHERE loads > 3 AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') ORDER BY loads DESC /
Executions of Objects in the Shared Pool - Most Executions First
SELECT owner, name||' - '||TYPE name, executions FROM v$db_object_cache WHERE executions > 100 AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') ORDER BY executions DESC /
AWR
Paralelismo de objetos
DEGREE in dba_* views is VARCHAR!
indexes
Object parallelism ignoring degree 0 or 1
SELECT owner, INDEX_NAME, degree FROM DBA_INDEXES WHERE NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' ) ;
TABLES
Object parallelism ignoring degree 0 or 1
SELECT OWNER, TABLE_NAME, DEGREE FROM DBA_TABLES WHERE NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' ) ;
SECCIÓN : Otros
Añadir un redo group
ALTER DATABASE ADD LOGFILE GROUP 5 ('+DG_RECO_01', '+DG_DATA_01') SIZE 512M ;
Hay que tener en cuenta los thread groups (rac), si tenemos varios thread groups, habrá que añadirlo al thread correspondiente:
ALTER DATABASE ADD LOGFILE thread 1 GROUP 5 ('+DG_RECO_01', '+DG_DATA_01') SIZE 512M ;
Errores de usuario
Hay veces que show err no muestra todos los errores:
SELECT * FROM user_errors WHERE name LIKE '%OBJECT_NAME%' ;
Desde SYS pueden no aparecer los errores, hay que conectarse con el usuario.
Crear un directorio
CREATE DIRECTORY <IDENTIFIER> AS '/real/path/of/disk';
List directories
col directory_name format a40 col directory_path format a160 SELECT directory_name, directory_path FROM all_directories ;
Estadísticas de generacion de REDO
SELECT A.*, Round(A.COUNT#*B.AVG#/1024/1024) Daily_Avg_Mb FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, COUNT(1) COUNT#, MIN(RECID) MIN#, MAX(RECID) MAX# FROM v$log_history GROUP BY To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC ) A, ( SELECT Avg(BYTES) AVG# FROM v$log ) B ORDER BY DAY ;
Log Buffer Hit Rate Histogram
In order to track the amount of redo read from the in-memory log buffer vs. the amount of redo read from disk, a log buffer hit rate histogram has also been implemented. A new x$
table (x$logbuf_readhist
) was added to display the histogram information. There are 5 columns of interest that can be obtained using the following query:
COL BUFSIZE FORMAT A30 COL BUFINFO FORMAT A30 SELECT BUFSIZE, RDMEMBLKS, RDDISKBLKS, HITRATE, BUFINFO FROM X$LOGBUF_READHIST;
It returns the following columns:
BUFSIZE
: the log buffer size in Kbytes. If “A” stands for the current log buffer size (as specified by the LOG_BUFFER parameter), then the values ofBUFSIZE
in the view are in the range of [0.5A, 2A] increasing by 10% for each row. There are a total of 16 rows.BUFINFO
: identifies the ‘current’ log buffer and ‘target’ log buffer. The log buffer withBUFINFO
equal to 'CURRENT' is the currently configured buffer size.RDMEMBLKS
: number of blocks read from memory.RDDISKBLKS
: number of blocks read from disk.HITRATE
: calculated by 100 *RDMEMBLKS
/ (RDMEMBLKS + RDDISKBLKS
) In the histogram, the data in the row pointed to byCURRENT
is captured while system is running.RDMEMBLKS
in that row stands for the number of blocks actually read from log buffer in memory, andRDDISKBLKS
stands for the number of blocks actually read from ORL on disk. TheHITRATE
is calculated correspondingly. The data in all other rows are calculated based on heuristics.RDMEMBLKS
in those rows stands for the number of blocks that would be read from the log buffer should the buffer size be increased or reduced. If the hit ratio is less than 100%, then if possible, increase the buffer size to the value needed to achieve a 100% hit rate.
(Doc ID 951152.1)
Switch de Archived por hora
SELECT to_date(first_time) DAY, to_char(SUM(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", to_char(SUM(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", to_char(SUM(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", to_char(SUM(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", to_char(SUM(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", to_char(SUM(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", to_char(SUM(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", to_char(SUM(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", to_char(SUM(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08", to_char(SUM(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", to_char(SUM(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", to_char(SUM(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", to_char(SUM(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", to_char(SUM(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", to_char(SUM(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", to_char(SUM(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", to_char(SUM(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", to_char(SUM(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", to_char(SUM(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", to_char(SUM(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", to_char(SUM(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", to_char(SUM(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", to_char(SUM(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22", to_char(SUM(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" FROM v$log_history WHERE to_date(first_time) > sysdate -8 GROUP BY to_char(first_time,'YYYY-MON-DD'), to_date(first_time) ORDER BY to_date(first_time) /
Flush de redos
ALTER system checkpoint ;
Stopping hardway
Stop/shutdown de emergencia cuando nada más funciona:
sqlplus -prelim "/ as sysdba" shutdown abort
Localizar una query desde un sql_id del alert
Para localizar la query, tenemos que tener el SQLID que aparece en el alert y esperar que la query no se haya ido de memoria. <code sql>SELECT SQLFULLTEXT FROM v$sql WHERE sqlid like 'IDENTIFICADORDE_QUERY' ;</code>
Dropping the database
shutdown abort; startup mount exclusive RESTRICT; DROP DATABASE; exit ;
Listar Parámetros ocultos e indocumentados
Información completa:
COL NAME FORMAT A60 COL VALUE FORMAT A30 COL DEFLT FORMAT A30 COL TYPE FORMAT A10 COL description FORMAT A120 SELECT a.ksppinm name, b.ksppstvl VALUE, b.ksppstdf deflt, decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) TYPE, a.ksppdesc description FROM sys.x$ksppi a, sys.x$ksppcv b WHERE a.indx = b.indx AND a.ksppinm LIKE '\_%' escape '\' order by name ;
únicamente nombre y descripción:
COL KSPPINM FORMAT A60 COL ksppdesc FORMAT A120 SELECT ksppinm, ksppdesc FROM x$ksppi WHERE substr(ksppinm,1,1) = '_' ORDER BY 1,2;
db_link / dblink
DATABASE LINKS!
crear
sqlplus SYSTEM
CREATE DATABASE LINK VOXEL CONNECT TO SYSTEM IDENTIFIED BY ******* USING 'ORIGEN';
listar
SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 COLUMN owner FORMAT A30 COLUMN db_link FORMAT A50 COLUMN username FORMAT A30 COLUMN host FORMAT A30 SELECT owner, db_link, username, host FROM dba_db_links ORDER BY owner, db_link /
Borrar
DROP DATABASE LINK <link_name>;
o
DROP PUBLIC DATABASE LINK <link_name>;
Multi-line comments
Para insertar un comentario multi-linea:
- Habilitar las blanklines:
SET SQLBLANKLINES ON
- Insertar el comentario:
COMMENT ON TABLE TESTTABLE IS 'TEST MULTILINE COMMENT! ' ;
REGEXP_LIKE
DBMS_SCHEDULER
Listar jobs
COL owner FORMAT A20 COL job_name FORMAT A40 COL job_subname FORMAT A30 COL job_style FORMAT A30 COL job_creator FORMAT A30 COL client_id FORMAT A30 COL program_owner FORMAT A20 COL program_name FORMAT A35 COL job_type FORMAT A30 COL job_action FORMAT A30 COL schedule_owner FORMAT A20 COL schedule_name FORMAT A30 COL REPEAT_INTERVAL FORMAT A70 COL START_DATE FORMAT A50 SELECT owner, job_name, -- job_subname, -- job_style, -- job_creator, -- client_id, -- program_owner, -- program_name, job_type, -- job_action, -- schedule_owner, -- schedule_name, START_DATE, REPEAT_INTERVAL, ENABLED FROM dba_SCHEDULER_JOBS WHERE ENABLED=TRUE ;
Historial de ejecutiones
COL owner FORMAT A20 COL job_name FORMAT A40 COL job_subname FORMAT A30 COL job_style FORMAT A30 COL job_creator FORMAT A30 COL client_id FORMAT A30 COL program_owner FORMAT A20 COL program_name FORMAT A35 COL job_type FORMAT A30 COL job_action FORMAT A30 COL schedule_owner FORMAT A20 COL schedule_name FORMAT A30 COL REPEAT_INTERVAL FORMAT A70 COL START_DATE FORMAT A50 SELECT owner, job_name, -- job_subname, -- job_style, -- job_creator, -- client_id, -- program_owner, -- program_name, job_type, -- job_action, -- schedule_owner, -- schedule_name, START_DATE, REPEAT_INTERVAL, ENABLED FROM dba_SCHEDULER_JOBS WHERE ENABLED=TRUE ;
SECCIÓN : Documentos adicionales
Auditoria
DataGuard
RMAN
RAC Status
Links
See here
Lista de DocId interesantes
- How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1)