Администратору в копилку несколько полезных запросов

dbstalker, 31 августа

Сегодня под руку эти администраторские запросы. Это для тех, кто считает, что все нужно делать в командной стороке. Enterprise manager - в топку!

--Проверяет наличие блокировок транзакций
SELECT S1.USERNAME || '@' || S1.MACHINE
       || ' ( SID=' || S1.SID || ' )  IS BLOCKING '
       || S2.USERNAME || '@' || S2.MACHINE || ' ( SID=' || S2.SID || ' ) ' 
       AS  BLOCKING_STATUS, S1.SQL_ID
FROM V$LOCK L1, V$SESSION S1, V$LOCK L2, V$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
      AND L1.BLOCK=1 AND L2.REQUEST > 0
      AND L1.ID1 = L2.ID1
      AND L2.ID2 = L2.ID2;
 -- Получение информации текущего статуса ожидания для всех активных сеансов базы данных
SELECT NVL(A.USERNAME, '(ORACLE)') AS USERNAME,
       A.OSUSER,
       A.SID,
       A.SERIAL#,
       D.SPID AS PROCESS_ID,
       A.WAIT_CLASS,
       A.SECONDS_IN_WAIT,
       A.STATE,
       A.BLOCKING_SESSION,
       A.BLOCKING_SESSION_STATUS,
       A.MODULE,
       TO_CHAR(A.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME
FROM   V$SESSION A,
       V$PROCESS D
WHERE  A.PADDR  = D.ADDR
AND    A.STATUS = 'ACTIVE'
ORDER BY USERNAME, OSUSER;
--Показывает управляющие файлы со структурой использования
SELECT TYPE, RECORDS_USED, RECORDS_TOTAL,
       RECORDS_USED/RECORDS_TOTAL*100 "PCT_USED"
FROM SYS.V_$CONTROLFILE_RECORD_SECTION;
--Показывает статистику по сеансам базы данных
SELECT SE.SID, SES.USERNAME, SES.OSUSER, N.NAME, SE.VALUE
FROM V$STATNAME N, V$SESSTAT SE, V$SESSION SES
WHERE N.STATISTIC# = SE.STATISTIC#
       AND SE.SID = SES.SID 
       AND NAME IN ('CPU used by this session','db block gets','consistent 
       gets','physical reads','free buffer requested',
       'table scans (long tables)','table scan rows gotten','sorts 
       (memory)','sorts (disk)','sorts (rows)',
       'session uga memory max' ,'session pga memory max')
ORDER BY SID, N.STATISTIC#;

--Показывает список всех системных параметров и их значения
SELECT SP.NAME,
    SP.TYPE,
    SP.VALUE,
    SP.ISSES_MODIFIABLE,
    SP.ISSYS_MODIFIABLE,
    SP.isINSTANCE_MODiFIABLE
FROM V$SYSTEM_PARAMETER SP
ORDER BY SP.NAME;


SELECT NVL(S.USERNAME, '(ORACLE)') AS USERNAME,
       S.SID,
       S.SERIAL#,
       SE.EVENT,
       SE.TOTAL_WAITS,
       SE.TOTAL_TIMEOUTS,
       SE.TIME_WAITED,
       SE.AVERAGE_WAIT,
       SE.MAX_WAIT,
       SE.TIME_WAITED_MICRO
FROM V$SESSION_EVENT SE,
     V$SESSION S
WHERE S.SID = SE.SID
      AND S.SID = &1
ORDER BY SE.TIME_WAITED DESC;


--  некоторые показатели производительности и комментарии к значению
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF

SELECT *
FROM V$DATABASE;
PROMPT

DECLARE
    V_VALUE  NUMBER;

    FUNCTION FORMAT(P_VALUE  IN  NUMBER) 
        RETURN VARCHAR2 IS
        BEGIN
            RETURN LPAD(TO_CHAR(ROUND(P_VALUE,2),'990.00') || '%',8,' ') || '  ';
        END;

BEGIN

  -- --------------------------
  -- DICTIONARY CACHE HIT RATIO
  -- --------------------------
  SELECT (1 - (SUM(GETMISSES)/(SUM(GETS) + SUM(GETMISSES)))) * 100
  INTO   V_VALUE
  FROM   V$ROWCACHE;

  DBMS_OUTPUT.PUT('DICTIONARY CACHE HIT RATIO       : ' || FORMAT(V_VALUE));
  IF V_VALUE < 90 THEN
    DBMS_OUTPUT.PUT_LINE('INCREASE SHARED_POOL_SIZE PARAMETER TO BRING VALUE ABOVE 90%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('VALUE ACCEPTABLE.');  
  END IF;

  -- -----------------------
  -- LIBRARY CACHE HIT RATIO
  -- -----------------------
  SELECT (1 -(SUM(RELOADS)/(SUM(PINS) + SUM(RELOADS)))) * 100
  INTO   V_VALUE
  FROM   V$LIBRARYCACHE;

  DBMS_OUTPUT.PUT('LIBRARY CACHE HIT RATIO          : ' || FORMAT(V_VALUE));
  IF V_VALUE < 99 THEN
    DBMS_OUTPUT.PUT_LINE('INCREASE SHARED_POOL_SIZE PARAMETER TO BRING VALUE ABOVE 99%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('VALUE ACCEPTABLE.');  
  END IF;

  -- -------------------------------
  -- DB BLOCK BUFFER CACHE HIT RATIO
  -- -------------------------------
  SELECT (1 - ((PHYS.VALUE) / ((DB.VALUE) + (CONS.VALUE)))) * 100
  INTO   V_VALUE
  FROM   V$SYSSTAT PHYS,
         V$SYSSTAT DB,
         V$SYSSTAT CONS
  WHERE  PHYS.NAME  = 'physical reads'
  AND    DB.NAME    = 'db block gets'
  AND    CONS.NAME  = 'consistent gets';

  DBMS_OUTPUT.PUT('DB BLOCK BUFFER CACHE HIT RATIO  : ' || FORMAT(V_VALUE));
  IF V_VALUE < 89 THEN
    DBMS_OUTPUT.PUT_LINE('INCREASE DB_BLOCK_BUFFERS PARAMETER TO BRING VALUE ABOVE 89%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('VALUE ACCEPTABLE.');  
  END IF;
   -- ---------------
  -- LATCH HIT RATIO
  -- ---------------
  SELECT (1 - (SUM(MISSES) / SUM(GETS))) * 100
  INTO   V_VALUE
  FROM   V$LATCH;

  DBMS_OUTPUT.PUT('LATCH HIT RATIO                  : ' || FORMAT(V_VALUE));
  IF V_VALUE < 98 THEN
    DBMS_OUTPUT.PUT_LINE('INCREASE NUMBER OF LATCHES TO BRING THE VALUE ABOVE 98%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('VALUE ACCEPTABLE.');
  END IF;

  -- -----------------------
  -- DISK SORT RATIO
  -- -----------------------
  SELECT (DISK.VALUE/MEM.VALUE) * 100
  INTO   V_VALUE
  FROM   V$SYSSTAT DISK,
         V$SYSSTAT MEM
  WHERE  DISK.NAME = 'sorts (disk)'
  AND    MEM.NAME  = 'sorts (memory)';

  DBMS_OUTPUT.PUT('DISK SORT RATIO                  : ' || FORMAT(V_VALUE));
  IF V_VALUE > 5 THEN
    DBMS_OUTPUT.PUT_LINE('INCREASE SORT_AREA_SIZE PARAMETER TO BRING VALUE BELOW 5%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('VALUE ACCEPTABLE.');  
  END IF;
 
  -- ----------------------
  -- ROLLBACK SEGMENT WAITS
  -- ----------------------
  SELECT (SUM(WAITS) / SUM(GETS)) * 100
  INTO   V_VALUE
  FROM   V$ROLLSTAT;

  DBMS_OUTPUT.PUT('ROLLBACK SEGMENT WAITS           : ' || FORMAT(V_VALUE));
  IF V_VALUE > 5 THEN
    DBMS_OUTPUT.PUT_LINE('INCREASE NUMBER OF ROLLBACK SEGMENTS TO BRING THE VALUE BELOW 5%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('VALUE ACCEPTABLE.');
  END IF;

  -- -------------------
  -- DISPATCHER WORKLOAD
  -- -------------------
  SELECT NVL((SUM(BUSY) / (SUM(BUSY) + SUM(IDLE))) * 100,0)
  INTO   V_VALUE
  FROM   V$DISPATCHER;

  DBMS_OUTPUT.PUT('DISPATCHER WORKLOAD              : ' || FORMAT(V_VALUE));
  IF V_VALUE > 50 THEN
    DBMS_OUTPUT.PUT_LINE('INCREASE MTS_DISPATCHERS TO BRING THE VALUE BELOW 50%');
  ELSE
    DBMS_OUTPUT.PUT_LINE('VALUE ACCEPTABLE.');
  END IF;
  
END;
/

PROMPT
SET FEEDBACK ON


--Показывает незавершенные транзакции
SELECT A.TERMINAL,A.OSUSER, A.USERNAME
FROM V$SESSION A,V$TRANSACTION B
WHERE B.SES_ADDR = A.SADDR AND 
       A.AUDSID <> USERENV('SESSIONID');
--количество блоков занятых данными таблицы       
       SELECT COUNT (DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ||
         DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "Used"
FROM &table_name;
-- или
SELECT COUNT (DISTINCT SUBSTR(ROWID,1,15)) "Used"
FROM &table_name;

--информацию по всем сеансам базы данных, включая фоновые процессы

SELECT SUBSTR(S.SID,1,3) SID, SUBSTR(S.SERIAL#,1,5) "Session serial",
       SUBSTR(OSUSER,1,8) "Operating system username",SPID "Os process identifier",
       SUBSTR(STATUS,1,3) "ACT=active INA=Inactive",SUBSTR(COMMAND,1,3) "Command number",
       SUBSTR(SCHEMANAME,1,10) "Oracle username",
       SUBSTR(TYPE,1,3) "USE=user BAC=background",
       SUBSTR(DECODE((CONSISTENT_GETS+BLOCK_GETS),0,'NONE',
        (100*(CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS)/
        (CONSISTENT_GETS+BLOCK_GETS))
        ),1,4) "Hit ratio in percent",VALUE "CPU being used",
       SUBSTR(BLOCK_CHANGES,1,5) "Block changes",
       SUBSTR(CONSISTENT_CHANGES,1,5) "Consistent changes"
FROM V$PROCESS P, V$SESSTAT T,V$SESS_IO I ,V$SESSION S
WHERE I.SID=S.SID AND P.ADDR=PADDR(+) AND 
       S.SID=T.SID AND T.STATISTIC#=12;
       
       
 -- Показывает информацию по блокировкам всех сеансов к базе данных
  --Если блокировки пристуствуют, то отображается в виде иерархии.
--Требуется доступ к представлениям V$

SELECT LPAD(' ', (level-1)*2, ' ') || NVL(S.USERNAME, '(oracle)') AS USERNAME,
       S.OSUSER,
       S.SID,
       S.SERIAL#,
       S.LOCKWAIT,
       S.STATUS,
       S.MODULE,
       S.MACHINE,
       S.PROGRAM,
       TO_CHAR(S.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIME
FROM   V$SESSION S
CONNECT BY PRIOR S.SID = S.BLOCKING_SESSION
START WITH S.BLOCKING_SESSION IS NULL;

--Показывает информацию по IO для каждого файла данных

SELECT SUBSTR(D.NAME,1,60) "FILE NAME",
       F.PHYBLKRD "BLOCKS READ",
       F.PHYBLKWRT "BLOCKS WRITEN",
       F.PHYBLKRD + F.PHYBLKWRT "TOTAL I/O"
FROM V$FILESTAT F,
     V$DATAFILE D
WHERE D.FILE# = F.FILE#
ORDER BY F.PHYBLKRD + F.PHYBLKWRT DESC;

-- Показывает блокировки объектов и пользователя который их держит

SELECT L.SID, S.USERNAME, L.TYPE,
       L.ID1, L.ID2, L.LMODE, L.REQUEST,
       L.CTIME, O.OBJECT_NAME
FROM V$LOCK L, V$SESSION S, V$LOCKED_OBJECT B, OBJ O
WHERE L.SID = S.SID AND S.USERNAME <> ' ' AND 
       L.SID = B.SESSION_ID AND B.OBJECT_ID = O.OBJECT_ID
ORDER BY 1;

-- Показывает активность пользователей в базе данных
SELECT OSUSER "O/S|USER", USERNAME "ORACLE|USERID", SEGMENT_NAME "R-S|NAME",
       SA.SQL_TEXT "CURRENT STATEMENT"
FROM V$SESSION S, V$TRANSACTION T, DBA_ROLLBACK_SEGS R, V$SQLAREA SA
WHERE S.TADDR = T.ADDR
    AND T.XIDUSN = R.SEGMENT_ID(+)
    AND S.SQL_ADDRESS = SA.ADDRESS(+)
--Показывает 20 самых активных пользователей    
SELECT NVL(S.USERNAME, 'ORACLE PROCESS') USERNAME, 
       S.SID SESSION_ID,
       S.FIXED_TABLE_SEQUENCE RATING
FROM V$SESSION S 
WHERE S.TYPE = 'USER' AND ROWNUM <= 20
ORDER BY 3 DESC;

-- Отчет о всех файлах данных, активных журналах повторного выполнения и
-- управляющих файлах базы данных

SELECT
    D.TABLESPACE_NAME         TABLESPACE,
    D.FILE_NAME               FILENAME,
    D.BYTES                   FILESIZE,
    D.AUTOEXTENSIBLE          AUTOEXTENSIBLE,
    D.INCREMENT_BY * E.VALUE  INCREMENT_BY,
    D.MAXBYTES                MAXBYTES
FROM
    SYS.DBA_DATA_FILES D,
    V$DATAFILE V,
    (SELECT VALUE
     FROM V$PARAMETER 
     WHERE NAME = 'db_block_size') E
WHERE
    (D.FILE_NAME = V.NAME)
UNION
SELECT
    D.TABLESPACE_NAME         TABLESPACE, 
    D.FILE_NAME               FILENAME,
    D.BYTES                   FILESIZE,
    D.AUTOEXTENSIBLE          AUTOEXTENSIBLE,
    D.INCREMENT_BY * E.VALUE  INCREMENT_BY,
    D.MAXBYTES                MAXBYTES
FROM
    SYS.DBA_TEMP_FILES D,
    (SELECT VALUE
     FROM V$PARAMETER 
     WHERE NAME = 'db_block_size') E
UNION
SELECT
    '[ ONLINE REDO LOG ]',
    A.MEMBER,
    B.BYTES,
    NULL,
    TO_NUMBER(NULL),
    TO_NUMBER(NULL)
FROM
    V$LOGFILE A,
    V$LOG B
WHERE
    A.GROUP# = B.GROUP#
UNION
SELECT
    '[ CONTROL FILE    ]',
    A.NAME,
    TO_NUMBER(NULL),
    NULL,
    TO_NUMBER(NULL),
    TO_NUMBER(NULL)
FROM
    V$CONTROLFILE A
ORDER BY 1,2

-- Предоставляет отчет по всем установленным опциям Oracle

SELECT PARAMETER,
       VALUE
FROM   V$OPTION
ORDER BY PARAMETER;

-- Для работы скрипта требуются привелегии 
-- DBA или CREATE ANY TRIGGER и
-- GRANT SELECT ON SYS.V_$SESSION


DROP TRIGGER LOG_ERRORS_TRIG;
DROP TABLE   LOG_ERRORS_TAB;

CREATE TABLE LOG_ERRORS_TAB (
	ERROR     VARCHAR2(30),
	TIMESTAMP DATE,
	USERNAME  VARCHAR2(30),
        OSUSER    VARCHAR2(30),
        MACHINE   VARCHAR2(64),
	PROCESS   VARCHAR2(8),
	PROGRAM   VARCHAR2(48));

CREATE OR REPLACE TRIGGER LOG_ERRORS_TRIG 
	AFTER SERVERERROR ON DATABASE
DECLARE
	VAR_USER     VARCHAR2(30);
	VAR_OSUSER   VARCHAR2(30);
	VAR_MACHINE  VARCHAR2(64);
	VAR_PROCESS  VARCHAR2(8);
	VAR_PROGRAM  VARCHAR2(48);
BEGIN
	SELECT USERNAME, OSUSER, MACHINE, PROCESS, PROGRAM
	INTO   VAR_USER, VAR_OSUSER, VAR_MACHINE, VAR_PROCESS, VAR_PROGRAM
	FROM   SYS.V_$SESSION
	WHERE  AUDSID = USERENV('SESSIONID');

	INSERT INTO LOG_ERRORS_TAB
	  VALUES(DBMS_STANDARD.SERVER_ERROR(1),SYSDATE,VAR_USER,
	         VAR_OSUSER,VAR_MACHINE,VAR_PROCESS,VAR_PROGRAM);
END;

ОднаКнопка

4 комментария

Прокоментировать

Мар
1 сентября 2009 г. в 09:46

Если бы ещё и комментарии были бы читабельными...

dbstalker
2 сентября 2009 г. в 09:08

Спасибо за внимание. исправилось вроде.

Anonymous
2 сентября 2009 г. в 07:58

прикольный код
--Iieacuaaao eioi?iaoe? ii IO aey ea?aiai oaeea aaiiuo

dbstalker
2 сентября 2009 г. в 09:10

Не спорю - прикольный. Только не код, а комментарий! :)

 

Новый комментарий

Я не спамер: введите суму 4+7



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Телепрограмма
23 июня, 1 ответа
Турция
23 июня, 4 ответа
Выбор люстры
22 июня, 1 ответа