IDS Forum

Re: SQL return high query cost on ids 12.10

Posted By: Art Kagel
Date: Monday, 16 May 2016, at 12:37 p.m.

In Response To: Re: SQL return high query cost on ids 12.10 (Fernando Nunes)

Fernando:

You are right. The addition of the index name in the v12.10 output (which
v11.10 did note suuply) threw me off. As I said, I hadn't looked carefully.
The main difference between the two queries are the costs and the estimated
rows. The former just reflects the latter (and the change in the
calculation) and so the cost can be ignored. The difference in the
estimated rows, however, cannot. That both queries returned zero rows but
v11.10 estimated returning 903 rows while v12.10 estimated returning
250,424 rows
indicates, as we all initially suggested, that the data distributions are
not ideal. I suspect that increasing the resolution of the distributions
will resolve that problem and that it is due to data skew.

However, that said, since, as you correctly pointed out, the query plans
are identical the performance of v12.10 should be at least as good as that
of v11.10. I suspect something else is going on here. There is some
difference between the two databases besides the version of the engine.

So, questions for Mohd:

- Are the two platforms the same (ie same OS and CPU hardware

architecture and speed)?

- Are the disk storage used by both systems the same as to

- RAW -vs- COOKED,

- RAID level,

- Number of disks in the array,

- Sharing of the disk structures by other applications, etc.

- Are the ONCONFIG settings comparable?

- Were the indexes on the v12.10 engine for this table build before the

data was loaded or after?

- Is the layout of the v12.10 server's tables different than the v11.10

layout (ie which tables are in the same dbspace as this acc_pay table? What

about the index? (I see in the v12.10 dbschema output that the i_acc_pay

index is in a different dbspace from the table itself, but I cannot tell

what the layout is under v11.10.)

- How many extents does the table have on the v11.10 engine versus the

v12.10 engine? (It looks like the v12.10 table is a single large extent,

but I cannot be certain of that from here.)

- Is the runtime of the test repeatable or dependent on outside

influences?

Art

Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.

On Mon, May 16, 2016 at 11:46 AM, Fernando Nunes <domusonline@gmail.com>
wrote:

> We should not deviate from the main point.
> The OP stated that he gets the same query plans on 11.10 and 12.10.
> Ifthat's the case, the discussion about the new parameter is irrelevant and
> we need to understand why the same query plan runs so much faster on 11.10.
>
> As I tried to say before, the only options I can think are:
>
> - V11.10 already has the data in the cache
> - V11.10 has a much better throughput accessing the disks
> - Eventually the $ONCONFIG settings are dragging the performance on V12.10
> down
> - A bug... which seems highly unlikely
>
> Regards
>
> On Mon, May 16, 2016 at 4:39 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > You can set this dynamically with:
> >
> > onmode -wm OPT_SEEK_FACTOR=0
> >
> > Not so much improve performance as restore the query plans to those that
> > v11.10 produced by eliminating the cost values associated with index scan
> > activity. The older v11.10 query plan used a single index while the
> v12.10
> > is using two indexes via the Multi-Index Scan technology introduced in
> > v11.70. I didn't look in detail, but it may be that together the two
> > indexes that v12.10 used included one or more filter columns from the
> WHERE
> > clause that were not present in the single index that v11.10 used. So,
> > with or without setting OPT_SEEK_FACTOR creating a new index that
> contains
> > ALL of the filters will make a new, third, query plan which is better
> than
> > either of the ones you have experienced. I had not thought of this when I
> > originally posted, but it is worth exploring.
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.com
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Mon, May 16, 2016 at 11:12 AM, MOHD FADZIL JUSOH <
> fadzil@isianpadu.com>
> > wrote:
> >
> > > Hi,
> > >
> > > did you drop all distributions after the upgrade to v12.10 and THEN
> > > run update statistics HIGH? yes, done for this step.
> > >
> > > "try including the undocumented parameter OPT_SEEK_FACTOR set to 0 in
> > > your ONCONFIG file. This controls the cost assigned to index scans and
> > > defaults to 6 (range 0-25). If setting it to zero helps you can try
> other
> > > settings below 6 since this costing change can improve performance for
> > some
> > > queries. Turning it off by setting to zero returns the behavior you
> were
> > > seeing in v11.10. "
> > >
> > > Will try this, need to restart database instance? Hope this parameter
> can
> > > improved our database peformance
> > >
> > > Thank you for your advice
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --14dae93b57d626eaff0532f76ee9
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001a1141bb9610fdd00532f78699
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--047d7bd75154ce84b10532f83c06

Messages In This Thread

• SQL return high query cost on ids 12.10
  MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 3:59 a.m.
  • Re: SQL return high query cost on ids 12.10
    Spokey Wheeler -- Monday, 16 May 2016, at 4:03 a.m.
    • Re: SQL return high query cost on ids 12.10
      MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 4:21 a.m.
      • Re: SQL return high query cost on ids 12.10
        Art Kagel -- Monday, 16 May 2016, at 10:03 a.m.
        • Re: SQL return high query cost on ids 12.10
          MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 11:12 a.m.
          • Re: SQL return high query cost on ids 12.10
            Art Kagel -- Monday, 16 May 2016, at 11:39 a.m.
            • Re: SQL return high query cost on ids 12.10
              Fernando Nunes -- Monday, 16 May 2016, at 11:46 a.m.
              • Re: SQL return high query cost on ids 12.10
                Art Kagel -- Monday, 16 May 2016, at 12:37 p.m.
  • Re: SQL return high query cost on ids 12.10
    Fernando Nunes -- Monday, 16 May 2016, at 5:34 a.m.
    • Re: SQL return high query cost on ids 12.10
      MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 8:32 a.m.
      • Re: SQL return high query cost on ids 12.10
        Fernando Nunes -- Monday, 16 May 2016, at 9:53 a.m.
        • Re: SQL return high query cost on ids 12.10
          MOHD FADZIL JUSOH -- Monday, 16 May 2016, at 11:24 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.