Monthly Archives: October 2012

The change in “dump sort statistics” trace (event 10032)

Posted on by Posted in CBO, oracle, trace Leave a comment

Earlier “Event 10032 – Dump Sort Statistics” showed information about sorts only(11.2.0.1 incl.), but in 11.2.0.3 it also showing “Abridged” call stack. I have not tested it on 11.2.0.2, so I can’t tell from which version it works.

alter session set events '10032 trace name context forever, level 10';

From trace file:

*** 2012-10-30 23:14:34.627
*** SESSION ID:(41.15067) 2012-10-30 23:14:34.627
*** CLIENT ID:() 2012-10-30 23:14:34.627
*** SERVICE NAME:(orasql.org) 2012-10-30 23:14:34.627
*** MODULE NAME:(SQL*Plus) 2012-10-30 23:14:34.627
*** ACTION NAME:() 2012-10-30 23:14:34.627

soropn: opened (new) sort, sordef 0x7fc4679e2550, flags 0x802
        maxkey 25, nflds 12, nkflds 1

*** 2012-10-30 23:14:34.678
----- Current SQL Statement for this session (sql_id=3ktacv9r56b51) -----
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
        Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-opifch2<-opifch<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-rpidrv<-rpifch<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kglgob<-kgldpo0<-qcdlgpo<-qcsRslvPLSQLInvoc1<-qcsRslvPLSQLInvoc<-qcsRslvName<-qcsridn<-qcsraic<-qcspqbDescendents
<-qcspqb<-kkmdrv<-opiSem<-opiDeferredSem<-opitca<-kksFullTypeCheck<-rpiswu2<-kksLoadChild<-kxsGetRuntimeLock<-kksfbc<-kkspsc0<-kksParseCursor<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-__libc_start_main
<-_start        End of abridged call stack trace.

*** 2012-10-30 23:14:35.328
soreod: sorp 0x7fc4679e2550
---- Sort Parameters ------------------------------
sort_area_size                    65536
sort_area_retained_size           65536
sort_multiblock_read_count        1
max intermediate merge width      3

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 connections incoming by dblink, and I decided to make it more convenient, without the trigger.
It’s just three simple steps:

  1. Create new service
  2. Turn on tracing on this service
  3. Edit tnsnames and add dblink for this service if needed

So Oracle will trace all connections 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);