Delete large amount of data....

If you are at home and want to delete large data from tables then just go ahead and delete it. No problem at all.


But ! But, What to do when you have a task to delete some unused/bad data (Usually these number is large) from very critical large table.


Before executing well known delete sql statement you need to think twice(or may be thrice ...counting....). first you should ask yourself whether delete is require, if yes then you need to calcualte amount of data that you want to delete, and then comapre that amount with actual number of records of table. If amount of unused rows exceed 65%(Approx., Dont expect rule of thumb here) of total size of table then transfer good data into some other identical table,drop old one, create require index in new table....and its done.(Yes! In most cases its done!!)


You need to take lots of other thing in consideratin as well, before executing delete operation

Some are:
First identify what is off-peak hours for your business.
calcualte size of data that you need to delete.
estimat amount of redo that might generate.
estimat time that it would take to complet delete operation, rebuilding indexes etc..
Try to devide whole delete operation in small chunks.
Use paralle and nologgin operation (With care, obviously !).

Recent interview

Hi,
Recently I was interviewed by one big company's very qualified DBA.

After some of question we have debate on how to trace and how to format trace file.

I told him that we can do it using tkprof, but he don't want to believe this as he stuck to his belief of oradebug. He said that if you want to format trace file there is one way and that's OraDebug only.

I am sure that tkprof do it better way.

I hope most of you are agree with me...if not let me correct

Left outer Join converted into Inner Join Part-2

Here is the detailed description of problem mentioned here.

CREATE TABLE TEMPA
(
P_UNIQUEID NUMBER(8),
COMPCODE NUMBER(4),
COUNTRYCD NUMBER(4),
STATECD NUMBER(4),
DISTRICTCD NUMBER(4),
PRODUCTCD VARCHAR2(50 BYTE),
STARTDATE DATE,
DISCONTINUEDATE DATE
)

CREATE TABLE TEMPE
(
COMPCODE NUMBER(4) NOT NULL,
PRODUCTCD VARCHAR2(50 BYTE) NOT NULL,
COUNTRYCD NUMBER(4),
STATECD NUMBER(4),
DISTRICTCD NUMBER(4),
SCHEMECD NUMBER(8) NOT NULL,
PARTYCODE VARCHAR2(10 BYTE),
SCHEMEFRDATE DATE NOT NULL,
SCHEMETODATE DATE NOT NULL,
SCHEMESHORTDESC VARCHAR2(30 BYTE) NOT NULL
)

Insert into TEMPA (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD,PRODUCTCD, STARTDATE, DISCONTINUEDATE)
Values(5233, 3, 1, 3, 999, 'Product-1', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEMPA (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD,PRODUCTCD, STARTDATE, DISCONTINUEDATE)
Values(5234, 3, 1, 3, 999, 'Product-2', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

Insert into TEMPE (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD,SCHEMECD, PARTYCODE, SCHEMEFRDATE, SCHEMETODATE, SCHEMESHORTDESC)
Values(3, 'Product-1', 1, 14, 999,1554, '999', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'LeftToInner');

Insert into TEMPE (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD, SCHEMECD, PARTYCODE,SCHEMEFRDATE, SCHEMETODATE, SCHEMESHORTDESC)
Values(3, 'Product-1', 1, 3, 999, 1554, '999', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'LeftToInner');

Commit;

Now lets check result of our sql statement in both 9i and 10g database.

Result of query on Oracle 9i Database (Oracle version 9.2.0.1.0, RHEL 2.1)

Select a.p_uniqueid,e.schemeshortdesc
from tempa a
left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd
and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
where
a.compcode=3 and
a.countrycd in(1,999) and
a.statecd in(3,999) and
a.districtcd in(14,999) and
'31-December-2010' between a.startdate and a.discontinuedate and
e.partycode in('A0101A0199','999') and
e.userid<>'0' and
e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
/

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner
5234


Result of query on Oracle 10gR2 Database

Select a.p_uniqueid,e.schemeshortdesc
from tempa a
left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
where
a.compcode=3 and
a.countrycd in(1,999) and
a.statecd in(3,999) and
a.districtcd in(14,999) and
'31-December-2010' between a.startdate and a.discontinuedate and
e.partycode in('A0101A0199','999') and
e.userid<>'0' and
e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
/

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner


As you notice we have different result set for Oracle 9i and Oracle 10g(For Oracle 11g as well). The reason is in Oracle 9i we execute left outer join query(At least we believe so!!) and Oracle execute it as left outer join operation. While in Oracle 10g, same sql statement executed as inner join operation.

To confirm inner join here is execution plan for query:-

Execution Plan
----------------------------------------------------------
Plan hash value: 3150946153

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 248 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 248 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TEMPE | 1 | 138 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEMPA | 2 | 220 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

The problem is the Sql is not written properly for left outer join operation. If you move some part of where clause up with ON clause then Oracle will perform Left outer join. See this:-

SQL> ed
Wrote file afiedt.buf
1 select a.p_uniqueid,e.schemeshortdesc,e.userid,e.partycode,e.schemefrdate,e.schemetodate
2 from tempa a
3 left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd
4 and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
5 and e.partycode in('A0101A0199','999') and
6 e.userid<>'0' and e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and 7 e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
8 where
9 a.compcode=3 and
10 a.countrycd in(1,999) and
11 a.statecd in(3,999) and
12 a.districtcd in(14,999) and
13* '31-December-2010' between a.startdate and a.discontinuedate
14 /

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner
5234

Execution Plan
----------------------------------------------------------
Plan hash value: 965687122

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 232 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 2 | 232 | 9 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEMPA | 2 | 114 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 59 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TEMPE | 1 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Left outer Join converted into Inner Join

Last week one of my friend ask me,
"Why database return different result set when we upgrade from Oracle9i to Oracle 11g?"

He mean to say that, sql statement which return correct result in Oracle 9i, is not returning same result set in Oracle 11g.

I immediately told him to check data, May be data are not properly migrated. But I was wrong as he already validate that. Then I ask for table structure and sql statement. After analysis of sql statement I come to the conclusion that new Oracle 11g(and even 10g) optimizer may ignore left outer join and instead perform Inner join.

This is possible when you have not written sql join properly or sometime because of ANSI sql.

I will post detailed scenario and explanation later on this week.

Extra Shot:-
Detailed description available here

tkprof trace file analysis.....

Friends & Experts,

Examine the below trace file, which generated after running
the application for 7 mins. The person who give me this trace
file told me to tune this query, because his application takes
around 7 mins to complete one task which suppose to takes only
few second.


Can you figure out what the problem is ?
SELECT MIN(DATE_Column)
FROM
SOME_TABLE WHERE SOME_COLUMNE = :B1

call     count       cpu    elapsed    disk      query   current      rows
------- ------  -------- ---------- ------- ---------- ---------  --------
Parse       41      0.00       0.00       0          0         0         0
Execute  10846      3.99       4.17       0          0         0         0
Fetch    10846     23.04      24.39       0     264858         0     10646
------- ------  -------- ---------- ------- ---------- ---------  --------
total    21733     27.03      28.57       0     244858         0     10646
Lets, first try to locate the problem.

Execution count=10846
Elapsed Time=28.57, So on avg one execution takes around 3ms....So this is good
Consistent read block=264858, So on avg 24 blocks/execution....So this seams OK..


Than, where the problem is?? Problem is in Application designing(coding).
Out of 7min of tracing only 29 second used by this query, So where the remaining
400 seconds spent by application. One should need to find out this, for this it is
require to look at detailed application tracing and/or statspack/AWR report.

One more thing I like to raise here: Why application executes this query 25 times
per second? If there are multiple session going on simultaneously then this is ok,
Otherwise we need to look at this also.(Actually I saw one application which executes sql
50 times per second because of poor application coding only.)

Why good Database Design is important

It is very important to have good database/Application design as per our current application requirement. But, it is also very important that we should keep in mind that, this is not the only application which uses this database and we are not only one who use this, there may be lots of other applications which may develop on this database. So we should try our best to design one strong base so that other peoples also can easily scale application and can build new features on it.

One very good example of good design is the way Oracle build core database engine /architecture, way back in the past. If you are DBA, then you might note that, despite addition of lots of new features in each version of Oracle RDBMS, the core of Oracle remains same. There is hardly any change on core structure to accommodate/build more feature on it.

Oracle has layered (storage) architecture, and at core it has block(Managed block, for it's own use) than extent than datafile, redo log file, control file. And because of this intelligently designed architecture, it is easy to add functionality and features without changing or rewriting core structure. (From here, I can see that, one day oracle RDBMS not even require OS and because of this, it would be very very fast database engine. ASM is just first step towards this)

Just for one example, take a look on below up-gradation of Oracle RDBMS

• Oracle first have redo log files, which fills circularly.
• Next time Oracle decide to archive them before it get overwrite and we have "Archived Log File"
• Than Oracle decide to use these log file for database recovery.
• Than Oracle thought, if Archived Log file is used to recover the database from which it originates then it might be possible that we can recover copy of original database and upgrade it, and result of this is we have luxury of "Dataguard" and now in 11g there is Active data guard as well....and this up-gradation going on and on...

Similar way, you can consider the improvements in Oracle memory (SGA/PGA) architecutre.

Back to the main point from world or Oracle....!!
Recently, I have one assignment to develop small application for "time calculation" functionality.
When I analyze the architecture and design of database I realize that, it would be much complex to complete this task than my first estimation.

The database misses the core rule of normalization. I don't want to post original design in detail over here as it is strictly confidential things but let me explain it why it is missing some basic design rule with one example.

There is one table which maintain working hours for weekdays. and it is store like one mention below:-
Old Design
Day | Hour(Varchar)
MON| 8:00-12:00,13:00-17:00
TUE | 8:00-12:00,13:00-17:00

As you can see original designer save lots of column :) and uses only one varchar column to save all time part in it. (Thanks god, this enterprise has only one break during the day :).

There are more than one way to redesign this table based on different requirements.
For example we can change this as one shown below:-

Day | InTime | Outtime | Breaktime
Mon | 8:00 | 17:00 | 12:00-13:00

Because of old table design, I have to do some string manipulation operation and type casting operation to get data that I need for my purpose. I wonder, how many other application uses this table and how many other people have to do type casting and string manipulation operation to get data in correct(usable) form. Because of all this, it took double of time for me to complete this task than it would takes if this table design follow some normalization rule.

So, next time when you start working on database design just remember that your database design would save lots of time and efforts of other people in future, if it has really good design.

How to start database tuning and how to know, what to tune?

How to start database tuning and how to know, what to tune?

This question is frequently asked by many people online as well as offline.
Actually there is no rule of thumb for performance tuning. Before starting tuning process, one should ask him self, why he want to tune? Is database really require to tune?

Sometime people read article from web regarding "Best practice for database PT" and start to implementing same on their production database. Don't do this. Always take some precautionary steps to avoid bottleneck but this is not mean that you should implement each "Best Practice" on your database.

Performance tuning practice is like medical practice. When you visit your doctor, he/she first ask you few questions to find out what is problem and what is CAUSE of problem, before suggesting any medicine. Just imagine what happen if he/she give you medicine for all general disease without diagnosing any thing. In that case do we really need doctor? No.

So, the important thing is diagnosis of problem. Without knowing actual cause of problem you can't resolve it for a long time. :(though, you can resolve it for a short time :). Suppose you have fever and because of that you have headache and body-ache. In this case root cause of problem is fever, so take medicine for fever, not for headache.

What I am trying to convey is, first identify symptom, root cause of problem and then take necessary steps to solve it. This will solve your problem for long time without any side effect.

Most of the time you have message like application is running slow from end user. After this, it is your task to find out why it is running slow. Most of the time you will found that slow response is because of poorly written sql query. So, try to rewrite it before upgrading hardware or software for this :)

And remember that the person suffering from last stage of cancer do not need medicine for headache.

Share your thoughts for the same. Have a happy weekend..