People – not just People rather they are your resources!

April 11, 2011 1 comment

Other people are your greatest resources! They give birth to you; they feed you, dress you, provide you with money, make you laugh and cry.  They comfort you, heal you, invest your money, service your car and bury you.  We can’t live without them.  We can’t even die without them.

Categories: Oracle, Performance

Tablespace growth history

March 11, 2015 3 comments

Recently we had a tablespace space run out and ended up in an application failure.  I have questioned my DBA and he just plainly blamed the application team members who loaded large number records without a prior notice.  A convincing answer, but you can’t really fool Oracle.

Oracle introduced a new DBA_TABLESPACE_USAGE_METRICS view from 10g onwards to report the space usage with in a tablespace.  I created a new tablespace and immediately space usage was reported in the view.

<pre>SQL> select * from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

------------------------------ ---------- --------------- ------------

MYDATA                                128           64000           .2


SQL> select USED_SPACE*8/1024,TABLESPACE_SIZE*8/1024 from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';

USED_SPACE*8/1024 TABLESPACE_SIZE*8/1024

----------------- ----------------------

                1                    500

SQL> select file_name,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='MYDATA';

FILE_NAME                                          AUT
-------------------------------------------------- ---
/oradata1/test/mydata01.dbf                        NO

 

Both the USED_SPACE and TABLESPACE_SIZE are reported in number of database blocks.  A non-auto extensible data file reported 500MB.  I have made the data file auto extensible and the size of the tablespace grown to MAXSIZE of the data file.


SQL> alter database datafile '/oradata1/test/mydata01.dbf' autoextend on;



Database altered.





SQL> select USED_SPACE*8/1024,TABLESPACE_SIZE*8/1024

from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';



USED_SPACE*8/1024 TABLESPACE_SIZE*8/1024

----------------- ----------------------

                1             32767.9844



SQL>  select * from DBA_TABLESPACE_USAGE_METRICS  where TABLESPACE_NAME='MYDATA';



TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

------------------------------ ---------- --------------- ------------

MYDATA                                128         4194302   .003051759

That is really a cool stuff.

Now this view is captured as part of the AWR report in the history tables as DBA_HIST_TBSPC_SPACE_USAGE.  So, it is easy for a DBA to report the growth of a tablespace with in the retention period.   Even though my DBA said, the failure was because of a sudden data growth, I know where to see the data growth.  And my report was –


SQL> select TABLESPACE_SIZE*8/1024/1024,TABLESPACE_MAXSIZE*8/1024/1024,TABLESPACE_USEDSIZE*8/1024/1024,RTIME

from DBA_HIST_TBSPC_SPACE_USAGE

where TABLESPACE_ID=33  order by SNAP_ID;



TABLESPACE_SIZE*8/1024/1024 TABLESPACE_MAXSIZE*8/1024/1024 TABLESPACE_USEDSIZE*8/1024/1024 RTIME

--------------------------- ------------------------------ ------------------------------- --------------------

                         60                             60                      59.0448608 03/07/2015 12:00:06

                         60                             60                      59.0448608 03/07/2015 13:00:21

                         60                             60                      59.0448608 03/07/2015 14:00:03

                         60                             60                      59.0448608 03/07/2015 15:00:08

                         60                             60                      59.0448608 03/07/2015 16:00:19

                         60                             60                      59.0448608 03/07/2015 17:00:26

                         60                             60                      59.0448608 03/07/2015 18:00:29

                         60                             60                      59.0526733 03/07/2015 19:00:33

                         60                             60                      59.9413452 03/07/2015 20:00:37

                 62.9384766                             90                      60.0664063 03/07/2015 21:00:41

                 62.9384766                             90                      60.0664063 03/07/2015 22:00:50

                 62.9384766                             90                      60.2773438 03/07/2015 23:00:49

                 62.9384766                             90                      61.1416016 03/08/2015 00:00:07

                 62.9384766                             90                      61.2900391 03/08/2015 01:00:12

                 62.9384766                             90                      61.2900391 03/08/2015 03:00:19

                 62.9384766                             90                      61.2900391 03/08/2015 04:00:27

                 62.9384766                             90                      61.2900391 03/08/2015 05:00:31

                 62.9384766                             90                      61.2900391 03/08/2015 06:00:08

                 62.9384766                             90                      61.2900391 03/08/2015 07:00:10

                 62.9384766                             90                      61.2900391 03/08/2015 08:00:13

                 62.9384766                             90                      61.2900391 03/08/2015 09:00:14

                 62.9384766                             90                      61.2900391 03/08/2015 10:00:22

                 62.9384766                             90                      61.2900391 03/08/2015 11:00:22

                 62.9384766                             90                      61.2900391 03/08/2015 12:00:07

                 62.9384766                             90                      61.2900391 03/08/2015 13:00:34

                 62.9384766                             90                      61.2900391 03/08/2015 14:00:26

                 62.9384766                             90                      61.2900391 03/08/2015 15:00:31

                 62.9384766                             90                      61.2900391 03/08/2015 16:00:36

                 62.9384766                             90                      61.2900391 03/08/2015 17:00:39

                 62.9384766                             90                      61.2900391 03/08/2015 18:00:41

                 62.9384766                             90                      61.2900391 03/08/2015 19:00:43

                 62.9384766                             90                      61.2900391 03/08/2015 20:00:47

                 62.9384766                             90                      61.2900391 03/08/2015 21:00:52

The growth report is clearly showing me that the space was added at 03/07/2015 21:00:41 and the growth was really normal!  Thanks to oracle.

x$bh and consistency

March 6, 2015 2 comments

Oracle caches the data blocks in buffer cache in various modes depends on the block usage.  As per the Oracle documentation it can CR (Consistent mode – reads), XCUR (Current mode – updates), FREE etc.   I understand and other heard saying – whenever a block READs into memory will be in CR mode while if the block is fetching for UPDATE it will be in XCUR mode so that sessions can apply the transactions under the exclusive lock mode.  And the working set of the buffer cache is divided into multiple working set and I believe each working set has a cold and hot region based on the touch count algorithm.  Arming with these details, I did the following test and the results were little confusing – not just little!

My one row – column table TEST is as follws –

<pre>SQL> desc TEST

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER

SQL> select * from test;

        C1
----------
         6

Then I flush the buffer cache, to clear the TEST table blocks.  Also made sure there was no transactions active this point.

<pre>SQL> alter system flush buffer_cache;
System altered.

SQL> col object_name for a25
SQL> set linesize 132
SQL> select
  2  indx
  3  ,o.object_name
  4  ,decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec'
  5  ,6,'irec',7,'write',8,'pi') state
  6  , dbarfil
  7  , dbablk
  8  , ba
  9  ,tch
 10  from x$bh b , dba_objects o
 11  where b.obj = o.data_object_id
 12  and o.object_name = 'TEST'
 13  and state > 0;

no rows selected

SQL> select count(*) from v$transaction;


  COUNT(*)

----------

         0

 

Test 1:

I read one row to the memory using ROWID to enable single block read from the TEST table.  Since I am reading – I was expecting a CR read.

<pre>
SQL> conn abc/abc

Connected.

SQL> select * from test where rowid='AAAUxpAABAAA0MJAAA';

        C1

----------

         5

But to my surprise, the block under XCUR mode and the touch count was 1.

<pre>
SQL> select

indx

  2    3  ,o.object_name

  4  ,decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec'

  5  ,6,'irec',7,'write',8,'pi') state

  6  , dbarfil

  7  , dbablk

  8  , ba

  9  ,tch

 10  from x$bh b , dba_objects o

 11  where b.obj = o.data_object_id

 12  and o.object_name = 'TEST'

 13  and state > 0;



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43737 TEST                      xcur           1     213769 00000003A7DFA000          1



 

I repeated the read one more time, and the same XCUR block increased the touch count to 2.

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43722 TEST                      xcur           1     213769 00000003A7DFA000          2



 

From the first session I have executed the SQL 4 more times and the count reached 6.

 

Note:  I did this with a 5 second interval – I will explain that in my next entry.

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43722 TEST                      xcur           1     213769 00000003A7DFA000          6

 

I read from multiple sessions and found the touch count was increasing with the XCUR state.

 

Test 2:

 

From the first session, did an UPDATE to my TEST table from ABC user.

 


SQL> update test set c1=7 where rowid='AAAUxpAABAAA0MJAAA';



1 row updated.

1

Oracle applied that UDPATE to the same XCUR block and increased the touch count to 7

1

SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43722 TEST                      xcur           1     213769 00000003A7DFA000          7



 

From the second ABC session I read the row and that created a CR block and more interestingly the touch count did not increase.

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43670 TEST                      cr             1     213769 00000003BBBCC000          1

     43671 TEST                      xcur           1     213769 00000003A7DFA000          7

 

I read 5 more times from the second session and that created 5 more CR blocks for the XCUR block.

 




SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43665 TEST                      cr             1     213769 00000003B4C5A000          1

     43666 TEST                      cr             1     213769 00000003ABCE0000          1

     43667 TEST                      cr             1     213769 00000003B6CD8000          1

     43668 TEST                      cr             1     213769 00000003AC36A000          1

     43669 TEST                      cr             1     213769 00000003A5030000          1

     43670 TEST                      cr             1     213769 00000003BBBCC000          1

     43671 TEST                      xcur           1     213769 00000003A7DFA000          7



7 rows selected.

 

When I read the 6th time, the count of CR blocks did not increase and that was expected.  At any point in time, a single block can have maximum of 7 versions in the buffer cache.  Any subsequent reads will use one of the existing CR block to build the new CR block.  The more interesting finding was, the touch count was never increased during a CR block build thus the access was unaccounted.  Each time Oracle build a new CR block and that block was send to the user, hence the original XCUR block touch count was not increased.  I am not sure why Oracle showing this discrimination to XCUR blocks which is having an active transaction.

 

Then I did execute the READ from the session where the transaction was active – and that increased the touch count again – to 8!

 


SQL> /



      INDX OBJECT_NAME               STATE    DBARFIL     DBABLK BA                      TCH

---------- ------------------------- ----- ---------- ---------- ---------------- ----------

     43665 TEST                      cr             1     213769 00000003B4C5A000          1

     43666 TEST                      cr             1     213769 00000003ABCE0000          1

     43667 TEST                      cr             1     213769 00000003B6CD8000          1

     43668 TEST                      cr             1     213769 00000003AC36A000          1

     43669 TEST                      cr             1     213769 00000003A5030000          1

     43670 TEST                      xcur           1     213769 00000003A7DFA000          8



6 rows selected.

 

I will not give much importance to reduction of 1 CR block as the temporary CR blocks can re-use any time.

 

So, my assumptions are –

  • When Oracle reads a block in to buffer cache first time it will be in XCUR mode not in CR mode
  • I consider this XCUR list as Master List of blocks and all the updates will go the master XCUR list only. Master List can be read directly if no transactions are active.
  • Under no transactions – any READ will reuse the XCUR block and increase the touch count
  • Too many consistent reads leads to CBC Latch waits as everyone will be contending on the XCUR block list
  • A transaction will modify the XCUR block and will increase the touch count
  • Any other session reading a XCUR block will create fresh CR block and rollback the transactions. There are can be maximum of 7 versions of a block can exist.  During the CR block access, the touch count will not increase
  • Transaction session read directly from the XCUR block without creating a CR block and the touch count will increase.

Bind peeking and session cached cursor

January 30, 2015 Leave a comment

Bind peeking is a nice feature in Oracle to have many optimized plans for an SQL for various bind values.  DBAs believe that bind peeking happens during a soft parse which will identify an alternate plan. Why do I say that?

 

Hard Parse: Parsing first time, nothing exists to bind peek

 

Soft Parse :  SQL cursor is existing and executing not the first time.  Under the soft parse, bind peeking will happen and the new  plan will be generated based on the selectivity for that literal.

 

Session Cached cursor (Softer Soft Parse):   Cursor is existing in the PGA, bind the new value and just execute.  This is the optimal way of an SQL execution – parse once and execute many.  Less CPU, less or no latches – just bind and execute.

 

Question for the session cached cursor – since there is no soft parsing , can bind peeking happen to a session cached cursor?  Can binding will force a ‘bind peeking’ and force for soft parse if the bind value is not safe?  I asked this question to an industry expert and he believes that a session cached cursor can never do bind peeking – it will just bind and execute.  But, that is a dangerous situation as it can produce inefficient plans just because it is executing a cached SQL.

 

So, let me test it!

 

My test table ABC got 117455 rows for OBJECT_ID=2 and just one row for OBJECT_ID=1 with an index on OBJECT_ID.  For the bind peeking  test, 10046 trace shows me two different plans for OBJECT_ID=1 and 2 – one is full table scan while other is index scan.

 

The test SQL is,

 


select count(OBJECT_TYPE) from abc where object_id=:oid ;

 

With object_id=1

 


PARSING IN CURSOR #47782675682600 len=56 dep=0 uid=70 oct=3 lid=70 tim=1421932643763296 hv=3165798175 ad='1f7dd8570' sqlid='99pd5nyyb4gsz'

select count(OBJECT_TYPE) from abc where object_id=:oid

END OF STMT

PARSE #47782675682600:c=0,e=440,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1421932643763295

BINDS #47782675682600:

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=2b7545793550  bln=22  avl=02  flg=05

  value=1

EXEC #47782675682600:c=1000,e=1336,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2627152604,tim=1421932643764692

WAIT #47782675682600: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932643764734

FETCH #47782675682600:c=0,e=54,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=2627152604,tim=1421932643764817

STAT #47782675682600 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4 pr=0 pw=0 time=63 us)'

STAT #47782675682600 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=43 us cost=4 size=11 card=1)'

STAT #47782675682600 id=3 cnt=1 pid=2 pos=1 obj=20551 op='INDEX RANGE SCAN I_OBJECT_ID (cr=3 pr=0 pw=0 time=33 us cost=3 size=0 card=1)'

WAIT #47782675682600: nam='SQL*Net message from client' ela= 220 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932643765178

FETCH #47782675682600:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2627152604,tim=1421932643765213

WAIT #47782675682600: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932643765253

WAIT #47782675682600: nam='SQL*Net message from client' ela= 219 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932643765489

*** SESSION ID:(2299.43369) 2015-01-22 07:17:23.767

 

And with OBJECT_ID=2, is a FULL table scan


PARSING IN CURSOR #47782683021480 len=57 dep=0 uid=70 oct=3 lid=70 tim=1421932662564677 hv=860935858 ad='2353e0480' sqlid='am229vwtp1ppk'

 select count(OBJECT_TYPE) from abc where object_id=:oid

END OF STMT

PARSE #47782683021480:c=0,e=374,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1421932662564676

BINDS #47782683021480:

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=2b75455c03a0  bln=22  avl=02  flg=05

  value=2

EXEC #47782683021480:c=1000,e=1225,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1045519631,tim=1421932662565992

WAIT #47782683021480: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932662566033

FETCH #47782683021480:c=20996,e=21498,p=0,cr=1590,cu=0,mis=0,r=1,dep=0,og=1,plh=1045519631,tim=1421932662587561

STAT #47782683021480 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1590 pr=0 pw=0 time=21489 us)'

STAT #47782683021480 id=2 cnt=117455 pid=1 pos=1 obj=20550 op='TABLE ACCESS FULL ABC (cr=1590 pr=0 pw=0 time=27469 us cost=447 size=1305040 card=118640)'

WAIT #47782683021480: nam='SQL*Net message from client' ela= 252 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932662588004

FETCH #47782683021480:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1045519631,tim=1421932662588047

WAIT #47782683021480: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932662588069

WAIT #47782683021480: nam='SQL*Net message from client' ela= 379 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1421932662588485

*** SESSION ID:(2299.43369) 2015-01-22 07:17:42.589

 

 

Then I executed the below SQL, to see the behavior –


ALTER SESSION SET events '10046 trace name context forever, level 12';

VARIABLE oid NUMBER;

EXECUTE :oid  := 1;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

 

Looking at the cursor closing type=3 shows that, the cursor closing with caching and that is the expected behavior.


$: grep 47370175687784 xxxx_ora_10255.trc | grep type

CLOSE #47370175687784:c=0,e=15,dep=0,type=1,tim=1421938140372548

CLOSE #47370175687784:c=0,e=11,dep=0,type=3,tim=1421938140493515

CLOSE #47370175687784:c=0,e=13,dep=0,type=3,tim=1421938140509940

CLOSE #47370175687784:c=0,e=11,dep=0,type=3,tim=1421938140560694

CLOSE #47370175687784:c=0,e=10,dep=0,type=3,tim=1421938140584517

CLOSE #47370175687784:c=0,e=11,dep=0,type=3,tim=1421938140593613

CLOSE #47370175687784:c=0,e=12,dep=0,type=3,tim=1421938140681558

CLOSE #47370175687784:c=0,e=12,dep=0,type=3,tim=1421938140788580

CLOSE #47370175687784:c=0,e=11,dep=0,type=3,tim=1421938140793258

CLOSE #47370175687784:c=0,e=12,dep=0,type=3,tim=1421938140797589

CLOSE #47370175687784:c=0,e=11,dep=0,type=3,tim=1421938140815194

CLOSE #47370175687784:c=0,e=12,dep=0,type=3,tim=1421938140866576

CLOSE #47370175687784:c=0,e=10,dep=0,type=3,tim=1421938140890609

CLOSE #47370175687784:c=0,e=11,dep=0,type=3,tim=1421938140901529

CLOSE #47370175687784:c=0,e=10,dep=0,type=3,tim=1421938140914521

CLOSE #47370175687784:c=0,e=8,dep=0,type=0,tim=1421938145465058

Here are the various values for TYPE while closing the cursor.

 

  • type 0 : Cursor is closed and not added to the session cache
  • type 1 : Cursor is closed but added to the session cache without flushing out an existing cursor
  • type 2 : Cursor is closed but added to the session cache after flushing out an existing cursor in cache
  • type 3 : Cursor is already existing in the cache

 

Now, let us see what will happen when we execute the below statement –


ALTER SESSION SET events '10046 trace name context forever, level 12';

VARIABLE oid NUMBER;

EXECUTE :oid  := 1;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

EXECUTE :oid  := 2;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

EXECUTE :oid  := 1;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

select count(OBJECT_TYPE) from abc where object_id=:oid ;

 

First 5 times, execute with a bind value 1 which will cache Indexed read plan cursor in the session cache and switch the bind to 2 which will do a full table scan if it is doing a bind peeking.  If bind peeking is not happening, it will end up with a wrong plan.  Looking at the trace files


$ :  grep  obj=20550  xxxxxx_ora_15962.trc

STAT #48000122322616 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=57 us cost=4 size=11 card=1)'

STAT #48000122322616 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=39 us cost=4 size=11 card=1)'

STAT #48000122322616 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=39 us cost=4 size=11 card=1)'

STAT #48000122322616 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=36 us cost=4 size=11 card=1)'

STAT #48000122322616 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=40 us cost=4 size=11 card=1)'



STAT #48000122321672 id=2 cnt=1879295 pid=1 pos=1 obj=20550 op='TABLE ACCESS FULL ABC (cr=24667 pr=0 pw=0 time=226535 us cost=6907 size=20702825 card=117455)'



STAT #48000122321672 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=12 us cost=4 size=11 card=1)'

STAT #48000122321672 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=15 us cost=4 size=11 card=1)'

STAT #48000122321672 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=36 us cost=4 size=11 card=1)'

STAT #48000122321672 id=2 cnt=1 pid=1 pos=1 obj=20550 op='TABLE ACCESS BY INDEX ROWID ABC (cr=4 pr=0 pw=0 time=13 us cost=4 size=11 card=1)'

 

Interesting, bind peeking is happening for a session cached SQL!   If you look closely SQL started with a cursor ID 48000122322616 for the initial index read and closed that session cached cursor and opened a new cursor 48000122321672 for FULL table scan.  However, when the bind value change back to 1, plan got changed under the same cursor ID.  I assume this is because, both plans were cached under the same cursor, so that a session cached cursor was able to execute both plans under the same cursor ID.  Oracle is doing a great job here and avoiding sub-optimal plans for a session cached cursor.  But an unanswered question is, how Oracle was able to do bind peeking under ‘softer soft parse’.

 

 

CRS Timezone

January 12, 2015 Leave a comment

Interesting problem – database was running normally without any issues and added to OCR.  But the log file started reporting a different time for all activities – for example.

Database and server is showing a time 4:25 while alertlog is ahead of 5 hours @9:25!

<pre>
oracle@prod # tail alert_xxxxxxx.log

Recovery of Online Redo Log: Thread 2 Group 16 Seq 129057 Reading mem 0

  Mem# 0: +ORAINDEX1/prod/onlinelog/group_16.282.864560747

Mon Jan 12 09:20:39 2015

Media Recovery Waiting for thread 1 sequence 129049 (in transit)

Recovery of Online Redo Log: Thread 1 Group 15 Seq 129049 Reading mem 0

  Mem# 0: +FLASH/prod/onlinelog/group_15.5059.864560603

Mon Jan 12 09:20:55 2015

Both database and server was showing almost 5 hrs less than the alert long entries.


oracle@prod  # date

Mon Jan 12 04:26:34 EST 2015



SQL> select to_char(sysdate,'DD-Mon HH24:MI:SS') from dual;



TO_CHAR(SYSDATE,'DD-MONHH24:MI:SS')

---------------------------------------------------------------------------

12-Jan 04:28:46

Also, found the CSSD log is showing 5 hrs ahead of DB and server time!

 


grid@prod # tail ocssd.log

2015-01-12 09:12:16.373: [    CSSD][24]clssnmSendingThread: sending status msg to all nodes

2015-01-12 09:12:16.373: [    CSSD][24]clssnmSendingThread: sent 4 status msgs to all nodes

2015-01-12 09:12:20.375: [    CSSD][24]clssnmSendingThread: sending status msg to all nodes

2015-01-12 09:12:20.375: [    CSSD][24]clssnmSendingThread: sent 4 status msgs to all nodes

2015-01-12 09:12:24.377: [    CSSD][24]clssnmSendingThread: sending status msg to all nodes

2015-01-12 09:12:24.377: [    CSSD][24]clssnmSendingThread: sent 4 status msgs to all nodes

2015-01-12 09:12:28.379: [    CSSD][24]clssnmSendingThread: sending status msg to all nodes

2015-01-12 09:12:28.379: [    CSSD][24]clssnmSendingThread: sent 4 status msgs to all nodes

2015-01-12 09:12:32.381: [    CSSD][24]clssnmSendingThread: sending status msg to all nodes

2015-01-12 09:12:32.381: [    CSSD][24]clssnmSendingThread: sent 4 status msgs to all nodes

The problem was – the server was setup with a time zone  TZ=US/Eastern while the CRS was installed with the time zone GMT.  You can check the CRS time zone at $GRID_HOME/crs/install/ s_crsconfig_<SERVER NAME>_env.txt

<pre>
grid@ #  more s_crsconfig_xxxxxx_env.txt

### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.

### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services


TZ=GMT

NLS_LANG=ENGLISH_UNITED KINGDOM.WE8ISO8859P1

Then, why the database was showing same as server time?  Being a CRS resource, should have been inherited the CRS time?  Remember, you can configure the database with a different time zone other than the CRS timezone – means in the same server – each database can take its own timezone.  You can view / set time zone for a database by

 


oracle@prod # srvctl getenv database -d xxxxxx

xxxxxx:

TZ=EST5EDT

LIBPATH=/xxxs/app/oracle/product/11.2.0/db_1/lib

 

So, even though CRS time zone is GMT, database level timezone is set to EST5EDT which is US/Eastern.

You may set DB level timezone by


srvctl setenv database -d <dbname> -t 'TZ=<the required time zone>'

 

 

enq: SQ – contention

January 9, 2015 Leave a comment

Yesterday, I have seen huge waits “enq SQ – contention’”  – in every snapshot there were thousands of waits.  But the fix was so simple!  Here is the root cause of the issue –

When you select from a sequence, the NEXTVAL generated from a the seq$ table if it is not cached.  If it is cached, it will be available in a memory structure and no need to generate the value which is a costly affair under the wait enq SQ – contention.  Below test shows what will happen for this wait from NOCACHE to CACHE 1000 NEXTVALs.

I have executed the below SQL from 20 concurrent sessions..

<pre>
declare

 col1 number;

begin

 for a in 1..20000 loop

  select s.nextval into col1 from dual;

 end loop;

end;

/

Used below statements to run this SQL from 20 different sessions.

<pre>nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

With NOCACHE


SQL> create sequence s;

select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (14,15,16,17,18,19,20);


  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

      2336       1677503813

 

With CACHE 50

 


SQL> alter sequence s cache 50;

Sequence altered.


SQL> select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (21,22,23,24,25,26,27);



  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

       701        459026341

 

With CACHE 500


SQL> alter sequence s cache 500;



Sequence altered.



SQL>

select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (23,24,25,26,27);



  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

       192         96392664

 

 

With CACHE 1000


SQL>  alter sequence s cache 1000 ;



Sequence altered.



SQL> select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (29,30,31,32,33,34,35,36,37);



  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

        84         23352139

 

 

With huge concurrency, the number of waits comes down from 2336 (time:1677503813 ) to 84 (time: 23352139)  by adjusting the sequence CACHE.

 

Version:

 


SQL> select * from v$version;



BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Solaris: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production



Autotrace

January 6, 2015 Leave a comment

Happy New Year!

Oracle supplied various tools to trace the SQL and identify execution plans for the SQLs.  10046 Event, AUTOTRACE, DBMS.XPLAN etc are some of the most used tracing methods in DBAs daily life.  Sometimes, we need to be very careful while using these tools specially using bind variables.  Following are some test cases where wrong plans reported by the above tracing tools.

Most easiest method to get the execution plan is AUTOTRACE both estimate and actual after execution it.  For example I have below tables ABC


SQL> desc abc

 Name                                                                                      Null?    Type

 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------

 OWNER                                                                                     NOT NULL VARCHAR2(30)

 OBJECT_NAME                                                                               NOT NULL VARCHAR2(30)

 SUBOBJECT_NAME                                                                                     VARCHAR2(30)

 OBJECT_ID                                                                                          NUMBER

 DATA_OBJECT_ID                                                                                     NUMBER

 OBJECT_TYPE                                                                                        VARCHAR2(19)

 CREATED                                                                                   NOT NULL DATE

 LAST_DDL_TIME                                                                             NOT NULL DATE

 TIMESTAMP                                                                                          VARCHAR2(19)

 STATUS                                                                                             VARCHAR2(7)

 TEMPORARY                                                                                          VARCHAR2(1)

 GENERATED                                                                                          VARCHAR2(1)

 SECONDARY                                                                                          VARCHAR2(1)

 NAMESPACE                                                                                 NOT NULL NUMBER

 EDITION_NAME                                                                                       VARCHAR2(30)



SQL> select OBJECT_ID,count(*) from abc group by OBJECT_ID;

 OBJECT_ID   COUNT(*)

---------- ----------

         1          1

         2     117455

ABC table got 1 row OBJECT_ID=1 and 117455 rows with OBJECT_ID=2.  Created an index OBJECT_ID, so that OBEJCT_ID=1 will always use and index while OBJECT_ID=2 a FTS.

My first test was using literals, and AUTOTRACE reported correct execution plans.


SQL>  select count(OBJECT_TYPE) from abc where object_id=2;

COUNT(OBJECT_TYPE)

------------------

            117455

Elapsed: 00:00:00.01

Execution Plan

---------------------------------------------------------

Plan hash value: 1045519631

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |   447   (1)| 00:00:06 |

|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| ABC  |   118K|  1274K|   447   (1)| 00:00:06 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OBJECT_ID"=2)

FTS for OBJECT_ID=2 and Index Range Scan for OBJECT_ID=1.


SQL> select count(OBJECT_TYPE) from abc where object_id=1;

COUNT(OBJECT_TYPE)

------------------

            1



Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 2627152604

--------------------------------------------------------------------------------------------

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |             |     1 |    11 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |             |     1 |    11 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| ABC         |     1 |    11 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I_OBJECT_ID |     1 |       |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("OBJECT_ID"=1)

Then I moved to the next level – get the AUTOTRACE plans using BIND Variables.


SQL>  set autotrace traceonly

SQL> VARIABLE oid NUMBER;

SQL> EXECUTE :oid  := 1;

SQL> select count(OBJECT_TYPE) from abc where object_id=:oid ;SQL>

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL>

Elapsed: 00:00:00.01



Execution Plan

----------------------------------------------------------

Plan hash value: 1045519631



---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |   448   (1)| 00:00:06 |

|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| ABC  |   118K|  1274K|   448   (1)| 00:00:06 |

---------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OBJECT_ID"=TO_NUMBER(:OID))



Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        536  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> VARIABLE oid NUMBER;

SQL> EXECUTE :oid  := 2;

SQL> select count(OBJECT_TYPE) from abc where object_id=:oid ;SQL>

PL/SQL procedure successfully completed.



Elapsed: 00:00:00.00

SQL>



Elapsed: 00:00:00.04



Execution Plan

----------------------------------------------------------

Plan hash value: 1045519631

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |   448   (1)| 00:00:06 |

|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| ABC  |   118K|  1274K|   448   (1)| 00:00:06 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------



   2 - filter("OBJECT_ID"=TO_NUMBER(:OID))



Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1964  consistent gets

          0  physical reads

          0  redo size

        538  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)</pre>
<ul>
	<li>rows processed</li>
</ul>
<pre>

The AUTOTRACE TRACEONLY reported both plans as FTS, though  OBJECT_ID-=1 is just reading one row from the table while OBJECT_ID is an indexed column.  I agree to the fact that, AUTOTRACE TRACEONLY is showing an estimated plan, so may be reporting the wrong plan.  Experts say that, AUTOTRACE TRACEONLY never a dependable way of plan generation.  Interestingly OBJECT_ID=1 reported just 4 consistent gets while OBJECT_ID=2 shows 1964 gets – so the estimation theory is not really going well here as I was expecting 1964 CR reads if the estimated plan is FTS.

Then moved to the next test – use AUTOTRACE EXPLAIN STAT.  I had some faith in this level as the SQL statement will get execute first and report the executed plan.  So the plan tends to be accurate.




SQL> set autotrace on exp stat

SQL> EXECUTE :oid  := 2;



PL/SQL procedure successfully completed.



Elapsed: 00:00:00.00

SQL> select count(OBJECT_TYPE) from abc where object_id=:oid ;



COUNT(OBJECT_TYPE)

------------------

            117455

Elapsed: 00:00:00.02

Execution Plan

----------------------------------------------------------

Plan hash value: 1045519631

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |   448   (1)| 00:00:06 |

|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| ABC  |   118K|  1274K|   448   (1)| 00:00:06 |

---------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - filter("OBJECT_ID"=TO_NUMBER(:OID))



Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       1590  consistent gets

          0  physical reads

          0  redo size

        538  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



SQL> EXECUTE :oid  := 1



PL/SQL procedure successfully completed.



Elapsed: 00:00:00.00

SQL> select count(OBJECT_TYPE) from abc where object_id=:oid ;



COUNT(OBJECT_TYPE)

------------------

                 1



Elapsed: 00:00:00.02



Execution Plan

----------------------------------------------------------

Plan hash value: 1045519631

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |   448   (1)| 00:00:06 |

|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| ABC  |   118K|  1274K|   448   (1)| 00:00:06 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------



   2 - filter("OBJECT_ID"=TO_NUMBER(:OID))



Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1590  consistent gets

          0  physical reads

          0  redo size

        536  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)</pre>
<ul>
	<li>rows processed</li>
</ul>
<pre>

To my surprise, both plans reported FTS regardless of the selectivity.  10046 trace shows Oracle is doing index range scan for OBEJCT_ID=1.  Using BINDS, the plan may not report correctly in the AUTOTRACE as there may be multiple child cursors existing due to Bind Peeking.  So, using BIND values in AUTOTRACE is really not working at all when we have multiple child cursors.  I am not sure, why Oracle can’t fix this issue, as AUTOTRACE just need to report the last executed plan, not from the estimation.

Then I turn to DBMS_XPLAN, and the results were surprised me even more.  For OBJECT_ID=1 reported a FTS, as


SQL> select count(OBJECT_TYPE) from abc where object_id=:oid ;



COUNT(OBJECT_TYPE)

------------------

                 1



Elapsed: 00:00:00.01

SQL>  select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST '));



PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

SQL_ID  99pd5nyyb4gsz, child number 1

-------------------------------------

select count(OBJECT_TYPE) from abc where object_id=:oid



Plan hash value: 1045519631



--------------------------------------------

| Id  | Operation          | Name | E-Rows |

--------------------------------------------

|   0 | SELECT STATEMENT   |      |        |

|   1 |  SORT AGGREGATE    |      |      1 |

|*  2 |   TABLE ACCESS FULL| ABC  |    118K|

--------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - filter("OBJECT_ID"=:OID)

 

I was not really expecting FTS using DBMS_XPLAN for OBJECT_ID=1, that was bit shocking.  Then I changed the option to ‘ALLSTATS LAST +PEEKED_BINDS’ from ‘ALLSTATS LAST ‘ and started reporting correct plans.


SQL>  select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS'));



PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------

SQL_ID  am229vwtp1ppk, child number 0

-------------------------------------

 select count(OBJECT_TYPE) from abc where object_id=:oid



Plan hash value: 2627152604



-------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |

-------------------------------------------------------------

|   0 | SELECT STATEMENT             |             |        |

|   1 |  SORT AGGREGATE              |             |      1 |

|   2 |   TABLE ACCESS BY INDEX ROWID| ABC         |      1 |

|*  3 |    INDEX RANGE SCAN          | I_OBJECT_ID |      1 |

-------------------------------------------------------------



Peeked Binds (identified by position):

--------------------------------------



   1 - :SYS_B_0 (NUMBER): 1



Predicate Information (identified by operation id):

---------------------------------------------------


</pre>
<ul>
	<li>- access("OBJECT_ID"=:OID)</li>
</ul>
<pre>

So, be careful when you use AUTOTRACE or any tools when you use BIND variables.  Oracle should put some more efforts to make it more dependable.  Always use 10046 event and tkprof to get a real picture.


SQL> select * from v$version;



BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production



APPEND and PARALLEL

November 21, 2014 1 comment

APPEND hint in oracle is very useful, there are many benefits

Fragmented data loading – data will clustered above the HWM so that similar data will be together. Otherwise Oracle will place the records in blocks using PCT_FREE rule

  • No undo will be generated – in large direct path insert will free up large amount of data blocks
  • Less CBC latches (because of no UNDO blocks), less CPU usage
  • Readers need not undo the records, consistent reads are less costly – just read below HWM
  • Rollback will be faster – Oracle will just discard all the blocks above the HWM and it is instant.
  • Less REDO as UNDO blocks are not generated.  In case of NOARCHIVELOG mode, very limited REDO, only to manage the internal operations.  Both instance recovery and media recovery is out of scope from REDO.
  • No UNDO  – no infamous ORA-1555 Snapshoot too old error!

 

To achieve direct path loading- we typically use /*+ APPEND */ clause and that will not generate UNDO.

SQL> insert /*+ APPEND */ into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:03.09

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
 FROM gv$session a, gv$transaction b
 WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         15          1          1   16782851 djzyaz5uj6vnr              .00

We just got one UNDO  record which may be used for internal operation including extent management.  While a traditional load with same data generated 43599 undo records in 950 blocks which is 7600KB in size.

<pre>SQL> insert into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:03.87

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
  2    FROM gv$session a, gv$transaction b
  3    WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         12      43599        950       7683 9c7qck66f77c0              .01

 

Using a direct path load is always useful.  Oracle did a wonderful thing and is documented it says – When you use PARALLEL insert it will be direct path and to enable traditional load, you should explicitly use the hint NOAPPEND.

 

https://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF50101

 

Well, may be true and it is false as well.  I am not sure what meant by DIRECT path load here.   All the above mentioned benefits of APPEND are getting while using PARALLEL hint? I thought it was true, till I test it.  It may be true that Oracle is placing all the blocks above the HWM, but is it reducing the UNDO.   One of the beauty of APPEND is reducing UNDO and if that is not achieved, a direct path load is less beneficial.

I ran the above test again in parallel –

<pre>SQL>  insert /*+ parallel (t1,5) */ into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:02.75

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
  2    FROM gv$session a, gv$transaction b
  3    WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         12      43743        952       7683 29hu6nmj0g66w              .01

 

To my surprise Oracle generated more UNDO records and blocks in a single thread.    So, Oracle is saying PARALLEL is APPENDing, but not all APPEND features are available in PARALLEL load.  Many times in the past I took a decision to use PARALLEL thinking it is real APPEND.   I re-tried the insert with both PARALLEL and APPEND – and it works as expected – but the it took more time to complete.

<pre>QL> insert /*+ parallel (t1,5) APPEND */ into t1 select * from t;
1803136 rows created.
Elapsed: 00:00:05.94

SQL> set linesize 180
SQL> col status for a8
SQL> col undousage for 999999999999.99
SQL>  SELECT b.INST_ID,a.sid, a.username, a.status,b.xidusn, b.used_urec, b.used_ublk,flag,sql_id,b.used_ublk*8/1024/1024 undousage
  2    FROM gv$session a, gv$transaction b
  3    WHERE a.saddr(+) = b.ses_addr and a.INST_ID(+) = b.INST_ID ;
   INST_ID        SID USERNAME                       STATUS       XIDUSN  USED_UREC  USED_UBLK       FLAG SQL_ID               UNDOUSAGE
---------- ---------- ------------------------------ -------- ---------- ---------- ---------- ---------- ------------- ----------------
         2       2299 TEST                           INACTIVE         15          1          1   16782851 avnd4v97v389t              .00

Well, it is just generated 1 UNDO block confirming my assumption.

Follow

Get every new post delivered to your Inbox.