sloooowww writing of a file after a sql command

[Todd McDaniel_1]

My DBAs have seen a very slow write in a situation on my box and I wanted to run it by you all.

They ran this same thing on a N-class and got the same result. I dont think with that taken into account it is a UNIX problem, but an Oracle one with writing out the file.


They executed a sql query to count the rows of a DB which about 6 minutes, but when they actually wrote out the file, it only wrote at about 30214 bytes/sec, or 1.8 M/min, about 19 hours to complete the file.

They also ran the prealloc command on the same filesystem to create a file and it went at 100times the rate of the file creation via sql command to write out the new file.


The filesystem is not striped, is concatenated disks. It is a 70GB FS with 51% free. The host is a Keystone 16-way 24GB memory.

I also have the buffer redirected to the Frame...but that didnt make a difference.

delaylog,nodatainlog,largefiles,mincache=direct,convosync=direct

Sorry I dont know exactly what they did, maybe you can help me decipher it.

[Todd McDaniel_1]

Here is a better explanation from my DBA... maybe you can help us solve this...

QUOTE:

At present, after spending a lot of time with Jim Zeng and Jack Shuler, we suspect the root cause of this specific problem to be a SQL coding-design issue. There is a function call that is being executed >5M times, and each execution involves a separate fetch. Each exec & each fetch takes .005 seconds. The sum is >13 hours.

This is the direction we are heading at this time.

[A. Clay Stephenson]

For non-database files (like your output) the mount options mincache=direct,convosync=direct are terrible choices because the buffer cache , which would definitely help in sequential writes like this, is completely bypassed.

Even for database files, especially if this is 11.11, I've found better performance using "cooked" files for Oracle rather than your "pseudo-raw" mount options assuming you have plenty of memory.

[Steven E. Protter]

I would tend not to blame the OS, but why not be sure. Run some performance data on the system during the write, look for bottlenecks. Glance or the script I'm attaching.

Then think about defragmenting the database(oracle function) or the filesystem(OS Online JFS).

SEP

[A. Clay Stephenson]

Oh, I should add that long before I suspect the hardware and OS tuning, I suspect the SQL code. Them DBA boys and girls have a nasty habit of doing things like "zigging" when they do 1 test query and "zagging" when they do another supposedly "identical" query. If your select count query and the actual query are truly comparable (and you've done the SQL explain query's) then I might begin to look elsewhere.

[Todd McDaniel_1]

Clay,

They saw this problem before I did that change. i will change it back to what it was originally. :D

[Hein van den Heuvel]

It's a SQL problem. Forget about the disk/filesystem/system speed. You and I know that the crappiest, most fragmented, of disks still write at more than 1mb/sec.

Todd, you failed to indicate the number of records counted! Was it approx that 5 million the dba indicated? Well certaintly if you execute a query per row, then that will take a while. 19 (hours) times 3600 second divided by 5 million (rows) leaves you about 13 milliseconds per execution. (hmmm.... about the time 1 IO might take).
If in that time you also have to go through SQLnet from a (SQLplus?) client to the (oracle) slave and back, then well that sounds about right. Is each row results 'appended' to the output?
How do they loop over the data?
Can it be turned into a single looping query?

Good luck,
Hein.

[Volker Borowski]

Hi Todd,

please let us have the actual SQL-code.
The COUNT function just scans the primary key, or any other unique index, whichever is the smallest in size. This is in general much smaller than the entire table.

If the SQL does some SORTs or GROUPs or aggregates, it might be, that the entire process does a lot more on the read-side than on the write-side.

If you have isolated the slow SQL-statement, please attach it. Please attach a "describe " of each table involved as well, and indicate on which columns you already have indexes.

That gives us a better chance to hunt this one.
Volker

[Todd McDaniel_1]

I dont have access to the sql code nor do I know what the query was actually doing.

I was just curious what could cause their dilemma and hoped to pick your brains here to see if I could offer them any assistance.

Thanks for your responses!

[Alzhy]

If your DBA has access to SQL checker tools, then it is a fool-proof way to ascertain if situation like this is a system/non-Oracle problem. PRECISE (now a Veritas Product) is what our DBA's are using and it has been a God-send... It totally eliminated most of the "wild-goose-chases" that we use to have before as it actually delves into a problematic transaction adn actually analyses SQL as it interacts with each instance's components.