slow query 
Author Message
 slow query

Hi All gurus,

This posting is in fact related to my previous post : "help me to run a query faster than oracle with informix.".

As a summary , on a test system i have a query which takes 2min. with oracle and 15min.with informix.I am responsible for running this query as fast as oracle.

After some work, i have found something strange. (Or it is strange for me.)

If i have a query which does a full sequental scan and finds no row (no result set) , iostat shows i have a 14mb. read / per second.
But if i have a result set from the same query , iostat shows i have poor 600kb. read per second.

(one point , oracle also does sequental scan . )

These are my query plans:

Fast one:
QUERY:        (FIRST_ROWS OPTIMIZATION)
select *
from hplandet
where borc1<0

Estimated Cost: 1192782
Estimated # of Rows Returned: 1

  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
fragments: ALL)

        Filters: informix.hplandet.borc1 < 0.00

Bu da digeri:

Slow one:

QUERY:        (FIRST_ROWS OPTIMIZATION)

------
select *
from hplandet
where borc1>=0

Estimated Cost: 1192782
Estimated # of Rows Returned: 3235377

  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
fragments: ALL)

        Filters: informix.hplandet.borc1 >= 0.00

And iostat values:

----------------------
Both sql statements do sequental scan.
(hplandet has 3,000,000 rows, fragmented 4 dbspaces by
round robin)
These sql statements run without pdq.

select *
from hplandet
where borc1<0    // there is no record like this  Fast
one

select *
from hplandet
where borc1>=0   // all the table    Deadly slow!!

First sqls iostat output:

device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 24  8  0 68
sd307      474.1    0.0 15022.1    0.0  0.0  2.4  
5.0   0  39
sd889      475.5    0.0 15045.0    0.0  0.0  1.7  
3.7   0  39
                  extended device statistics          
             cpu
device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 26  8  0 67
sd307      484.1    0.3 15371.3    0.7  0.0  3.4  
7.0   0  50
sd889      483.1    0.0 15368.7    0.0  0.0  2.4  
5.0   0  47

second sqls iostat output:

device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 29  2  0 69
sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
  0   1
sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
  0   1

This is a great difference!

There is only 1 difference. The first sql statement
finds no row so no
data is returned. But the second sql statement finds
data.
I have made other tests.If the query has something for
the result set , the performance is poor. (2. iostat
output)

Am i doing something wrong? Do i need to set a
configuration problem ?
Or is this a known problem?
----------------------------------
Table Name          hplandet
Owner               informix
Row Size            649
Number of Rows      3235377
Number of Columns   86
Date Created        08/11/2001

MULTIPROCESSOR  1               # 0 for
single-processor, 1 for multi-processor
NUMCPUVPS       4               # Number of user (cpu)
vps
SINGLE_CPU_VP   0               # If non-zero, limit
number of cpu vps to one

NOAGE           0               # Process aging
AFF_SPROC       0               # Affinity start
processor
AFF_NPROCS      0               # Affinity number of
processors

# Shared Memory Parameters

LOCKS           200000            # Maximum number of
locks
BUFFERS         200000            # Maximum number of
shared buffers
NUMAIOVPS       8               # Number of IO vps
PHYSBUFF        512              # Physical log buffer
size (Kbytes)
LOGBUFF         512              # Logical log buffer
size (Kbytes)
LOGSMAX         60              # Maximum number of
logical log files
CLEANERS        8               # Number of buffer
cleaner processes
SHMBASE         0x10a000000       # Shared memory base
address
SHMVIRTSIZE     128000            # initial virtual
shared memory segment size
SHMADD          64000            # Size of new shared
memory segments (Kbytes)
SHMTOTAL        0               # Total shared memory
(Kbytes). 0=>unlimited
CKPTINTVL       300             # Check point interval
(in sec)
LRUS            8               # Number of LRU queues
LRU_MAX_DIRTY   95              # LRU percent dirty
begin cleaning limit
LRU_MIN_DIRTY   90              # LRU percent dirty
end cleaning limit
LTXHWM          50              # Long transaction
high water mark percentage
LTXEHWM         60              # Long transaction
high water mark (exclusive)
TXTIMEOUT       0x12c             # Transaction
timeout (in sec)
STACKSIZE       32              # Stack size (Kbytes)

RA_PAGES      512                 # Number of pages to
attempt to read ahead
RA_THRESHOLD  500                # Number of pages
left before next group

I have also searched the newsgroup for similiar problems, and found people who had this problem. But i did not see any posting about a solution.

Kind Regards,
hope

==================================
Poster's IP address: 213.74.48.238
Posted via http://www.***.com/
Linux Programmer's Site



Sat, 12 Jun 2004 00:29:26 GMT
 slow query

Hi All gurus,

This posting is in fact related to my previous post : "help me to run a query faster than oracle with informix.".

As a summary , on a test system i have a query which takes 2min. with oracle and 15min.with informix.I am responsible for running this query as fast as oracle.

After some work, i have found something strange. (Or it is strange for me.)

If i have a query which does a full sequental scan and finds no row (no result set) , iostat shows i have a 14mb. read / per second.
But if i have a result set from the same query , iostat shows i have poor 600kb. read per second.

(one point , oracle also does sequental scan . )

These are my query plans:

Fast one:
QUERY:        (FIRST_ROWS OPTIMIZATION)
select *
from hplandet
where borc1<0

Estimated Cost: 1192782
Estimated # of Rows Returned: 1

  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
fragments: ALL)

        Filters: informix.hplandet.borc1 < 0.00

Bu da digeri:

Slow one:

QUERY:        (FIRST_ROWS OPTIMIZATION)

------
select *
from hplandet
where borc1>=0

Estimated Cost: 1192782
Estimated # of Rows Returned: 3235377

  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
fragments: ALL)

        Filters: informix.hplandet.borc1 >= 0.00

And iostat values:

----------------------
Both sql statements do sequental scan.
(hplandet has 3,000,000 rows, fragmented 4 dbspaces by
round robin)
These sql statements run without pdq.

select *
from hplandet
where borc1<0    // there is no record like this  Fast
one

select *
from hplandet
where borc1>=0   // all the table    Deadly slow!!

First sqls iostat output:

device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 24  8  0 68
sd307      474.1    0.0 15022.1    0.0  0.0  2.4  
5.0   0  39
sd889      475.5    0.0 15045.0    0.0  0.0  1.7  
3.7   0  39
                  extended device statistics          
             cpu
device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 26  8  0 67
sd307      484.1    0.3 15371.3    0.7  0.0  3.4  
7.0   0  50
sd889      483.1    0.0 15368.7    0.0  0.0  2.4  
5.0   0  47

second sqls iostat output:

device       r/s    w/s   kr/s   kw/s wait actv  svc_t
 %w  %b  us sy wt id
 29  2  0 69
sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
  0   1
sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
  0   1

This is a great difference!

There is only 1 difference. The first sql statement
finds no row so no
data is returned. But the second sql statement finds
data.
I have made other tests.If the query has something for
the result set , the performance is poor. (2. iostat
output)

Am i doing something wrong? Do i need to set a
configuration problem ?
Or is this a known problem?
----------------------------------
Table Name          hplandet
Owner               informix
Row Size            649
Number of Rows      3235377
Number of Columns   86
Date Created        08/11/2001

MULTIPROCESSOR  1               # 0 for
single-processor, 1 for multi-processor
NUMCPUVPS       4               # Number of user (cpu)
vps
SINGLE_CPU_VP   0               # If non-zero, limit
number of cpu vps to one

NOAGE           0               # Process aging
AFF_SPROC       0               # Affinity start
processor
AFF_NPROCS      0               # Affinity number of
processors

# Shared Memory Parameters

LOCKS           200000            # Maximum number of
locks
BUFFERS         200000            # Maximum number of
shared buffers
NUMAIOVPS       8               # Number of IO vps
PHYSBUFF        512              # Physical log buffer
size (Kbytes)
LOGBUFF         512              # Logical log buffer
size (Kbytes)
LOGSMAX         60              # Maximum number of
logical log files
CLEANERS        8               # Number of buffer
cleaner processes
SHMBASE         0x10a000000       # Shared memory base
address
SHMVIRTSIZE     128000            # initial virtual
shared memory segment size
SHMADD          64000            # Size of new shared
memory segments (Kbytes)
SHMTOTAL        0               # Total shared memory
(Kbytes). 0=>unlimited
CKPTINTVL       300             # Check point interval
(in sec)
LRUS            8               # Number of LRU queues
LRU_MAX_DIRTY   95              # LRU percent dirty
begin cleaning limit
LRU_MIN_DIRTY   90              # LRU percent dirty
end cleaning limit
LTXHWM          50              # Long transaction
high water mark percentage
LTXEHWM         60              # Long transaction
high water mark (exclusive)
TXTIMEOUT       0x12c             # Transaction
timeout (in sec)
STACKSIZE       32              # Stack size (Kbytes)

RA_PAGES      512                 # Number of pages to
attempt to read ahead
RA_THRESHOLD  500                # Number of pages
left before next group

I have also searched the newsgroup for similiar problems, and found people who had this problem. But i did not see any posting about a solution.

Kind Regards,
hope

==================================
Poster's IP address: 213.186.155.242
Posted via http://nodevice.com
Linux Programmer's Site



Sat, 12 Jun 2004 00:29:27 GMT
 slow query

Quote:

> Hi All gurus,

> This posting is in fact related to my previous post : "help me to run a query faster than oracle with informix.".

> As a summary , on a test system i have a query which takes 2min. with oracle and 15min.with informix.I am responsible for running this query as fast as oracle.

> After some work, i have found something strange. (Or it is strange for me.)

> If i have a query which does a full sequental scan and finds no row (no result set) , iostat shows i have a 14mb. read / per second.
> But if i have a result set from the same query , iostat shows i have poor 600kb. read per second.

> (one point , oracle also does sequental scan . )

> These are my query plans:

> Fast one:
> QUERY:        (FIRST_ROWS OPTIMIZATION)
> select *
> from hplandet
> where borc1<0

> Estimated Cost: 1192782
> Estimated # of Rows Returned: 1

>   1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> fragments: ALL)

>         Filters: informix.hplandet.borc1 < 0.00

> Bu da digeri:

<SNIP>

I think you have a serious tuning problem.  I suspect the values of RA_* are WAY too
high and that you do not have enough buffers, LRUS, CLEANERS, or
aio vps configured.  Post the following:

onstat -d
onstat -p
onstat -P
onstat -g iov
onstat -g iof

and note how long ago the stats were cleared (onstat -z).

Art S. Kagel



Mon, 14 Jun 2004 05:42:18 GMT
 slow query

Quote:

>Hi All gurus,

>This posting is in fact related to my previous post : "help me to run a query

faster than oracle with informix.".
Quote:

>As a summary , on a test system i have a query which takes 2min. with oracle

and 15min.with informix.I am responsible for running this query as fast as
oracle.
Quote:

>After some work, i have found something strange. (Or it is strange for me.)

>If i have a query which does a full sequental scan and finds no row (no

result set) , iostat shows i have a 14mb. read / per second.
Quote:
>But if i have a result set from the same query , iostat shows i have poor

600kb. read per second.

What are you using to retrieve the data?  If you are returning all of the data
from the database to the client, the database can only send the data as fast
as the client program asks for it.  Do and AVG on some column for the query
which returns data, so only one row needs to be returned to the client.  If
this responds quickly, then you need to find out how to get the client to ask
for the data in bigger chunks, or at a greater speed, or both.

Quote:

>(one point , oracle also does sequental scan . )

>These are my query plans:

>Fast one:
>QUERY:        (FIRST_ROWS OPTIMIZATION)
>select *
>from hplandet
>where borc1<0

>Estimated Cost: 1192782
>Estimated # of Rows Returned: 1

>  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
>fragments: ALL)

>        Filters: informix.hplandet.borc1 < 0.00

>Bu da digeri:

>Slow one:

>QUERY:        (FIRST_ROWS OPTIMIZATION)

>------
>select *
>from hplandet
>where borc1>=0

>Estimated Cost: 1192782
>Estimated # of Rows Returned: 3235377

>  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
>fragments: ALL)

>        Filters: informix.hplandet.borc1 >= 0.00

>And iostat values:

>----------------------
>Both sql statements do sequental scan.
>(hplandet has 3,000,000 rows, fragmented 4 dbspaces by
>round robin)
>These sql statements run without pdq.

>select *
>from hplandet
>where borc1<0    // there is no record like this  Fast
>one

>select *
>from hplandet
>where borc1>=0   // all the table    Deadly slow!!

>First sqls iostat output:

>device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> %w  %b  us sy wt id
> 24  8  0 68
>sd307      474.1    0.0 15022.1    0.0  0.0  2.4
>5.0   0  39
>sd889      475.5    0.0 15045.0    0.0  0.0  1.7
>3.7   0  39
>                  extended device statistics
>             cpu
>device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> %w  %b  us sy wt id
> 26  8  0 67
>sd307      484.1    0.3 15371.3    0.7  0.0  3.4
>7.0   0  50
>sd889      483.1    0.0 15368.7    0.0  0.0  2.4
>5.0   0  47

>second sqls iostat output:

>device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> %w  %b  us sy wt id
> 29  2  0 69
>sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
>  0   1
>sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
>  0   1

>This is a great difference!

>There is only 1 difference. The first sql statement
>finds no row so no
>data is returned. But the second sql statement finds
>data.
>I have made other tests.If the query has something for
>the result set , the performance is poor. (2. iostat
>output)

>Am i doing something wrong? Do i need to set a
>configuration problem ?
>Or is this a known problem?
>----------------------------------
>Table Name          hplandet
>Owner               informix
>Row Size            649
>Number of Rows      3235377
>Number of Columns   86
>Date Created        08/11/2001

>MULTIPROCESSOR  1               # 0 for
>single-processor, 1 for multi-processor
>NUMCPUVPS       4               # Number of user (cpu)
>vps
>SINGLE_CPU_VP   0               # If non-zero, limit
>number of cpu vps to one

>NOAGE           0               # Process aging
>AFF_SPROC       0               # Affinity start
>processor
>AFF_NPROCS      0               # Affinity number of
>processors

># Shared Memory Parameters

>LOCKS           200000            # Maximum number of
>locks
>BUFFERS         200000            # Maximum number of
>shared buffers
>NUMAIOVPS       8               # Number of IO vps
>PHYSBUFF        512              # Physical log buffer
>size (Kbytes)
>LOGBUFF         512              # Logical log buffer
>size (Kbytes)
>LOGSMAX         60              # Maximum number of
>logical log files
>CLEANERS        8               # Number of buffer
>cleaner processes
>SHMBASE         0x10a000000       # Shared memory base
>address
>SHMVIRTSIZE     128000            # initial virtual
>shared memory segment size
>SHMADD          64000            # Size of new shared
>memory segments (Kbytes)
>SHMTOTAL        0               # Total shared memory
>(Kbytes). 0=>unlimited
>CKPTINTVL       300             # Check point interval
>(in sec)
>LRUS            8               # Number of LRU queues
>LRU_MAX_DIRTY   95              # LRU percent dirty
>begin cleaning limit
>LRU_MIN_DIRTY   90              # LRU percent dirty
>end cleaning limit
>LTXHWM          50              # Long transaction
>high water mark percentage
>LTXEHWM         60              # Long transaction
>high water mark (exclusive)
>TXTIMEOUT       0x12c             # Transaction
>timeout (in sec)
>STACKSIZE       32              # Stack size (Kbytes)

>RA_PAGES      512                 # Number of pages to
>attempt to read ahead
>RA_THRESHOLD  500                # Number of pages
>left before next group

>I have also searched the newsgroup for similiar problems, and found people

who had this problem. But i did not see any posting about a solution.

- Show quoted text -

Quote:

>Kind Regards,
>hope

>==================================
>Poster's IP address: 213.186.155.242
>Posted via http://nodevice.com
>Linux Programmer's Site



Tue, 15 Jun 2004 00:02:33 GMT
 slow query
Hi Everybody,

First , i wish you a very happy year .
And thank you very much for all your replies.

Second , my original posting was 2 months ago , but yesteray i saw
suddenly that it appeared again in newsgroups an this time i am lucky
i have lots of responses......

Let me give you more information:

Quote:
>I think you have a serious tuning problem.  I suspect the values of
RA_* are >WAY too
>high and that you do not have enough buffers, LRUS, CLEANERS, or
>aio vps configured.  Post the following:
>onstat -d
>onstat -p
>onstat -P
>onstat -g iov
>onstat -g iof
>and note how long ago the stats were cleared (onstat -z).

-----------
Quote:
>I can almost certainly say that your RA parameters are too high, even
>for what you're doing.
>What happens with PDQPRIORITY 1? >1?
>Have you done UPDATE STATS?
>What is your hardware config?

----------

The machine is a test system. No user , only my query is run. It is
sun solaris 2.8 , 9.21.fc4 . Upate statistics is run. Table has no
index. I do use sequental scan. If i use the defaults for RA_xx , the
response time is bad.
With Light scan i have better performance. With pdq>1 the performance
is better. But , my aim is to compare the performance against oracle.
If the customer asks me "hey , i want this query run faster , do what
you can " , then
i have couple of ways to do this. One of them is to use PDQ + better
fragmentation. Basically on test system , even with 1 large disk and 4
fragments for the table (round robin)  , my query takes 3.5 min .
(without pdq, it takes 15min...) Alas, the customer asked me run this
without pdq.
So , new database instance , no user , only one table with 4 fragments
, 300mb.memory . (All these settings are same with oracle on the same
server.)
My observation is , whenever i add more columns inside the sum , my
iorate decreases.  The oracle behaviour is not like this. For just 1
fiel for sum ,
by using RA_parameters or light scans , i can get much better values
than oracle. (For example , 20mb.read per second with informix an
5mb.read /sec with oracle. This 5mb. is always same with oracle. But
for informix the more fiels in sum , the lower bytes rea /sec. )

Quote:
>What are you using to retrieve the data?  If you are returning all of
the data
>from the database to the client, the database can only send the data
as fast
>as the client program asks for it.  Do and AVG on some column for the
query
>which returns data, so only one row needs to be returned to the
client.  If
>this responds quickly, then you need to find out how to get the
client to ask
>for the data in bigger chunks, or at a greater speed, or both.

I have written a java program . I also use dbaccess etc...

With java:

     1. rs=pstmt.executeQuery("select * from customer")
     2. while(rs.next())
       3.   fetch data
      The program immedi?ately passes to step 2., an client
starts to fetch ata. Now you are right that the spee epens on the
client's  capability.
    But ,
     1. rs=pstmt.executeQuery("select
sum(col1+col2+......),sum(.....),... from customer")
     2. while(rs.next())
       3.   fetch data
        This time step 1 takes the most time . I am trying to speed up
it.

---

Quote:
>turn off the first row optimisation and see what happens.  First row
>stuff is normally detrimental in these queries ie returning all the
>rows

--------
At first it was off. I just trie it. The results are same. I am
testing without first row optimization.
---------

Quote:
>Also, try> bouncing the server before you run each query - if you run
>them one after the other on a "cold" engine, you will see all sorts
of
>caching effects...

--------
I have already done like this. For every query , i restart the db
server.

--------

Quote:
>Drop your read ahead params - 32 and 28 should be ample.
>Turn off first  rows optimization
>increase LRU's  (127) - 8 is probably not enough for 200 K buffers
>increase CLEANERS (41) 8 is probably not enough for so many
buffers/lru's
>Drop Lru_Max_Dirty (10)
>Drop Lru Min_dirty (2)
>also try the queries again with an "onstat -z" before the query and
post the
>ouput of onstat -p after the query.

-------------
     I have no activity on this server . So lru_max /min has no effect
.
I have alreay run onstat -z before each query.

Kind Regards,
hope

Quote:


> >Hi All gurus,

> >This posting is in fact related to my previous post : "help me to run a query
>  faster than oracle with informix.".

> >As a summary , on a test system i have a query which takes 2min. with oracle
> and 15min.with informix.I am responsible for running this query as fast as
> oracle.

> >After some work, i have found something strange. (Or it is strange for me.)

> >If i have a query which does a full sequental scan and finds no row (no
>  result set) , iostat shows i have a 14mb. read / per second.
> >But if i have a result set from the same query , iostat shows i have poor
> 600kb. read per second.

> What are you using to retrieve the data?  If you are returning all of the data
> from the database to the client, the database can only send the data as fast
> as the client program asks for it.  Do and AVG on some column for the query
> which returns data, so only one row needs to be returned to the client.  If
> this responds quickly, then you need to find out how to get the client to ask
> for the data in bigger chunks, or at a greater speed, or both.

> >(one point , oracle also does sequental scan . )

> >These are my query plans:

> >Fast one:
> >QUERY:        (FIRST_ROWS OPTIMIZATION)
> >select *
> >from hplandet
> >where borc1<0

> >Estimated Cost: 1192782
> >Estimated # of Rows Returned: 1

> >  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> >fragments: ALL)

> >        Filters: informix.hplandet.borc1 < 0.00

> >Bu da digeri:

> >Slow one:

> >QUERY:        (FIRST_ROWS OPTIMIZATION)

> >------
> >select *
> >from hplandet
> >where borc1>=0

> >Estimated Cost: 1192782
> >Estimated # of Rows Returned: 3235377

> >  1) informix.hplandet: SEQUENTIAL SCAN  (Serial,
> >fragments: ALL)

> >        Filters: informix.hplandet.borc1 >= 0.00

> >And iostat values:

> >----------------------
> >Both sql statements do sequental scan.
> >(hplandet has 3,000,000 rows, fragmented 4 dbspaces by
> >round robin)
> >These sql statements run without pdq.

> >select *
> >from hplandet
> >where borc1<0    // there is no record like this  Fast
> >one

> >select *
> >from hplandet
> >where borc1>=0   // all the table    Deadly slow!!

> >First sqls iostat output:

> >device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> > %w  %b  us sy wt id
> > 24  8  0 68
> >sd307      474.1    0.0 15022.1    0.0  0.0  2.4
> >5.0   0  39
> >sd889      475.5    0.0 15045.0    0.0  0.0  1.7
> >3.7   0  39
> >                  extended device statistics
> >             cpu
> >device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> > %w  %b  us sy wt id
> > 26  8  0 67
> >sd307      484.1    0.3 15371.3    0.7  0.0  3.4
> >7.0   0  50
> >sd889      483.1    0.0 15368.7    0.0  0.0  2.4
> >5.0   0  47

> >second sqls iostat output:

> >device       r/s    w/s   kr/s   kw/s wait actv  svc_t
> > %w  %b  us sy wt id
> > 29  2  0 69
> >sd307       10.7    0.7  341.3    1.3  0.0  0.1    5.0
> >  0   1
> >sd889       11.3    0.7  342.6    5.7  0.0  0.0    2.8
> >  0   1

> >This is a great difference!

> >There is only 1 difference. The first sql statement
> >finds no row so no
> >data is returned. But the second sql statement finds
> >data.
> >I have made other tests.If the query has something for
> >the result set , the performance is poor. (2. iostat
> >output)

> >Am i doing something wrong? Do i need to set a
> >configuration problem ?
> >Or is this a known problem?
> >----------------------------------
> >Table Name          hplandet
> >Owner               informix
> >Row Size            649
> >Number of Rows      3235377
> >Number of Columns   86
> >Date Created        08/11/2001

> >MULTIPROCESSOR  1               # 0 for
> >single-processor, 1 for multi-processor
> >NUMCPUVPS       4               # Number of user (cpu)
> >vps
> >SINGLE_CPU_VP   0               # If non-zero, limit
> >number of cpu vps to one

> >NOAGE           0               # Process aging
> >AFF_SPROC       0               # Affinity start
> >processor
> >AFF_NPROCS      0               # Affinity number of
> >processors

> ># Shared Memory Parameters

> >LOCKS           200000            # Maximum number of
> >locks
> >BUFFERS         200000            # Maximum number of
> >shared buffers
> >NUMAIOVPS       8               # Number of IO vps
> >PHYSBUFF        512              # Physical log buffer
> >size (Kbytes)
> >LOGBUFF         512              # Logical log buffer
> >size (Kbytes)
> >LOGSMAX         60              # Maximum number of
> >logical log files
> >CLEANERS        8               # Number of buffer
> >cleaner processes
> >SHMBASE         0x10a000000       # Shared memory base
> >address
> >SHMVIRTSIZE     128000            # initial virtual
> >shared memory segment size
> >SHMADD          64000            # Size of new shared
> >memory segments (Kbytes)
> >SHMTOTAL        0               # Total shared memory
> >(Kbytes). 0=>unlimited
> >CKPTINTVL       300             # Check point interval
> >(in sec)
> >LRUS            8               # Number of LRU queues
> >LRU_MAX_DIRTY   95              # LRU percent dirty
> >begin cleaning limit
> >LRU_MIN_DIRTY   90              # LRU percent dirty
> >end cleaning limit
> >LTXHWM          50              # Long transaction
> >high water mark percentage
> >LTXEHWM         60              # Long transaction
> >high water mark (exclusive)
> >TXTIMEOUT       0x12c             # Transaction

...

read more »



Tue, 15 Jun 2004 06:59:47 GMT
 slow query

Comments embedded

Quote:

>Hi Everybody,

>First , i wish you a very happy year .
>And thank you very much for all your replies.

>Second , my original posting was 2 months ago , but yesteray i saw
>suddenly that it appeared again in newsgroups an this time i am lucky
>i have lots of responses......

>Let me give you more information:

>>I think you have a serious tuning problem.  I suspect the values of
>RA_* are >WAY too
>>high and that you do not have enough buffers, LRUS, CLEANERS, or
>>aio vps configured.  Post the following:
>>onstat -d
>>onstat -p
>>onstat -P
>>onstat -g iov
>>onstat -g iof
>>and note how long ago the stats were cleared (onstat -z).

>-----------
>>I can almost certainly say that your RA parameters are too high, even
>>for what you're doing.
>>What happens with PDQPRIORITY 1? >1?
>>Have you done UPDATE STATS?
>>What is your hardware config?
>----------

>The machine is a test system. No user , only my query is run. It is
>sun solaris 2.8 , 9.21.fc4 . Upate statistics is run. Table has no
>index. I do use sequental scan. If i use the defaults for RA_xx , the
>response time is bad.
>With Light scan i have better performance. With pdq>1 the performance
>is better. But , my aim is to compare the performance against oracle.
>If the customer asks me "hey , i want this query run faster , do what
>you can " , then
>i have couple of ways to do this. One of them is to use PDQ + better
>fragmentation. Basically on test system , even with 1 large disk and 4
>fragments for the table (round robin)  , my query takes 3.5 min .
>(without pdq, it takes 15min...) Alas, the customer asked me run this
>without pdq.

Is the table fragmented in oracle?  I don't know if Oracle will do parallel
scans these days, if so, not using PDQ would be giving a large advantage to
Oracle.  Is there a reason for the customer not wanting to use PDQ?

Quote:
>So , new database instance , no user , only one table with 4 fragments
>, 300mb.memory . (All these settings are same with oracle on the same
>server.)
>My observation is , whenever i add more columns inside the sum , my
>iorate decreases.  The oracle behaviour is not like this. For just 1
>fiel for sum ,
>by using RA_parameters or light scans , i can get much better values
>than oracle. (For example , 20mb.read per second with informix an
>5mb.read /sec with oracle. This 5mb. is always same with oracle. But
>for informix the more fiels in sum , the lower bytes rea /sec. )

Are you summing some fields which are not integers or something?  I am
surprised you are seeing a significant difference when you sum multiple
fields, but I haven't done any benchmarking along those lines.

- Show quoted text -

Quote:

>>What are you using to retrieve the data?  If you are returning all of
>the data
>>from the database to the client, the database can only send the data
>as fast
>>as the client program asks for it.  Do and AVG on some column for the
>query
>>which returns data, so only one row needs to be returned to the
>client.  If
>>this responds quickly, then you need to find out how to get the
>client to ask
>>for the data in bigger chunks, or at a greater speed, or both.

>I have written a java program . I also use dbaccess etc...

>With java:

>     1. rs=pstmt.executeQuery("select * from customer")
>     2. while(rs.next())
>       3.   fetch data
>      The program immedi?ately passes to step 2., an client
>starts to fetch ata. Now you are right that the spee epens on the
>client's  capability.
>    But ,
>     1. rs=pstmt.executeQuery("select
>sum(col1+col2+......),sum(.....),... from customer")
>     2. while(rs.next())
>       3.   fetch data
>        This time step 1 takes the most time . I am trying to speed up
>it.

Does min have the same result as sum?

If you sum one integer column, vs 4 integer columns what is the difference.

What is the exact query you are running on Oracle.  If it is retrieving lots
of data, what is the client you are using.

Hope to help,
Will

- Show quoted text -

Quote:
>---
>>turn off the first row optimisation and see what happens.  First row
>>stuff is normally detrimental in these queries ie returning all the
>>rows
>--------
>At first it was off. I just trie it. The results are same. I am
>testing without first row optimization.
>---------

>>Also, try> bouncing the server before you run each query - if you run
>>them one after the other on a "cold" engine, you will see all sorts
>of
>>caching effects...
>--------
>I have already done like this. For every query , i restart the db
>server.

>--------
>>Drop your read ahead params - 32 and 28 should be ample.
>>Turn off first  rows optimization
>>increase LRU's  (127) - 8 is probably not enough for 200 K buffers
>>increase CLEANERS (41) 8 is probably not enough for so many
>buffers/lru's
>>Drop Lru_Max_Dirty (10)
>>Drop Lru Min_dirty (2)
>>also try the queries again with an "onstat -z" before the query and
>post the
>>ouput of onstat -p after the query.
>-------------
>     I have no activity on this server . So lru_max /min has no effect
>.
>I have alreay run onstat -z before each query.

>Kind Regards,
>hope

<SNIP>


Tue, 15 Jun 2004 23:37:44 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. slow slow query

2. Slow query execution through JDBC - runs fast in Query Analyzer

3. Very slow query performance for any query

4. Slow query-performance

5. Slow Query

6. Slow query (Called from MSAccess)

7. Problem with slow query.

8. Slow Query on SQL 6.5

9. Slow query to remote MSDE (SQL server)

10. Slow queries from Access to MSSQL server

11. Slow query response

12. ADO, OLEDB, and Slow Queries


 
Powered by phpBB® Forum Software