I was told that most of the internet sites use MySQL for pagination. Pagination is a concept of providing web based search results and having an interface to scroll through the result set. Apparently MySQL has a built-in feature to scroll through m through n rows of a result set. So at my place MySQL is being used as a session store. Recently a benchmark was done for pagination and the response times weren’t that impressive from a business perspective. I was told that Oracle Server was taking more time to return the result set. Don’t know why but off late, I’m taking comments made at oracle db in a more personal way – probably I’m going through the ex-employee syndrome. But having looked at the product in a deeper way, I’m sure things can be improved from the Oracle’s side. Would have preferred looking at it as “Hey we’ve tried to extract so much from Oracle, is there anything else that we could try to get more out of this.” Probably I’m living in a box called as Oracle and my vision is restricted within this box. Maybe not. I’m keen on getting my hands on different technologies.
Anyway, after hearing that pagination can’t be implemented in Oracle, I started to develop a mini-howto for it. I’ll publish the mini-howto later on this site.
After developing my PL/SQL APIs for it, I started to do a small time benchmark on my test RAC db. Trying out the runs with 1, 10, 20, 40, 100, 150, 200 sessions for each run. During each run, the number sessions represented the concurrent searches performed against a table containing 100 million rows in it. The test runs for 1 & 10 sessions performed as expected; response times in milli seconds. During the 20 sessions run, one session took a long time to finish. Obviously it had a very high elapsed time. Surprised at this, did another run of 20 sessions; this time 2 sessions were experiencing the same thing. These two sessions were performing a full table scan of the 100 million rows table. During build time, they were using the ‘index range scan’ and over here, doing a ‘full table scan’ which resulted in high elapsed times. Couple of more hours into this, found out that ‘statistics’ were missing for one of the tables which captured the result sets. After collecting the statistics for it, the elapsed times were lightning.
Each of the session had turned on sql tracing. So I had about 520 sql trace files. I had to extract the cpu time and elapsed time used for parse, execute & fetch phases of the work. tkprof has a useful feature of generating a sql file which contains the insert statements to load the data to a table. If all of these data gets loaded into the table, then I just have to write simple sql to get my values needed to plot a graph in excel (will move over to htmldb soon). There is a irritating problem with the sql file generated by tkprof. Most of the INSERT statements in the file were spanning multiple lines. The last line contained ‘); characters to signify the end of the statement. When this sql script is run as-is through SQL*Plus, some of the statements used to fail. Its really tedious to manually edit 520 odd files to correct that.
For the first time, I’ve managed to get a perl ‘scrubber’ script to join these multiple lines into one single line of insert statement. With little help from bash shell’s for statement, managed to correct the script. Now I can just run the script and get my graphs done J
Here is my crude perl script:
#!/usr/bin/perl
$fname = (@ARGV[0]);
open(orig_file,$fname) or die “Can’t open file: $! \n”;
while( $line = ){
chomp($line);
if ($line =~ /^INSERT./) {
$longLine = $line;
}elsif ($line =~ /^\’\)\;/) {
$longLine = $longLine.” “.$line;
print “$longLine\n”;
}else {
$longLine = $longLine.” “.$line;
}
}
close(orig_file);
I need to modify it and include some comments, so that my nimble brain won’t be wondering what each line does.
