Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Archive for January, 2007

Transforming from list to a set of rows

Posted by Riyaj Shamsudeen on January 11, 2007

A developer asked me how to transform a column with a list of values to a set of rows ?
Column value is of this format:
1, ’100,101,102,103,104,105′
Requirement was to convert above value to the format below:
1, 100
1, 101
1, 102

Solution

:

It is easy to do this using a pipelined function. Rows returned from a function can be used as a row source, as if it is a table row source. Metalink document 136909.1 describes this pipelined functions excellently.

Here is the test case, for the solution. Some part of code is borrowed from Tom Kyte’s original asktom posting.

– Dropping, recreate table tlist and populate with two rows.
drop table tlist;
create table tlist (c1 number, c2 varchar2(30) );
insert into tlist values (1,’100,101,102,103,104,105′);
insert into tlist values (2,’200,201,202,203,204,205′);
commit;

– Pipelined functions returns collections. Declaring a type TestListObj.
– TestList is an array of TestListObj
drop type TestList;
drop type TestListObj;
create or replace type TestListObj as object
(c1 number, c2 number);
create or replace type TestList as table of TestListObj;

— Creating a pipelined function.
REM This function :
REM for each row from tlist table
REM Read column values c1 and c2.
REM c2 has list of values separated by comma
REM For each value from the list
REM Construct TestListObj and store that as a member of TestList table object
REM Return TestList

create or replace function in_list_Test
return
TestList
as

l_string
long;
l_data
TestList := TestList();
n number;
begin
for l_csr in (select c1,c2 from tlist )
loop
l_string := l_csr.c2 || ‘,’;
loop
exit when l_string is null;
n := instr( l_string, ‘,’ );
l_data.extend;
l_data(l_data.count) := TestListObj( l_csr.c1, ltrim( rtrim( substr( l_string, 1, n-1 ) ) ) );
l_string := substr( l_string, n+1 );
end loop;
end loop;
return l_data;
end;

pause
show errors
select * from table (cast (in_list_Test as TestList))
/

C1 C2
1 100
1 101
1 102
….
Hope, you can make use of this.

Posted in SQL new features | Leave a Comment »

Log group switch sequence

Posted by Riyaj Shamsudeen on January 11, 2007

Q: How does Oracle determines which log group to use next,
at the onset of log switch ?
Q: How does adding a log group alters the sequence ?

Log group to switch, is determined from controlfile.
Let’s review the controlfile for pertinent information
Dumping the control file using following commands reveals more information:

oradebug setmypid
oradebug dump controlf 12

Above commands generated a trace file in
user_dump_dest directory and following is the excerpt from the file.
Only few lines shown to improve readability.

**********************************************************
LOG FILE RECORDS
**********************************************************

(blkno = 0×9, size = 72, max = 16, in-use = 8, last-recid= 15)
LOG FILE #1:
siz: 0xc8000 seq: 0x0000022b hws: 0×2 bsz: 512 nab: 0×9 flg: 0×1 dup: 1
LOG FILE #2:
siz: 0xc8000 seq: 0x0000022e hws: 0×2 bsz: 512 nab: 0×39 flg: 0×1 dup: 1
LOG FILE #3:
siz: 0xc8000 seq: 0×00000230 hws: 0×1 bsz: 512 nab: 0xffffffff flg: 0×8 dup: 1
LOG FILE #4:
siz: 0×5000 seq: 0×00000228 hws: 0×2 bsz: 512 nab: 0xc flg: 0×1 dup: 1
LOG FILE #5:
siz: 0×5000 seq: 0x0000022a hws: 0×2 bsz: 512 nab: 0×4 flg: 0×1 dup: 1
LOG FILE #6:
siz: 0×5000 seq: 0x0000022c hws: 0×2 bsz: 512 nab: 0×98 flg: 0×1 dup: 1
LOG FILE #7:
siz: 0×5000 seq: 0x0000022f hws: 0×2 bsz: 512 nab: 0xe9 flg: 0×1 dup: 1
LOG FILE #8:
siz: 0×5000 seq: 0x0000022d hws: 0×2 bsz: 512 nab: 0×5 flg: 0×1 dup: 1

Sequence# above is log sequence # in hexadecimal.

Oracle determines the next log group to switch:
i) searching for the log group with lowest sequence# that is archived.
ii) If a new log group is added, then that new log group will have log sequence# as zero and so that group will be selected
iii) If there are two new log groups, then first one encountered in the above array is selected.

Third point is quite important, since if we drop a log group, then that log group is simply marked as deleted, not completely removed from the array. So, if the same log group is readded, then deleted entry is reused.

Luckily, above controlfile array is externalized as x$kccle table. Instead of dumping the controlfile, we could use x$kccle to test our understanding:

Following SQL, can predict which log group will be selected by Oracle, during next log switch:

SELECT indx, lenum group#, leseq sequence#, archived,
DECODE (leflg, 8, ‘CURRENT’) curstatus,
CASE WHEN leseq = minseq AND indx = minind THEN ‘NEXT’
END NEXT
FROM (SELECT indx, lenum, leseq, leflg,
DECODE (BITAND (leflg, 1), 0, ‘NO’, ‘YES’) archived,
FIRST_VALUE (leseq) OVER (ORDER BY leseq, indx,BITAND (leflg, 1) DESC) minseq,
FIRST_VALUE (indx) OVER (ORDER BY leseq, indx, BITAND (leflg, 1) DESC) minind
FROM x$kccle
WHERE lesiz != 0 AND inst_id = USERENV (‘instance’))
ORDER BY indx

For RAC:

SELECT lethr, indx, lenum group#, leseq sequence#, archived,
DECODE (leflg, 8, ‘CURRENT’) curstatus,
CASE WHEN leseq = minseq AND indx = minind THEN ‘NEXT’
END NEXT
FROM (SELECT lethr, indx, lenum, leseq, leflg,
DECODE (BITAND (leflg, 1), 0, ‘NO’, ‘YES’) archived,
FIRST_VALUE (leseq) OVER (partition by lethr ORDER BY leseq, indx,BITAND (leflg, 1) DESC) minseq,
FIRST_VALUE (indx) OVER ( partition by lethr ORDER BY leseq, indx, BITAND (leflg, 1) DESC) minind
FROM x$kccle
WHERE lesiz != 0 AND inst_id = USERENV (‘instance’))
ORDER BY indx

See Oracle log sequence for complete test case.

Posted in Oracle database internals | Leave a Comment »

At last, my blog is up.

Posted by Riyaj Shamsudeen on January 9, 2007

This has been one of my long pending wishes to have a blog and update it regularly.

I am an Oracle Database Administrator and I specialize in Oracle database internals and performance tuning. My emphasis currently in Real Application Clusters and E-Business Suite.

Hopefully, I will keep this blog updated and love to hear your comments.

I have also written couple of articles, presented few and will upload them here soon.

Posted in Uncategorized | 11 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 179 other followers