Back to basics Part-1

Folks,
Hope you all doing well! After listening to so many basic questions from beginners, I decide to post some database basic things that helps beginners to work with database.

First thing, that you need is location(url) from where you can get database software to install? You can get database software from oracle's website itself, its free for development but, If you want to use it commercially, you have to first purchase license for that.

After downloading it, just follow some steps as instructed by Oracle installer software and you have database ready in your machine.
There are 3- different types of installation are possible 1. Enterprise edition 2.Standard edition 3.Personal Edition, Select Enterprise edition. Please note down password that you specify during installation. Later on, by using this password you can login into database as SYS user.

Here is good step by step explanation for oracle installation on windows

Once installation is complete follow below steps:

To login into database and to create test table just follow steps mention below(Windows OS):

start>cmd
c:>sqlplus sys/password1 as sysdba
sql>create table test(col1 number,col2 varchar2(100),col3 char(10));

Please note that it is not recommended to create table under sys account. In Part-2 we will learn how to create other user/schema in database.

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.