ccwei_at_ms1.pchome.com.tw (Alvin) wrote in message news:<f344d6c4.0302221949.27a051e2_at_posting.google.com>...
> I've a stored procedure which runs every hour to insert about 1000
> rows to Table A and then a query will be executed against this
> table and insert into Table B . It costs about 2 mins to run
> normally but it will always run much slower to take about 24 hours
> to complete every 7 days. What I can do now is copy data in Table A
> to a temp Table, recreate Tabla A from the temp Table, the procedure
> will then run normally.
>
This doesn't make a lot of sense. Haven't you monitored your insert/query
while they are running? The easiest way is to query v$session_wait. It
tells you what your process waits on when it's running slow.
> I've checked that although the query is a little bit complex but
> it hits the Index and the consitent/physical gets are almost
> the same with the two (normal/slower) situations.
>
> What else should I solve for this dilemma?
>
> It really bothers me for a long long time?
>
> PS: There're about 500000 rows in Table A and B.
Received on Sun Feb 23 2003 - 17:16:16 CST
