 |
IDS Forum
Re: Question about sysindexes.clust
Posted By: Eric Rowell
Date: Tuesday, 18 November 2008, at 3:24 p.m.
In Response To: Re: Question about sysindexes.clust (Obnoxio The Clown)
This will be hard to look at without putting it in something to line it all
up. The following is the stats form sysptprof for just this application
running. These are just for the table in question. The other table
involved are processed by a different loop of code and appears to have no
difference in the sysptprof's captured at runtime.
Base
tabname partnum isreads bufreads pagreads
ix_qty_det_pn_sl 112197634 0 0 0
ix_qty_det_sk 95420418 0 0 0
ix_qty_det_sl 95420419 52177249 56982064 22533
ix_qty_det_vi_ed_sl 112197635 0 0 0
qty_det 19922946 8661696 8661632 321953
qty_det 56623106 8635728 8635745 377128
qty_det 83886082 8656948 8656879 377073
qty_det 85983234 8648646 8648658 371842
qty_det 134217730 8649673 8649651 375125
qty_det 137363458 8667249 8667221 410169
Bad
tabname partnum isreads bufreads pagreads
ix_qty_det_pn_sl 112197634 0 0 0
ix_qty_det_sk 95420418 0 0 0
ix_qty_det_sl 95420419 52210032 55410714 11602
ix_qty_det_vi_ed_sl 112197635 0 0 0
qty_det 19922946 8522764 8527210 23631
qty_det 56623106 8339915 8344259 22995
qty_det 83886082 8642249 8647025 25673
qty_det 85983234 8368976 8371153 24633
qty_det 134217730 9862512 9864620 27256
qty_det 137363458 8185929 8192707 25085
Diff- Base vs. Bad
tabname partnum isreads bufreads pagreads
ix_qty_det_pn_sl 112197634 0 0 0
ix_qty_det_sk 95420418 0 0 0
ix_qty_det_sl 95420419 5785 61657 22553
ix_qty_det_vi_ed_sl 112197635 0 0 0
qty_det 19922946 5838 5930 1323271
qty_det 56623106 12784 12790 1360551
qty_det 83886082 -16823 -16727 1349929
qty_det 85983234 -14263 -14267 1362454
qty_det 134217730 17986 18054 1374809
qty_det 137363458 -3544 -3484 1386884
Good
tabname partnum isreads bufreads pagreads
ix_qty_det_pn_sl 112197634 0 0 0
ix_qty_det_sk 95420418 0 0 0
ix_qty_det_sl 95420419 52183034 57043721 45086
ix_qty_det_vi_ed_sl 112197635 0 0 0
qty_det 19922946 8667534 8667562 1645224
qty_det 56623106 8648512 8648535 1737679
qty_det 83886082 8640125 8640152 1727002
qty_det 85983234 8634383 8634391 1734296
qty_det 134217730 8667659 8667705 1749934
qty_det 137363458 8663705 8663737 1797053
Diff- Base vs. Good
tabname partnum isreads bufreads pagreads
ix_qty_det_pn_sl 112197634 0 0 0
ix_qty_det_sk 95420418 0 0 0
ix_qty_det_sl 95420419 32783 -1571350 -10931
ix_qty_det_vi_ed_sl 112197635 0 0 0
qty_det 19922946 -138932 -134422 -298322
qty_det 56623106 -295813 -291486 -354133
qty_det 83886082 -14699 -9854 -351400
qty_det 85983234 -279670 -277505 -347209
qty_det 134217730 1212839 1214969 -347869
qty_det 137363458 -481320 -474514 -385084
On Tue, Nov 18, 2008 at 3:01 PM, Obnoxio The Clown
<obnoxio@serendipita.com>wrote:
> 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.
>
>
--
Eric B. Rowell
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
