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.
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.)
Wednesday, December 22, 2010
tkprof trace file analysis.....
Friday, December 10, 2010
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
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.
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...
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.
Subscribe to:
Posts (Atom)