понедельник, 19 сентября 2016 г.

Liquibase test: sorting in includeAll

Let we have the following folder structure

folder_01
  subfolder_03
    file_1.sql
    file_2.sql
  subfolder_04
    file_1.sql
    file_2.sql
folder_02
  subfolder_01
    file_1.sql
    file_2.sql
  subfolder_02
    file_1.sql
    file_2.sql
file_01.sql
file_02.sql
zfile.sql

and I have changelog with includeAll:

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
     <includeAll path="/multifolder/"/>
</databaseChangeLog>

the order of inclusion will be following

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: multifolder.xml
-- Ran at: 19.09.16 15:40
-- Against: null@offline:oracle
-- Liquibase version: 3.5.1
-- *********************************************************************

SET DEFINE OFF;

-- Changeset multifolder/file_01.sql::1::includeall
select 'file01' f from dual;

-- Changeset multifolder/file_02.sql::1::includeall
select 'file02' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/1.sql::1::includeall
select 'folder_01subfolder_03_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_03/2.sql::1::includeall
select 'folder_01subfolder_03_file2' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/1.sql::1::includeall
select 'folder_01subfolder_04_file1' f from dual;

-- Changeset multifolder/folder_01/subfolder_04/2.sql::1::includeall
select 'folder_01subfolder_04_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/1.sql::1::includeall
select 'folder_02subfolder_01_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_01/2.sql::1::includeall
select 'folder_02subfolder_01_file2' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/1.sql::1::includeall
select 'folder_02subfolder_02_file1' f from dual;

-- Changeset multifolder/folder_02/subfolder_02/2.sql::1::includeall
select 'folder_02subfolder_02_file2' f from dual;

-- Changeset multifolder/zfile.sql::1::includeall
select 'zfile.sql' f from dual;

So files and folders are sorted together level by level.

One moment you should to keep in mind. If I write

     <includeAll path="multifolder/"/>

instead of

     <includeAll path="/multifolder/"/>

I’ve got Unexpected error running Liquibase: Unknown Reason

среда, 14 сентября 2016 г.

ORA-12154: space is really matter

Got an stupid error ORA-12154: TNS:could not resolve the connect identifier specifed.
Double check tnsnames.ora, sqlnet.ora, TNS_ADMIN – all were correct
This one in tnsnames doesn’t work:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
)
)

and this one works:

TEST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL= TCP)(HOST= myhost)(Port= 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = db1)
 )
)

Difference only in one space in penultimate line.

It’s interesting, that tnsping shows another error

TNS-12533: TNS:illegal ADDRESS parameters

but sqlplus

ORA-12154: TNS:could not resolve the connect identifier specifed

четверг, 8 сентября 2016 г.

Sql*plus login.sql file

SQLPATH/login.sql

The User Profile is executed after the Site Profile and is intended to allow users to specifically customize their session. The User Profile script is generally named login.sql. SQL*Plus searches for the User Profile in your current directory, and then the directories you specify with the SQLPATH environment variable. SQL*Plus searches this colon-separated list of directories in the order they are listed.
Content of my file


set echo off
set termout off
set feedback off
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
define _editor="C:\Program Files\IDM Computer Solutions\UltraEdit-32\Uedit32.exe"
define sqlplprompt=idle
column sqlplprompt noprint new_value sqlplprompt
-- user@connect_identifier
--select LOWER(user) || '@' || '&_CONNECT_IDENTIFIER' sqlplprompt from dual;
-- user@sqlprompt
--select LOWER(user) || '@' || global_name sqlplprompt from global_name;
-- connect identifier only
define sqlplprompt=&_CONNECT_IDENTIFIER
set sqlprompt '&sqlplprompt.>'
ALTER SESSION SET statistics_level = ALL;
set termout on
set feedback on

--set timing ondefine

Also you can use glogin.sql file, but usually we don’t have access to it

The Site Profile script is generally named glogin.sql. SQL*Plus or the iSQL*Plus Server executes this script whenever a user starts a SQL*Plus or iSQL*Plus session and successfully establishes the Oracle Database connection. The Site Profile enables the DBA to set up SQL*Plus environment defaults for all users of a particular SQL*Plus or iSQL*Plus Application Server installation. Users cannot directly access the Site Profile.The Site Profile script is $ORACLE_HOME/sqlplus/admin/glogin.sql in UNIX, and %ORACLE_HOME%\sqlplus\admin\glogin.sql in Windows.

четверг, 1 сентября 2016 г.

dba_hist_sqlstat statistics

Template for PL/SQL developer based on link

Counts execution statistics from dba_hist_sqlstat for given sql_id

select to_char(min(s.end_interval_time),'DD.MM.YYYY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000000),3) sec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
&<name="start_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.end_interval_time >= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
&<name="end_time_dd_mm_YYYY_hh24_mi"
  prefix="and s.begin_interval_time <= to_date(trim('"
  suffix="'),'DD.MM.YYYY hh24:mi')">
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value;

вторник, 30 августа 2016 г.

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.

пятница, 19 августа 2016 г.

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’ом, если известа сессия, которая испытывает проблемы. Очень удобный и мощный механизм.

среда, 3 августа 2016 г.

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

понедельник, 25 июля 2016 г.

Staсkedit hangs on starting

В нижеуказанном Stackedit случилась небольшая неприятность.
После попытки импортировать страницу (Import URL) он наглухо повис и перестал открываться после перезагрузки. Судя по всему пытался отрисовать страницу с большим количеством HTML не преобразованного в маркдаун.
После тщетных попыток c переоткрытиями, запретами скриптов и т.д. были предприняты исследования по результатам которых:

  • база хранится локально в так называемом Web storage
  • в Firefox есть просмоторщик (правда без возможности редактирования) этого Web storage. Редактирование обещают в следующих релизах. Причем под Mozilla есть несколько плагинов, которые позволяют смотреть/редактировать Web storage. Ни один из них так и не заработал, причины были разные. Какой-то уменьшал окно до минимума, какие-то просто висли.
  • При помощи Noscript отключаем скрипты, заходим на https://stackedit.io/
  • Запускаем Storage inspector (Shift-F9, если включен в настройках или открыть панель разработчика и включить в настройках). В Local storage ищем, какие файлы хранятся на компьютере
  • Находим файл, который подвешивает систему, копируем его ключ и вставляем в скрипт очистки
var filename = 'file.q0rBqPMvChLNtkN55HimybX9';

localStorage.removeItem(filename + '.content');
localStorage.removeItem(filename + '.title');
localStorage.removeItem(filename + '.editorEnd');
localStorage.removeItem(filename + '.editorStart');
localStorage.removeItem(filename + '.publish');
localStorage.removeItem(filename + '.selectTime');
localStorage.removeItem(filename + '.sync');
localStorage.removeItem(filename + '.title');
localStorage.setItem('file.list', localStorage.getItem('file.list').replace(';' + filename + ';', ';'));
console.log(localStorage.getItem('file.list'));
  • Запускаем скрипт в консоли и база очищена

Статья, приведшая к зависанию будет удалена!

Вообще Stackedit отличный сервис для публикаций! Вот только в последее время публикация в Blogger работает раз из пяти

Written with StackEdit.

Технология дня

Отличная статья от Toon Koppelaars с перечнем front-end технологий, которые появились за последние несколько лет. И многие из них, как правильно заметил автор

technologies du-jour: hot today, forgotten tomorrow

Надеюсь, что он прав и нам, разработчикам БД, можно расслабиться и продолжать развиваться в своей области.

суббота, 23 июля 2016 г.

DBLINK и сессии

В ходе исследований по loopback links чуть было не попал впросак с отловом сессий, создаваемых для dblink.
Судя по всему Oracle создает сессию для dblink один раз. В доказательство этого сделаем after logon trigger, который будет собирать информацию о подключениях

SQL> DROP TABLE log_session PURGE;

Table dropped.

SQL> CREATE TABLE log_session(username VARCHAR2(30), conn_time timestamp, info VARCHAR2(4000));

Table created.
SQL> CREATE OR REPLACE TRIGGER ta_connect AFTER logon ON DATABASE
  2  BEGIN
  3    INSERT INTO log_session VALUES (USER, SYSTIMESTAMP, NULL);
  4    COMMIT;
  5  END;
  6  /

Trigger created.

SQL> SHOW ERRORS
No errors.
SQL> CONNECT SYSTEM/manager
Connected.
SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         1

Мы подключились, строка вставилась

SQL> SELECT * FROM dual@loopback;

D
-
X

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         2

Первое обращение по dblink, создалась сессия

SQL> SELECT * FROM dual@loopback;

D
-
X

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         2


Второе обращение по dblink – сессия не создалась, используется предыдущая

SQL> SELECT 'Hello' FROM a@loopback WHERE ROWNUM=1;

'HELL
-----
Hello

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         2


Поменяем таблицу – результат тот же. Новой сессии нет

SQL> CONNECT SYSTEM/manager
Connected.
SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         3


Переподключились, теперь счетчик стал 3

SQL> SELECT * FROM dual@loopback;

D
-
X

SQL> SELECT COUNT(*) FROM log_session;

  COUNT(*)
----------
         4

В свежей сессии для dblink выполняется еще одно подключение

Loopback links, ORA-04091 и dirty read в Oracle

При чтении статьи встретилась знакомая по триггерам ошибка mutating table error в неожиданном месте, интересный способ ее обхода и совершенно неожиданные результаты.

CREATE TABLE a(n NUMBER);
Table created
INSERT INTO a VALUES(100);
1 row inserted
INSERT INTO a VALUES(200);
1 row inserted
INSERT INTO a VALUES(300);
1 row inserted
COMMIT;
Commit complete
CREATE OR REPLACE FUNCTION a_avg RETURN NUMBER AS
  l_avg NUMBER;
BEGIN
  SELECT AVG(n) INTO l_avg FROM a;
  dbms_output.put_line('avg=' || l_avg);
  RETURN l_avg;
END a_avg;
/
Function created
SHOW ERRORS
No errors for FUNCTION SYSTEM.A_AVG
UPDATE a SET n = a_avg();
UPDATE a SET n = a_avg()
ORA-04091: table SYSTEM.A is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.A_AVG", line 4

Мы создали простую таблицу и функцию, которая считает среднее значение по этой таблице. С помощью функции мы попробуем усреднить все значения.
В результате мы получаем ORA-04091 без каких-либо триггеров

Далее в статье приводится способ обхода через loopback database link. Модифицируем немного функцию и вставляем @loopback

CREATE OR REPLACE FUNCTION a_avg_loopback RETURN NUMBER AS
  l_avg NUMBER;
BEGIN
  SELECT AVG(n) INTO l_avg FROM a@loopback;
  dbms_output.put_line('avg=' || l_avg);
  RETURN l_avg;
END a_avg_loopback;
/
Function created
SHOW ERRORS
No errors for FUNCTION SYSTEM.A_AVG_LOOPBACK
UPDATE a SET n = a_avg_loopback();
avg=200
avg=233.333333333333333333333333333333333333
avg=244.444444444444444444444444444444444444
3 rows updated
ROLLBACK;
Rollback complete

Ошибка пропала, но… Через loopback мы смогли увидеть данные, которые еще не закоммичены. Этакий dirty read, но скорее всего мы просто присоединяемся к той же транзакции. Причем, как видно, результат для каждой строчки считается с учетом обновленных строк.

UPD: дальнейшее исследование показало, что loopback link вообще не создал отдельной сессии.

UPD2: сессия создается, но ровно 1 раз за сессию. Исследование тут

Классификация ограничений

Наткнулся на очень интересную статью адепта триггеров о констрейнтах.
Классификация вышла такая:

  • Статические ограничения
    • на атрибут – salary > 10000
    • на группу атрибутов – усы могут быть только у мужчин
    • табличные – проверяется несколько строчек таблицы, например уникальные ключи
    • базы данных – проверяются несколько таблиц, например сумма заказа не превышает остатка по счету клиента
  • Динамические ограничения – те, что невозможно проверить по снапшоту базы данных (т.е. запросом или несколькими запросами), они связаны с изменением во времени. Например по каждому документу в течении рабочего дня должен быть выписан акт.

Такие классификации полезны для построения однозначных архитектур, т.к. формализовав правила реализации каждого из видов мы не получим всего многообразия методов :)

воскресенье, 17 июля 2016 г.

Parallel merge

Немножко о parallel merge в упрощенном виде. Для того, что бы часть insert/update шла параллельно необходимо:
1. alter session enable parallel dml;
2. указывать в хинте таблицу в которую мержим

merge /*+ parallel(t1) */ into t1
USING (select c1, c2 from t2) t2
on (t1.c1 = t2.c1)
when matched then
         update set t1.c2 = t1.c2
when not matched then
INSERT(c1, c2) values(t2.c1, t2.c2)

Взято отсюда
К сожалению проблема с прода, когда параллельность не возникала на стенде не воспроизвелась.

среда, 15 июня 2016 г.

Если Merge не использует индекс

Если Merge не использует индекс, то можно попробовать явно указать колонки явно. Т.е. вместо

merge into t1
using t2

написать

merge into (select c1, c2 from t1) t1
using (select c3 from t2) t2

Это же относится и к избыточной работе с temp/памяти для HASH JOIN.
Отличные исследования по этому поводу:
https://alexanderanokhin.wordpress.com/2012/07/18/dont-forget-about-column-projection/
https://jonathanlewis.wordpress.com/2016/06/06/merge-precision/

среда, 4 мая 2016 г.

Capture an Optimizer trace for an already existing SQL statement

Стырено отсюда

If you don’t control the SQL execution then you can still create an Optimizer trace file if you know the SQL_ID of the SQL statement. The SQL_ID can then be added to the DBMS_SQLDIAG.DUMP_TRACE
command (added to DBMS_SQLDIAG in Oracle Database 11g R2). To create an Optimizer trace for any SQL statement that has been run and is in the shared pool.
Note that this procedure will automatically trigger a hard parse of the statement.
The following will show an example for SQL_ID 1n482vfrxw014

begin
DBMS_SQLDIAG.DUMP_TRACE(
p_sql_id=>'1n482vfrxw014',
p_child_number=>0,
p_component=>'Compiler',
p_file_id=>'MY_SPECIFIC_STMT_TRC');
end;
/

After running the procedure above you can find the trace file in the USER_DUMP_DEST directory.
To make it easier to find the trace file you should set the P_FILE_ID parameter to a string that starts with an alphabetic character and does not contain any leading or trailing white space

суббота, 13 февраля 2016 г.

Get saved wi-fi passwords in Windows with PowerShell script

Powershell script which get a list of all saved wi-fi networks with passwords:

function Get-WifiNetworks {
  $networks = netsh wlan show profiles | where {$_ -match '^.*All User Profile.*$'}
  foreach ($network in $networks) {
    $SSID = $network.split(':')[1].Trim()
    $networkInfo = netsh wlan show profiles key=clear name="$SSID"
    $current = @{}
    $current['SSID']=$SSID
    foreach ($infoString in $networkInfo) {
        if ($infoString -match '^\s+(.*)\s+:\s+(.*)\s*$') {
            $current[$matches[1].trim()] = $matches[2].trim()           
        }
    }
    new-object psobject -property $current
  }
}
Get-WifiNetworks | select SSID, "Key Content", Authentication | Format-Table -Wrap -Autosize

пятница, 12 февраля 2016 г.

Узнать сохраненный пароль от WIFI в Windows 10

В командной строке под Администратором

netsh wlan show profiles

Profiles on interface Wi-Fi:

Group policy profiles (read only)
---------------------------------
    <None>

User profiles
-------------
    All User Profile     : asrc_acess point
    All User Profile     : SWEETINN-AP1

Находим нужную сеть и подставляем его в параметр команды

netsh wlan show profiles key=clear name="asrc_acess point"

Profile asrc_acess point on interface Wi-Fi:
=======================================================================

Applied: All User Profile

Profile information
-------------------
    Version                : 1
    Type                   : Wireless LAN
    Name                   : asrc_acess point
    Control options        :
        Connection mode    : Connect automatically
        Network broadcast  : Connect only if this network is broadcasting
        AutoSwitch         : Do not switch to other networks
        MAC Randomization  : Disabled

Connectivity settings
---------------------
    Number of SSIDs        : 1
    SSID name              : "asrc_acess point"
    Network type           : Infrastructure
    Radio type             : [ Any Radio Type ]
    Vendor extension          : Not present

Security settings
-----------------
    Authentication         : WPA2-Personal
    Cipher                 : CCMP
    Security key           : Present
    Key Content            : 1243683176383

Cost settings
-------------
    Cost                   : Unrestricted
    Congested              : No
    Approaching Data Limit : No
    Over Data Limit        : No
    Roaming                : No
    Cost Source            : Default

Пароль в строке Key Content

вторник, 17 ноября 2015 г.

Left and right deep trees

Отличная статья про left deep trees, right deep trees и bushy joins http://www.oaktable.net/content/right-deep-left-deep-and-bushy-joins
Что можно подчерпнуть
1. Для получения right deep trees скорее всего потребуется хинтовать SWAP_JOIN_INPUTS
2. right deep trees могут возвращать результат сразу. Но это потребует несколько больше памяти для создания working sets.
3. Nested loops может быть только left deep
Ну и в картинках очень здорово показано, как работают left и right deep trees

четверг, 22 октября 2015 г.

Перезапуск scheduled tasks на powershell

Скрипт, который для Windows Server 2003 (в котором нет Get-ScheduledTask):
1. Останавливает запущенные задания
2. Переносит логи рекурсивно не сохраняя структуры директорий
3. Запускает остановленные задания
Bat файл для запуска

@ECHO OFF
PowerShell.exe -NoProfile -ExecutionPolicy Bypass -Command "& '%~dpn0.ps1'"
PAUSE

Скрипт Powershell

$Source = "C:\Work\bat\source"
$Target = "C:\Work\bat\target"

Write-Host "Stoping tasks"

$scheduledTasks = schtasks /query /fo csv | ConvertFrom-Csv
$runningTasks =@()

foreach($task in $scheduledTasks) 
    {
 if ($task.Status -eq 'Running' -and $task.taskName -eq 'testTask') {
  $runningTasks += $task.taskName
  Write-Host $task.taskName " will be stopped"
  schtasks /END /TN $task.taskName
  }
 }

Write-Host "Moving files from $Source to $Target"
Get-Childitem $Source -recurse -include *.txt | Move-Item -destination $Target -force 

Write-Host "Starting Tasks"
if ($runningTasks.count -gt 0) {
 foreach($runningTask in $runningTasks)
   {
   Write-Host "$runningTask starting"
   schtasks /RUN /TN $runningTask  
   }

 }

пятница, 25 сентября 2015 г.

Save Clob to file

Нашел на форуме быстый и элегантный способ сохранить clob в файл на сервере

DBMS_XSLPROCESSOR.clob2file(
  cl => l_clob
, flocation => 'XML_LOG'
, fname => myfile_name
);

среда, 23 сентября 2015 г.

Temporary lobs

Встала задачка собрать много строчек таблицы в один BLOB.
Краткий алгоритм как это сделать: делаем temporary lob - изменяем его, вставляя строки - вставляем - очищаем - возвращаемся к шагу 1.
В нижележащих тестах разница, на сколько отличается по времени работа с temporary lob, созданного с параметром cache = true и cache = false
Результаты: cache = true в 20 раз быстрее
Код и результата теста

SET SERVEROUTPUT ON

PROMPT CACHE=TRUE

DECLARE
  bl BLOB;
  l_raw RAW(32767);
  l_temp_cnt NUMBER;
  l_start_time NUMBER := dbms_utility.get_time;
  FUNCTION get_temp_blocks_cnt RETURN NUMBER IS
    l_cnt NUMBER;
  BEGIN
    SELECT SUM(u.blocks) 
    INTO l_cnt
    FROM v$tempseg_usage u, v$session s
    WHERE u.session_addr = s.saddr AND AUDSID = USERENV('SESSIONID');
    RETURN NVL(l_cnt, 0);
  END;
BEGIN
  l_temp_cnt := get_temp_blocks_cnt;
  DBMS_LOB.createtemporary(bl, TRUE);

  FOR i IN 1 .. 100000 LOOP
    l_raw := utl_raw.cast_to_raw(RPAD(i, 1000, ' '));
    dbms_lob.writeappend(bl, utl_raw.length(l_raw), l_raw);
    IF MOD(i, 10) = 0 THEN 
      dbms_lob.freetemporary(lob_loc => bl);
      DBMS_LOB.createtemporary(bl, TRUE);
    END IF;
  END LOOP; 

  dbms_output.put_line('Temp used=' || (get_temp_blocks_cnt - l_temp_cnt)); 
  dbms_output.put_line('Time used ' || (dbms_utility.get_time - l_start_time) / 100); 
END;
/

PROMPT CACHE=FALSE

DECLARE
  bl BLOB;
  l_raw RAW(32767);
  l_temp_cnt NUMBER;
  l_start_time NUMBER := dbms_utility.get_time;
  FUNCTION get_temp_blocks_cnt RETURN NUMBER IS
    l_cnt NUMBER;
  BEGIN
    SELECT SUM(u.blocks) 
    INTO l_cnt
    FROM v$tempseg_usage u, v$session s
    WHERE u.session_addr = s.saddr AND AUDSID = USERENV('SESSIONID');
    RETURN NVL(l_cnt, 0);
  END;
BEGIN
  l_temp_cnt := get_temp_blocks_cnt;
  DBMS_LOB.createtemporary(bl, FALSE);

  FOR i IN 1 .. 100000 LOOP
    l_raw := utl_raw.cast_to_raw(RPAD(i, 1000, ' '));
    dbms_lob.writeappend(bl, utl_raw.length(l_raw), l_raw);
    IF MOD(i, 10) = 0 THEN 
      dbms_lob.freetemporary(lob_loc => bl);
      DBMS_LOB.createtemporary(bl, FALSE);
    END IF;
  END LOOP; 

  dbms_output.put_line('Temp used=' || (get_temp_blocks_cnt - l_temp_cnt)); 
  dbms_output.put_line('Time used ' || (dbms_utility.get_time - l_start_time) / 100); 
END;
/  

CACHE=TRUE
Temp used=0
Time used 2.08
PL/SQL procedure successfully completed
CACHE=FALSE
Temp used=0
Time used 42.29
PL/SQL procedure successfully completed

Примечательно, что единственный способ, который сработал для очистки BLOB это

      dbms_lob.freetemporary(lob_loc => bl);
      DBMS_LOB.createtemporary(bl);

Ни присвоение NULL, ни empty_blob, который работает только для insert и select, как оказалось из найденной документации от версии 8.1

An exception is raised if you use these functions anywhere but in the VALUES clause of a SQL INSERT statement or as the source of the SET clause in a SQL UPDATE statement.
А для свежих версий
You cannot use the locator returned from this function as a parameter
to the DBMS_LOB package or the OCI.

Для дальнейших исследований можно заняться замерами потребляемой памяти.

пятница, 18 сентября 2015 г.

Синхронизация пользователя из SSO (OID) в OEBS

Случайно удалил пользователя OEBS в OID. Как результат: пользователь в OEBS живет (а удалить оотуда ничего нельзя), не изменяется (пишет ошибку на выполение пакета fnd_ldap_wrapper) и не синхронизируется назад.

Покопавшись, так и не нашел культурного способа синхронизировать одного пользователя.

Решил действовать в лоб, а именно:
1. Завести руками пользователя в OID
2. Узнать его guid
3. Прописать guid в fnd_user
Первый шаг делается через Oracle Directory Manager, второй и третий скриптом

DECLARE
  l_user_name VARCHAR2(4000) := 'test_user';
  l_guid      RAW(16);
BEGIN
  l_guid := fnd_ldap_user.get_user_guid_and_count(p_user_name => l_user_name,
                                                  n           => :user_count);
  dbms_output.put_line('l_guid=' || rawtohex(l_guid));
  UPDATE fnd_user u SET u.user_guid = l_guid WHERE user_name = l_user_name;
END;

После этого пользователь без ошибок меняется через интерфейс и может зайти под своим паролем.

Два самых больших вопроса, которые меня мучают и о которых, возможно, напишу позднее:
* есть ли культурный способ запушить пользователя (ровно одного, без удаления всей ветки) в OID
* Если верить функции fnd_ldap_user.get_user_guid GUID пользователя хранится в атрибуте orclguid. Но в Oracle Directory Manager такого атрибута я так и не нашел. Наверное эта утилита не позволяет смотреть raw значения. Ну хоть бы написало, что атрибут есть и не пустой :(

четверг, 3 сентября 2015 г.

SORT GROUP BY

В одном из планов вылез ужасно медленный SORT GROUP BY
После беглого анализа нашел статью
http://guyharrison.squarespace.com/blog/2009/8/5/optimizing-group-and-order-by.html
В котором в красивых картинках все было рассказано, а после приведен хинт /*+USE_HASH_AGGREGATION*/
К сожалению надежды на быструю починку запроса разрушились комментарием VJ Kumar и следующим его подтверждением Jason

when subquery is used to insert records into another table, Oracle
seems to always use sort group by, even hint USE_HASH_AGGREGATION is
in place.
К счастью на 11.2.0.4 добавление хинта сделало запрос работающим как полагается.

понедельник, 31 августа 2015 г.

Index monitoring и сбор статистики

В продолжении темы мониторинга индексов провел небольшое исследование про index monitoring + v$object_usage
Когда анализировал использование индексов по dba_hist_sql_plan запросы, которые собирают статистику приходилось отфильтровывать руками.
**Для index monitoring написал небольшой тест, который показал, что
index monitoring показывает только select по индексу и не показывает сбор статистики и insert в таблицу**

DROP TABLE t PURGE;
Table dropped
CREATE TABLE t(ix NUMBER);
Table created
CREATE INDEX ix_t ON t(ix);
Index created
ALTER INDEX ix_t MONITORING USAGE;
Index altered
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
INSERT INTO t
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 1000;
1000 rows inserted
COMMIT;
Commit complete
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
BEGIN
  dbms_stats.gather_table_stats(USER, 'T', CASCADE => TRUE);
END;
/
PL/SQL procedure successfully completed
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
SELECT COUNT(*) FROM t WHERE ix = 10;
  COUNT(*)
----------
         1
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        YES  08/31/2015 19:18:23 

По теме удаления неиспользуемых индексов в последнюю неделю начали писать все.
Статья Льюиса https://jonathanlewis.wordpress.com/2015/08/17/index-usage/ (обещал продолжение)
Вторая статья Льюиса немного не по теме https://jonathanlewis.wordpress.com/2015/08/29/index-usage-2/ – про то, что с 11.2.0.2 не надо делать индексы по trunc(datetime column),Oracle сам научился добавлять предикаты
Статья на форуме https://jonathanlewis.wordpress.com/2015/08/17/index-usage/
Старая статья Тима Холла про monitoring usage https://oracle-base.com/articles/10g/index-monitoring

Parallel hint

В статье https://blogs.oracle.com/datawarehousing/entry/parallel_execution_precedence_of_hints
интересные результаты получились для строчек, в которых используются хинты PARALLEL, PARALLEL(degree) и PARALLEL(auto) (строки 9, 10 и 11). Получается, что если установлен такой хинт, то делать

ALTER SESSION ENABLE PARALLEL DML

не надо?
Надо провести тесты.

вторник, 25 августа 2015 г.

Unusable index monitoring

Типичная задача об удалении неиспользуемых индексов решается индивидуально для каждой системы.
Можно выбрать такой алгоритм:
выбрать индексы, которые реально необходимо оптимизировать, например
* большие по размеру
* на поддержку которых уходит много времени: много операций записи

индексы не используются
* проверяем по dba_hist_sql_plan без учета сбора статистики
* проверяем по dba_hist_seg_stat

Главная идея такая: собираем все подознительное и из этого откидываем, что есть в планах или читается.

Мой запрос такой, но его можно подкорректировать в зависимости от нужд (в части bad_idx + поиграться с количеством чтений и записей + еще что-нибудь дописать)

WITH large_idx AS (
    SELECT owner, segment_name, segment_type, ROUND(sum(bytes)/1024/1024/1024, 2) size_gb
    FROM   dba_segments t
    WHERE  segment_type LIKE 'INDEX%'
    GROUP BY owner, segment_name, segment_type
    HAVING ROUND(sum(bytes)/1024/1024/1024, 2) > 5
),
seg_stat AS (
  SELECT o.object_name, o.owner, SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) READS,
    SUM(s.physical_write_requests_delta) + SUM(s.physical_writes_direct_delta) + sum(s.physical_writes_delta) writes
  FROM dba_hist_seg_stat s, dba_objects o
  WHERE s.obj# = o.object_id
    AND o.object_type LIKE 'INDEX%'
    AND o.owner LIKE 'OWNER%'
  GROUP BY o.object_name, o.owner
  HAVING SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) = 0
),
sql_plan AS (
SELECT /*+ MATERIALIZE*/
 p.*
FROM   (SELECT DISTINCT o.owner,
                        o.object_name,
                        sql_id
        FROM   dba_objects       o,
               dba_hist_sql_plan p
        WHERE  o.object_type LIKE 'INDEX%'
        AND    owner LIKE 'OWNER%'
        AND    p.object_owner = o.owner
        AND    p.object_name = o.object_name) p
WHERE  EXISTS (SELECT NULL
        FROM   dba_hist_sqltext t
        WHERE  p.sql_id = t.sql_id
        AND    t.sql_text NOT LIKE '%\*%dbms_stats%*\%')
),
bad_idx AS (
SELECT owner, segment_name object_name, 'LARGE' reason FROM large_idx
UNION
SELECT owner, object_name, 'NOT USED' reason FROM seg_stat WHERE READS = 0 AND writes > 10000
)
SELECT /*+ PARALLEL(8)*/i.owner, i.object_name, round((SELECT sum(bytes) FROM dba_segments s WHERE s.owner = i.owner 
  AND s.segment_name = i.object_name)/1024/1024, 2) mb, LISTAGG(reason, '; ') WITHIN GROUP (ORDER BY 1),
  'ALTER INDEX ' || i.owner || '.' || i.object_name || ' MONITORING USAGE;' monitiring_on,
    'ALTER INDEX ' || i.owner || '.' || i.object_name || ' NOMONITORING USAGE;' monitiring_off
FROM bad_idx i
WHERE (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM sql_plan)
  AND (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM seg_stat WHERE READS > 0)
GROUP BY i.owner, i.object_name
;

Пока сильные подозрения вызываем правильность заполнения dba_hist_seg_stat

понедельник, 24 августа 2015 г.

Initial extent

Таблица после MOVE не уменьшилась, хотя по оценкам должна быть в 15 раз меньше.
Выяснилось, что причина в неадекватном initial extents
Уменьшить его можно в самом move

ALTER TABLE test MOVE STORAGE (INITIAL 2097152) PARALLEL 8;

воскресенье, 9 августа 2015 г.

Удаление индексов перед вставкой при наличии constraints

Один INSERT очень сильно тормозил из-за существующих на таблице индексов. Быстрее было их удалить, вставить и построить их заново.
Тут столкнулся с проблемой: на индексах были построены primary key и unique key, а на PK еще и ссылались FK с других таблиц.
Минимальный работающий алгоритм получился такой:
1. Удалить внешние ключи (именно DROP, DISABLE не работает)
2. Удалить или сделать DISABLED PK и UK
3. Удалить индексы. Те, которые поддерживают PK и UK именно DROP, а не UNUSABLE
4. INSERT
5. Создаем назад индексы и FK, делаем PK ENABLE (для скорости можно NOVALIDATE)

Выводы
1. Любой FK мешает удалить PK или UK на который он ссылается. Даже DISABLE.
2. Индекс, поддерживающий PK или UK можно сделать UNUSABLE, но вставить записи в таблицу потом нельзя.
3. Удалить индекс, который поддерживает PK или UK невозможно.
4. Но можно сделать PK UNUSABLE, удалить индекс и вставлять

Скрипт и результаты работы

DROP TABLE chi PURGE;
DROP TABLE par PURGE;

CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));

CREATE TABLE chi(par_id NUMBER);

ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;

-- тест с первичным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_pk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_pk; 

-- подготовка к следующему эксперементу
ALTER TABLE chi DROP CONSTRAINT chi_fk;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;

ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;

-- тест с уникальным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_uk; 

-- но можно сделать constraint DISABLE
ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
-- с UNUSABLE INDEX все равно нельзя вставить
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;

--а вот без индекса можно
DROP INDEX par_uk; 
INSERT INTO par SELECT ROWNUM FROM dual;

Скрипт с результатами

SQL> CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));
Table created
SQL> CREATE TABLE chi(par_id NUMBER);
Table created
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с первичным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_pk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_PK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_pk;
DROP INDEX par_pk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- подготовка к следующему эксперементу
SQL> ALTER TABLE chi DROP CONSTRAINT chi_fk;
Table altered
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
Table altered
SQL> ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
Table altered
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с уникальным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_uk;
DROP INDEX par_uk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- но можно сделать constraint DISABLE
SQL> ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
Table altered
SQL> -- с UNUSABLE INDEX все равно нельзя вставить
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> --а вот без индекса можно
SQL> DROP INDEX par_uk;
Index dropped
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
1 row inserted

воскресенье, 26 июля 2015 г.

Fast refreshable materialized view errors

Отличное исследование почему материализованное представление не может быть сделано fast refresh.
Рассмотрены все варианты

General Restrictions
Join restriction
Aggregate restrictions
Union all
Nested MV
dbms_mview.explain_mview
Итого
Документация

Для всех ограничений примеры с исходным кодом.