what does info from onstat -g ses xxx indicate here

From what i know so far, this thread looks like its waiting on user input. But just seems to be a simple query that is taking ages. Apparently the developer done "set isolation dirty read". Any ideas ? session #RSAM total used id user tty pid hostname threads memory memory 925 gregor pts013 2571 venus 1 73728 7184 tid name rstcb flags curstk status 985 sqlexec ac27c4c Y--P--- 572 ac27c4c cond wait(netnorm) Current statement name : slctcur Current SQL statement : select mast_pol_num from pal_obmast where (mast_allocated="N" ormast_allocated="Y") and mast_tab_num="329"

Run that query with SET EXPLAIN ON, I suspect it is doing a table scan due to either insufficient stats causing a selection of the wrong index, the optimizer electing to not use an index, or a missing index. If you need more help post version info, a schema of the table, and sqexplain output. Art S. Kagel Tam McLaughlin wrote: > > From what i know so far, this thread looks like its waiting on user input. > But just seems to be a simple query that is taking ages. Apparently the > developer done "set isolation dirty read". > Any ideas ? > > session #RSAM > total used > id user tty pid hostname threads memory > memory > 925 gregor pts013 2571 venus 1 73728 > 7184 > > tid name rstcb flags curstk status > 985 sqlexec ac27c4c Y--P--- 572 ac27c4c cond wait(netnorm) > > Current statement name : slctcur > Current SQL statement : > select mast_pol_num from pal_obmast where (mast_allocated="N" or> mast_allocated="Y") and mast_tab_num="329"

Bit of inside knowledge here............may be wrong But I think........ The indexes are causing the problem. > Current statement name : slctcur > Current SQL statement : > select mast_pol_num from pal_obmast where (mast_allocated="N" or> mast_allocated="Y") and mast_tab_num="329" mast_tab_num is indeed indexed but its part of a composite key i.e ( an_other, and_an_other, mast_tab_num ) are the fields listed in the index. Now for the above query neither of the 1st two fields are involved , so in my experience I think that the index will be being scanned sequentially and therefore its useless. I suggested to Tam that creating an index on mast_tab_num would improve things. Also there is an index on mast_allocated which is just Y or N, a bit pointless - do people agree ?

> The indexes are causing the problem. > > > Current statement name : slctcur > > Current SQL statement : > > select mast_pol_num from pal_obmast where (mast_allocated="N" or> > mast_allocated="Y") and mast_tab_num="329" > > mast_tab_num is indeed indexed but its part of a composite key > > i.e ( an_other, and_an_other, mast_tab_num ) are the fields listed in the > index. > > Now for the above query neither of the 1st two fields are involved , so in > my experience I > think that the index will be being scanned sequentially and therefore its > useless. > I wouldn't say useless. Even if it is being sequentially scanned, it's better than sequentially scanning the table, where each row would be significantly larger than the rows in the index. > I suggested to Tam that creating an index on mast_tab_num would improve > things. Most likely. > Also there is an index on mast_allocated which is just Y or N, a bit > pointless - do people agree ? Most definitely. Assuming that mast_allocated only contains Y or N. On a boolean value like this, the statistics would be so far out of whack that the optimizer shouldn't use it anyway. You might want to check onstat -g mgm and make sure the user isn't waiting in some gate. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <864nt2$a5l$1@nnrp1.deja.com>, mars1972@my-deja.com wrote: > > > The indexes are causing the problem. > > > > > Current statement name : slctcur > > > Current SQL statement : > > > select mast_pol_num from pal_obmast where (mast_allocated="N" or> > > mast_allocated="Y") and mast_tab_num="329" > > > > mast_tab_num is indeed indexed but its part of a composite key > > > > i.e ( an_other, and_an_other, mast_tab_num ) are the fields listed in > the > > index. > > > > Now for the above query neither of the 1st two fields are involved , > so in > > my experience I > > think that the index will be being scanned sequentially and therefore > its > > useless. > > > > I wouldn't say useless. Even if it is being sequentially scanned, it's > better than sequentially scanning the table, where each row would be > significantly larger than the rows in the index. Forgive my ignorance, but is it not true that if you're filtering on a column that is not the head of an index, even if that column is part of an index, the index isn't used? If that's the case, and the first two fields are NEVER used as filters, the index is a little worse than useless... It slows down updates, inserts and deletes, and is never used for queries... > > > I suggested to Tam that creating an index on mast_tab_num would > improve > > things. > > Most likely. > > > Also there is an index on mast_allocated which is just Y or N, a bit > > pointless - do people agree ? > > Most definitely. Assuming that mast_allocated only contains Y or N. On > a boolean value like this, the statistics would be so far out of whack > that the optimizer shouldn't use it anyway. The only case that I can think of where this would be useful is if the distribution of values is VERY skewed, and the queries are always on the side of the value that contains few values. For example, if almost all of the values are "Y", but the query is looking only for rows where the value is "N", the index should help query performance... Provided of course that the statistics are up to date... > > You might want to check onstat -g mgm and make sure the user isn't > waiting in some gate. > > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy. > I hope I've not confused the issue... -- Dan Michaelis Database Administrator dan@kax.com Sent via Deja.com http://www.deja.com/ Before you buy.