Save 
Join IIUG
 for   
 

Informix News
29 July 09 - Wall Street Journal - IBM to Acquire SPSS, Adding to Acquisitions... Read
11 February 09 - InformationWeek - IBM Drifts Slowly Toward Mainstream Cloud Computing... Read
11 February 09 - CNNMoney.com - IBM to Deliver Software via Cloud Computing With Amazon Web Services... Read
07 January 09 - Huliq News - IBM Power Servers Helps Bank of Chengdu Build Up Core Banking System.. Read
04 December 08 - Steeleye - Multicarta achieves 99.997% uptime for its Informix-based Credit Card Authorization Centre... Read
16 October 08 - VendorRate - Informix Earns Top Customer Satisfaction Score on VendorRate in Q3... Read
14 August 08 - IIUG.org - Sellout Expected for the 2009 IIUG Informix Conference... Read
29 April 08 - IntelligentEnterprise.com - IBM Informix Upgrade Enhances Clustering, Database Management... Read
29 April 08 - itweek.com - IBM 'Cheetah 2' mauls data costs... Read
28 April 08 - eWeek.com - IBM Uncages Cheetah 2 Data Server... Read
28 April 08 - CNNMoney.com - IBM Helps Clients Reduce Data Management Costs With New Informix Dynamic Server... Read
09 April 08 - CNNMoney.com - MediaSpan Embeds IBM Informix Dynamic Server Software for Delivering News to Print, Web and Wireless Devices... Read
08 April 08 - IT-Director.com - Informix seeks developers... Read
18 February 08 - marketwire.com - Icarus Studios Partners With IBM to Upgrade Performance, Availability for Its Online Games... Read
17 January 08 - eWeek.com - IBM Adds Mac Support to IDS for Higher Education... Read
17 January 08 - informationweek.com - Lotus Notes For iPhone Signals Closer Ties Between IBM, Apple... Read
16 January 08 - marketwire.com - IBM Informix Dynamic Server to Deliver Support for Mac OS X... Read
16 January 08 - internetnews.com - IBM's IDS to Support Mac Platform... Read
28 June 07 - REG Developer - IBM and Informix tie down Cheetah... Read
27 June 07 - CBRonline.com - IBM corrects its own Informix customer figures... Read
14 June 07 - vnunet.com - IBM changes spots with Informix 'Cheetah' database... Read
14 June 07 - eChannelLine - IBM expands scope for IDS... Read
14 June 07 - Resellernews - IBM: Informix database alive and kicking... Read
13 June 07 - DB2 Magazine - Cheetah is now out of the gate... Read
12 June 07 - IBM - IDS 11 release announcement (pdf)... Read
12 June 07 - ChannelWeb Network - IBM Uncages IDS 11, Aka Cheetah, Database... Read
12 June 07 - eWeek.com - IBM's 'Cheetah' Ready to Pounce... Read
12 June 07 - InformationWeek - IBM Unleashes 'Cheetah' Database... Read
12 June 07 - WebWire - IBM Strengthens Database Portfolio With New Informix Dynamic Server... Read
12 June 07 - Intelligent Enterprise - IBM Unveils Informix Upgrade... Read
12 June 07 - ComputerWeekly.com - IBM's Cheetah IDS makes leap to better data centre clustering... Read
12 June 07 - ebiz - IBM Unveils Next Generation Informix Dynamic Server... Read
12 June 07 - computerworld.com - Will 'Cheetah' help IBM's Informix chase down market share?... Read
12 June 07 - Internetnews.com - No Data Can Outrun This 'Cheetah'... Read
12 June 07 - de.internet.com - IBM neuer Datenbank-Server mit Codenamen Cheetah ist fertig... Read
12 June 07 - verifox.de - IBM stärkt Datenbank-Portfolio mit neuem Informix Dynamic Server... Read
12 June 07 - golem.de - Informix 11 vorgestellt... Read
12 June 07 - Computerwoche.de - IBM stellt neue Informix-Version vor... Read
12 June 07 - IBM.de - IBM stärkt Datenbank-Portfolio mit neuem Informix Dynamic Server... Read
12 June 07 - Heise - IBM gibt Informix 11 frei... Read
25 May 07 - Taiwan.CNET.com - Local Taiwan Informix user group established... (Chinese language) ... Read
18 May 07 - ChannelWeb Network - IBM Musters Partners For Cheetah Release... Read
18 May 07 - eWeek.com - IBM Looks to 'Cheetah' to Speed Up Blade Servers... Read
7 May 07 - DB2 Magazine - SQL Shortcuts - Use these tricks to generate IDS SQL scripts... Read




End of Support Dates
IDS 7.31 - 30 Sep 2009
IDS 9.40 - 30 Apr 2009
IDS 10.00 - 30 Sep 2010
Subscribe to notifications

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

Re: Question about sysindexes.clust

Posted By: Richard Snoke
Date: Tuesday, 18 November 2008, at 3:37 p.m.

In Response To: Re: Question about sysindexes.clust (Obnoxio The Clown)

Have you looked at the read ahead parameters and utilizaiton? Are you
using KAIO or not? How much of your buffer pool is occupied by the pages
of this table and its indexes? At this point this feels like it might be
that the buffer pages are being thrashed when the data is not pre-sorted
(which the clustering accomplishes). I've seen that as the cause of
similar symptoms in other applications.

Dick Snoke
IBM Data Management - ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595

From:
"Obnoxio The Clown" <obnoxio@serendipita.com>
To:
ids@iiug.org
Date:
11/18/2008 03:08 PM
Subject:
Re: Question about sysindexes.clust [14015]

Eric Rowell wrote:
> The following is the explain plain for the only query in the program
which
> isn't running correctly. The first is the plan from the normal run
(filter
> data had to be changed to project my job). The info after that is from a

> diff of the explain outs for 2 more configurations. Also during all
tests
> we used a production like system (smaller CPU and slower SAN) but we
know
> the approx. modifier from prod to dev. When we sorted the data by the
> serial_link and reloaded (changing the fragmentation as shown earlier
things
> run much better.
>
> EXPLAIN from normal run...
>
> QUERY:
> ------
> SELECT qd.option_list, oq.optid
> FROM qty_head qh, qty_det qd, optcombo_qty oq
> WHERE ((qh.set_no = "FINDA" AND qh.version = "09") OR
>
> (qh.set_no = "ORFND" AND qh.version = "01"))
>
> AND qh.serial_key = qd.serial_link
>
> AND qd.serial_key = oq.option_group_id
>
> AND qh.dept_code IN ("AAA","BBB","CCC","DDD","EEE")
>
> AND qh.community = "00"
>
> Estimated Cost: 1049986
> Estimated # of Rows Returned: 1083718
> 1) informix.qh: INDEX PATH
>
> (1) Index Keys: dept_code community set_no version phase_no lot
> area_group_id
>
> (Key-First) (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.qh.dept_code = 'AAA' AND
> informix.qh.community = '00' )
>
> Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND
> informix.qh.version = '09')
>
> OR (informix.qh.set_no = 'ORFND' AND
> informix.qh.version = '01')))
>
> (2) Index Keys: dept_code community set_no version phase_no lot
> area_group_id
>
> (Key-First) (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.qh.dept_code = 'BBB' AND
> informix.qh.community = '00' )
>
> Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND
> informix.qh.version = '09')
>
> OR (informix.qh.set_no = 'ORFND' AND
> informix.qh.version = '01')))
>
> (3) Index Keys: dept_code community set_no version phase_no lot
> area_group_id
>
> (Key-First) (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.qh.dept_code = 'CCC' AND
> informix.qh.community = '00' )
>
> Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND
> informix.qh.version = '09')
>
> OR (informix.qh.set_no = 'ORFND' AND
> informix.qh.version = '01')))
>
> (4) Index Keys: dept_code community set_no version phase_no lot
> area_group_id
>
> (Key-First) (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.qh.dept_code = 'DDD' AND
> informix.qh.community = '00' )
>
> Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND
> informix.qh.version = '09')
>
> OR (informix.qh.set_no = 'ORFND' AND
> informix.qh.version = '01')))
>
> (5) Index Keys: dept_code community set_no version phase_no lot
> area_group_id
>
> (Key-First) (Serial, fragments: ALL)
>
> Lower Index Filter: (informix.qh.dept_code = 'EEE' AND
> informix.qh.community = '00' )
>
> Index Key Filters: (( (informix.qh.set_no = 'FINDA' AND
> informix.qh.version = '09' )
>
> OR (informix.qh.set_no = 'ORFND' AND
> informix.qh.version = '01')))
> 2) informix.qd: INDEX PATH
>
> (1) Index Keys: serial_link (Serial, fragments: ALL)
>
> Lower Index Filter: informix.qh.serial_key = informix.qd.serial_link
> NESTED LOOP JOIN
> 3) informix.optcombo_head: INDEX PATH
>
> (1) Index Keys: serial_key option_list option_count (Serial,
> fragments: ALL)
>
> Lower Index Filter: informix.qd.optcombo_head_key =
> informix.optcombo_head.serial_key
> NESTED LOOP JOIN
> 4) product.qty_detail: INDEX PATH
>
> (1) Index Keys: serial_key (Serial, fragments: ALL)
>
> Lower Index Filter: informix.qd.serial_key =
> product.qty_detail.serial_key
> NESTED LOOP JOIN
> 5) informix.oq: INDEX PATH
>
> (1) Index Keys: optcombo_head_key opt_id (Key-Only) (Serial,
> fragments: ALL)
>
> Lower Index Filter: informix.oq.optcombo_head_key =
> product.qty_detail.optcombo_head_key
> NESTED LOOP JOIN
>
> Diff of Explain Plan after just reloading the table:
> < Estimated Cost: 1049986
> < Estimated # of Rows Returned: 1083718
> ---
>> Estimated Cost: 2348476
>> Estimated # of Rows Returned: 1486409
>
> Diff of Explain Plan after reloading the sorting data (using
serial_link):
> < Estimated Cost: 1049986
> < Estimated # of Rows Returned: 1083718
> ---
>> Estimated Cost: 851019
>> Estimated # of Rows Returned: 1094319
>
> The cost difference appears to be in line with the change to the
> sysindex.clust value for the indexes.

And which table do you query from to see if there are missing rows and
insert them?

Basically, I can't disagree with your analysis, I'm just curious as to
why ordering should have such a measurable impact on performance given
the explain plan. In general, it doesn't.

I feel like there is something lurking in the nether hells of my brain,
but I can't quite drag it out. Last time I saw something like this, the
root cause was "XXXX in my opinion" but I can't remember what "XXXX" was.

Give me time. Or Art will be along shortly. :o)

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com

*******************************************************************************

Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.