Pages taking forever to load

[BigDaddy]

Just started a couple of days ago...of course it's the web pages' fault--"they must be inefficient"...

Sorry if I sound bitter or sarcastic...

The issue is that the queries are too slow.

My boss was just sure that I was doing something wrong, and proceeded to execute a few queries in SQLPlus, instead of the web page, until a query took wayyyy longer to run than it should. :p Apparently now he realizes the web developer was right and there's nothing wrong with the page.

I've used index hints to point the query at the right index, and I've re-written all the queries to optimize them, but they're still slow....

Anyway, any Oracle guys out there (hint: Hey OracleGuy) that know if an Oracle Index can "valid" when looked at in Oracle, yet still be messed up to the point where you can't use it effectively?

Can it get fragmented? Would that slow down the query?

[Roy Sinclair]

Have you considered network bandwidth issues too? Maybe the queries and database server are all fine but your pipe between the database server and the web server is overloaded.

Performance issues can be very complex, make sure you know the source of the problem before you run off spending time on fixes that aren't the true bottleneck.

[fractalvibes]

Post the SQL causing the problem - perhaps someone will be able to point out something wrong/ineffecient.

fv

[oracleguy]

Originally posted by BigDaddy
(hint: Hey OracleGuy)

:o Contrary to what people first think of, I don't know Oracle database software. My name comes from the prophet meaning of the word. :)

You might also want to have a trip over to http://www.dbforums.com/ and see if someone there can help you. I don't think there are a whole lot of people on this forum that know Oracle.

[glenngv]

Have you checked if you close and kill recordset and connection objects in your codes?

[raf]

Post the query.

No point in freewheeling about other possible reasons without seeing the query.
Maybe your db-admin can lookup the acces-path that is used when the query is executed, but my first bet would be an inefficient join (like between tables with an n-m relation) or subquery

[BigDaddy]

Nah...we've actually ran the query in Oracle, and verified that it is joining efficiently, with my DBA helping me. At first it wasn't finding the correct indexes, but we used Index hints to point it at the right index, and it still was slow as molasses...a little faster, but still slow.

[fractalvibes]

Originally posted by raf
Post the query.

No point in freewheeling about other possible reasons without seeing the query.
Maybe your db-admin can lookup the acces-path that is used when the query is executed, but my first bet would be an inefficient join (like between tables with an n-m relation) or subquery

Yes, absolutely - let's see the SQL. Otherwise we will just be guessing at the 10,000 things which could slow the page down...

fv

[raf]

Originally posted by BigDaddy
Nah...we've actually ran the query in Oracle, and verified that it is joining efficiently, with my DBA helping me. At first it wasn't finding the correct indexes, but we used Index hints to point it at the right index, and it still was slow as molasses...a little faster, but still slow.
Well, if Oracle says its joining efficient :rolleyes:

I don't see how we can help you, based on the info you supplied.

[Roy Sinclair]

Even an "efficient" join can still perform like a pig. Sometimes you can improve efficiency quite a lot by reducing the size of the joins by preselecting some data into a temporary table and then using it for the join.

[BigDaddy]

I honestly doubt that it's the query. This problem started Monday, with our intranet site and internet site both experiencing problems on querying the same table.

The pages have not been changed prior to this in at least a month--so if it was working 2 weeks ago, it should work now--in theory.

They did just add about 300,000 records to the database on the one table we join to, making it about 1.5 million recs, but Oracle still should be able to handle that efficiently, I would think. I read an article today about a table that had 4.2 BILLION records in it.

Anyway, after adding some hints to the queries to point them at the right indexes (for some reason Oracle wasn't using the right index), we have noticed a marked improvement, but it still is slow. We've tried re-building the indexes, but it doesn't improve it a lot.

I'm beginning to think that my queries were probably a bit inefficient, and the extra records were just the straw that broke the camel's back.

I don't know....I was hoping maybe someone would have a little insight, or could possibly point me in the right direction. I would post the queries, but with the amount of subqueries and oracle hints included in it, I don't know that anyone would understand them w/out knowing the database.

Thanks to anyone who posted a comment or suggestion!

[oracleguy]

Originally posted by BigDaddy
They did just add about 300,000 records to the database on the one table we join to, making it about 1.5 million recs, but Oracle still should be able to handle that efficiently, I would think. I read an article today about a table that had 4.2 BILLION records in it.

Yeah but they probably had one hell of a server cluster setup to handle it. If it just started out of the blue and it was working before, it could be network lag, your database server is reaching it's throughput capacity on processing power or network bandwidth. Just things to consider.

[BigDaddy]

It's wierd....it seems like, for some reason, Oracle just isn't finding the indexes that are set up for it. I'm not the DBA, so it's not my job necessarily to tune the thing, but I shouldn't have to tell Oracle in my query what index to use, assuming it's tuned right.

Anyway...just venting. Thanks for listening :)

[fractalvibes]

Maybe the table needs to be reorged...sounds like your DBAs need to do a little tuning, at any rate.