RE: Very Slow Process problem.
carlson1@bellsouth.net wrote:
> Abhay Mannur wrote:
> >
> > Environment : OnLine 7.23uc4 and PeopleSoft
> >
> > One query is running for 18 hours and we don't want to kill the =
process.
> >
> > Query goes like this :
> >
> > insert into c
> > select distinct col1,col2.....coln
> > from a,b
> > where b.col1=3Dsome value
> > and a.col1=3D some value
> > and a.col2=3Db.col2
> > and a.col3=3Db.col3 ....> >
> > Table a has 700000 rows and b has 8000 rows.
> >
> > I checked no. of rows in c and there is nothing.
> > Does it mean that the select is still running ?
> >
>
> What indices do the tables have? Please post a schema of the tables.
>
> John Carlson
> Informix DBA
> WH Smith, Inc.
>
Because the task is being done as a single transaction, the rows may not =
appear in 'c' until they are committed. I suspect the reason you can't =
see them is because you are using the default isolation level of =
COMMITTED READ.
Try firing up dbaccess and running the following:
SET ISOLATION TO DIRTY READ;INFO STATUS FOR c;
This doesn't answer the question why it has taken so long, but may show =
you the speed it is running at (and prove that it is actually doing =
something ;-)
Also, run onstat -m and confirm that the database is not 'hung' because =
the logical log files are full or something equally as handy.
Hope that helps,
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+