Re: a heated discussion between Development Team & "a" DBA: reorg or not to reog

I believe you said the following:

# I have a script that turns on sql trace and emails me the TOP-N SQL
# (sorted by exec time, parse time and fetch time) of most CPU
intensive
# process on unix for given period. I ran the script several times
during
# night and each run shows that a DELETE is run thousands of times
# against a 80M table and it does tablescan. I recommended the index,
# created it in the test server and it cut down the time to half. But
# this particular manager of development team still wants to do
# export/drop db/import before trying the indexes.

I think you may be taking a sub-optimal approach here. So creating an index takes the time down 50 percent - ok.

But the DELETE apparently runs thousands of times ... doing thousands of full table scans?

If I understand you correctly, my approach would be to try to reduce the number of DELETE calls. Can you do it just once or at least a lot less.

How about creating some kind of inline view that SELECTs all the rows that would be deleted and having the DELETE run against that in one call.

If possible I recommend ignoring all the issues with the developer's manager and help them with the business logic and making the SQL and the processing flow better.

You seem to have a good grasp of the oracle related possibilities but this seems like an application problem ... so you have to fix the application. Received on Fri Jun 24 2005 - 06:52:04 CDT