Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Words fail me

I just stumbled across this fine example of the art of writing news articles when you clearly don’t understand the subject. The article has a date of 2 days ago on the front page, though curiously a September 2009 date on the article. The article purports to discuss the omotion feature of Oracle Rac One-Node. My [...]

Session Snapper v3.11 – bugfix update – now ASH report works properly on Oracle 10.1 too

This is an updated version of Snapper, which works ok on Oracle 10.1 now as well (9i support is coming some time in the future :)

Thanks to Jamey Johnston for sending me the fix info (and saving me some time that way :)

So if you have some problems with Snapper on Oracle 10.1, please make sure you have the latest version v3.11, which you can get from here:

http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper

The output below is from Snapper 3.11 on Oracle 10.1.0.5, the ASH columns in the bottom part of the output are displayed correctly now:

SQL> @snapper ash,ash1,ash2,ash3,stats,gather=t 15 1 all
Sampling with interval 15 seconds, 1 times...

-- Session Snapper v3.11 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

----------------------------------------------------------------------------------------------------------------------
    SID, USERNAME  , TYPE, STATISTIC                               ,         DELTA, HDELTA/SEC,    %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------
     52, SYSTEM    , TIME, PL/SQL execution elapsed time           ,         53968,      3.6ms,      .4%, |          |
     52, SYSTEM    , TIME, DB CPU                                  ,         10000,   666.67us,      .1%, |          |
     52, SYSTEM    , TIME, sql execute elapsed time                ,        118225,     7.88ms,      .8%, |@         |
     52, SYSTEM    , TIME, DB time                                 ,        118632,     7.91ms,      .8%, |@         |
     54, SYSTEM    , TIME, hard parse elapsed time                 ,        289905,    19.33ms,     1.9%, |@         |
     54, SYSTEM    , TIME, parse time elapsed                      ,        528034,     35.2ms,     3.5%, |@         |
     54, SYSTEM    , TIME, PL/SQL execution elapsed time           ,       5010579,   334.04ms,    33.4%, |@@@@      |
     54, SYSTEM    , TIME, DB CPU                                  ,      10660000,   710.67ms,    71.1%, |@@@@@@@@  |
     54, SYSTEM    , TIME, sql execute elapsed time                ,      12920952,    861.4ms,    86.1%, |@@@@@@@@@ |
     54, SYSTEM    , TIME, DB time                                 ,      12937606,   862.51ms,    86.3%, |@@@@@@@@@ |
     54, SYSTEM    , TIME, sequence load elapsed time              ,          1079,    71.93us,      .0%, |          |
     56, (MMNL)    , TIME, background cpu time                     ,           940,    62.67us,      .0%, |          |
     56, (MMNL)    , TIME, background elapsed time                 ,           940,    62.67us,      .0%, |          |
     58, (MMON)    , TIME, background cpu time                     ,           158,    10.53us,      .0%, |          |
     58, (MMON)    , TIME, background elapsed time                 ,           158,    10.53us,      .0%, |          |
     64, (RBAL)    , TIME, background cpu time                     ,            86,     5.73us,      .0%, |          |
     64, (RBAL)    , TIME, background elapsed time                 ,            86,     5.73us,      .0%, |          |
     68, (CJQ0)    , TIME, background cpu time                     ,           820,    54.67us,      .0%, |          |
     68, (CJQ0)    , TIME, background elapsed time                 ,           820,    54.67us,      .0%, |          |
     70, (SMON)    , TIME, background cpu time                     ,           141,      9.4us,      .0%, |          |
     70, (SMON)    , TIME, background elapsed time                 ,           141,      9.4us,      .0%, |          |
     71, (CKPT)    , TIME, background cpu time                     ,         14515,   967.67us,      .1%, |          |
     71, (CKPT)    , TIME, background elapsed time                 ,         14515,   967.67us,      .1%, |          |
     72, (LGWR)    , TIME, background cpu time                     ,       1530000,      102ms,    10.2%, |@         |
     72, (LGWR)    , TIME, background elapsed time                 ,       1954778,   130.32ms,    13.0%, |@@        |
     73, (DBW0)    , TIME, background cpu time                     ,         10000,   666.67us,      .1%, |          |
     73, (DBW0)    , TIME, background elapsed time                 ,        268787,    17.92ms,     1.8%, |@         |
     74, (MMAN)    , TIME, background cpu time                     ,           141,      9.4us,      .0%, |          |
     74, (MMAN)    , TIME, background elapsed time                 ,           141,      9.4us,      .0%, |          |
     75, (PMON)    , TIME, background cpu time                     ,          1636,   109.07us,      .0%, |          |
     75, (PMON)    , TIME, background elapsed time                 ,          1636,   109.07us,      .0%, |          |
--  End of Stats snap 1, end=2010-03-27 16:37:13, seconds=15

-----------------------------------------------------------------------
Active% | SQL_ID          | EVENT                     | WAIT_CLASS
-----------------------------------------------------------------------
    61% | 6d0z2j01c8ytc   | ON CPU                    | ON CPU
    22% |                 | log file parallel write   | System I/O
     7% | 6d0z2j01c8ytc   | db file sequential read   | User I/O
     3% | 0zkt25f36kbzd   | ON CPU                    | ON CPU
     3% |                 | db file parallel write    | System I/O
     2% | g1xapjmt4vm5c   | ON CPU                    | ON CPU
     2% |                 | ON CPU                    | ON CPU
     2% | gaxwgwd72b3pn   | ON CPU                    | ON CPU
     1% | 4ftbahd08ab2a   | ON CPU                    | ON CPU
     1% | c69wrxcndxuzw   | ON CPU                    | ON CPU

-----------------------------------------------------
Active% | EVENT                     | WAIT_CLASS
-----------------------------------------------------
    76% | ON CPU                    | ON CPU
    22% | log file parallel write   | System I/O
     9% | db file sequential read   | User I/O
     3% | db file parallel write    | System I/O
     3% | db file scattered read    | User I/O
     1% | direct path write temp    | User I/O

----------------------------------
Active% |    SID | SQL_ID
----------------------------------
    69% |     54 | 6d0z2j01c8ytc
    23% |     72 |
     3% |     54 | 0zkt25f36kbzd
     3% |     73 |
     3% |     54 | 8qs4shjvhk2w4
     2% |     54 | g1xapjmt4vm5c
     2% |     54 | gaxwgwd72b3pn
     1% |     54 | 3w6304ztrww4h
     1% |     54 | b86h705svfmjz
     1% |     54 | drppqann6dwfa

---------------------------------------------------
Active% | PLSQL_OBJE | PLSQL_SUBP | SQL_ID
---------------------------------------------------
    69% | N/A        | N/A        | 6d0z2j01c8ytc
    27% | N/A        | N/A        |
     3% | N/A        | N/A        | 0zkt25f36kbzd
     3% | N/A        | N/A        | 8qs4shjvhk2w4
     2% | N/A        | N/A        | g1xapjmt4vm5c
     2% | N/A        | N/A        | gaxwgwd72b3pn
     1% | N/A        | N/A        | 3w6304ztrww4h
     1% | N/A        | N/A        | b86h705svfmjz
     1% | N/A        | N/A        | drppqann6dwfa
     1% | N/A        | N/A        | c69wrxcndxuzw

--  End of ASH snap 1, end=2010-03-27 16:37:13, seconds=15, samples_taken=96

PL/SQL procedure successfully completed.

SQL>

Share/Bookmark

“Ideas build off ideas”… making use of Social Networking sites

OracleUsersPh

In this post (which has been looong overdue :p ) I’ll show you how you could make use of Social Networking Sites to make knowledge sharing and meeting people more fun. As you can see from the illustration above, I’ve made use of the following sites/services to build a dynamic and multidimensional means of communication:

  • Facebook
  • Twitter
  • FeedBurner RSS
  • FeedBurner Mail Subscription
  • Google Sites
  • LinkedIn

The story behind it…

I am a DBA for 4years+, and started with zero knowledge about Oracle. I joined SQL*Wizard, which has a great training program and exposed me to a lot of difficult DBA situations. After a while I got my OCA and OCP then got interested on Linux and got my RHCT… well, hopefully RHCE soon. All of my accumulated knowledge came from three things. First: lots of reading/research Second: doing test cases….. Third:…..guess what….

it’s the community.

And speaking of community, we have our local users group here, called Philippine Oracle Users Group (POUG) which has been inactive for a long time (speaking based on my experience) I joined the POUG Yahoo Group last November 2007, ever since I joined there was no activity… then months have passed I was able to gain more knowledge and experience as a DBA, joined other forums, participated on online communities, mailing list, and even follow other Users Group activities. Then one day, I was checking on some of the Yahoo Groups that I’ve joined and a couple of mouse clicks brought me again to POUG Yahoo Group and noticed the last message was still year 2007…. yes I felt bad…. I think the community spirit among technology groups in the US or other countries seems to be stronger… I admire these guys, and I want to have one here, locally. It’s just difficult to motivate people to share ideas and help each other.

BTW the latest news about POUG… it is closing down as a corporation. There could be a number of reasons and one of them could be the inactive status. Whatever are the reasons for closing it, I am sure I’m not alone wanting to have a local community…

It got me thinking, why not do some social experiment? … If I could pool a number of Oracle Users (DBA/Developers), feed them updates about the latest in Oracle, give helpful articles, links, blogs, videos, or any technical info that would be useful on their everyday tasks will stir up the curiosity and I may find like minded people that could help me build (or revive) the local Users Group/community. Well, I’d like to have an environment similar to Oracle-L (http://www.freelists.org/list/oracle-l) or China’s ITPUB (http://www.itpub.net), where you can find lots of good stuff, good questions, good replies, no one is ranting about something (but this can’t be completely avoided), somebody is sharing of a problem then the other guy (expert or a newbie) shares his solution, nobody thinks of competition (company A vs. company B, or consultant A or B vs C)…

just pure technology…(but yes, preferably there should be real interaction)

All of these inspired me to create a Facebook page, and called it “Oracle Users – Philippines” (sorry I can’t think of a better name </p />
</p></div>

    	  	<div class=

VST - complete for DB Optimizer 2.5 !


The last step is finished before we functioinal freeze on the VST diagrams in DB Optimizer 2.5. We have now added Filter Ratios to the diagram ! Functional freeze is next week and production date is targeted for May 3.

  • blue numbers are the percent of the table returned after the predicate filters have been applied
  • red numbers are the two table join sizes
  • green numbers are the table sizes
The idea is to start at the most selective filter and then join into keep the running row set to the smallest size.
Start at A, the most selective filter
Join to C, the smallest running row set size
Join to G, the smallest row set size
Join last to D
This path was almost 3x as fast as the default path chosen by the optimizer. I just looked at the diagram , order the tables in that fashion and used the /*+ ORDERED */ hint
Interesting items in the diagram
  • only fields used in the where clause are shown by default
  • clicking on a link shows the fields used in the join (above I've clicked on two links higlight two joins)
  • Fields with an "F" have a filter on them
There many other interactive features in the diagram.
The text for this query was
select distinct * from foo.a, foo.c, foo.d, foo.g
WHERE a.planted_date = to_date('02/10/2008','dd/mm/yyyy')
AND a.pears = 'D'
AND a.green_beans = '1'
AND a.planted_date = c.planted_date
AND a.pears = c.pears
AND a.zuchinis = c.zuchinis
AND a.brocoli = c.brocoli
AND a.planted_date = d.planted_date
AND a.pears = d.pears
AND a.harvest_size = d.harvest_size
AND c.oranges = d.oranges
AND c.apples = d.apples
AND (d.lemons = 0 OR d.lemons IS NULL)
AND a.planted_date = g.planted_date
AND a.pears = g.pears
AND a.harvest_size = g.harvest_size
AND c.oranges = g.oranges
AND c.apples = g.apples
AND (g.lemons = 0 OR g.lemons IS NULL)
and a.zuchinis='0236'
ORDER BY a.zuchinis, a.brocoli;

Difference between Session and Process

Someone asked me the other day the difference between process and session in Oracle. Are they the same thing? Well, why then there are two parameters in the initialization parameter file – sessions and processes? Is it possible that a process does not have a corresponding session? Conversely, is it possible to have a session without a process?

And, furthermore, the processes parameter is larger than the sessions. So, are there more processes than sessions? To answer that, she got the count from v$session and v$process:

SQL> select count(1) from v$process;

COUNT(1)
----------
23

SQL> select count(1) from v$session;

COUNT(1)
----------
20

At least the answer was pretty clear – there are more processes than sessions (23 versus 20). But then, someone pointed to the view V$RESOURCE_LIMIT, which records the current usage of the various definable limits. Checking the limits for two resources – sessions and processes:

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49

This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.

This contradicts what we saw earlier. The processes usage matches – 23 processes seen from the resource limit view and count from v$process; but the session count differs. From the resource limit, we see that there are 26 sessions; but v$session shows only 20. Why there is a difference?

Processes without Sessions

Let’s see the first one – processes without sessions. We can easily find them:

select pid, spid, addr, program
from v$process
where addr not in (
select paddr from v$session
)

PID SPID ADDR PROGRAM
---------- ---------- -------- ------------------------------
1 4116A2B8 PSEUDO
18 23904 411758AC oracle@oradba1 (D000)
19 23906 41176360 oracle@oradba1 (S000)

Other than the first one, these are real processes. The processes refer to the dispatcher process and the shared server processes. These are not background processes; so they don’t appear in V$BGPROCESS.

Sessions without Processes

Now what about the second case – the sessions without processes? That one is harder to visualize. A session in Oracle is a connection to the database. As we know from the two-task architecture, a client process of a session kicks off a server process which in turn does the heavy lifting from the Oracle database. Keeping that in mind, it might be difficult to envision a session without a process. With a twist of the same concept, is it possible for two sessions to share a single process? Once again, that defies understanding as well. A session is an exclusive conduit to the database; how can that be shared? Even in case of share servers, each shared server session has its own process.

It is possible, under some special circumstances. They are used by Recursive Database Sessions, persistent for a very small amount of time; but they are sessions. When you create a table, alter an index or perform other tasks, Oracle must perform certain tasks on your behalf, which are called recursive actions. One example may make it clear. When you create a table, Oracle must determine that you have sufficient privileges to do that and the determination is made through a recursive session. Let’s see a demonstration of the sessions in action.

First connect to the database and get your own SID:

select sid, paddr, spid
from v$session s, v$process p
where s.audsid = userenv('SESSIONID')
and p.addr = s.paddr
/

SID PADDR SPID
---------- -------- ------------------------
17 4117F938 24042

This shows that my own session has the SID 17, the OS process id of 24042 and the address of the server process is 4117F938. I know, I know; this is a 32-bit system, so the process address is small.

If you look at v$process, you can get the details on that process as well, using the paddr value. Conversely, if you select the sessions with that paddr value:

SQL> select sid from v$session where paddr = '4117F938';

SID
---
17

There is exactly one session. If you check it for any process paddr, you will see exactly one SID. But, that’s where the mystery lies. The v$session view does not tell the whole story; it does not show all the sessions. Here is the definition of the view:

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SESSION';

VIEW_DEFINITION
----------------------------------------------------------------------------------
select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetr
n),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIP
ED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'), s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ksusemnm,s.ksusetid,
s.ksusepnm, decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_numbe
r(null), s.ksusesch), s.ksusesesta, decode(s.ksuseseid, 0, to_number(null), s.ksuseseid), s.ksusepsq, s.ksusepha, s.ksusepsi, decode(s.ksusepch, 65535,
to_number(null), s.ksusepch), s.ksusepesta, decode(s.ksusepeid, 0, to_number(null), s.ksusepeid), decode(s.ksusepeo,0,to_number(null),s.ksusepeo), decod
e(s.ksusepeo,0,to_number(null),s.ksusepes), decode(s.ksusepco,0,to_number(null),s.ksusepco), decode(s.ksusepco,0,to_number(null),s.ksusepcs), s.ksuseapp,
s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxo
pt, 12),0,'NO','YES'),decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),dec
ode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),decode(bitand(s.ks
usepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'
DISABLED','ENABLED')), s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLD
ER', 4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292
,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_numb
er(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 65535)),w.kslwtseq,w.kslwtevt,e.kslednam, e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass, decode(w.kslwtin
wait, 0,decode(bitand(w.kslwtflags,256), 0,-2, decode(round(w.kslwtstime/10000), 0,-1,
round(w.kslwtstime/10000))), 0), decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000), round(w.kslwtstime/1000000)), de
code(w.kslwtinwait,1,'WAITING', decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME', decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME', 'WAIT
ED KNOWN TIME'))),w.kslwtstime, decode(w.kslwtinwait,0,to_number(null), decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), , decode(
bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE'),decode(b
itand(s.ksuseflg2,65536) + bitand(s.ksuseflg2,131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC'),s.ksuudsae,s.ksusecre,s.ksusecsn from x$ksuse s, x$ksled e, x$kslwt w where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.indx=w.kslwtsid and w.kslwtevt=e.indx

Note the section shown in red. The ksuseflg column in x$ksuse contains a lot of information, which the v$session view extracts and presents in a readable manner. Let’s use exactly the same SQL directly from the prompt. Noting that the ksusepro and indx and ksuudsna columns show the process address (paddr), the SID and the username respectively, we can extract the information:

select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/
INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP

It shows the same information that v$session would have shown; but with a big difference. Note the view definition again – there is a join condition at the end with two other views: x$ksled and x$kslwt. Not all the session information is available on the other views. So the join filters out some rows from x$ksuse. To examine when it does that and that sessions get created automatically, you have to conduct an experiment.

First you have to look at a view called V$RESOURCE_LIMIT, which shows the various definable limits, how much is being used and – most important – how much has been the high water mark usage of the limit. We are interested in only 2 limits – sessions and processes.

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49

This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.

In this experiment, keep the above session connected to SYS. On a different OS prompt, connect to database using SQL*Plus as user ARUP. At the SQL> prompt, type host, which will show an OS prompt. From that shell, connect to the database again as ARUP. From the prompt go to host prompt and connect using SQL*Plus. Do it over and over so that the number of sessions builds up as shown below:

SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 24 21:54:56 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup
.....
and so on ....

After some time you will receive an error like this:

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

From session 1 (where you are connected as sysdba), check the resource limits:

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 39 40 40
sessions 45 47 49

This makes perfect sense. Note the current number of processes – 39, and the limit is 40. The very last session tried to create a session; and it failed, since it would have pushed the limit above 40.

Now, in the session of ARUP, execute a PL/SQL code that creates and drops a lot of objects in succession.

begin
for i in 1..1000 loop
execute immediate 'drop table t';
execute immediate 'create table t as select * from all_objects where 1=2';
end loop;
end;
/

This simply creates and drops the table called t in succession, 1000 times. While this code is running, check for the session from the x$ksuse view directly. You already know the process address:

select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/

INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP
10 RECURSIVE SYS

This is interesting. There are two sessions – SIDs 7 and 10 against the same process with address 41182408. Why is that? SID 7 is the actual session which you connected. If you look at the decoded value, it shows RECURSIVE for the SID 10, which shows that for the user SYS. This session was kicked off by Oracle for the recursive actions, which pushed the session count up without a real user session. This is a situation where a single process serves 2 sessions.

Another interesting point is to know what this recursive session’s waiting on? To know that you have to check the view X$KSLWT. First set the null display to “?” so that null values show up as “?”:

SQL> set null ?

Now execute the query to get the wait event:

select s.indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name, w.kslwtevt
from x$ksuse s, x$kslwt w
where ksusepro = '41182408'
and s.indx=w.kslwtsid (+)
/

INDX DECODE(BIT USER_NAME KSLWTEVT
---------- ---------- ------------------------------ ----------
4 RECURSIVE SYS ?
7 USER ARUP 72

Note, I used the outer join. Why? It’s because the session will not be found in the wait events view, which is confirmed by the null output. As you can glean from the above output, the wait event is not recorded for the recursive sessions. This is why a corresponding row was not found in the view V$SESSION which joins x$ksuse and x$kslwt without an outer join.

Now you must be curious about other such sessions where they share the same process. Let’s check:

SQL> select ksusepro, count(1)
2 from x$ksuse
3 group by ksusepro
4 having count(1) > 1;

KSUSEPRO COUNT(1)
-------- ----------
00 2
4117AE4C 2
41174344 2
41178E30 2
4117EE84 2
4117D91C 2
41174DF8 2
4117B900 2
4117837C 2
4117CE68 2
4116CD88 2
4117A398 2

We got several processes with addresses. Next, let’s find out the exact program names.

SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select v.addr, v.program
9 from proc_tab p, v$process v
10 where v.addr = p.addr;

ADDR PROGRAM
-------- ------------------------------------------------
4117AE4C oracle@oradba1 (FBDA)
41174344 oracle@oradba1 (MMON)
41178E30 oracle@oradba1 (ARC1)
4117EE84 oracle@oradba1 (W000)
4117D91C oracle@oradba1 (CJQ0)
41174DF8 oracle@oradba1 (MMNL)
4117B900 oracle@oradba1 (QMNC)
4117837C oracle@oradba1 (ARC0)
4117CE68 oracle@oradba1 (q001)
4116CD88 oracle@oradba1 (DBRM)
4117A398 oracle@oradba1 (ARC3)

These are the sessions related to the background processes – Flashback Data Archiver, Memory Monitor and so on. Let’s dive down even further and look into the sessions these processes serve:

SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select p.addr, indx sid, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
9 ksuudsna user_name
10 from x$ksuse x, proc_tab p
11 where ksusepro = p.addr
12 order by 1,2;

ADDR SID DECODE(BIT USER_NAME
-------- ---------- ---------- ------------------------------
00 1 RECURSIVE SYS
00 10 RECURSIVE SYS
4116CD88 23 RECURSIVE SYS
4116CD88 45 BACKGROUND SYS
41174344 30 RECURSIVE SYS
41174344 34 BACKGROUND SYS
41174DF8 20 RECURSIVE SYS
41174DF8 33 BACKGROUND SYS
4117837C 3 RECURSIVE SYS
4117837C 29 BACKGROUND SYS
41178E30 2 RECURSIVE SYS
41178E30 28 BACKGROUND SYS
4117A398 25 RECURSIVE SYS
4117A398 26 BACKGROUND SYS
4117AE4C 21 RECURSIVE SYS
4117AE4C 22 BACKGROUND SYS
4117B900 19 RECURSIVE SYS
4117B900 24 BACKGROUND SYS
4117CE68 15 RECURSIVE SYS

Note, for each of the background processes has a corresponding recursive session, which is counted towards the total session count in V$RESOURCE_LIMIT, even if you don’t actually create user sessions. These recursive sessions are not visible in V$SESSION.

Takeaways

1. The first lesson to learn is a process not always ≠ a session. In many cases there is a one-to-one relationship; but not always. When Oracle has to run a recursive SQL as a result of some user commands, it creates the session objects behind the scenes to fulfill that request.

2. Second, these recursive sessions count towards the overall sessions limit. To know the current sessions connected, use the V$RESOURCE_LIMIT view instead of selecting the sum from V$SESSION.

Index too big

I thought I’d posted this a couple of years ago – but maybe it was something I put on the OTN database forum in response to a question. If it was, the same (or similar) question has recently appeared.  “How come my index is so big when there’s no data in the table ?”
Of course, [...]

Enhanced Subquery Optimizations in Oracle

While googling I found a fresh article from Oracle for the VLDB journal: Enhanced Subquery Optimization in Oracle. It primarily discusses subqueries – how Oracle deals with them on optimization and at run-time. Paper’s topics overview: subquery coalescing – here I’ve done some testing of the feature partially available in 11gR2 (parallel) group-by pushdown – [...]

Capturing DDL for Database Objects Not Managed by PeopleTools

I have written before about the challenges of managing database objects and attributes of database objects that are not managed by PeopleTools Application Designer. I proposed a DDL trigger to prevent such objects being dropped or altered. However, sometimes it is necessary to temporarily disable this DDL trigger, such as during patch or upgrade release it is necessary to disable this trigger to apply the changes.

Now, I have another DDL trigger and a packaged procedure that captures the DDL to recreate objects that are recursively dropped (such as DML triggers on tables). The DDL is stored in a database table. This trigger can remain permanently enabled, and the table it maintains can be used to see what objects are missing, as well as holding the SQL to rebuild them.

An common example of where this is can be valuable is where a system uses database triggers to capture audit data.  This method is often preferred because it generally performs better than having the application server generate additional DML to the audit table, and also captures updates made in other processes.  PeopleSoft even deliver processes to generate the DML triggers that write to the audit tables.  However, if you alter the table in Application Designer, perhaps only because you are applying a PeopleSoft fix, and apply the changes to the database by recreating the table, then the trigger will be lost.  It is then up to the customer to make sure the audit trigger is replaced.  There is absolutely nothing to warn you that the trigger is lost, and the application will still function without the trigger, but your updates will not be audited.

When a table is dropped, the trigger calls a procedure in the package that checks for:

  • indexes that are not managed by PeopleTools (such as function-based indexes),
  • triggers not managed by PeopleTools (other than the PSU triggers created for mobile agents),
  • materialised view logs.
  • If the table is partitioned or global temporary the DDL for the object being dropped is also captured.

When an index is dropped the index check is performed. Similarly the DDL to rebuild partitioned indexes or indexes on Global Temporary tables is also captured.

When an object for which the DDL has been captured is explicitly dropped, this is indicated on the table GFC_REL_OBJ by storing the time at which it was dropped. When it is recreated this time-stamp is cleared.  Thus it is possible to decide whether something was deliberately or accidentally dropped.

Capturing DDL for Database Objects Not Managed by PeopleTools

I have written before about the challenges of managing database objects and attributes of database objects that are not managed by PeopleTools Application Designer. I proposed a DDL trigger to prevent such objects being dropped or altered. However, sometimes it is necessary to temporarily disable this DDL trigger, such as during patch or upgrade release it is necessary to disable this trigger to apply the changes.

Now, I have another DDL trigger and a packaged procedure that captures the DDL to recreate objects that are recursively dropped (such as DML triggers on tables). The DDL is stored in a database table. This trigger can remain permanently enabled, and the table it maintains can be used to see what objects are missing, as well as holding the SQL to rebuild them.

An common example of where this is can be valuable is where a system uses database triggers to capture audit data.  This method is often preferred because it generally performs better than having the application server generate additional DML to the audit table, and also captures updates made in other processes.  PeopleSoft even deliver processes to generate the DML triggers that write to the audit tables.  However, if you alter the table in Application Designer, perhaps only because you are applying a PeopleSoft fix, and apply the changes to the database by recreating the table, then the trigger will be lost.  It is then up to the customer to make sure the audit trigger is replaced.  There is absolutely nothing to warn you that the trigger is lost, and the application will still function without the trigger, but your updates will not be audited.

When a table is dropped, the trigger calls a procedure in the package that checks for:

  • indexes that are not managed by PeopleTools (such as function-based indexes),
  • triggers not managed by PeopleTools (other than the PSU triggers created for mobile agents),
  • materialised view logs.
  • If the table is partitioned or global temporary the DDL for the object being dropped is also captured.

When an index is dropped the index check is performed. Similarly the DDL to rebuild partitioned indexes or indexes on Global Temporary tables is also captured.

When an object for which the DDL has been captured is explicitly dropped, this is indicated on the table GFC_REL_OBJ by storing the time at which it was dropped. When it is recreated this time-stamp is cleared.  Thus it is possible to decide whether something was deliberately or accidentally dropped.