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

La vista LONGOPS nos da información de las operaciones largas/costosas.

• Burleson info.
• Official view information.

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;

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
;

The display_cursor funcion has the following undocumented format options:

• ALL
• ADVANCED
• OUTLINE
• PROJECTION

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ADVANCED');

Information about the formatting ops

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);

SELECT * FROM TABLE (
   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

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;
/

• http://www.oracle-base.com/articles/11g/sql-plan-management-11gr1.php
• http://fordba.wordpress.com/tag/dbms_spm-alter_sql_plan_baseline/
• http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm

  SECCIÓN : Performance

  Hard parse
• How to Identify Hard Parse Failures (Doc ID 1353015.1)
• http://wiki.ciberterminal.net/doku.php?id=killing_hardparse

  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
;

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%' ;

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
;

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;

SET pagesize 132
 
COLUMN owner format a16
COLUMN name  format a36
COLUMN sharable_mem format 999,999,999
COLUMN executions   format 999,999,999

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
/

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
/

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
/

• Automated Workload Repository (awr)

DEGREE in dba_* views is VARCHAR!

Object parallelism ignoring degree 0 or 1

SELECT
  owner,
  INDEX_NAME,
  degree
FROM
  DBA_INDEXES
WHERE
  NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' )
;

Object parallelism ignoring degree 0 or 1

SELECT OWNER,
    TABLE_NAME,
    DEGREE
FROM
    DBA_TABLES
WHERE
    NOT RTRIM(LTRIM(DEGREE)) IN ( 'DEFAULT', '1', '0' )
;

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 ;

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.

CREATE DIRECTORY <IDENTIFIER> AS '/real/path/of/disk';

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
;

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 of BUFSIZE 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 with BUFINFO 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 by CURRENT is captured while system is running. RDMEMBLKS in that row stands for the number of blocks actually read from log buffer in memory, and RDDISKBLKS stands for the number of blocks actually read from ORL on disk. The HITRATE 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)

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)
/

ALTER system checkpoint ;

Stop/shutdown de emergencia cuando nada más funciona:

sqlplus -prelim "/ as sysdba"
shutdown abort

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>

shutdown abort;
startup mount exclusive RESTRICT; 
DROP DATABASE;
exit ;

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;

DATABASE LINKS!

sqlplus SYSTEM

CREATE DATABASE LINK VOXEL CONNECT TO SYSTEM IDENTIFIED BY ******* USING 'ORIGEN';

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
/

DROP DATABASE LINK <link_name>;

o

DROP PUBLIC DATABASE LINK <link_name>;

Para insertar un comentario multi-linea:

• Habilitar las blanklines:

SET SQLBLANKLINES ON

• Insertar el comentario:

COMMENT ON TABLE TESTTABLE IS 'TEST
 
MULTILINE
 
COMMENT!
'
;

• Official reference
• Regexp in oracle#1
• Regexp in oracle#2
• Regexp in oracle#3

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
;

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
;

• Auditing Mini-HOWTO

• Documento

• RMAN basic querys

• CommandLine tools para el control de Oracle

See here

• How To Find The Size Of A Number Of Rows Of A Table (Doc ID 1370050.1)