Poor Performance

[BattleDroid42]

I've been in the progress of migrating my Access backend databases to Oracle 9i through ODBC. I'm now starting to regret it and could use some good advice for resolving poor performance problems. Essentially tables containing a large number of records, say 1-2 million recs perform so poorly that I have had to go back to the Access backend tables. I have tried different drivers, indexing, partitioning and using Oracle Expert all without a sand grain's worth of sucess. It's also been a while since my performance tuning training. Any help would be much appreciated.

 

[Steppingrazor76]

Any thoughts/ideas about my post? Thanks in advance.

 

[sem]

I'd suggest you to start a new thread, because, as you stated, your case differs.

Regards, Dima

 

[BattleDroid42]

Okay, now we're cooking with gas. I'm going to up the SGA big time. I knew it had to be something global considering that even a simple delete in SQL*Plus performs so slowly. Thanks sybaseguru! I'll let you all know how it turns out.

BattleDroid42

 

[sybaseguru]

No sweat BattleDroid42. Make sure that you have enough db buffer cache as well. Anyway are you using win32 or Linux as interest?
Good luck

 

[BattleDroid42]

Sorry - Windows 2000 Server. Still testing

 

[BattleDroid42]

Still experiencing some slow performance. Here's my new SGA.

SQL> show sga

Total System Global Area 1611343872 bytes
Fixed Size 456704 bytes
Variable Size 662700032 bytes
Database Buffers 947912704 bytes
Redo Buffers 274432 bytes

 

[sem]

I don't think you need to expand your SGA further. Here's SGA of PRODUCTION MIXED (not pure OLTP) system with 100+ simultaneously working users making WELL-TUNED queries/updates on tables with up to 100 000 000 records and normally obtaining results in less then 1 second.

Total System Global Area 969141952 bytes
Fixed Size 69312 bytes
Variable Size 653434880 bytes
Database Buffers 314572800 bytes
Redo Buffers 1064960 bytes

Below is SGA of my testing system with 10 users

Total System Global Area 185368736 bytes
Fixed Size 73888 bytes
Variable Size 106307584 bytes
Database Buffers 78643200 bytes
Redo Buffers 344064 bytes

Simple query joining 2 tables (using indexes appropriately) with 300 000 and 3 000 000 records respectively, still runs in less than 1 second. Though the same query with manually suppressed indexes needs 15 seconds.

Did you gather schema statistics (DBMS_STATS package) after loading data?
Can you choose a VERY SLOW query and provide more information on it: tables involved, their structure, indexes, approximate number of rows, execution plan, used in your environment?
Can you launch the same query from sql*plus and compare speeds? The main problem with ODBC is in slow IO, so if your queries return a lot of records, this may be an issue. As for updates , with pass-through processing the difference is minor.

Regards, Dima

 

[BattleDroid42]

Yes, I have run statistics. Most tables contain around only half a million records. The largest table in the database contains around 4 million records. I now have some simple queries that run very fast through ODBC. One of them even runs against the table containing 4 million records. Now, some SQL tuning may be in order.

Some background…

The database is 99.9% data warehouse with minimal user access (10 web users updating a single cross reference table with less than 3,000 rows, probably only 1-2 users on at any given time), minimal deletes, with a small number of rows appended daily through ODBC (all INSERT code runs reasonably well). All tables have reasonable primary keys. Admittedly, I’m still playing with the indexes. The application handles some data loading and automated reporting. One of the major reasons for migrating to Oracle is to allow web-based querying of the DB through a middle tier web server.

Also, I’m using OEM and like it, even with its little quirks. I’m a Windows GUI guy. I did not use the Oracle Workbench for migration.

BTW…date fields are in heavy use and ALL queries use the BETWEEN…AND syntax without the Oracle TO_DATE function. I can’t use this function in Access as far as I know.

Running queries in SQL*Plus won't necessarily work because it’s application-specific and would have to be translated from Jet SQL to Oracle SQL which would change the results of testing the SQL in SQL*Plus anyway. I don’t think ODBC is the problem any more. Some of the queries are also quite complex and even DBAs that don't eat quiche would quake at entering that much SQL manually.

I’ll post some general structural information as soon as I collect it.

I’m attempting to use SQL*Analyze but welcome suggestions on better tools. I’m not impressed with SQL*Analyze. However, according to it I’m getting excellent buffer response.

 

[MagicDragon]

Just for kicks ..

set _b_tree_bitmap_plans = FALSE

and lesse what happens