четверг, 20 сентября 2018 г.

План запроса

При реальном выполнении запроса, во время hard parse, происходит peeks bind values.
Explain plan и SQL*Plus autotrace - do not peek bind values, когда они порождают планы.

А также Explain plan и SQL*Plus autotrace не учитывают преобразование типов данных.
Например, если столбец символьного типа сравнивается с числом, происходит неявное
преобразование типов и индекс столбца не может использоваться.


Получить реальный план выполнения запроса можно из представления v$sql_plan.

Определяем hash_value, address, child_number, sql_id, plan_hash_value

SELECT hash_value, address, child_number, sql_id, plan_hash_value
FROM v$sql
WHERE  sql_text LIKE '%select sysdate from dual%'
AND sql_text NOT LIKE '%v_sql%';


Получить план можно так:


По hash_value:

SELECT
   lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
   pt.cost,
   pt.cardinality,
   pt.bytes,
   pt.cpu_cost,
   pt.io_cost,
   pt.temp_space,
   pt.access_predicates,
   pt.filter_predicates,
   pt.qblock_name as "Query Block name"
FROM (select *
             from v$sql_plan
             where HASH_VALUE = 2343063137
             and ADDRESS =  '00007FF7C88C4128'
             and  CHILD_NUMBER = 0
           ) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;


Или по sql_id:

SELECT
   lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' || pt.object_name "Plan Access Path",
   pt.cost,
   pt.cardinality,
   pt.bytes,
   pt.cpu_cost,
   pt.io_cost,
   pt.temp_space,
   pt.access_predicates,
   pt.filter_predicates,
   pt.qblock_name as "Query Block name"
FROM (select *
             from v$sql_plan
             where   sql_id =  '7h35uxf5uhmm1'
             and child_number = 0
            ) pt
CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;



Получить план из AWR по sql_id и plan_hash_value:

SELECT lpad(' ', 2 * level) || pt.operation || ' ' || pt.options || ' ' ||
       pt.object_name "Query Plan",
       pt.cost,
       pt.cardinality,
       pt.bytes,
       pt.cpu_cost,
       pt.io_cost,
       pt.temp_space,
       pt.access_predicates,
       pt.filter_predicates,
       pt.qblock_name
 FROM (select *
              from dba_hist_sql_plan
              where sql_id = '7h35uxf5uhmm1'
              and plan_hash_value = 1388734953
        ) pt
 CONNECT BY PRIOR pt.id = pt.parent_id
START WITH pt.id = 0;



Для получения плана запроса удобно использовать пакет DBMS_XPLAN

Следующие функции этого пакета :

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id            IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no   IN  NUMBER    DEFAULT  0,
   format            IN  VARCHAR2  DEFAULT  'TYPICAL');


DBMS_XPLAN.DISPLAY_AWR(
   sql_id            IN      VARCHAR2,
   plan_hash_value   IN      NUMBER DEFAULT NULL,
   db_id             IN      NUMBER DEFAULT NULL,
   format            IN      VARCHAR2 DEFAULT TYPICAL);


DBMS_XPLAN.DISPLAY_SQLSET(
   sqlset_name      IN  VARCHAR2,
   sql_id           IN  VARCHAR2,
   plan_hash_value  IN NUMBER := NULL,
   format           IN  VARCHAR2  := 'TYPICAL',
   sqlset_owner     IN  VARCHAR2  := NULL)
  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;


DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
   sql_handle      IN VARCHAR2 := NULL,
   plan_name       IN VARCHAR2 := NULL,
   format          IN VARCHAR2 := 'TYPICAL')
  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;


DBMS_XPLAN.DISPLAY_SQL_PATCH_PLAN(
   name             IN VARCHAR2,
   format           IN VARCHAR2 DEFAULT 'TYPICAL')
  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;


DBMS_XPLAN.DISPLAY_SQL_PROFILE_PLAN(
   name             IN VARCHAR2,
   format           IN VARCHAR2 DEFAULT 'TYPICAL')
  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;



Параметр format управляет уровнем детализации плана.


basic - отображает минимальную информацию о плане выполнения.

- id операции
- имя операции 
- опции

typical (default) - отображает  самую актуальную информацию о плане выполнения.

Тоже что и basic плюс

- #rows
- #bytes
- cost

Также отображается информация

- parallel
- predicate

Исключается тоько информация

- projection
- alias
- remote sql


serial - отображает тоже что и typical

Но не отображает в плане

- parallel

даже если запрос выполняется параллельно.


all - максимальный уровень

Тоже что и typical плюс

- projection
- alias
- remote sql


advanced - отображает тоже что и all

Плюс информацию об используемых Outline (наборы подсказок)
из поля other_xml представления v$sql_plan_statistics_all


adaptive - отображает всё

плюс все потенциальные варианты адаптивного плана выполнения курсора



Примеры:

format => 'basic'

format => 'ALL +peek_binds -alias -predicate'
(т.е. можно добавлять или удалять выводимую информацию)

Например к уровню basic можно добавить вывод дополнительных столбцов столбцов:

format => 'basic +rows +bytes +cost +parallel +partition'

Хотя уровнь all всё это и так включает.

basic +rows +bytes +cost +parallel = typical без time
typical +projection +alias +remote = all
all +outline = advanced


Дополнительно можно добавить

+peeked_binds  - выводит дополнительную секцию со значениями связанных переменных,
                 которые оптимизатор учитывает при подготовке плана выполнения.

+report  - для адаптивного плана

+metrics  - для 12с могут быть показаны спискм доступных/используемых SQL Plan Directives


Чтобы дополнительно добавить столбцы со статистикой ввода-вывода и использования памяти нужно добавить

+iostats
+memstats

или просто ALLSTATS


format => 'advanced allstats'


По умолчанию статистика в плане указана для всех выполнений курсора.
Чтобы получить статистику только для последнего выполнения, используйте ключевое слово last.

format => 'advanced allstats last'


select * from table (dbms_xplan.display_cursor('adcv4sbhvh2rv',0,'ADVANCED ALLSTATS LAST +peeked_binds +report +metrics'));



Значения столбцов статистики плана выполнения


starts - количество выполнений операции

e-rows - ожидаемое количество строк при каждом выполнении операции

e-time - ожидаемое время выполнения

a-rows - наблюдаемое суммарное количество строк за время всех выполнений операций

a-time - наблюдаемое время выполнения


При добавлении дополнительных столбцов со статистикой ввода-вывода и использования памяти (allstats)
появятся следующие поля:

buffers   - consistent gets + current gets

reads     - physikal reads

0Mem      - сколько потребуется памяти для обработки курсора полностью в памяти (v$sql_workarea.estimated_optimal_size)

1Mem      - сколько потребуется памяти для обработки курсора за один проход (v$sql_workarea.estimated_onepass_size)

used-mem  - реальное значение памяти использованной в течении последнего выполнения курсора (v$sql_workarea.last_memory_used)



Для получения столбцов со статистикой ввода-вывода (allstats) необходимо установить значение параметра statistics_level = ALL.


alter system set statistics_level = all;

или
alter session set statistics_level = all;

наш запрос:
select count(*) from emp;

смотрим план:
select * from table (dbms_xplan.display_cursor('0vx5dc8jftang',1,'ADVANCED ALLSTATS LAST +peeked_binds +report +metrics'));


|Id|Operation|Name|Starts|E-Rows|E-Bytes|Cost (%CPU)|E-Time|A-Rows|A-Time|Buffers|OMem|1Mem|Used-Mem|


Иначе в плане мы не увидим некоторых столбцов:

|Id|Operation|Name|E-Rows|E-Bytes|Cost (%CPU)|E-Time|OMem|1Mem|Used-Mem|


Хинт gather_plan_statistics используется для сбора расширенной статистики выполнения запроса в соответствии с планом выполнения.
Действует аналогично установке значения параметра statistics_level = all или _rowsource_execution_statistics = TRUE на уровне сессии или системы.
Данные по статистике плана выполнения при этом сохраняются в обзоре:
v$sql_plan_statistics_all


set linesize 150
set pagesize 2000
set serveroutput OFF

select /*+ gather_plan_statistics */ count(*) from t;

смотрим план:
select * from table (dbms_xplan.display_cursor('0vx5dc8jftang',1,'ADVANCED ALLSTATS LAST +peeked_binds +report +metrics'));





вторник, 18 сентября 2018 г.

Как восстановить и открыть базу данных


Сценарий 1: доступен Current Controlfile



1) Убедитесь, что Instance is Mounted and ALL Datafiles ONLINE

    Если используется CURRENT CONTROLFILE, вы можете запустить  recover database, и она будет
    применять все архивные файлы и онлайн-текущий редолог, если они доступны, после чего вы можете открыть базу данных.

    Пример:

SQL> select name, controlfile_type from v$database ;

   NAME      CONTROL
   --------- -------
   V1123     CURRENT

   SQL> recover automatic database ;
    ..
   Media recovery complete   
   SQL> alter database open


Сценарий 2: Используем Backup Controlfile



 SQL> select name, controlfile_type from v$database ;

NAME      CONTROL
--------- -------
V1123     BACKUP      


select status,
       resetlogs_change#,
       resetlogs_time,
       checkpoint_change#,
       to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
       count(*)
  from v$datafile_header
 group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time
 order by status, checkpoint_change#, checkpoint_time ;


STATUS  RESETLOGS_CHANGE# RESETLOGS_TIME       CHECKPOINT_CHANGE# CHECKPOINT_TIME        COUNT(*)
------- ----------------- -------------------- ------------------ -------------------- ----------
ONLINE             995548 15-FEB-2012:17:17:20            2446300 13-FEB-2013 15:09:44          1 
ONLINE             995548 15-FEB-2012:17:17:20            2472049 13-FEB-2013 16:02:22          6

Файлы данных имеют разные значения checkpoint_change# (scn), т.е. в несогласованном состоянии.




-- Check for datafile status, and fuzziness
select STATUS, ERROR, FUZZY,  count(*)  from v$datafile_header group by STATUS, ERROR, FUZZY;

STATUS  ERROR                                                      FUZ   COUNT(*)
------- ----------------------------------------------------------------- --- ----------
ONLINE                                                                    YES          7

Существуют файлы в статусе FUZZY




-- Check for MIN, and MAX SCN in Datafiles
select min(CHECKPOINT_CHANGE#), max(CHECKPOINT_CHANGE#) from v$datafile_header;

MIN(CHECKPOINT_CHANGE#) MAX(CHECKPOINT_CHANGE#)
----------------------- -----------------------
                2446300                 2472049


Посмотрим информацию об  online redolog:


select  substr(L.GROUP#,1,6)          GROUP#
       ,substr(L.THREAD#,1,7)         THREAD#
       ,substr(L.SEQUENCE#,1,10)      SEQUENCE#
       ,substr(L.MEMBERS,1,7)         MEMBERS
       ,substr(L.ARCHIVED,1,8)        ARCHIVED
       ,substr(L.STATUS,1,10)         STATUS
       ,substr(L.FIRST_CHANGE#,1,16)  FIRST_CHANGE#
       ,substr(LF.member,1,60)        REDO_LOGFILE
 from GV$LOG L, GV$LOGFILE LF
where L.GROUP# = LF.GROUP# ;

GROUP# THREAD# SEQUENCE#  MEMBERS ARC STATUS     FIRST_CHANGE#    REDO_LOGFILE
------ ------- ---------- ------- --- ---------- ---------------- ------------------------------------------------------------
1      1       454        1       NO  CURRENT    2471963          /u01/app/oracle/oradata/V1123/redo01.log  
3      1       453        1       YES INACTIVE   2471714          /u01/app/oracle/oradata/V1123/redo03.log
2      1       452        1       YES INACTIVE   2451698          /u01/app/oracle/oradata/V1123/redo02.log


Мы видим, что текущий Current Redolog SEQ# 454 доступен.
Найдём начальный first SEQ# 'number' и archivelog file, необходимый для восстановления.
Вся цепочка файлов от first SEQ# 'number' до Current Redolog SEQ# 454 должна быть доступна для восстановления.


Чтобы найти first SEQ# 'number' используем ранее полученный MIN(CHECKPOINT_CHANGE#) 2446300 :

select thread#, sequence#, substr(name,1,80) from v$Archived_log
 where 2446300 between first_change# and next_change#;

   THREAD#  SEQUENCE# SUBSTR(NAME,1,80)
---------- ---------- --------------------------------------------------------------------------------
         1        449 /u01/app/oracle/oradata/V1123/arch1/arch_1_449_775329440.arc
         1        449 /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_449_8kq7oc6y_.arc
         1        450 /u01/app/oracle/oradata/V1123/arch1/arch_1_450_775329440.arc
         1        450 /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc


Т.е. для восстановления должны быть доступны все archivelogs начиная с /u01/app/oracle/oradata/V1123/arch1/arch_1_449_775329440.arc  SEQ# 449


select * from v$recover_file;     -- Checking for Datafile(s) which needs recovery

FILE# ONLINE  ONLINE_ ERROR                                                    CHANGE# TIME
----- ------- ------- ----------------------------------------------------------------- ---------- --------------------
    6 ONLINE  ONLINE                                                                       2446300 13-FEB-2013:15:09:44


Как видим data file 6 нуждается в восстановлении.



Если вы используете «BACKUP CONTROLFILE» или ранее использовали команду восстановления с UNTIL CANCEL
то вам нужно восстановиться и «вручную» применить online current redolog..

SQL> select name, controlfile_type from v$database;

NAME      CONTROL
--------- -------
V1123     BACKUP


Запускаем восстановление:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 2446300 generated at 02/13/2013 15:09:44 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc
ORA-00280: change 2446300 for thread 1 is in sequence #450

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2451694 generated at 02/13/2013 16:00:25 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_451_8kqbnbmh_.arc
ORA-00280: change 2451694 for thread 1 is in sequence #451
ORA-00278: log file '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_450_8kqbn929_.arc' no longer needed for this recovery
...
 < all required logs applied >
...

ORA-00279: change 2471963 generated at 02/13/2013 16:02:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc
ORA-00280: change 2471963 for thread 1 is in sequence #454
ORA-00278: log file '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_453_8kqbqvrk_.arc' no longer needed for this recovery   < - All Redo, up to and including SEQ# 453 is applied


ORA-00308: cannot open archived log '/u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc'    < - "SEQ# 454" requested, which is in ONLINE REDOLOG as seen before
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/V1123/system01.dbf'


Все archivelogs, включая SEQ# 453 были применены.
Процесс восстановления запрашивает следующий "SEQ# 454", который содержится в ONLINE REDOLOG.


select * from v$recover_file;

FILE# ONLINE  ONLINE_ ERROR   CHANGE# TIME
----- ------- ------- ------  ------- --------------------
    6 ONLINE  ONLINE          2471963 13-FEB-2013:16:02:19


Как видим data file 6 попрежнему нуждается в восстановлении.


Пробуем открыть:

alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/V1123/system01.dbf'

Не получается.


Следующий запрос покажет вам SCN, к которому мы, по крайней мере, должны восстановиться,
чтобы все файлы данных были согласованы.

select  min(FHSCN) "LOW FILEHDR SCN"
      , max(FHSCN) "MAX FILEHDR SCN"
      , max(FHAFS) "Min PITR ABSSCN"
  from X$KCVFH;

LOW FILEHDR SCN  MAX FILEHDR SCN  Min PITR ABSSCN
---------------- ---------------- ----------------
2446300          2472049          0


LOW FILEHDR SCN - SCN с которого начинается recovery process
MAX FILEHDR SCN - SCN до которого необходимо восстановиться чтобы все файлы данных были согласованы.

Если  "Min PITR ABSSCN"  != 0 AND  >  "MAX FILEHDR SCN"
то "Min PITR ABSSCN" - это SCN, до которого необходимо восстановиться чтобы обеспечить согласованность всех файлов данных.

ABSSCN = Absolute SCN


В приведенном выше примере  мы использовали redo (archivelogs), для восстановления файла данных 6, но данный файл все еще требует дополнительного восстановления.
Восстановление базы данных не завершилось, потому что текущий online 'current' redolog не применяется автоматически с 'BACKUP' controlfile.

Поскольку мы используем Backup Controlfile, мы должны «вручную» применять online 'current' redolog  /u01/app/oracle/oradata/V1123/redo01.log,
имеющий SEQ#454 (SCN 2472049)!


Поскольку мы восстанавливаемся с помощью Backup Controlfile или
Controlfile созданного из Tracefile (sql> alter database backup to trace;),
то запрос v$log/v$logfile, не даст правильной информации, какой logfile содержит необходимый номер последовательности (seq#).

Чтобы найти  Online log, который будет использоваться для восстановления:

   a: Проверьте файл Alert.log на последние sequences, используемые с 'Online Redolog files'

   b: Если Alert.log потерян, вы можете просто попробовать все онлайн-файлы redolog, если выбран неправильный файл журнала, ничего не будет применено,
      но вы увидите в выходном сообщении, какая последовательность находится в этом онлайн-файле redolog.
      Затем просто попробуйте следующий онлайн-файл redolog, пока не получите сообщение «media recovery complete».

   c: Вы также можете создать dump the file log file headers for Online redolog file(s).
      Пример:
      --------
      sql> alter system dump logfile '/u01/app/oracle/oradata/V1123/redo01.log' scn min 1 scn max 1;

      - Получим файл трассировки с дампом заголовка в папку «trace» (11g) [или udump (<= 10g)]
      - Найдите в файле трассировки аналогичные записи, например ...
      ~~~
      ..
       descrip: "Thread 0001, Seq # 0000000454 ...
       ..
       Low scn: 0x .....
       Next scn: 0x .....
      ..
      ~~~


Продолжим восстановление вручную:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
.
ORA-00279: change 2471963 generated at 02/13/2013 16:02:19 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fra/V1123/archivelog/2013_02_13/o1_mf_1_454_%u_.arc
ORA-00280: change 2471963 for thread 1 is in sequence #454


Specify log: {=suggested | filename | AUTO | CANCEL}
'/u01/app/oracle/oradata/V1123/redo01.log'                  < - specify the online redologfile having SEQ# 454 to be manually applied
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.


Если после применения всех archive logs и online redologfile база данных не открывается
пожалуйста, предоставьте следующий вывод сценария в поддержку Oracle, чтобы помочь в восстановлении.
(Загрузите файл: recovery_info.txt)


set pagesize 20000
     set linesize 180
     set pause off
     set serveroutput on
     set feedback on
     set echo on
     set numformat 999999999999999

     Spool recovery_info.txt
     select substr(name, 1, 50), status from v$datafile;
     select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
     select GROUP#,substr(member,1,60) from v$logfile;
     select * from v$recover_file;
     select distinct status from v$backup;
     select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, FHAFS ABSSCN , fhrba_Seq SEQUENCE from x$kcvfh;
     select distinct (fuzzy) from v$datafile_header;
     spool off
     exit;