Oracle

June 5, 2016

Analyze index validate structure “online” does not populate “index_stats”

Filed under: Uncategorized — srivenu @ 6:35 pm

Using “online” clause on analyze index … validate structure does not populate index_stats. It hasn’t changed even in the latest version of Oracle Database 12c. If you don’t use the online clause, the session running analyze index locks up other sessions performing dml’s on the index on a TM enqueue.

To reduce the locking and get the index_stats, i use the analyze command twice, first with the online clause to fill the cache with the index blocks immediately followed by the same analyze command without the online clause.

ex –

analyze index ix validate structure online;

immediately followed by

analyze index ix validate structure;

May 23, 2016

Restart MMON process without bouncing the database

Filed under: Uncategorized — srivenu @ 4:40 pm

Today one of my team members was mentioning to me that AWR snapshot’s were not getting generated in one of his instance. It seems that MMON process crashed with ORA-600. As a workaround, he created a cron job to manually generate AWR snapshots. Looks like he couldn’t find the solution for the problem and was going to bounce the instance to fix the issue. I had experience the issue of MMON crash a few times in the past and i used this workaround to fix it. Thought i would share it for the benefit of others.

Run the following commands from a sqlplus session and this should start a new MMON process.

alter system enable restricted session;

alter system disable restricted session;

(For junior DBA’s, be very careful and don’t do this on a critical production system. Even though you put the system in restricted mode only for a brief duration, be aware of the restrictions of restricted mode – refer to “Restricting Access to an Instance at Startup” in Oracle Database Administrator’s Guide)

January 23, 2013

Storage Virtualization and Dynamic Tiering

Filed under: Uncategorized — srivenu @ 9:28 am

One of my recent projects, a DW project for a Telecom company in South Africa, involved configuring Hitachi VSP storage. Its good to see how the storage technologies are evolving, freeing the DBA’s from the nuts and bolts activity of configuring the storage.
I turned nostalgic, remembering how things were during the start of my career. At the start, while using direct attached storage, i used to carefully design file systems according to their IO pattern and performance requirements and carve them on different sectors of the hard disks. Later on volume managers simplified things. I still vividly remember configuring the high-end EMC Symmetrix 8830 boxes (as well as HP Superdome 24000) in 2001. Configuring that high-end storage too wasn’t free from the nuts and bolts activity. I was closely working with the EMC engineers in carving out Hypers and Metas. We still worked at sector level for better performance of some classes of data like REDO.
I feel that enhancements in storage virtualization and Dynamic Tiering are going to reduce a lot of storage configuration effort and headache for the DBA’s. It would be interesting to watch how the storage technologies fan out in the future.

March 29, 2012

Tuning sql with predicates on Scalar Subqueries

Filed under: Uncategorized — srivenu @ 12:13 am

Due to lack of standardization on technical terms, i want to mention at the outset that by “Scalar subqueries” i meant subqueries appearing in the FROM clause. The Scalar subquery executes for each row of the driving query and returns one row. The scalar subqueries could be converted into outer joins, but if there are predicates in the scalar subqueries or on values returned by them, we need to take care of how we write the predicates using outer joins lest they become outer join killers.

Following is one sql that i worked on recently.


SELECT	....
FROM	(
	SELECT	TA.ID,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'FT'
		) 	TY,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'EOI'
		)	EOI,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'IG'
		)	IG,
		....
	FROM 	TASK TA
	WHERE	TA.NAME = 'HPE'
	)
WHERE	IG = 'N'
AND	EOI = '123456'
AND	TY = 'PP'
/

Following is the plan and some relevant execution stats


------------------------------------------------------------------------------------
| Id  | Operation            | Name                    | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |                         |      1 |      1 |    5003 |
|*  2 |   INDEX RANGE SCAN   | IDX_TASK_NA_ID          |      1 |      0 |    5003 |
|*  3 |    INDEX RANGE SCAN  | IDX_PROP_NA_ID_VA       |    902 |    902 |    2801 |
|*  4 |     INDEX RANGE SCAN | IDX_PROP_NA_ID_VA       |    613 |    613 |    1910 |
|*  5 |      INDEX RANGE SCAN| IDX_PROP_NA_ID_VA       |      0 |      0 |       0 |
------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / TA@SEL$2
   3 - SEL$5        / PROP@SEL$5
   4 - SEL$4        / PROP@SEL$4
   5 - SEL$3        / PROP@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TA"."NAME"='HPE')
       filter((='N' AND ='123456' AND ='PP'))
   3 - access("PROP"."NAME"='IG' AND "PROP"."ID"=:B1)
   4 - access("PROP"."NAME"='EOI' AND "PROP"."ID"=:B1)
   5 - access("PROP"."NAME"='FT' AND "PROP"."ID"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   3 - "PROP"."VAL"[VARCHAR2,254]
   4 - "PROP"."VAL"[VARCHAR2,254]
   5 - "PROP"."VAL"[VARCHAR2,254]

The following select on TASK table retrieves 902 rows.


SELECT	TA.ID, ......
FROM	TASK TA
WHERE	TA.NAME = 'HPE'

The IDX_TASK_NA_ID index on TASK table is on columns NAME, ID, …(and a few other in that order)
The IDX_PROP_NA_ID_VA index on PROP table is on the columns NAME, ID, VAL (in that order)

The code path of the plan can be deduced as follows


Step 1 - Get columns ID, .. from IDX_TASK_NA_ID where TA.NAME = 'HPE'
Step 2 -  for each row from Step 1, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'IG' and PROP.ID = TA.ID
Step 3 -   for each row from Step 2, check if IG = 'N' if true go to Step 4 else go to Step 1 and retrieve next row
Step 4 -    for each row from Step 3, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'EOI' and PROP.ID = TA.ID
Step 5 -     for each row from Step 4, check if EOI = '123456', if true go to Step 6 else go to Step 1 and retrieve next row
Step 6 -      for each row from Step 5, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'PP' and PROP.ID = TA.ID
Step 7   -     for each row from Step 6, check if TY = 'PP', if true select the row else go to Step 1 and retrieve next row

Steps 2, 4 & 6 may in fact not result in an actual index access due to something called “scalar subquery caching”. You could refer to this “exchange” between the masters for more info.
So from the execution stats, we can make out that,
At Step 1, 902 rows were retrieved from TASK table.
At Step 2, the IDX_PROP_NA_ID_VA index was accessed 902 times to retrieve PROP.NAME for that ID, out of the 902 rows, only 613 rows had PROP.NAME = ‘IG’.
At Step 4, the IDX_PROP_NA_ID_VA index was accessed 613 times to retrieve PROP.NAME for that ID, out of the 613 rows, 0 rows had PROP.NAME = ‘EOI’.
The rest of the steps were not executed at all.

The key to tuning the above query is to see that the predicates are ordered in the right way and see that the max throw away occurs at the first occassion itself. This would have been easier to control if outer joins were used instead of the scalar subquery variant. I couldn’t find (atleast in 10g) any hint or outline to order the evaluation of scalar subqueries. But we are not without hope as we could either use the ORDERED_PREDICATE hint or put the predicates in required order and the CBO automatically takes the order into consideration.

Following are the stats for the different variations in the order of predicates. Using the predicate EOI = ‘123456’ (which has the max throwaway) at the start will result in the best performance.




********************************************************************************

WHERE   TY = 'PP'
AND     EOI = '123456'
AND     IG = 'N'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.04          0       5914          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.04          0       5914          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5914 pr=0 pw=0 time=42585 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=5914 pr=0 pw=0 time=42556 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2808 pr=0 pw=0 time=19535 us)(object id 169317)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=16792 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   TY = 'PP'
AND     IG = 'N'
AND     EOI = '123456'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.05          0       7816          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.06          0       7816          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7816 pr=0 pw=0 time=59340 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=7816 pr=0 pw=0 time=59308 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2808 pr=0 pw=0 time=21716 us)(object id 169317)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2806 pr=0 pw=0 time=18735 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=11329 us)(object id 169317)

********************************************************************************

WHERE   IG = 'N'
AND     TY = 'PP'
AND     EOI = '123456'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.04          0       6905          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.04          0       6905          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=6905 pr=0 pw=0 time=41378 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=6905 pr=0 pw=0 time=41352 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2801 pr=0 pw=0 time=14822 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1902 pr=0 pw=0 time=9199 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=9992 us)(object id 169317)

********************************************************************************

WHERE   IG = 'N'
AND     EOI = '123456'
AND     TY = 'PP'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.03          0       5003          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.03          0       5003          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5003 pr=0 pw=0 time=31018 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=5003 pr=0 pw=0 time=30988 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2801 pr=0 pw=0 time=14333 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=10980 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   EOI = '123456'
AND     IG = 'N'
AND     TY = 'PP'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.02          0       3106          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0       3106          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3106 pr=0 pw=0 time=22878 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=3106 pr=0 pw=0 time=22853 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=17653 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   EOI = '123456'
AND     TY = 'PP'
AND     IG = 'N'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0       3106          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0       3106          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3106 pr=0 pw=0 time=18483 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=3106 pr=0 pw=0 time=18454 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=14361 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

March 6, 2012

Blocking_Instance and Blocking_Session values in GV$SESSION could be wrong

Filed under: Uncategorized — srivenu @ 10:26 am

Recently in one of our RAC systems (64bit – 10.2.0.4.0 on AIX 5.3), I observed several sessions waiting on “enq: TX – row lock contention”.
The waiters locked out at the following update


UPDATE 	CUST
SET 	UPDATED_DATE = :1 
WHERE 	CUSTID = :2

I tried to find out the enqueue holders


select 	blocking_instance, blocking_session, inst_id, sid
from 	gv$session 
where 	event = 'enq: TX - row lock contention'
order	by 1, 2, 3, 4
/


                                     Inst
BLOCKING_INSTANCE BLOCKING_SESSION   ance    Sid
----------------- ---------------- ------ ------
                1              489      1    962
                1              489      3    642
                1              840      3    777
                1              875      1    953
                1              875      3    883
                1              986      3    928
                1              986      3    971
                1             1042      1    821
                1             1079      3    533

                2              495      2    762
                2              495      3    729
                2             1079      3    604

                4              841      4    733
                4              887      4   1005
                4              968      3    916
                4             1029      3    581

Sid 1079 on Instance 1 is one of the enqueue holders.

And following are the details of sid 1079 on instance 1


col sid head "Sid" form 99999
col pid head "PID" form 9999
col serial# form 99999 head "Ser#"
col username head "DB User" form a8
col program head "Program" form a10
col module head "Module" form a10
col action head "Client|Action" form a12
col machine head "Client|Machine" form a15
col cprocess head "Client|Process|ID" form a9
col osuser head "Client|OS User" form a7 
col spid head 'Oracle|Back|ground|Process|ID' form a8


select 	s.sid, s.serial#, p.pid, s.process cprocess, s.osuser osuser,
	s.program, s.module, s.action, s.machine machine, s.username username, p.spid
from 	v$session s,
	v$process p
where 	s.paddr(+)=p.addr
and	sid = 1079
order 	by 1
/

                                                                                                  Oracle
                                                                                                  Back
                    Client                                                                        ground  
                    Process   Client                        Client       Client                   Process 
   Sid   Ser#   PID ID        OS User Program    Module     Action       Machine         DB User  ID      
------ ------ ----- --------- ------- ---------- ---------- ------------ --------------- -------- --------
  1079      1    18 13541454  oracle  oracle@abc                         abcora1racprod           13541454
                                      def1racpro
                                      d (DBW0)

The holder is the DBW0 process on instance 1
Why does a DBW0 process hold up a foreground session on a TX enqueue on a user table?
Having experienced this issue several times in the past, i decided to put in a small note to warn others not to take the Blocking_Instance and Blocking_Session values from GV$SESSION view at face value.

I normally use the following sql to find out the holders of “enq: TX – row lock contention”


col wi head "Wai|ti|ng|In|St|an|ce" form 9999
col wu head "Waiting|User" form a10
col wsid head "Waiting|Sid" form 99999
col wser head "Waiting|Ser#" form 99999

col hi head "Ho|ld|ing|In|St|an|ce" form 9999
col hu head "Holding|User" form a10
col hsid head "Holding|Sid" form 99999
col hser head "Holding|Ser#" form 99999

select  hs.inst_id hi, hs.username hu, hs.sid hsid, hs.SERIAL# hser, 
	ws.inst_id wi, ws.username wu, ws.sid wsid, ws.SERIAL# wser, 
	t.xidusn, t.xidslot, t.xidsqn, t.status
from 	gv$session ws, gv$session hs, gv$transaction t
where 	hs.taddr = t.addr
and	hs.inst_id = t.inst_id
and	t.xidusn = trunc(ws.p2/65536)
and	t.xidslot = mod(ws.p2,65536)
and	t.xidsqn = ws.p3
and	ws.event like 'enq: TX%'
order 	by hs.inst_id, hs.sid, hs.SERIAL#, ws.inst_id, ws.sid
/

The output is something like this



   Ho                              Wai
   ld                               ti
  ing                               ng
   In                               In
   St                               St                             undo    undo     undo
   an Holding    Holding Holding    an Waiting    Waiting Waiting   Seg    Slot      Seq
   ce User           Sid    Ser#    ce User           Sid    Ser#    No      No       No Status
----- ---------- ------- ------- ----- ---------- ------- ------- ----- ------- -------- ------
    1 ABCDEFGH       489   16589     1 ABCDEFGH       962    7535     5      27   394378 ACTIVE
    1 ABCDEFGH       489   16589     2 ABCDEFGH       423     365     5      27   394378 ACTIVE
    1 ABCDEFGH       840   61100     2 ABCDEFGH       580    1346    97      37    12608 ACTIVE
    1 ABCDEFGH       875   64492     1 ABCDEFGH       953   40122    56      43    87618 ACTIVE
    1 ABCDEFGH       875   64492     2 ABCDEFGH       726   20828    56      43    87618 ACTIVE
    1 ABCDEFGH       967   47330     2 ABCDEFGH       815    3000    55      42   116791 ACTIVE
    1 ABCDEFGH       986   60240     2 ABCDEFGH       764   54442    71       8   212341 ACTIVE
    1 ABCDEFGH       986   60240     2 ABCDEFGH      1017    4489    71       8   212341 ACTIVE
    1 ABCDEFGH      1042   42742     1 ABCDEFGH       821   42845    58       0   100629 ACTIVE

    2 ABCDEFGH       425   14272     1 ABCDEFGH      1028   42640   113      14   291960 ACTIVE
    2 ABCDEFGH       425   14272     4 ABCDEFGH       717   20891   113      14   291960 ACTIVE
    2 ABCDEFGH       495    8828     1 ABCDEFGH       458   28807   195      40    55394 ACTIVE
    2 ABCDEFGH       495    8828     2 ABCDEFGH       762   39933   195      40    55394 ACTIVE
    2 ABCDEFGH       706   54438     1 ABCDEFGH       960   24251   209      18     7352 ACTIVE
    2 ABCDEFGH       872   55363     4 ABCDEFGH       887   19939   212      30    21108 ACTIVE
    2 ABCDEFGH       893   60900     1 ABCDEFGH       843   38790   240      33     2689 ACTIVE

Next Page »

Create a free website or blog at WordPress.com.