Oracle SQL

TwitterGoogleLinkedInRSS

Deceptive commit after select from dblink.

Posted on by Posted in commit, documentation, oracle, remote statements, trace, transactions Leave a comment

Recently on our russian forum we discussed about distributed transaction: is “insert /*+ append */ into x select * from t@dblink” a distributed transaction or not?
According to the documentation – no:

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database

And Tom Kyte said the same:

In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote@table; – we just have a single site transaction.

But v$global_transaction(but v$transaction), another part of documentation and v$lock(type=’DX’) assure that it is.

Distributed statement: A statement that accesses data on two or more distinct nodes/instances of a distributed database.
A remote statement accesses data on one remote node of a distributed database.

Who is right?

But it not so interesting as my another finding: commit after simple “select * from dblink” would be “read-only” commit on local db, but on remote it became “read-only” rollback! Except cases when there was any dml in that transaction(with no difference local or not). But how Oracle determining: read-only or not, execute commit or rollback?

Yet another interesting thing: If we do a few times “commit” after “select from dblink”, then, as expected, rollback will be executed only once on remote. But when we closing our session, there is another commit on remote(real read-only commit).

Tests below: Continue reading→

Easy way to tracing

Posted on by Posted in diagnostic event 10046, oracle, trace Leave a comment

Recently I needed to trace queries from dblink, and I decided to make it more convenient, without the trigger.
These are the three simple steps:

  1. Creating new service
  2. Turning on tracing on this service
  3. Editing tnsnames and adding dblink for this service if needed

Now we can enable tracing anything just connecting to this service.

Below code for sql*plus:

begin
  DBMS_SERVICE.CREATE_SERVICE('db11203trace','db11203trace');
end;
/
column new_value new_value new_value;
select p.value||',db11203trace' new_value from v$parameter p where p.name like 'service_names';
alter system set service_names='&new_value';
exec dbms_monitor.serv_mod_act_trace_enable(service_name => 'db11203trace',waits => true,binds => true);

Amazing optimization of getting distinct values from the index, and TopN for each of them

Posted on by Posted in oracle, query optimizing Leave a comment

A couple of days ago someone posted a question on the forum which at the first glance seemed old, boring, beaten up and down. Here it is:

There is a news feed. All news are divided into 10 categories (Politics, sport, auto, real estate, etc).
I need to get top 4 news sorted by time descending for each category with 1 query.
If you sort the results – you get 4 politics news, then 4 sport news etc.

But the task was to make it optimal, and the standard solution with usual TopN using row_number can not be called optimal in any way, especially in case of big tables, relatively small number of categories and uneven distribution or just overall low selectivity.

So after getting several more or less acceptable variants, and spotting the solution using PostgreSQL (I didn’t dig deep into it, as I saw recursion, min and predicate, and that was enough), I got a great variant.

But first things first:

1. Getting distinct values from the index  Continue reading→

Parallelizing queries with “index range scan” on non-partitioned indexes and tables

Posted on by Posted in oracle, parallel, query optimizing Leave a comment

Today I had to speed up a huge single unloading (in my case it was “insert/*+append*/ select”, but it is not that important in this context): a big non-partitioned table, fairly good selectivity by index ( ~1.2%), over 95% of time is spent on the “lookup” from index to the table. Index size is 44Gb…

The initial query was:

select t_big.*
from t_big
where t_big.a=:a

Naturally enough I wanted to get it paralleled, but “index range scan” on non-partitioned index and table does not work in parallel, that’s why I had to use a little trickery, which allowed to increase the execution speed by more than 13 times with DOP = 16.

The method is simple: as index reading is quite fast, we can just read and materialize rowids through IRS, and after that we go paralleled to the table using TABLE ACCESS BY USER ROWID.

The final query:

with rids as(
            select--+ materialize
               rowid rid
            from t_big t1
            where t1.a=:a
)
select/*+ use_nl(rids t_big) parallel(16) */ t_big.*
from t_big, rids
where t_big.rowid=rids.rid

Creating constrains in parallel:
The newly created constraints are validated unparalleled by default, which significantly slows the process. Especially considering that this is usually happening during technological breaks when lots of resources are available. This problem is solved easily: you have to create it with “enable novalidate”, and then turn on “enable validate” separately:

UPD: I have made a replacement with “enable novalidate”, for as Timur Akhmadeev fairly noted, “disable” would not work with “unique” and “foreign key”, but only with “check constraints”.

Link to the online unwrapper in the official Oracle documentation

Posted on by Posted in curious, documentation, oracle 1 Comment

Of course, everyone knows about “unwrappers” and I guess many of you even tried to make an unwrapper of your own (I also tried to make one after Pete Finnigan’s presentation:). But nevertheless its funny that Oracle in its documentation for 11.2 “loudly” states that unwrap does not protect anything from viewing, and even gives a direct link to the 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.

Here is a comparison of unwrap capabilities in documentations for different versions:

  • In 9.2 everything is safely hidden, except for literals, names of variables, tables and columns:

    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.

  • In 10.2 the data is hidden only from “most users”, but at least it makes reverse-engineering difficult!

    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.

  • In 11.1 everything looks humble and boring:

    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.

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

Posted on by Posted in documentation, oracle, result_cache Leave a comment

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”?
Continue reading→

An interesting question from Valentin Nikotin

Posted on by Posted in Uncategorized Leave a comment

Test table:

create table tclob(c clob);

What is this code going to put out from the following blocks with and without “rollback”:

Spoiler:: Task 1 SelectShow

Spoiler:: Task 2 SelectShow
Spoiler:: Task 3 SelectShow
Spoiler:: Task 4 SelectShow

Check it both in Windows and Solaris/Linux :)

Explanation
It is clear that this bug is platform-depending and that the matter is in specifics of working with memory. The answer lies in the fact that cl3 and cl2 have unsynchronized lengths, which means that Oracle “forgets” to change the lengths of all remaining variables, that point to this “clob”, and as every operation of changing cl2/cl3 in fact changes the same thing, it turns out that “superfluous” becomes overwritten.

A lot of “latch free dml allocation latch” in concurrent queries to v$lock

Posted on by Posted in Uncategorized Leave a comment

This problem is old actually, but it became possible to find out what is the real matter here and to deal with it only now thanks to the “Oracle Core” book by Jonathan Lewis.

Here is a quote from the chapter “Latches for lock”:

If the enqueue resource is in place already, then pick a row from the relevant enqueue structure (x$ksqeq, et al.), but to do this you have to get the associated enqueue latch to stop other people from picking the same enqueue row at the same time. The latch you need to acquire depends on the specific type of enqueue you are using; for example, if you want a row from x$ksqeq you need to get the enqueue latch but for a row from x$ktadm you need to get the dml allocation latch. Drop this latch as soon as you have made the enqueue row safe.

And this “fixed” table itself is in “v$lock”, which I had repeatedly seen in the plans for it:

Spoiler:: Plan SelectShow

Now as we know the root of the problem, what’s left is to find out if we can escape unnecessary requests to X$KTADM, which seems possible considering that it’s a part of “union all”.
So we had to “parse” this v$lock, to get the full text of the query. To do this I began with getting the query text by tracing 10053:

Spoiler:: Hidden text SelectShow

But I was too lazy to format it after beautifier processing, so I decided to parse it progressively with v$fixed_view_definition. This led to a much better result:

Spoiler:: 10.2.0.4-11.2.0.1 SelectShow

Spoiler:: 11.2.0.3 SelectShow

By the way, as you can see gv$lock varies in different versions, which has to be taken into account (I haven’t tried it on 11.2.0.2 so I will update it later):
in 11.2.0.3 predicate “l.raddr=r.addr” was changed to concat(USERENV(‘Instance’),l.raddr)=concat(r.inst_id,r.addr).
Only particular blockings were queried from v$lock in my case, where most important of them were “user-locks”, which means they were of ‘UL’ type. That’s why after parsing the code, I had to get what locks are returned by each particular “union all” block. For this purpose I have created a modified GV$LOCK:
Spoiler:: xt_gv$_lock for 11.2.0.3 SelectShow

Now we can get relevance of block types ~ particular block:

with t as (select distinct sq,type from xt_gv$lock l)
select sq
      ,listagg(t.TYPE,',') within group(order by t.type)
from t
group by sq

Where “sq” is a number of “union all” block.
Or just get the block number using filter by block type. So, for example ‘UL’ will now be in the first block, and if we need to select them we can just make queries from this new view adding “sq=1” predicate to avoid torturing other blocks.

select * 
from xt_gv$lock l
where l.type='UL'
and l.sq=1 -- ul in first block only

During a load testing in a concurrency my modified query with “user locks” not only completely solved the problem with latches, but also increased the speed of query processing by 200 times and lowered the CPU usage.

Script files:
11.2.0.1: xt_gv$_lock_11_2_0_1.sql
11.2.0.3: xt_gv$_lock_11_2_0_3.sql

dbms_random in parallel

Posted on by Posted in documentation, oracle, parallel Leave a comment

The documentation for dbms_random states:

It will automatically initialize with the date, user ID, and process ID if no explicit initialization is performed.

This phrase does not answer the question, which “process id” is going to be used for initialization in case of parallel execution. That’s why I decided to give a vivid example that shows independence of “dbms_random” generator from “process id” of slave, that is generating identical values in parallel:

with
 t  as ( select/*+ materialize */ level n from dual connect by level<=4000)
,t1 as (
         select--+ materialize parallel(t 4)
            dbms_random.string('x',4)
            ||';'
            ||(select sid||';'||process||';'||pid
               from v$session, v$process
               where sid=sys_context('USERENV','SID')
                 and PADDR=ADDR
                 and n>0
              ) f
         from t
)
,t2 as (
         select
            t1.f
           ,count(*) over(partition by regexp_substr(f,'^[^;]+')) cnt
         from t1
)
select f
      ,regexp_substr(f,'[^;]+') rnd
      ,regexp_substr(f,'[^;]+',1,2) sid
      ,regexp_substr(f,'[^;]+',1,3) process
      ,regexp_substr(f,'[^;]+',1,4) pid
from t2 
where cnt>1
order by f

Result:


F RND SID PROCESS PID
AARV;130;5472;30 AARV 130 5472 30
AARV;68;2228;29 AARV 68 2228 29
AC2R;130;5472;30 AC2R 130 5472 30
AC2R;68;2228;29 AC2R 68 2228 29
AC8O;130;5472;30 AC8O 130 5472 30
AC8O;68;2228;29 AC8O 68 2228 29
AKVZ;130;5472;30 AKVZ 130 5472 30
AKVZ;68;2228;29 AKVZ 68 2228 29
ALTQ;130;5472;30 ALTQ 130 5472 30
ALTQ;68;2228;29 ALTQ 68 2228 29
… … … … …

About the performance of exception handling

Posted on by Posted in oracle, query optimizing Leave a comment

This article is about a well-known fact about the poor performance of exception handling.

Yes, the exception handling is rather slow, however, it is not necessary to try to avoid exceptions whenever possible, and by any means. For example, I often see that people are trying to avoid them even in cases of search by primary key where probability of receiving “no_data_found” is minimal.
In general, we should consider the probable frequency of exceptions and “overhead”, which is added by the chosen way around the exceptions.

Let me explain this with an example, which I mentioned earlier: suppose we have a code that returns a field from the table by “pk” and it returns “null” in case there is no such entry.
Test table:

create table t_test(a primary key, b)
as
select level,level from dual connect by level<=1e5;

Lets create a standard function for tests:

create or replace function f1(p in number) return number
as
  res number;
begin
  select/*+ F1 */ b into res
  from t_test t
  where t.a=p;
  return res;
exception when no_data_found then
  return null;
end;

The most common options to avoid the exceptions mechanism in these cases are the followings:

Spoiler:: Variant 1 SelectShow

By the way, don’t use this in case your cursor can not have more than one entry:
Spoiler:: Hidden text SelectShow

Otherwise, there will be attempts of the second iteration, which you can observe in the profiler.
Spoiler:: Variant 2 SelectShow

Here I would like to propose my own option for this:
Spoiler:: Hidden text SelectShow

And now lets carry out a basic test by executing these functions for the test table:

declare
  v       integer;
  v_start integer:= 1;
  v_end   integer:= 100000;
    l_timer integer := dbms_utility.get_time;
    procedure print(msg varchar2) is
    begin
      dbms_output.put_line(to_char((dbms_utility.get_time-l_timer)/100,'9990.00')||' '||msg);
      l_timer:=dbms_utility.get_time;
    end;
     
begin
  print('start');
  for i in v_start..v_end loop
    v:=f1(i);
  end loop;
  print('1');
  for i in v_start..v_end loop
    v:=f2(i);
  end loop;
  print('2');
  for i in v_start..v_end loop
    v:=f3(i);
  end loop;
  print('3');
  for i in v_start..v_end loop
    v:=f4(i);
  end loop;
  print('4');
end;

As a result, we get the following ratio:

Variant Time(sec)
Variant 1(with exception) 3.03
Variant 2(with cycle) 3.62
Variant 3(with min) 3.34
Variant 4(скалярный подзапрос) 3.10

As you can see, the original query is the fastest in case the exceptions are not called! Lets now check it with different percents of exceptions: exceptions will be for queries с i<=0, the total number of calls will be 100001, I will change v_start and v_end in pairs: (-5000, 95000), (10000, 90000), (-50000, 50000), (-90000, 10000):

Spoiler:: Hidden text SelectShow

Summary table of multiple comparisons:

Variant 0% ~5% ~10% ~50% ~90%
Variant 1(with exception) 3.04 3.12 3.16 3.82 4.51
Variant 2(with cycle) 3.18 3.21 3.20 3.51 3.85
Variant 3(with min) 3.37 3.34 3.29 3.25 3.18
Variant 4(скалярный подзапрос) 3.12 3.06 3.03 2.98 2.94

What conclusions can be drawn from this:

  • As you can see, 5% of exceptions is a kind of turning point for this table, when the standard option with exception becomes less effective than the option with subquery (by about ~4.5% to be precise), and about ~10% than the remaining two.
  • Options with “min” and a cycle as a whole are worse than the option with a subquery.
  • Options with subquery and “min” accelerate as the number of "empty" queries increases.