Any Relational Database Management System, temp database (in MS SQL) or tablespace (in Oracle) extensively used during data sorting, index creation, join operation on multiple tables. These activities heavily engaged writing operations on disk storage.
So, the files belong to temporary database/tablespace where it placed in the disk is really cautious decision making at the time of database implementation.
Better place in disk storage for temp files is RAID-1, because of high load of writing data in disk. Try to avoid place in RAID-5, which could cause I/O congestion, eventually cause performance issue in database server.
Monday, August 23, 2010
Sunday, July 11, 2010
sqlldr nls_date_format
The environment variable NLS_DATA_FORMAT can be used to define date format of data in input file (holding text data). The oracle manual mentioned this point in "DIRECT PATH LOAD" session.
Mostly, the data conversion from client side to server can define better in control files rather than depending on this environment variable.
But, the behavior of sqlldr differs between WINDOWS and UNIX clients.
In UNIX, we have to use this option "DIRECT=true" of sqlldr to get effect of using NLS_DATE_FORMAT environment variable.
In Windows, no need to be using "DIRECT" loading option, still conventional loading also take account of this environment variable to define the date format of value in input file.
When you convert a script from Windows to UNIX platform which performs data loading by using sqlldr, then this information will be really helpful.
Mostly, the data conversion from client side to server can define better in control files rather than depending on this environment variable.
But, the behavior of sqlldr differs between WINDOWS and UNIX clients.
In UNIX, we have to use this option "DIRECT=true" of sqlldr to get effect of using NLS_DATE_FORMAT environment variable.
In Windows, no need to be using "DIRECT" loading option, still conventional loading also take account of this environment variable to define the date format of value in input file.
When you convert a script from Windows to UNIX platform which performs data loading by using sqlldr, then this information will be really helpful.
Tuesday, May 4, 2010
Oracle Performance Tuning - Creating Indexes
We have noticed few queries in production database which doing full table access in two big tables holding more than 1.5 million rows. This was causing heavy CPU utilization, USER I/O waiting.
Actually, the database server does not have good storage equipment, all data files stored in local hard disks in RAID 5. There are few e-business applications using this database in 24x7.
We spent our time to find out alternate queries which can use existing indexes on these tables by analyzing SQL Execution Plans.
Finally, pass those NEW set of SQL coding to Application team for reviewing and follow their tests in QA System.
For them, this is unnecessary work, also not taking risks to amend their application code which is almost one decade old. After few months, there was no news from them. Whether, they are still doing the code review or shelved it.
The database server was still hitting high CPU usage and wait for USER I/O. The possible way to jump for this issue is, by creating new indexes and find out existing SQL coding to avoid full table access in these tables and start using the new indexes.
Consider the followings before creating indexes in Production database
1. Gather Tablespace size where tables and its existing indexes were stored
2. Gather size of Tables and its indexes
3. Estimate the size requirement for new indexes
4. Come up with syntax and do a dry run in testing environment, find out estimated time of execution (this could be varied in production server)
5. Remember to use "NOLOGGING" option in the syntax in order to avoid unnecessary logging in redo logs otherwise index creation could be taken longer duration to complete
6. Very Important option is "ONLINE" which helps to execute DML statements on these tables and not get application disturbed
Well, after introducing new indexes, the CPU usage/USER I/O wait reduced drastically.
Actually, the database server does not have good storage equipment, all data files stored in local hard disks in RAID 5. There are few e-business applications using this database in 24x7.
We spent our time to find out alternate queries which can use existing indexes on these tables by analyzing SQL Execution Plans.
Finally, pass those NEW set of SQL coding to Application team for reviewing and follow their tests in QA System.
For them, this is unnecessary work, also not taking risks to amend their application code which is almost one decade old. After few months, there was no news from them. Whether, they are still doing the code review or shelved it.
The database server was still hitting high CPU usage and wait for USER I/O. The possible way to jump for this issue is, by creating new indexes and find out existing SQL coding to avoid full table access in these tables and start using the new indexes.
Consider the followings before creating indexes in Production database
1. Gather Tablespace size where tables and its existing indexes were stored
2. Gather size of Tables and its indexes
3. Estimate the size requirement for new indexes
4. Come up with syntax and do a dry run in testing environment, find out estimated time of execution (this could be varied in production server)
5. Remember to use "NOLOGGING" option in the syntax in order to avoid unnecessary logging in redo logs otherwise index creation could be taken longer duration to complete
6. Very Important option is "ONLINE" which helps to execute DML statements on these tables and not get application disturbed
Well, after introducing new indexes, the CPU usage/USER I/O wait reduced drastically.
Subscribe to:
Posts (Atom)