OraFAQ Forum: Server Administration » query to slow after lots of insert and delete operations

Home » RDBMS Server » Server Administration » query to slow after lots of insert and delete operations

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query to slow after lots of insert and delete operations [message #63174]

Thu, 16 September 2004 01:11

Henry
Messages: 14
Registered: November 2000

Junior Member

I am using oracle9i in window2000 server for my work.

I have several tables(has primary key and foreign key for each one), and there has a lot of operations(over 500,000) such as insert/delete on each one. After the program runs about�12-18 hours, query on those table becomes very slow, the buffer_gets and disk_reads beomes very big too.

please help :)

Report message to a moderator

Re: query to slow after lots of insert and delete operations [message #63175 is a reply to message #63174]

Thu, 16 September 2004 01:41

easywebtech
Messages: 20
Registered: August 2004

Junior Member

I think that you need to rebuild table indexes.
here is the script.

to get index script run this.
select 'alter index '||owner||'.'||index_name||' rebuild ;'
from dba_indexes
where owner=[[your schema]]

result rows of this run one by one.

webmaster
www.easywebtech.com

Report message to a moderator

Re: query to slow after lots of insert and delete operations [message #63178 is a reply to message #63174]

Thu, 16 September 2004 03:26

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

rebuilding the index has nothing to do with performance.
You need to analyze the schema/tables ( gather statistics).
These statistics are used by the CBO.

Report message to a moderator

Re: query to slow after lots of insert and delete operations [message #63182 is a reply to message #63178]

Thu, 16 September 2004 04:08

Henry
Messages: 14
Registered: November 2000

Junior Member

Do you have any idea of analyzing the schema/tables?
What reasons cause the slow query do you think?

Report message to a moderator

Re: query to slow after lots of insert and delete operations [message #63185 is a reply to message #63182]

Thu, 16 September 2004 05:36

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>What reasons cause the slow query do you think?
There might be a ton of them.
Without proper analysis (like running a statspack) we cannot guess.

To start with,
In most cases, the query is slow becuase of its index.

1. First is there an index?
2. Is the query utilizing that/those index(s)?
3. Are the tables/indexes analyzed?
during select/updates , CBO ( cost based optimizer) will try to
select a path that is the most shortest(best?) to fetch the data.
CBO can comeup with ONE such execution plan only if the statistics
are correct statistics available to it.

4. Rebuilding the indexes are NOT going update the statisitcs. Especially if the table/index is large it is going to be very painful.
5. Analyzing will update the stats.
6. How to analyze?
many pros and cons are involved.
REFER DOCS

Report message to a moderator

Re: query to slow after lots of insert and delete operations [message #63237 is a reply to message #63174]

Mon, 20 September 2004 23:18

dbalaji
Messages: 2
Registered: August 2004

Junior Member

Hi Henry ,
if you are working on unix based oracle then

1. create a subdirectory as testing and run the utlbstat.sql that is available in the $oracle_home/rdbms/admin directory

2.then run the utlestat.sql from the same subdirectory.This is available in the same directory as utlbstat.sql

3.this will create a file called report.txt that is self explaining and will releive you of this headache.

regards
balaji

Report message to a moderator

Previous Topic:	automatic ftp script for unix
Next Topic:	ORA-12505 error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 24 13:33:54 CST 2025