Как заблокировать/разблокировать статистику таблицы.

Пример показывает: 

- как заблокировать сбор статистики;

- что происходит когда Вы попытаетесь собрать на такой таблице статистику; 

- как разблокировать сбор статистики на таблицу.

Создаём таблицу

SQL> create table test ( x number );

Table created.

Создаём индекс

SQL> create index test_idx on test(x);

Index created.

Запрос возвращает NULL когда статистика не заблокирована 


SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
STATT
—–

Заблокируем сбор статистики на таблице

SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.


Запрос возвращает ALL когда статистика заблокирована

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
STATT
—–
ALL

Пробуем собрать статистику на "залоченной" таблице

SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');

BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;
 *
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

Пробуем собрать статистику на индексе используя analyze

SQL> analyze index ajaffer.test_idx compute statistics;

analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked


Разблокируем сбор статистики на таблице

SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

Использование сессиями ресурсов

# SID - Session ID
# Username - Name of the user
# Statistic - Name of the statistic
# Value - Current value

    select     ses.SID,
        nvl(ses.USERNAME,'ORACLE PROC') username,
        sn.NAME statistic,
        sest.VALUE
    from     v$session ses,
        v$statname sn,
        v$sesstat sest
    where     ses.SID = sest.SID
    and     sn.STATISTIC# = sest.STATISTIC#
    and     sest.VALUE is not null
    and     sest.VALUE != 0           
    order     by ses.USERNAME, ses.SID, sn.NAME

Использование CPU сессиями

# Username - Name of the user
# SID - Session id
# CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

    select     nvl(ss.USERNAME,'ORACLE PROC') username,
        se.SID,
        VALUE cpu_usage
    from     v$session ss,
        v$sesstat se,
        v$statname sn
    where      se.STATISTIC# = sn.STATISTIC#
    and      NAME like '%CPU used by this session%'
    and      se.SID = ss.SID
    order      by VALUE desc

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

1. Ищем имя датафайла(оф) :

SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;

/u01/oradata/orcl/temp01.dbf   TEMP

2. Проверяем есть ли свободное место в сегментах TEMP

SQL> SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
——————————-              ———-            ———-           ———-
TEMP                                     1024              1012              12

Свободно 12 Мб
.
3. Решение проблемы


a) Изменяем размер табличного пространства TEMP

SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/orcl/temp01.dbf’ RESIZE 3072M;

b) Добавляем датафайл в табличное простанство TEMP

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/orcl/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 1024M;

Ищем корень проблемы


1.  Анализируем использование сегментов временного табличного пространства сессиями

SQL> SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

2. Ищем SQL наиболее сильно использующий TEMP

SQL> SELECT  S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;