Why are we using root cause analysis?

February 19, 2013 1 comment

RCA stands for Root-Cause Analysis. Why are we using RCA or specifically, why we are using RCA in operation.

It is clear that documenting, archiving, publishing RCA, helps to solve problems faster in re-occurences of problems.

But the there seems to be a deeper philosophy behind…

While looking at Project Management Quality Management topics in Rita Mulcahy(RIP), I see a good principle which may help us in future problems.

 

The 80 percent of problems are due to 20 percent of the root causes. [Pareto Principle, 80/20 principle by Joseph Juran]

If this is real then solving 20 percent of root causes will solve our 80 percent of our problems. So why not find and deal with those big fishes… Or preventing them before occuring.

Advertisements

PLS-00302 error worths me two cups of Turkish tea!

February 13, 2013 Leave a comment

Problem:

A db user came to me that, something strange is happening, he could not manage to execute a PL/SQL block.

He is getting PLS-00302 error while executing an anonymous PLSQL block.

In PLSQL block, there is a function QUERYFUNC which is already created in the same schema.

Thus giving right with grant option is nonsense.

The weird thing does not happened when I executed in SYSTEM schema.

This is what happens:

DUMMY@MYDB:node1> select DUMMY.QUERYFUNC(‘345354’) from dual;

DUMMY.QUERYFUNC(‘345354’)
—————————————————————————-
X

1 row selected.

DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := ‘12345’;
8
9  RetVal := DUMMY.QUERYFUNC ( MYVAR );
10
11  –DBMS_OUTPUT.Put_Line(‘RetVal = ‘ || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15
16
17  /
RetVal := DUMMY.QUERYFUNC ( MYVAR );
*
ERROR at line 9:
ORA-06550: line 9, column 15:
PLS-00302: component ‘QUERYFUNC’ must be declared
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

Cause:

Below code worked, in which DUMMY schema is not specified.
DUMMY@MYDB:node1> declare
2
3  RetVal VARCHAR2(200);
4  MYVAR VARCHAR2(200);
5
6  BEGIN
7  MYVAR := ‘12345’;
8
9  RetVal := QUERYFUNC ( MYVAR );
10
11  –DBMS_OUTPUT.Put_Line(‘RetVal = ‘ || DUMMY.QUERYFUNC ( MYVAR ));
12
13  END;
14
15  /

PL/SQL procedure successfully completed.

First of all I guessed it was because we have same function somewhere else, or there is synonym object which overrides the QUERYFUNC function.

But after querying dba_objects I could not find any.

At last, the goal comes from other side…

Solution

There was a table named DUMMY in the schema named DUMMY.

Which assumes DUMMY.QUERYFUNC is a table column or something I guess.

Thank you for the solution, Mustafa Saburlu. “Afiyet olsun” for Turkish tea 🙂

Using Index Unusable For Gaining Disk Space In Partitioned Tables

February 9, 2013 Leave a comment

Why dedicate disk space for indexes that you are not using for data access in older partitions.

–index partition usable

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’)

Plan
SELECT STATEMENT ALL_ROWS Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2
3 PARTITION RANGE SINGLE Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 1 Partitions accessed #1
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 2 Partitions accessed #1
1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: “DATETIME”=TO_DATE(‘ 2011-10-31 23:45:00’, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 159 Cardinality: 79.677 CPU Cost: 17.926.550 IO Cost: 158 Time: 1 Partition #: 3 Partitions accessed #1

–index partition unusable, after

ALTER INDEX ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK

MODIFY PARTITION P20111031

UNUSABLE;

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’)

Plan
SELECT STATEMENT ALL_ROWS Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9
2 PARTITION RANGE SINGLE Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 1 Partitions accessed #1
1 TABLE ACCESS FULL TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Filter Predicates: “DATETIME”=TO_DATE(‘ 2011-10-31 23:45:00’, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 2 Partitions accessed #1

–other partitions goes over indexes (usable index partitions) Beware that, if you are using bind variables, you may go with FTS over indexed partitions. You may use hints for a solution for access path, like INDEX(CELLHANDOVERS CELLHANDOVERS_PK).

 

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date(‘01.11.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’);

Plan
SELECT STATEMENT ALL_ROWS Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1
3 PARTITION RANGE SINGLE Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 1 Partitions accessed #2
2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 2 Partitions accessed #2
1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: “DATETIME”=TO_DATE(‘ 2011-11-01 23:45:00’, ‘syyyy-mm-dd hh24:mi:ss’) Cost: 7 Cardinality: 2.920 CPU Cost: 749.500 IO Cost: 7 Time: 1 Partition #: 3 Partitions accessed #2

Unique constraint also worked for usable index partition.       

Inserting same row in the range usable index triggered below error:

ORA-00001: unique constraint (ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK) violated

Only thing to remember is: DML activity on unusable partitions is impossible, without rebuilding the index!

Inserting existing row, which must trigger unique constraint, gives unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date(‘31.10.2011 23:45:00′,’dd.mm.yyyy hh24:mi:ss’),’KRP1R01′,’YUNUP32′);

ORA-01502: index ‘ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK’ or partition of such index is in unusable state

Inserting non-existent row, which must not trigger unique constraint, also gives  unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date(‘31.10.2011 23:44:00′,’dd.mm.yyyy hh24:mi:ss’),’KRP1R01′,’YUNUP32′);

ORA-01502: index ‘ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK’ or partition of such index is in unusable state

In a 1TB sized table RNC_CELLSTATS, making unusable frees 15 GB. Thus, you may not gain big spaces from big tables. While selecting targets  focus on index segments having huge sizes.

SEGMENT_NAME GB # partitions
RNC_CELLSTATS

980

91

RNC_CELLSTATS_PK

15

91

Below, focusing on index segments sizes over 100G, you may earn 6 TB disk space. Data is extracted by using DBA_SEGMENTS dictionary view.

SEGMENT_NAME Size GB # partitions Earned GB
CELLHANDOVERS_PK

2.666

1.100

2132

CELLHANDOVERS_IND1

859

1.100

686

CELLHANDOVERS_IND3

819

1.100

654

CELLHANDOVERS_IND2

811

1.100

648

SGSN_CDR_NDX03

725

65

362

RNC_CELLHO_PK

311

91

155

MGW_E1TTP_PK

271

91

216

RNC_GSMRELATION_PK

245

91

122

CELLIDENTITY_NDX

228

65

114

RNC_CELLHO_PK2

208

91

103

RECORDOPENINGDATE_NDX

193

65

96

INTERNAL_SOHO_DS_MISSING_NE_IX

188

31

93

ENUM_FULL_IND01

161

180

128

MGW_E1TTP_D_PK

146

91

72

NEIGHBOUR_CELL_PK

128

100

63

CELLHANDOVERS_CL_PK

123

1.100

98

CCNPERFCOUNTER_PK

122

31

60

MGW_TDMTERMGRP_PK

109

91

54

CELLHANDOVERS_CL_IND2

102

1.100

81

6,280

How to kill, get rid of sticky RMAN processes

February 9, 2013 1 comment

Killing RMAN processes with alter system kill session… will not have an effect on them.They will marked as killed but never killed.

You have no time to wait, but those silly RMAN processes have much of that time. So, how to deal with these guys?

Finding the SPID of the process and killing from OS side is a solution, which most people recommend.

I have a practical, handy solution for this case, don’t wait them:) Use disconnect session clause with immediate keyword with alter system disconnect session. For example, you can disconnect a session having SID 1122 and serial# 222 like below:

alter system disconnect session ‘1122,222’ immediate;

Below is a script which could be used to generate disconnect session sqls for all of the RMAN sessions.

SELECT 'alter system disconnect session '''||sid||','||serial#||''' immediate;' 
FROM V$SESSION s 
 WHERE 
(
UPPER( s.PROGRAM )LIKE 'RMAN%'
)

 

Remember that there are differences between disconnect and kill session clauses.
Most remarkable is ability to give inst_id or not.

In case of disconnect session, You cannot issue disconnect session to sessions spread on cluster instances from one session. Thus, you must directly connect to sessions’ instance first, and then issue the alter system disconnect session statement.

In case of kill session, you have a chance to give third parameter “inst_id” in kill session clause. This allows you to kill sessons spread on cluster instances from one session.

It woths referring to SQL Language Reference for details of disconnect session and kill session clauses.

Hope this helps!

blogging 2012 in review, sharing is everything

January 1, 2013 Leave a comment

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

4,329 films were submitted to the 2012 Cannes Film Festival. This blog had 32,000 views in 2012. If each view were a film, this blog would power 7 Film Festivals

Click here to see the complete report.

Categories: Uncategorized Tags: ,

Oracle RACSIG subscription

December 25, 2012 Leave a comment

While looking for something in Oracle discussion forum, I bumped into webinar link, which lead me to subscribe Oracle RAC SIG.

It will enable me to get enthusiast with RAC live webinars, documents from now on.

You may subcribe from http://www.oracleracsig.org/pls/apex/f?p=105:51:0::NO:51

Enjoy it !racsig

Categories: tips Tags: , , , ,

Which rights must be granted for displaying TOAD session browser

November 22, 2012 1 comment

Following rights must be given to specific user, here MYUSER, in order to display sessions in TOAD session browser.

GRANT SELECT ON GV_$SESSION TO MYUSER;
GRANT SELECT ON GV_$PROCESS TO MYUSER;
GRANT SELECT ON GV_$SESS_IO TO MYUSER;
GRANT SELECT ON GV_$SESSION_WAIT TO MYUSER;
GRANT SELECT ON GV_$SESSION_EVENT TO MYUSER;
GRANT SELECT ON GV_$ACCESS TO MYUSER;
GRANT SELECT ON GV_$SESSTAT TO MYUSER;
GRANT SELECT ON GV_$SQL_PLAN TO MYUSER;
GRANT SELECT ON GV_$SQLTEXT_WITH_NEWLINES TO MYUSER;

Categories: tips Tags: , , , ,
Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.