Dec/090
Oracle Performance Tuning Tactics Part 2: Using Indexes
To recap, the “weapons” available in your “arsenal” for performance tuning are as follows:- Full table scans ; Indexes; Joins; Views; De-normalization; Oracle Stored Procedures; Sub-queries.
2. Indexes
There are several types of indexes available with Oracle databases:
* normal/B-Tree (balanced/ binary tree) indexes;
* bitmap indexes (which literally use a map of bits to represent the whole index and are
* function-based indexes (which are based on the value of an expression instead of the value of a column);
* partitioned indexes (tables and indexes may be partitioned separately)
* domain indexes (application-specific indexes created on complex data types such as spatial data).
This article only discusses bitmap and normal (b-tree) indexes.
2.1 B-Tree Indexes
B-Tree Indexes are most appropriate when retrieving a small amount of data from a very large table. The bigger the table and the lower the number of rows that you want to retrieve then the more effective the index.
If you think of the printed material in the real world, this makes perfect sense, you wouldn’t bother to create an index for a short article, but you would certainly want one for a lengthy reference book. The reasons are analogous too. When reading a short article it would take longer to find the areas of interest using the index then it would to read the whole article. It’s the same when you’re querying the database – for a small table with Oracle’s multi-block read feature of Oracle, the whole table would be read in one go, so by going to the index first, the database would have to read more data overall.
Indexes may improve read access to data and update operations (including insert and delete) using sub-queries. However update performance will be impaired because every time a row is inserted into, deleted from or updated (if the indexed column(s) is/are updated) in the database, the indexes have to be adjusted accordingly, thereby increasing the amount of i/o that has to performed.
Therefore having many indexes is ideal when the database is mostly read only, but if there are a high proportion of inserts and deletes and only a few read operations, then you would find that adding more indexes would degrade rather than improve performance. You also need to ensure that the column(s) you are proposing to index is/are used frequently as the limiting conditions for a query, if not you will waste a lot of space and degrade database performance.
Another factor to consider with b-tree indexes in Oracle databases, is how specific they are – their cardinality. The ideal b-tree index is one that refers to only one row in the table, as a primary key does by definition. This reduces considerably the amount of i/o required to retrieve the data, but if the index value relates to many rows because it is not selective, then a lot more data will be retrieved resulting in unnecessary i/o.
Dec/090
Oracle Performance Tuning Part 1: Using Full Table Scans
It is a common misconception that all SQL queries on all tables in Oracle databases should be index driven. In fact, using full-table scans can improve performance in two scenarios: when querying very small tables and when querying very large tables.
The Effect of Full Table Scans When Querying Small Tables
Let’s suppose your using your Oracle database to run in-house designed and built HR application. Consider a reference table such as a list of department ids and the associated department names. Even a large company is likely to have only a few departments – HR, Sales, Marketing, Finance, IT, so the table is going to be quite small.
Now let’s suppose the table has just 2 columns – department id and department name – with an index on the department id. To find the department name for a given id, we would have to read the index and then read the table, but because the table is so small and because Oracle reads multiple database blocks in one read operation the whole table is scanned in just one read, so however efficient the index, by using it we will be performing unnecessary i/o.
In this case, therefore, a full table scan is faster than an index scan and table lookup. The exception to this of course is when the table has been created as an index-only table (available since Oracle 
which means that the whole table is stored in a B-tree structure (although you may have pointers to overflow areas).
The Effect of Full Table Scans When Querying Very Large Tables
Let’s look at using this technique for querying very large tables in your Oracle database. Surely they should use an index? Otherwise you might have to read thousands of blocks. It is correct to say that a full table scan of a very large table could read many thousands of data blocks, but as we shall see it may be better to do this than to perform an index scan and table lookup.
The situation when the a full table scan is very likely to perform better than an index scan and table lookup is when you are retrieving 10% or more of the data in the table and it may perform better even when you are retrieving as little as 1% of the table data. Of course if you only want to retrieve one row in the table, then you would want to use an index.
Index Scan And Table Lookup Vs. Full Table Scan For Very Large Tables in Oracle Databases
Let’s look at the 2 scenarios then – retrieving 10% of the table data by index scan and table lookup vs. full table scan.
To make the maths easy, assume our table has 10,000,000 rows with 10 data rows per block and 100 index entries per block. Therefore to read 10% of the table via an index scan and table lookup, we would have to read 10,000 (1,000,000/100) index blocks plus 100,000 (1,000,000/10) data blocks. That’s 110,000 blocks in total.
However this assumes that the data is stored in order which means that we only retrieve the blocks of data that we want. If the data is not sorted then the worst case is that we would have to read 1 block for each row of data i.e. 1,000,000 blocks, which would would give us a worst case total of 1,010,000 blocks.
For a full table scan the maths is easy: (10,000,000 rows)/(10 rows per block) = 1,000,000 blocks. This is less than the worst case scenario for an indexed read, but more than the best case scenario for an indexed read. This would seem to suggest that if you sort your data before loading, an indexed read would be faster than a full-table scan.
Whilst it is true that pre-sorting the data of very large table will improve performance , it is not necessarily correct that the read via the index will be better overall. We also need to take into account what happens to the blocks stored in the buffer cache of the Oracle SGA and the impact this will have on other users of the database.
Let’s look at the effect on the buffer cache of reading many data blocks via an index. As we know, data and index blocks are stored in the buffer cache by Oracle for reuse by other queries by being marked as least recently used when we do an indexed read. However, those data blocks read by a full table scan are quickly aged out of the buffer cache, because they are not marked as least recently used.
What this means is that the large number of blocks (1,010,000) of index and table data read via the indexed read of our table will be saved in the SGA flushing practically all other data from it – which will obviously have an effect on other users.
Conversely, when a full table scan is performed only the last blocks read are held in the SGA (the actual number is determined by the multi-block read count) so the impact on other users of the database would be minimal.
Summary
To decide whether or not a full-table scan would be better than an indexed read, for a large table you need to consider what proportion of the table the query will retrieve from your Oracle database and consider the likely effect of that on other users. The denser the data, the more efficient a full table scan is for very large tables, but generally if you’re reading more than 1-10% of a very large table, a full table scan would be more efficient than an index scan and table lookup.
For small tables you will get much better performance from your Oracle database by caching the table in its entirety (so that it is always in memory), or by using an index-organised table then you will by relying on indexes. Having said that, every table should have a primary key index to guarantee uniqueness, but you don’t have to use it.
Dec/090
Oracle Performance Tuning Overview
There isn’t the time or space to provide an exhaustive study – whole books have been written about Oracle performance tuning. Instead we’ll just consider a few of the most important tactics and there’ll be suggestions for further reading, so you can expand your knowledge of this important subject.
One point that needs to be made clear is that performance tuning is not an exact science – you can’t always predict that a certain tactic or technique will improve performance. Every change has to be tested, because whilst it may improve performance in one area, it may also degrade performance in another area. Adding an index is a classic example of this – it may improve query performance but the performance of inserts and deletes become slower because the index entries also have to be updated.
The other thing to remember about performance tuning is that you need to decide on your goals before you start – do you need a 10% decrease in response time for queries ? Is adding new records or updating existing records 20% slower than targets ?
You also need to be aware that improving response time is not the only aim that you might have – you might want to reduce memory usage instead – which might mean having to redesign or rewrite some stored procedures. All these issues need to be considered before you start looking at performance.
Let’s start with a list of the topics that we’ll cover:
Use of full-table scans
How and when to use indexes.
How you can optimise joins.
How to use views to get a high performance database.
Why your database should NOT be normalised.
How to use stored procedures to sky-rocket Oracle performance.
How to use sub queries to boost Oracle performance
Dec/090
Brief introduction to UNIX
Who Uses UNIX ?
UNIX is a very efficient, multi-user, multi-tasking operating system and is used particularly for database servers and Internet servers. Traditionally it has been used by large companies and educational establishments, but due to the popularity of Linux, it is now becoming more widely used.
It is scalable from a small system right up to a mainframe-class system (all you need to do is add extra hardware), which makes it suitable for anyone looking for a low cost, reliable operating system.
For programmers it has a wonderful set of built-in utilities, a programmable shell (command/user interface) and a straight forward structure that makes it very easy to quickly produce quite complex programs. The basics of using the shell are taught in our other UNIX tutorials including UNIX Fundamentals and Advanced UNIX Tools.
For end users, it has a friendly graphical interface (called X Windows) and many business applications and games.
UNIX Hardware Requirements
As it is very light on resource requirements it will run on 386 or 486 computers with 2Mb RAM and 40Mb of disk space. The GUI (X-WIndows) requires another 2Mb RAM. There is also a cut-down version which will run on a Psion 3a with 512kb RAM.
UNIX Performance Tuning
There are many kernel parameters which may need to be tweaked to run heavy duty applications such as Oracle (these will be specified in the installation guide), but generally they should be left alone.
To improve performance you need to know how the system is performing currently and which of the three main resources (cpu, memory, i/o) is under the most load.
UNIX provides many performance monitoring and statistics gathering tools which you can run to determine current loads and thus the limiting factor. Generally, though, in descending order of cost effectiveness, the things that you can do are:
* add more memory
* use a faster i/o bus technology (eg. SCSI instead of IDE)
* replace the cpu (the processor chip) with a faster one (eg Pentium III instead of a Pentium II). This may mean that the motherboard has to be replaced as well as all the memory chips.
* add a second processor. Again this may require a new motherboard and new memory chips
* schedule some jobs to run over night, or restrict access to certain applications (eg. games), if they consume a lot of resources un-necessarily.
UNIX Vendors
There are many commercial versions of UNIX from IBM, HP, Sun, Silicon Graphics, SCO, as well as several free versions – Linux, FreeBSD, NetBSD. Some of the commercial vendors are also starting to offer free/low cost versions, hoping to make their money from the sale of applications and/or support.
Advantages of UNIX
UNIX is widely supported, by many companies large and small and many recent graduates will have used it extensively during their courses. This means that there is a large pool of skilled resources able to provide help and support to anyone that needs it.
The other advantages already mentioned are that it is stable, scalable and makes efficient use of resources.
Disadvantages of UNIX
The one drawback with UNIX used to be that there weren’t many desktop or office applications available for it, but this is changing due to the popularity of Linux.
Nov/090
First look at Linux
Who’s it for?
Linux is a very efficient, multi-user, multi-tasking operating system and is used particularly for database servers and Internet servers.
Traditionally it has been used by students and other people who enjoy playing with new technologies, but is now used by web site hosting companies, ISPs., educational establishments, hospitals, etc. Large, conservative (non technology) companies are also starting to evaluate and use Linux.
Many of the big computer companies, including IBM, already offer Linux on their servers as an alternative to their own version of UNIX or other operating systems.
Like other versions of UNIX, Linux is scalable from a small system right up to a mainframe-class system (all you need to do is add extra hardware), which makes it suitable for anyone looking for a low cost, reliable operating system. For programmers it has a wonderful set of utilities and a straight forward structure that makes it easy to produce quite complex programs in a relatively short time and is very much like other versions of UNIX. For end users, it has a friendly graphical interface (called X Windows) and many business applications such as spread sheets, databases, word processors, etc.
Hardware Requirements
As it is very light on resource requirements it will run on old 386 or 486 computers with 2Mb RAM and 40Mb of disk space. The GUI (X-Windows) requires another 2Mb RAM. Therefore a 386-based computer with 4 Mb of RAM is all that is needed to run both Linux and X-Windows. There is also a cut-down version which will run on a Psion 3a with 512kb RAM!As always, the more memory the better, but Linux makes efficient use of resources, so this is not as important as it would be for Windows 95/98/NT/2000.
How To Improve Performance
As with other versions of UNIX, there are kernel parameters which may need to be tweaked to run heavy duty applications such as Oracle (these will be specified in the installation guide), but generally they should be left alone.
The key to improving performance is to know how the system is performing currently and which of the three main resources (cpu, memory, i/o) is under the most load.
Linux provides performance monitoring and statistics gathering tools which you can run to determine current loads and thus the limiting factor. Generally, though, in descending order of cost effectiveness, the things that you can do are:
* add more memory
* use a faster i/o bus technology (eg. SCSI instead of IDE) or use a RAID device.
* replace the cpu (the processor chip) with a faster one (e.g. Pentium III instead of a Pentium II). This may mean that the motherboard has to be replaced as well as all the memory chips.
Other options may be to:
* schedule some jobs to run over night
* restrict access to certain applications (e.g. games), if they consume a lot of resources unnecessarily
* tune and optimise in-house written applications For those who are really keen, Linux comes complete with source code which means you can build your own kernel and have it finely tuned to the type of applications you run. This is not recommended, though, unless you know a lot about operating system kernels and C programming or employ someone who does.
Distributors
Linux is a free version of UNIX, but it may be bundled with applications and support and sold commercially. In strict terms, Linux means the just kernel of the operating system, therefore excluding all applications and utilities provided with it. Commercial organisations that sell Linux are entitled to add whatever utilities and applications they like to go with the kernel. Naturally each company has it’s own set of applications/utilities, so the different companies offerings are called distributions. There are many distributions available but the main ones are SuSE from Germany and Red Hat, Caldera and Corel in the U.S. These companies provide the kernel, the utilities and applications and provide support.
Advantages
Linux is widely supported especially on the Internet, as well as by many smaller companies (such as ourselves) and many recent graduates will have used it extensively during their courses. This means that there is plenty of support and help available for anyone that needs it. The other advantages already mentioned are that it is stable, scalable and makes efficient use of resources. Also, it can be downloaded for free from the Internet, or bought at low cost (which usually includes support) from any of the distributors.
It is compatible with MS Windows, in that it will support full access to Windows file systems and with the help of emulators will run Windows software (some better than others).
There are also plenty of native applications available, including databases, compilers, office applications, networking software, etc. These applications will run unchanged from a single-user system up to an enterprise-wide system.
Disadvantages
Linux doesn’t support quite such a wide range of hardware as Windows 95/98 (for example – the winmodems that come with most pcs). At the moment there is no IRDA (infrared) support, but this should changed in the next release of the kernel sometime later this year.
Conclusion
Linux is a stable, efficient operating system that is widely supported and provides all the tools and applications that any business could want.
However if you have IRDA devices you have a slight problem as Linux doesn’t support these directly. You could keep one PC running MS Windows to access those devices, but that’s an unnecessary expense.
You could have a dual-boot system with Linux and MS Windows, but it can be a pain to keep re-booting your PC.
You could use WINE (a free MS Windows emulator available from www.winehq.com), which should allow you to access these devices. Or you could create an MS Windows vrtual machine that runs under Linux by using something like VMWARE (available from www.vmware.com). This is available for a free 30-day trial and aims to provide an environment for Windows programs that is as close as possible to the environment that would exist if they were actually running under Windows. There is also software available to allow MS Windows to read from and write to Linux disk partitions available from http://uranus.it.swin.edu.au/~jn/linux/.
Nov/090
7 Steps for I.T. Project Success
In 2004 KPMG’s research revealed that I.T. project failure is “rampant” with 56% of firms having classified 1 or more I.T. projects as a failure in 2003.
Other research by Gartner has discovered that a full 40% of I.T. projects are canceled and 28% are over budget and/or late.
Research by Bloor has shown that the failure rate of I.T. projects has not changed since the 1970s which proves that the I.T. industry suffers over and over again from the same problems of
* under investment in training;
* too many manual processes;
* poorly tuned systems;
* programmers and project managers under too much pressure;
* hiring the wrong people
Fortunately research by other organizations has highlighted the solutions to these problems:
1. Train your team – financial and other returns from training can be between 30% and 7000% (NCVER, Australia 2005).
2. Automate your I.T. systems – both production and development. Research by Sun discovered that 80% of system failures are caused by a combination of human errors and process errors. System failures can cost up to £4000/minute (Standish Group, 2001).
3. Tune your existing systems – £23bn is wasted on I.T. including over specified hardware (Gartner).
4. Reduce stress by creating realistic plans. The biggest cause of stress is unrealistic workloads, 12.8 million working days were lost in the U.K. in 2004 due to stress, depression and anxiety (Health & Safety Executive).
5. Hold design reviews – “… the single most effective way of identifying errors and thereby reducing costs” (Professor McDermid, York University, England 2001)
6. Prepare for the coming skills shortage – 45% of organizations predict they will find it harder to attract good IT people, up from 34% three months ago. (Recruitment Confidence Index (RCI) survey May 2005).
7. Hire the best – the best developers are twice as productive as the next best, but worse than that is the fact some developers have negative productivity, anything they do has to be redone by somebody else. (Professor McDermid, York University, England 2001)
By following these seven steps you can ensure your I.T. projects don’t add to the statistics on failures.
Nov/090
Optimizing Mysql Tables
Just like you need to defrag and check your file system, it’s important to do the same thing with MySQL tables. If you don’t, you might end up with slow and corrupted database tables. Follow this easy tutorial to set up an automatic optimization for your db.
Speed in MySQL is all about how fast the database can find the information you request. To help MySQL clean up and get all the best possibilities to gather your information, you can perform the command “Optimize” on your tables.
Want to speed up your MySQL performance in a simple but effective way?
Because you probably will add and delete tables from time to time, you want a solution that works no matter how your database looks like. For this I will give you a PHP script that finds all your tables, and then perform Optimize on every single one. A good idea can be to do this every night (or whenever your server is least accessed) with “cron” because you don’t want to delay your surfers to much.
Here is the script (really simple actually):
Make sure you modify the four variables at the top of the script so they match your configuration.
// Change these four variables to match your configuration.
// You will probably only need to change “user”, “pwd” and “dbName”.
$server = ”localhost”;
$user = ”mysql_user”;
$pwd = ”mysql_password”;
$dbName = ”mysql_dbName”;
$link = mysql_connect($server, $user, $pwd);
if (!$link) {
die(’Could not connect: ‘ . mysql_error());
}
$db_selected = mysql_select_db($dbName, $link);
if (!$db_selected) {
die (’Can\’t use $dbName : ‘ . mysql_error());
}
// Find the names of all tables in the selected db.
$alltables = mysql_query(”SHOW TABLES”);
// Go through all tables.
while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{
// Perform the magic “OPTIMIZE” on every table in the db.
mysql_query(”OPTIMIZE TABLE ‘”.$tablename.”‘”)
or die(mysql_error());
}
}
Oct/090
very basic sql optimization in mysql
It’s important to know how to write your queries fast. And there are also ways to do your queries faster Inside mysql, called Indexes. They help mysql do the work faster, but you need to tell how…
When you start getting large tables you can sometimes end up with slow running queries.
It’s important to optimize your queries and tables from the beginning, so you don’t end up with slow queries one year later when your tables have increased.
Badly created tables and queries is hard to find when you only have limited data in your tables. But there are tools to use to get a hint on if you will have problems later…
First the basics behind the scen in databases. Let’s say you are combining three tables with a join. Then mysql has to look through all three tables and then find a way to combine them in the correct way.
Take this sql query that will find the person “donald duck” and information connected to him in three tables:
SELECT a.id
FROM a, b, c
WHERE a.id = b.a_id
AND b.id = c.b_id
AND c.name = “donald duck”
Let’s say there are 2 rows in each table. What mysql does now is to start looking through all tables from the top down to the bottom and try to find matches.
The important thing to know about this is some math. Either you understand it or you just trust me that it is correct…
To combine all three tables, mysql needs to go through 2*2*2 combinations. This is 8 combinations to find the correct one.
Doesn’t sound that much of a deal, but let’s say we have 500 rows in the tables each. This gives our beloved database 500*500*500 = 1 250 000 different combinations to go through.
Luckily there are some smart people that have found a way to deal with this
It’s called “Indexes” and they help the database to cut down on the number of rows it has to go through to find the match.
The first thing you should ALWAYS do is to make the id in your tables to a PRIMARY key. Then you get that column indexed.
Look at indexes as a way to decrease the number of rows to look through to find the match. You put an index on a seperate column and you should only put it on columns that need it, otherwise your queries will go slower instead.
A basic rule that works very well is that you should put indexes on columns that your “search on” and combine in JOIN’s.
So if we look at the query above, what columns might be interesting to put indexes on?
First of all, make a.id, b.id, c.id PRIMARY keys and they get indexed automaticly.
After that I would set b.a_id and c.b_id as indexes. That is because they are used to combine the tables. If we can decrease the number of rows that needs to be looked through there, the queries will be faster.
The last column that might be interesting is c.name.
A summary:
- Make the id in each table PRIMARY
- Make columns that are “foreign keys” Indexed.
- If you have any other columns that are use in the WHERE clause, make this Indexed aswell.
This is a simplified guide on how to fasten your sql queries, but very good to know the basics.
I
will soon write a tutorial on how to check if your queries are slow or fast, and what to do about it…
Jul/090
What is a Database
Definitions:
A database is a collection of information organized into interrelated tables of data and specifications of data objects.
A table in a relational database is a predefined format of rows and columns that define an entity.
Database tables are composed of individual columns corresponding to the attributes of the object.
In a relational database, a row consists of one set of attributes (or one tuple) corresponding to one instance of the entity that a table schema describes.
Also Known As: Record
A single data item related to a database object. The database schema associates one or more attributes with each database entity.
Also Known As: field, column
A database record consists of one set of tuples for a given relational table. In a relational database, records correspond to rows in each table.
Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables. If you’re familiar with spreadsheets like Microsoft Excel, you’re probably already accustomed to storing data in tabular form. It’s not much of a stretch to make the leap from spreadsheets to databases. Let’s take a look.
Database Tables
Just like Excel tables, database tables consist of columns and rows. Each column contains a different type of attribute and each row corresponds to a single record. For example, imagine that we were building a database table that contained names and telephone numbers. We’d probably set up columns named “FirstName”, “LastName” and “TelephoneNumber.” Then we’d simply start adding rows underneath those columns that contained the data we’re planning to store.
If we were building a table of contact information for our business that has 50 employees, we’d wind up with a table that contains 50 rows.
Databases and Spreadsheets
At this point, you’re probably asking yourself an obvious question – if a database is so much like a spreadsheet, why can’t I just use a spreadsheet? Databases are actually much more powerful than spreadsheets in the way you’re able to manipulate data. Here are just a few of the actions that you can perform on a database that would be difficult if not impossible to perform on a spreadsheet:
* Retrieve all records that match certain criteria
* Update records in bulk
* Cross-reference records in different tables
* Perform complex aggregate calculations
Jul/090
What is Oracle PLSQL
PL/SQL is Oracle’s SQL++ programming language providing structure and flow control extensions to SQL. The name PLSQL is derived from the term “Procedural Language extensions to SQL”.
On its own, SQL enables you to specify what you want done but not how it is done. However, you often need more control over how data is retrieved and manipulated and this is where PL/SQL comes in.
The procedural capabilities combined with standard SQL in Oracle PLSQL gives developers far more control of how their SQL statements interact with the database and makes using PL/SQL an excellent alternative to developing applications in other languages such as Java or C or VB.
The language itself is modeled on Ada, so Java/C/C++ programmers will find the syntax rather strange and probably won’t like the single”=” being used for comparison, but anyone who’s used Pascal or Ada or Modula2 will fell right at home.
PL/SQL is not a pure object-oriented language like Java or Ada, but it does support some obect-oriented features such as classification, polymorphism and, in the later versions, inheritance.