Имаме следната задача: Table1 съдържа имена на клиентите на най-голямата квартална бакалия в Мюнхен, а Table2 съдържа феновете на Байерн Мюнхен. Пита се колко от феновете на Байерн пазаруват в бакалията.
Да предположим, че данните са First Name (FNAME), Last Name (LNAME), CITY, COUNTRY.

Най-елементарното решение е следното:

select *
  from table2 t2
 where exists (select 1
          from table1 t1
         where t1.fname = t2.fname
           and t1.lname = t2.lname
           and t1.city = t2.city
           and t1.country = t2.country)

Това ще ни даде резултат ако всички имена са написани по паспорт. Но както всички знаем, данните не винаги са така чисти. Примерно господин Schmitt от феновете може да е записан в бакалията като Schmidt; госпожица Pavlovich може да е Pavlovic; John Smith не съвпада с John Smit и т.н. Абе трябва нещо по-добро.

Има една функция, което се опитва да оцени звуковото съответствие (работи само за латиница!) и това е soundex. Начина и на действие е относително прост и е описан тук. Да видим какво става с нея:

select *
  from table2 t2
 where exists (select 1
          from table1 t1
         where soundex(t1.fname) = soundex(t2.fname)
           and soundex(t1.lname) = soundex(t2.lname)
           and t1.city = t2.city
           and t1.country = t2.country)

(за простота, или от простотия, приемам че държавите и градовете са написани еднакво. Иначе може да се приложи същата трансформация)

Опа… излязоха твърде много резултати. За мен няма нищо общо между Marko Mjagkov и Marica Moisejevs, но на soundex му звучат еднакво – M620 M221. Да опитаме нещо друго

Има едни красив, относително нов пакет, известен като utl_match. В този пакет има реализирани 2 алгоритъма, разработени за борба с точно такъв проблем – сравняване на имена. Единият е измислен от руския математик Владимир Левенщайн през далечната 1965 (функцията EDIT_DISTANCE), а другия комай е с американски произход, резултат от дългогодишния труд на господата Matthew Jaro и William Winkler, публикуван през 1990. Колкото и да бях добър с математиката като ученик и студент, сега съм забравил всичко и математическия апарат ми е твърде сложен; но пък мога да използвам функциите наготово и без да му мисля много, да проверя колко са „близки“ имената:

select *
  from table2 t2
 where exists (select 1
          from table1 t1
         where utl_match.jaro_winkler(t1.fname, t2.fname) > 0.9
           and utl_match.jaro_winkler(t1.lname, t2.lname) > 0.9
           and t1.city = t2.city
           and t1.country = t2.country)

Тук, обаче, достигам до друг проблем – няма ефективен начин а ускоря заявката. Ако бакалията има 10 000 клиенти от Мюнхен, а Байерн има 500 000 фена в родния си град, то имаме реален шанс да изпълним функцията utl_match.jaro_winkler над 5 000 000 000 пъти. При това ще сравнява, примерно, Адолф с Грета – нищо общо. Никакви индекси или оптимизации не помагат, просто груба сила. Това си отнема доста (процесорно) време.

И така, за нуждите на моето изследване аз достигнах до следното комбинирано решение

select *
  from table2 t2
 where exists (select 1
          from table1 t1
         where soundex(t1.fname) = soundex(t2.fname)
           and soundex(t1.lname) = soundex(t2.lname)
           and utl_match.jaro_winkler(t1.fname, t2.fname) > 0.9
           and utl_match.jaro_winkler(t1.lname, t2.lname) > 0.9
           and t1.city = t2.city
           and t1.country = t2.country)

Тук със soundex сравнението орязвам значително проверките, макар и да имаме доста false positives. За по-извратени случаи може да направим функционален индекс по soundex.
После с utl_match.jaro_winkler намирам най-вероятните съвпадения от вече намаления набор. Вярвате или не, това върви доста добре – при таблици с 20К и 2M реда (всичките от един град), минава за десетки секунди и дава доста смислени резултати. Разбира се, доста от съвпаденията може да са случайни – не само един мюнхенец се казва Ханс Шмит. Но все пак е някакво начало.

P.S. По време на research-а попаднахме на многообещаващия Петьо. Отказахме се от него, защото иска extproc, а ние избягваме този механизъм защото може да отвори дупка в сигурността. Иначе изглежда прекрасно

 

Презентацията ми „The adventure of upgrading to 11.2″ от есенния семинар на БГПО в Хисар – тук

Отново изразявам моите огромни благодарности и възхищение на Милена, СПИ и другите организатори, които инвестират много от личното си време и енергия за да направят тези семинари. Страхотна работа, отново!

 

Това е една друга интересна мотика при upgrade от 10g на 11g. По време на автоматизираните тестове забелязахме, че audit trail таблицата се раздува с бясна скорост и една забележима част от IO операциите са именно писане в SYS.AUD$.

Открихме следната нота в Metalink:
Huge/Large/Excessive Number Of Audit Records Are Being Generated In The Database [ID 1171314.1]

1.) Starting with Oracle 11g the BY SESSION clause is obsolete. This is documented in the Database Security Guide :

“ The BY SESSION clause of the AUDIT statement now writes one audit record for every audited event. In previous releases, BY SESSION wrote one audit record for all SQL statements or operations of the same type that were executed on the same schema objects in the same user session. Now, both BY SESSION and BY ACCESS write one audit record for each audit operation. In addition, there are separate audit records for LOGON and LOGOFF events. “

Because of the above change it is expected that the number of the audit records/files will grow considerably.

Засилвайки се към документацията открих следното:
Oracle® Database SQL Language Reference 11g Release 1 (11.1)

BY SESSION

In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. BY SESSION continues to populate different values to the audit trail compared with BY ACCESS. If you specify neither clause, then BY SESSION is the default.

На практика същото пише и в Oracle® Database SQL Language Reference 11g Release 2 (11.2)

Още по-неясно е написаното в Oracle® Database Security Guide 11g Release 2 (11.2)

Benefits of Using the BY ACCESS Clause in the AUDIT Statement

By default, Oracle Database writes a new audit record for every audited event, using the BY ACCESS clause functionality. To use this functionality, either include BY ACCESS in the AUDIT statement, or if you want, you can omit it because it is the default. (As of Oracle Database 11g Release 2 (11.2.0.2), the BY ACCESS clause is the default setting.)

Oracle recommends that you audit BY ACCESS and not BY SESSION in your AUDIT statements. The benefits of using the BY ACCESS clause in the AUDIT statement are as follows:

Честно ви казвам, тия неща ми звучат като „Ъъъъ… бе нещо се повреди тоя BY SESSION, ама успяхме да го позакрепим, макар че вече работи почти като BY ACCESS“

Трябваше да предефинираме audit политиката си преди upgrade-a. За щастие повечето noaudit операции стават online. Единствено noaudit execute прави гадни заключвания и трябваше да го правим по време на downtime.

Ето и едно полезно query по въпроса:

select 'noaudit select on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where sel like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit execute on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where exe like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit update on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where upd like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit insert on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where ins like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')
union all
select 'noaudit delete on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'
  from DBA_OBJ_AUDIT_OPTS
 where del like 'S/%'
   and owner in ('...application schemas...')
   and object_name not in ('...exclusions...')

И още нещо много важно: не забравяйте да си прегледате какво пише в ALL_DEF_AUDIT_OPTS преди upgrade. Иначе catupgrd ще ви създаде хиляда хиляди обекта с кофти auditing.

 

Това не го очаквах. Обикновено с новите версии на Oracle нещата скалират по-добре; има по-малко заключваници от предишните версии. Това, което ще опиша, е ново за 11g

Някой би казал, че това е некадърен дизайн на приложението. Всеки знае, че foreign key колоните трябва да се индексират. Обаче в 10g това водеше до по-бавно изтриване от parent таблицата, или по-бавен update на primary key в нея (докато се сканира child таблицата за съответни записи). А сега имаме изцяло ново поведение – заключване, което може да продължи произволно дълго време. Един прост INSERT в parent таблицата може да блокира всички други сесии, които се опитват да направят UPDATE или DELETE (пак на parent таблицата). Тук изобщо не говорим за промяна в child таблицата – достатъчно е да я има, ако ще и празна. И за да ни е по-весело, ние настъпахме тая мотика с child таблица от 724 реда, на която full scan-а в 10g е минавал светкавично и не сме усетили никакъв проблем.

* * *

Ето и подробностите. Note 1343365.1 казва следното

There is change in lock modes behavior introduced intentionally by fix 5909305 in 11.1 onward.

Change has been made to DML (TM) lock modes for foreign key constraints (Doc ID 5909305.8).

The change in behavior mentioned in note 5909305.8 , introduces higher level of lock on parent table while running DML against Child table. ( SX vs SS )

There are cases unrelated to this bug which lack of indexes on foreign key columns increase the time locks take place. This reflect as wait on TM-enq.

Звучи безобидно. Поведението на SS и SX е почти еднакво. Има 2 разлики – SX не е съвместим с други S и SSX (Share и Share Sub-eXclusive) заключвания. Но тия двете не се срещат в ежедневната работа с DML заявки. Почти.

Тук се включва неиндексирания foreign key. Това е единствения DML за който знам, който прави S заключване на цялата child таблица. И така, със следните прости стъпки аз успявам да се заключа всеки път на 11.2.0.2:

1. Подготвям си таблици и данни

drop table t2 purge;
drop table t1 purge;

create table t1 (pk number primary key);

create table t2 (pk number primary key, fk number references t1(pk));

insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);

insert into t2 values (11, 1);
insert into t2 values (21, 2);
insert into t2 values (22, 2);
insert into t2 values (31, 3);
insert into t2 values (32, 3);
insert into t2 values (33, 3);

commit;

2. Залагам капана от сесия 1

insert into t1 values (5);

Кратък поглед във v$lock показва, че нашата сесия 1 държи 3 заключваници (всъщност малко повече в 11g, но трите са най-интересни)
- mode 6 (eXclusive) TX lock върху новия ред
- mode 3 (Sub eXclusive) TM lock върху таблицата t1 (демек parent таблицата). обърнете внимание, че това е заключване на таблицата, а не на някой ред в нея
- mode 2 (Sub Share) при 10g или Mode 3 (Sub eXclusive) при 11g, TM lock на таблица t2 (това е child таблицата). Отново това не е свързано с никакви редове в нея – може да има, може и да няма засегнати редове. Такива заключвания има за всички таблици, които имат foreign key constraint, сочещ към нашия parent.

3. Опитваме да променим или изтрием произволен (друг) ред от parent таблицата от сесия 2:

delete from t1 where pk=4;

или

 update t1 set pk=2 where pk=2;

В 10g това минава безгрижно. Обаче в 11g и двете зависват, опитвайки се да получат Mode 4 (Share) lock на child таблицата – защото foreign key колоната не е индексирана.

Как да си намерим неиндексираната таблица:
FВсъщност аз забелязвам сесиите, чакащи за TM locks в Grid Control. Там излизат едни червени неща, лесно се виждат. Може да ги видим и във V$SESSION:

 SQL> select sid, final_blocking_session
  2    from v$session
  3   where event = 'enq: TM - contention';

       SID FINAL_BLOCKING_SESSION
---------- ----------------------
       132                    332

За да намерим проблемния ресурс (с други думи – таблицата без индекс), пускаме това:

 SQL> select SID, type, id1, lmode, request, block
  2    from v$lock
  3   where sid in (132, 332)
  4     and type = 'TM';

       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       132 TM       950744          3          0          0
       132 TM       950746          0          4          0
       332 TM       950744          3          0          0
       332 TM       950746          3          0          1

На ред номер 4 пише BLOCK=1, т.е. това заключване *блокира* някой друг. На ред номер 2 виждаме REQUEST=4 и LMODE=0 – това показва, че сесията има нужда от mode 4 lock, обаче не може да се уреди. Колоната ID1 в случая е object ID (за TM locks):

 SQL> select owner, object_name, object_type
  2    from dba_objects
  3   where object_id = 950746;

OWNER      OBJECT_NAM OBJECT_TYPE
---------- ---------- -------------------
YAVOR      T2         TABLE

Решението е лесно, след като сме открили проблема. Просто създаваме индекс на foreign key колоната от child таблицата

 create index t2_fk on t2(fk);

След създаването на индекс, промяната върху parent таблицата вече не прави S заключване на child. Вместо това се прави SX заключване, което е съвсем съвместимо с другия SX.

Това поведение е трудно за обяснение на develpers. Честно казано, дори и аз не мога да си обясня как индекса премахва нуждата от S lock и го намаля на SX. Ние не засягаме никакви редове в child таблицата. Ама… така са го направили.

 

Интересно, всички медии полагат огромни усилия да не споменават имена на партии и кандидати. А из блогосферата всеки втори постинг, да не е и по-често, е политически – „Аз гласувах за този и този, защото това и онова“. Което, от правна гледна точка, си е агитация.
Блоговете минават за нещо като неофициална медия. Интересно как би могло да се дефинира това в изборния кодекс.
Иначе и аз гласувах. И ако ще агитирам за нещо, това е – гласувайте! Ако не друго, валидния глас е най-ефективния „протестен вот“ срещу купуването на гласове. Което, всички сме съгласни, е гадно.

 

В петък се излежавах до около 8 часа. После хапнах за последно от континенталната закуска и тръгнах да си събирам багажа. Много предизвикателна задача: от подаръци и сувенири куфара се напълни около 2 пъти. Започнах да го затварям от едната страна и да натъпквам дрехите към нея; после още малко напред с ципа, още дрехи натъпкани и така. Процедурата сработи, справих се за около час (с почистването на стаята), но остана малкото притеснение да не ме накарат да си отварям багажа на летището, че после няма прибиране. А не е като да няма съмнителни (като за рентген) неща вътре – стъклена бутилка, един голям обектив, дезодорант, ел. четка за зъби и самобръсначка и т.н.
(всъщност когато дрехите престоят няколко часа в куфара се смачкват до толкова, че едно отваряне за малко не е проблем)

После освободих стаята – така или иначе трябва да се изнесеш до 12 – и оставих куфара на рецепцията. Реших да използвам няколкото свободни часа с фото-разходка из центъра. Имах и още няколко подаръка за купуване, както и една от най-важните задачи: да си намеря ракиена чашка с надпис San Francisco.

И така, няколко снимки от Сан Франциско. Ето една типична баир-улица:

На всички по-големи улици има надписи кога се мият:

Гадната миризма, от която се оплаках в неделя, не се повтори. Но е истина и че всяка нощ валеше.

Ето Union Square:

Забележете горе вдясно панорамните асансьори. Сигурни е много готино да се возиш в тях.

Тези туристически автобуси за разходка тръгват от Union Square. Два са пълни, има опашка за следващия. И като става дума за опашка, вижте какво чакане е да се повозиш на cable car:

Трамвайчето стои разтоварено малко преди обръщалото. Опашката започва вдясно, минава покрай „обръщалото“ и завършва вляво, където е качването. Но всяко трамвайче има ограничен капацитет:

Следваща тема: анти-грипни ваксини. Масова рекламна кампания. Вероятни са спонсорирани от държавата и са далавера, защото всяка по-смислена аптека предлага даже да ви ги бият на място:

<