Fast access to dba_hist_active_sess_history

dba_hist_active_sess_history view code

create or replace view sys.dba_hist_active_sess_history
(snap_id, dbid, instance_number, sample_id, sample_time, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, top_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, session_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, top_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_compilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ecid, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes, delta_interconnect_io_bytes, pga_allocated, temp_space_allocated)
as
select /* ASH/AWR meta attributes */
       ash.snap_id, ash.dbid, ash.instance_number,
       ash.sample_id, ash.sample_time,
       /* Session/User attributes */
       ash.session_id, ash.session_serial#,
       decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
       ash.flags,
       ash.user_id,
       /* SQL attributes */
       ash.sql_id,
       decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
       ash.sql_child_number, ash.sql_opcode,
       (select command_name from DBA_HIST_SQLCOMMAND_NAME
        where command_type = ash.sql_opcode
        and dbid = ash.dbid) as sql_opname,
       ash.force_matching_signature,
       decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
       decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
              ash.top_level_sql_opcode),
       /* SQL Plan/Execution attributes */
       ash.sql_plan_hash_value,
       decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
       (select operation_name from DBA_HIST_PLAN_OPERATION_NAME
        where  operation_id = ash.sql_plan_operation#
          and  dbid = ash.dbid) as sql_plan_operation,
       (select option_name from DBA_HIST_PLAN_OPTION_NAME
        where  option_id = ash.sql_plan_options#
          and  dbid = ash.dbid) as sql_plan_options,
       decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
       ash.sql_exec_start,
       /* PL/SQL attributes */
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_object_id),
       decode(ash.plsql_entry_object_id,0,to_number(NULL),
              ash.plsql_entry_subprogram_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_object_id),
       decode(ash.plsql_object_id,0,to_number(NULL),
              ash.plsql_subprogram_id),
       /* PQ attributes */
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
       decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
       decode(ash.px_flags,      0, to_number(NULL), ash.px_flags),
       /* Wait event attributes */
       decode(ash.wait_time, 0, evt.event_name, NULL),
       decode(ash.wait_time, 0, evt.event_id,   NULL),
       ash.seq#,
       evt.parameter1, ash.p1,
       evt.parameter2, ash.p2,
       evt.parameter3, ash.p3,
       decode(ash.wait_time, 0, evt.wait_class,    NULL),
       decode(ash.wait_time, 0, evt.wait_class_id, NULL),
       ash.wait_time,
       decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
       ash.time_waited,
       (case when ash.blocking_session = 4294967295
               then 'UNKNOWN'
             when ash.blocking_session = 4294967294
               then 'GLOBAL'
             when ash.blocking_session = 4294967293
               then 'UNKNOWN'
             when ash.blocking_session = 4294967292
               then 'NO HOLDER'
             when ash.blocking_session = 4294967291
               then 'NOT IN WAIT'
             else 'VALID'
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_session_serial#
        end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then to_number(NULL)
             else ash.blocking_inst_id
          end),
       (case when ash.blocking_session between 4294967291 and 4294967295
               then NULL
             else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
                         0, 'N', 'Y')
          end),
       /* Session's working context */
       ash.current_obj#, ash.current_file#, ash.current_block#,
       ash.current_row#, ash.top_level_call#,
       (select top_level_call_name from DBA_HIST_TOPLEVELCALL_NAME
        where top_level_call# = ash.top_level_call#
        and dbid = ash.dbid) as top_level_call_name,
       decode(ash.consumer_group_id, 0, to_number(NULL),
              ash.consumer_group_id),
       ash.xid,
       decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
       ash.time_model,
       decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
                                                         as in_connection_mgmt,
       decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
       decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
       decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
       decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
                                                         as in_plsql_execution,
       decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
       decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
                                                       as in_plsql_compilation,
       decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
                                                       as in_java_execution,
       decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
       decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
       decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
       decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
                                                       as capture_overhead,
       decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
                                                           as replay_overhead,
       decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
       decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
       /* Application attributes */
       ash.service_hash, ash.program,
       substrb(ash.module,1,(select ksumodlen from x$modact_length)) module,
       substrb(ash.action,1,(select ksuactlen from x$modact_length)) action,
       ash.client_id,
       ash.machine, ash.port, ash.ecid,
       /* DB Replay info */
       ash.dbreplay_file_id, ash.dbreplay_call_counter,
       /* stash columns */
       ash.tm_delta_time,
       ash.tm_delta_cpu_time,
       ash.tm_delta_db_time,
       ash.delta_time,
       ash.delta_read_io_requests,
       ash.delta_write_io_requests,
       ash.delta_read_io_bytes,
       ash.delta_write_io_bytes,
       ash.delta_interconnect_io_bytes,
       ash.pga_allocated,
       ash.temp_space_allocated
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where      ash.snap_id          = sn.snap_id(+)
      and  ash.dbid             = sn.dbid(+)
      and  ash.instance_number  = sn.instance_number(+)
      and  ash.dbid             = evt.dbid
      and  ash.event_id         = evt.event_id;
comment on table SYS.DBA_HIST_ACTIVE_SESS_HISTORY is 'Active Session Historical Statistics Information';

WRH$_ACTIVE_SESSION_HISTORY partitioned by DBID and SNAP_ID columns, so the fastest way to query this view – using snap_id ranges.
You can determine right snap_id from dba_hist_snapshot view.

This query uses partition pruning and run fast

select * from dba_hist_active_sess_history WHERE snap_id >= 12345

and this is not (it usees FTS on WRH$_ACTIVE_SESSION_HISTORY ash)

select * from dba_hist_active_sess_history WHERE sample_time >= SYSTIMESTAMP -1 

Be careful with subqueries. The following queries can also use FTS:

select * from dba_hist_active_sess_history WHERE snap_id >= (SELECT :1 FROM dual);
select * from dba_hist_active_sess_history WHERE snap_id >= (SELECT min(snap_id) FROM Dba_Hist_Snapshot WHERE begin_interval_time >= SYSTIMESTAMP - 1)

So, the safest way is determine snap_id in separate query.

PL/SQL Developer session window

В окне списка сессий PL/SQL Developer (Tools -> Sessions) можно добавить кучу полезных вкладок, которые будут выполнять скрипты и запросы, получая любое значение из подсвеченной сессии в списке через :[ИМЯ_КОЛОНКИ v$session]. В настоящее время в стоковом PL/SQL Developer (версия 11) есть 5 вкладок:
Cursors
SQL Text
Statistics
Locks
Sql Monitor

Добавлять свои вкладки можно при помощи кнопки с гаечным ключиком -> Details

В настоящий момент я использую

План запроса dbms_xplan

Особое внимание /* concatenate */ из последней строчки – результат будет сцеплен в одно поле, его можно скопировать и вставить в другое окно для детального анализа.

SELECT t.plan_table_output || CHR(10) plan_table_output 
FROM table(dbms_xplan.display_cursor(:sql_id, :sql_child_number,format => 'ADVANCED')) t
/* concatenate */

План запроса из v$

SELECT decode(id, 1, child_number) || decode(:sql_address, '00', '-P') AS c,
       output_rows AS tot_r,
       last_output_rows AS r,
       rpad(' ', depth * 3) || operation || ' ' || options ||
       nvl2(object_name, ' -> ', '') || object_name AS op,
       cost,
       cardinality AS card,
       bytes,
       access_predicates AS "ACCESS",
       filter_predicates AS filter,
       round(temp_space / 1024 / 1024) AS temp_mb,
       partition_start || nvl2(partition_start, ' - ', '') || partition_stop AS p,
       partition_id,
       other,
       other_tag,
       cpu_cost,
       io_cost,
       distribution,
       object_owner,
       optimizer,
       position,
       search_columns,
       executions,
       last_starts,
       starts,
       last_cr_buffer_gets,
       cr_buffer_gets,
       last_cu_buffer_gets,
       cu_buffer_gets,
       last_disk_reads,
       disk_reads,
       last_disk_writes,
       disk_writes,
       round(last_elapsed_time / 1000000, 2) AS last_ela_time,
       round(elapsed_time / 1000000, 2) AS elapsed_time,
       policy,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_degree,
       total_executions,
       optimal_executions,
       onepass_executions,
       multipasses_executions,
       round(active_time / 1000000, 2) AS active_time_avg,
       max_tempseg_size,
       last_tempseg_size
FROM (SELECT *
      FROM v$sql_plan_statistics_all
      WHERE address = hextoraw(:sql_address)
      AND hash_value = :sql_hash_value
      UNION ALL
      SELECT *
      FROM v$sql_plan_statistics_all
      WHERE address = hextoraw(:prev_sql_addr)
      AND hash_value = :prev_hash_value) t
CONNECT BY address = PRIOR address
    AND hash_value = PRIOR hash_value
    AND child_number = PRIOR child_number
    AND PRIOR id = parent_id
START WITH id = 1
ORDER BY address, hash_value, child_number DESC, id, position

SQL Workarea

Объем памяти, потребляемой сессией. Правда не очень часто пригождается

SELECT operation_type,
       policy,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_degree,
       total_executions,
       optimal_executions,
       onepass_executions,
       multipasses_executions,
       active_time,
       max_tempseg_size,
       last_tempseg_size
FROM v$sql_workarea
WHERE address = hextoraw(:sql_address)
AND hash_value = :sql_hash_value

Таким способом достаточно удобно заниматься troubleshooting’ом, если известа сессия, которая испытывает проблемы. Очень удобный и мощный механизм.

Copy Outlook folders to another machine

Возникла проблема переноса правил Outlook с машины на машину. Решения, приводимые в интернете с import-export почтового ящика не работают из-за недостатка прав. Единственное решение в этом случае – скрипт на VBA
На основании скрипта выгрузки дописал создание структуры папок.
Ограничения:

• переносятся папки из Inbox в Inbox
• не работают русские буквы в названиях папок
• если папка с таким именем есть, то создание упадет с ошибкой

Ошибки, кроме русских букв, легко устраняются исправлением кода

1. На машине, откуда надо экспортировать файлы заходим в Outlook и оттуда в редактор Visual Basic (Alt-F11).
2. Вставляем в него скрипт, приведенный ниже. Запускаем процедуру ExportFolderNames (F5)
3. В результате работы процедуры на рабочем столе создается файл outlookfolders.txt. Передаем его на машину, где надо создать папки и кладем на рабочий стол
4. На машине-получателе запускаем редактор Visual Basic (Alt-F11), вставляем скрипт и запускаем createFolders
5. Проверяем и радуемся

Текст макроса (особо не причесывал, просто добился работоспособности)

Private myFile As String
Private Structured As Boolean
Private Base As Integer

Private Function getIndent(folderName)
  i = 1
  Do Until Mid(folderName, i, 1) <> "-"
    i = i + 1
  Loop

  getIndent = i - 1

End Function
Public Sub createFolders()
  Dim objNewFolder, objParentFolder As Outlook.Folder
  Dim myFile As String, line, folderName As String

  Dim parentObjects(100) As Outlook.Folder
  ' init for inbox folder
  Set parentObjects(0) = Session.GetDefaultFolder(olFolderInbox)

  myFile = GetDesktopFolder() & "\outlookfolders.txt"
  Open myFile For Input As #1

  Do Until EOF(1)
    Line Input #1, line
     curIndent = getIndent(line)

     If curIndent > 0 Then
       folderName = Right(line, Len(line) - curIndent)
       Set objParentFolder = parentObjects(curIndent - 1)
       'MsgBox ("create folder " + folderName + " in " + objParentFolder.Name + " level=" & curIndent)
       Set objNewFolder = objParentFolder.Folders.Add(folderName)
       Set parentObjects(curIndent) = objNewFolder
     End If
  Loop
  Close #1
  Set objNewFolder = Nothing
  Erase parentObjects

End Sub

Public Sub ExportFolderNames()
  Dim F As Outlook.MAPIFolder
  Dim Folders As Outlook.Folders

  Set F = Session.GetDefaultFolder(olFolderInbox)
  Set Folders = F.Folders

  Dim Result As Integer

  Structured = True


  myFile = GetDesktopFolder() & "\outlookfolders.txt"
  Base = Len(F.FolderPath) - Len(Replace(F.FolderPath, "\", "")) + 1

  WriteToATextFile (StructuredFolderName(F.FolderPath, F.Name))

  LoopFolders Folders

  Set F = Nothing
  Set Folders = Nothing
End Sub


Private Function GetDesktopFolder()
  Dim objShell
  Set objShell = CreateObject("WScript.Shell")
  GetDesktopFolder = objShell.SpecialFolders("Desktop")
  Set objShell = Nothing
End Function

Private Sub LoopFolders(Folders As Outlook.Folders)
  Dim F As Outlook.MAPIFolder

  For Each F In Folders
    WriteToATextFile (StructuredFolderName(F.FolderPath, F.Name))
    LoopFolders F.Folders
  Next
End Sub

Private Sub WriteToATextFile(OLKfoldername As String)
  fnum = FreeFile()

  Open myFile For Append As #fnum
    Print #fnum, OLKfoldername
  Close #fnum
End Sub

Private Function StructuredFolderName(OLKfolderpath As String, OLKfoldername As String) As String
  If Structured = False Then
    StructuredFolderName = Mid(OLKfolderpath, 3)
  Else
    Dim i As Integer
    i = Len(OLKfolderpath) - Len(Replace(OLKfolderpath, "\", ""))

    Dim x As Integer
    Dim OLKprefix As String
    For x = Base To i
      OLKprefix = OLKprefix & "-"
    Next x

    StructuredFolderName = OLKprefix & OLKfoldername
  End If
End Function