Category Archives: parallel

Simple function returning Parallel slave info

Posted on by Posted in oracle, parallel, query optimizing, SQL, statistics, troubleshooting 1,589 Page views Leave a comment

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as
   vSID int;
   res varchar2(30);
begin
   vSID:=userenv('sid');
   select 
           to_char(s.server_group,'fm000')
    ||'-'||to_char(s.server_set,'fm0000')
    ||'-'||to_char(s.server#,'fm0000')
    ||'('||s.sid||','||s.degree||'/'||s.req_degree||')'
    into res
   from v$px_session s 
   where s.sid=vSID;
   return res;
exception when no_data_found then
   return 'no_parallel';
end;
/

Simple example:

select--+ parallel
  px_session_info, count(*)
from sys.obj$
group by px_session_info
/
PX_SESSION_INFO           COUNT(*)
------------------------  --------
001-0002-0001(630,2/2)     38298
001-0002-0002(743,2/2)     34706

Smart Scan and Recursive queries

Posted on by Posted in adaptive serial direct path reads, CBO, hints, oracle, parallel, SmartScan, trace, troubleshooting 1,709 Page views Leave a comment

Since Christmas I have been asked to investigate two different “failures to use Smart Scan”. It turns out they both fell into the same little known restriction on the use of Direct Read. Smart Scan critically depends on Direct Read in order to read the synthetic output blocks into private buffers in PGA so with Direct Read disabled Smart Scan is also disabled. In these two cases the restriction is on using Direct Read on Serial Recursive queries.

Case 1: Materialized View Refresh

A customer asked me to investigate why his MView refresh was running slowly and was failing to use Smart Scan. He had used 'trace[NSMTIO] disk=highest' which showed the cause as:

Direct Read for serial qry: disabled(::recursive_call::kctfsage:::)
Continue reading→

How to speed up slow unicode migration of a table with xmltype columns

Posted on by Posted in oracle, parallel, query optimizing, troubleshooting 2,324 Page views Leave a comment

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute 

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER';

to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);
commit;

5. Disable event 22838:

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF';

6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

dbms_random in parallel

Posted on by Posted in documentation, oracle, parallel 1,816 Page views 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
… … … … …

A funny fact about collect

Posted on by Posted in collect, oracle, parallel, query optimizing 2,291 Page views 1 Comment

Many people know that oracle creates domain types on its own when necessary, for example when using a type declared in a package (before 11g they could be observed in dba_objects with the name like ‘PLSQL%’).

Fact #1

It acts in the same way when calling an aggregate function “collect”.

-- Firstly we check if there are such types
DB11G/XTENDER> select t.type_name,t.type_name,t.typecode 
 2 from dba_types t 
 3 where t.type_name like 'SYSTP%';
 
no rows selected
 
 
-- Executing a query with collect
DB11G/XTENDER> select collect(level) from dual connect by level<=10;
 
COLLECT(LEVEL)
-------------------------------------------------------------------------
 
SYSTPZvGjVQTySRSjYVlHXyEE2Q==(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
 
1 row selected.
 
 
-- We check it again and observe that a new type SYSTP% has appeared
DB11G/XTENDER> select t.type_name,t.type_name,t.typecode 
 2 from dba_types t 
 3 where t.type_name like 'SYSTP%';
 
TYPE_NAME                      TYPE_NAME                      TYPECODE
------------------------------ ------------------------------ ------------
SYSTPZvGjVQTySRSjYVlHXyEE2Q==  SYSTPZvGjVQTySRSjYVlHXyEE2Q==  COLLECTION

And also we select data on it from sys.obj$. We will need it later:

DB11G/XTENDER> select obj#,type#,ctime,mtime,stime,status
  2  from sys.obj$ o$
  3  where o$.name = 'SYSTPZvGjVQTySRSjYVlHXyEE2Q==';
 
  OBJ#  TYPE# CTIME               MTIME               STIME              
------ ------ ------------------- ------------------- -------------------
103600     10 28.04.2012 01:02:35 28.04.2012 01:02:35 28.04.2012 01:02:35

This is a natural result as you can not return information to the client without having described it.

Fact #2

Now as we know that the type is created, it is interesting to know what will happen to this type: whether it will be removed either after fetch or after disconnecting the client? For example, the domain type was automatically dropped when the packet type was removed. Are we going to have a similar automatic remove here?

According to Bug 4033868: COLLECT FUNCTION LEAVES TEMPORARY SYS TYPES BEHIND this bug is fixed in “11.0”, but I am testing it on 11.2.0.1 and a basic check after disconnection showed, that this type exists until instance restart. In fact, it stays there even after that, but it is not displayed in dba_objects.

I will drop it manually, so I won’t have to restart the instance once again. This is absolutely similar to how oracle “removes” this type in 11.2:

DB11G/XTENDER> drop type "SYSTPZvGjVQTySRSjYVlHXyEE2Q==";
 
Type dropped.

And now lets check it:

DB11G/XTENDER> select * from dba_types
   where type_name='SYSTPZvGjVQTySRSjYVlHXyEE2Q==';
 
no rows selected

It seems like we have removed it, but what if we look in the sys.obj$:

DB11G/XTENDER> select obj#,type#,ctime,mtime,stime,status
  2  from sys.obj$ o$
  3  where o$.name = 'SYSTPZvGjVQTySRSjYVlHXyEE2Q==';
 
  OBJ#  TYPE# CTIME               MTIME               STIME              
------ ------ ------------------- ------------------- -------------------
103600     10 28.04.2012 01:02:35 28.04.2012 01:40:37 31.12.4712 23:59:59

As you can see, the object is still there, but with type#=10 and with “stime” equal to the last date of year 4712, and before that it was type#=13 and stime=mtime=ctime, and in 10.2 after manual drop of this type no entries were left. I will explain the correspondence of the fields from “sys.obj$” and “dba_objects” to clarify this: obj# – object_id, type# ~ type code, ctime,mtime,stime – created, last_ddl_time, timestamp respectively. By the “dba_objects” view code we will see that type# = 10 is supposedly “NON-EXISTENT” and displaying it is not necessary.

and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)

And the date being set to 31.12.4712 23:59:59 indicates its irrelevance – this future is too distant 🙂

Fact #3

“Collect” is a pretty buggy thing in general, as I have repeatedly noticed, and Metalink has information about lots of bugs related to “collect” (for example, Bug 11906197 “Parallel query with COLLECT function fails with ORA-7445/ORA-600.”,”Bug 8912282: COLLECT+UNIQUE+ORDER DOES NOT REMOVE DUPLICATES”, “Bug 6145841: ORA-600[KOLOGSF2] ON CAST(COLLECT(..)) CALL”,”Bug 11802848: CAST/COLLECT DOES NOT WORK IN VERSION 11.2.0.2 WITH TYPE SYS.DBMS_DEBUG_VC2COLL”, “Bug 6996176: SELECT COLLECT DISTINCT GROUP BY STATEMENT RETURNS DUPLICATE VALUES”)

Test table

[sourcecode language=”sql”]
create table test_parallel parallel 8 as
select mod(level,8) a, level b
from dual
connect by level<=1000;
create index IX_TEST_PARALLEL on TEST_PARALLEL (A);
[/sourcecode]

[collapse]

Errors

[sourcecode language=”sql”]
DB11G/XTENDER> select/*+ PARALLEL(2)*/ cast(collect(a) as number_table) from test_parallel ;
select/*+ PARALLEL(2)*/ cast(collect(a) as number_table) from test_parallel
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-21710: argument is expecting a valid memory address of an object
 
Elapsed: 00:00:00.12
DB11G/XTENDER> select cast(collect(b) as number_table) from test_parallel group by a;
select cast(collect(b) as number_table) from test_parallel group by a
*
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly
 
Elapsed: 00:00:17.57
[/sourcecode]

[collapse]

I don’t experience such errors while using my slow aggregate. Here is a common dilemma: whether to use an unstable but speedy “collect”, or a slow aggregate of my own…

An example of an aggregate

[sourcecode language=”sql”]
create or replace type ncollect_type as object
(

data sys.ku$_objnumset,

static function ODCIAggregateInitialize
( sctx in out ncollect_type )
return number ,

member function ODCIAggregateIterate
( self in out ncollect_type ,
val in number
) return number ,

member function ODCIAggregateDelete
( self in out ncollect_type,
val in number
) return number ,
member function ODCIAggregateTerminate
( self in ncollect_type,
returnval out sys.ku$_objnumset,
flags in number
) return number ,

member function ODCIAggregateMerge
( self in out ncollect_type,
ctx2 in ncollect_type
) return number
)
/
create or replace type body ncollect_type is

static function ODCIAggregateInitialize
( sctx in out ncollect_type )
return number
is
begin
sctx := ncollect_type( sys.ku$_objnumset()) ;
return ODCIConst.Success ;
end;

member function ODCIAggregateIterate
( self in out ncollect_type ,
val in number
) return number
is
begin
self.data:=self.data multiset union sys.ku$_objnumset(val);
return ODCIConst.Success;
end;

member function ODCIAggregateDelete
( self in out ncollect_type,
val in number
) return number
is
begin
self.data:=self.data multiset except sys.ku$_objnumset(val);
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate
( self in ncollect_type ,
returnval out sys.ku$_objnumset ,
flags in number
) return number
is
begin
returnval:=self.data;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge
( self in out ncollect_type ,
ctx2 in ncollect_type
) return number
is
begin
self.data := self.data multiset union ctx2.data;
return ODCIConst.Success;
end;
end;
/
[/sourcecode]

[collapse]

And the results:

Variant Time(sec)
select/*+ NO_PARALLEL*/ cast(collect(b) as number_table) from test_parallel group by a; 0.03
select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel group by a 0.08
select ncollect(b) from test_parallel group by a; 0.07
select/*+ NO_PARALLEL*/ collect(a) from test_parallel; 0.02
select/*+ NO_PARALLEL*/ ncollect(a) from test_parallel 0.18
select ncollect(a) from test_parallel; 0.19
select/*+ NO_PARALLEL*/ collect(b) from test_parallel; 0.02
select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel 0.18
select ncollect(b) from test_parallel; 0.06