О рекурсивных сессиях

Отличная статья от Танела Поддера: http://tech.e2sn.com/oracle/oracle-internals-and-architecture/recursive-sessions-and-ora-00018-maximum-number-of-sessions-exceeded

Трассировка Event 10046, уровни 16,32,64

Christian Antognini опубликовал полный список уровней для 10046-й трассы.
Утащу к себе, пока не запомню :)

Repost: Christian Antognini. "Event 10046 – Full List of Levels"

Extended SQL trace (a.k.a. debugging event 10046 at a level higher than 1) is one of the key features provided by Oracle to troubleshoot applications using Oracle Database. For many years the available levels were always the same (4, 8 and 12). In fact, since I wrote my first paper about it in May 2000 and the release of 11g nothing changed.

With 11g, as I described in this post, new levels (16 and 32) were introduced.

More recently, with the introduction of the fix for bug 8328200, a new one was added to the list (64).

So, I thought it was time to publish the current list of available levels…

Level	Description
0	The debugging event is disabled.
1	The debugging event is enabled. For each processed database call, the following information is given: SQL statement, response time, service time, number of processed rows, number of logical reads, number of physical reads and writes, execution plan, and little additional information. Up to 10.2 an execution plan is written to the trace file only when the cursor it is associated with is closed. The execution statistics associated to it are values aggregated over all executions. As of 11.1 an execution plan is written to the trace file only after the first execution of every cursor. The execution statistics associated to it are the ones of the first execution only.
4	As in level 1, with additional information about bind variables. Mainly, the data type, its precision, and the value used for each execution.
8	As in level 1, plus detailed information about wait time. For each wait experienced during the processing, the following information is given: the name of the wait event, the duration, and a few additional parameters identifying the resource that has been waited for.
16	As in level 1, plus the execution plans information is written to the trace file for each execution. Available as of 11.1 only.
32	As in level 1, but without the execution plans information. Available as of 11.1 only.
64	As in level 1, plus the execution plans information might be written for executions following the first one. The condition is that, since the last write of execution plans information, a particular cursor consumed at least one additional minute of DB time. This level is interesting in two cases. First, when the information about the first execution is not enough for analysing a specific issue. Second, when the overhead of writing the information about every execution (level 16) is too high. Generally available as of 11.2.0.2 only.

In addition to the levels described in the previous table, you can also combine the levels 4 and 8 with every other level greater than 1. For example:

• Level 12 (4 + 8): simultaneously enable level 4 and level 8.
• Level 28 (4 + 8 + 16): simultaneously enable level 4, level 8 and level 16.
• Level 68 (4 + 64): simultaneously enable level 4 and level 64.

If you are using dbms_monitor or dbms_session for enabling extended SQL trace, here is the mapping between the levels and the parameters:

• Level 4: waits=FALSE, binds=TRUE, plan_stat=’first_execution’
• Level 8: waits=TRUE, binds=FALSE, plan_stat=’first_execution’
• Level 16: waits=FALSE, binds=FALSE, plan_stat=’all_executions’
• Level 32: waits=FALSE, binds=FALSE, plan_stat=’never’
• Level 64: not available yet

As you can see from the previous list, it is not possible to enable level 64 through dbms_monitor and dbms_session. Hence, statements like the following ones or ORADEBUG have to be used:

alter session set events '10046 trace name context forever, level 64'

alter session set events 'sql_trace wait=false, bind=false, plan_stat=adaptive'

I really hope that this limitation will be removed very soon.

Ссылка на онлайн unwrapper в документации Oracle

Все, конечно, знают про unwrapper'ы и многие даже пробовали свой написать (и я в том числе, после после презентации Pete Finnigan'а :), но тем не менее забавно, что Oracle в документации к 11.2 прямо-таки "громогласно" заявляет о том, что ничего unwrap от просмотра не защищает, но еще и прямую ссылку дает на online unwrapper :)

11.2 PL/SQL Source Text Wrapping:

Note:

Wrapping text does not prevent anyone from displaying it with a utility such as:

http://www.codecheck.info/UnwrapIt/

For high-assurance security, use Oracle Database Vault, described in Oracle Database Vault Administrator's Guide.

И для сравнения прогресс документации от версии к версии о возможности unwrap'а:

• В 9.2 все скрывает надежно, кроме литералов и названий переменных, таблиц, колонок:

  String literals, number literals, and names of variables, tables, and columns remain in plain text within the wrapped file. Wrapping a procedure helps to hide the algorithm and prevent reverse-engineering, but it is not a way to hide passwords or table names that you want to be secret.

• А в 10.2 уже защищено лишь от бóльшего количества пользователей, но все-таки затрудняет реверс-инжиниринг!

  Although wrapping a compilation unit helps to hide the algorithm and makes reverse-engineering difficult, Oracle Corporation does not recommend it as a secure method for hiding passwords or table names. Obfuscating a PL/SQL unit prevents most users from examining the source code, but might not stop all attempts.

• В 11.1 как-то скромно и скучно:

  Wrapping is not a secure method for hiding passwords or table names. Wrapping a PL/SQL unit prevents most users from examining the source code, but might not stop all of them.

Регулярные выражения Oracle с вычисляемой заменяемой частью

Интересная тема с возможными решениями двух стандартных задач:
1) sprintf в oracle в SQL
2) замена по регулярке с вычисляемой заменяемой частью(а ля модификатор "e" в perl)

When oracle invalidates result_cache function results without any changes in objects on which depends

On our production servers we have simple function with result_cache, like this:

create or replace function f_rc(p_id number) return number result_cache
is
  ret number;
begin
  select t.val into ret from rc_table t where t.id=p_id;
  return ret;
exception 
  when no_data_found then 
     return null;
end;
/

And its results frequently invalidates without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:
1. "select for update" from this table with commit;
2. deletion of unrelated rows from parent table if there is unindexed foreign key with "on delete cascade".
I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases for this i will show below.
But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with "select for update", it still does not stop invalidating.
In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and "commits"?

Дальше »

Интересный вопрос от Валентина Никотина

Тестовая таблица:

create table tclob(c clob);

Что будет выведено кодом из нижеследующих блоков с rollback и без:

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1:='1';
  insert into tclob values(cl1) returning c into cl2;
  cl3:=cl2;
  dbms_lob.append(cl3,'2');
  select c into cl4 from tclob;
--  rollback;

  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '2');
  select c into cl4 from tclob;
--  rollback;

  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '2');
  dbms_lob.append(cl3, '3');
  select c into cl4 from tclob;
--  rollback;

  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '22');
  dbms_lob.append(cl3, '3');
  dbms_lob.append(cl2, '44');
  select c into cl4 from tclob;
--  rollback;
  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

Проверьте и на винде, и на солярке/линуксе :)

Ясно, что этот баг платформозависим и что дело в особенностях работы с памятью. Ответ кроется в том, что у cl3 и cl2 не синхронизированы длины, т.е. Oracle "забывает" изменить длины всех остальных переменных, указывающих на этот clob, а т.к. каждая операция изменения cl2/cl3 фактически изменяет одно и то же, то "лишнее" перезаписывается.

Ура! Сын родился!

Теперь у нас полная семья - и доча и сын :)
Уррррррррраааааа! :)