Doubt in Update statistics, resolution and confide
A DBA on Solaris 9 / IDS 11.50.FC4 reported that queries against a huge round-robin fragmented table (~2.5 billion rows, 870GB) ran worse after UPDATE STATISTICS with distributions than with no distributions, and asked how to tune resolution and confidence for such large tables. Responders asked for the exact statistics commands, PDQPRIORITY, query text, schema and query plans. Art Kagel suspected OPTCOMPIND=0 (favouring nested-loop/indexed access) was wrong for a DSS-type workload and suggested OPTCOMPIND=2 or 1, or USE_HASH hints; Cesar added SET ENVIRONMENT OPTCOMPIND, a light-scan APAR link, {+FULL} directives and raising read-ahead. The poster never replied with results, so no confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, We are having some performance issues running queries in a huge table, we did some tests. When we run update statistics with distributions the performance is worse than without distribution using the default parameters of resolution and confidence. We need distribution for the optimizer use light scans. How can I set up those parameters, resolutions and confidence for huge tables (2,5 billion records, 53,125,000 16K pages or 870GB) Row size = 328 bytes Number of records = 2.526.113.375 Number of 16k pages = 53,125,000 Size in GB = 870 Fragment by round robin in 17 dbspaces 3 Indexes detached Thanks in advance, Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509
Just to complete the information SO: Solaris 9 64 bits IDS 11.50.FC4 Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Celso Cabral Coimbra Enviada em: terça-feira, 17 de novembro de 2009 14:08 Para: ids@iiug.org Assunto: Doubt in Update statistics, resolution and con.... [18137] Hi all, We are having some performance issues running queries in a huge table, we did some tests. When we run update statistics with distributions the performance is worse than without distribution using the default parameters of resolution and confidence. We need distribution for the optimizer use light scans. How can I set up those parameters, resolutions and confidence for huge tables (2,5 billion records, 53,125,000 16K pages or 870GB) Row size = 328 bytes Number of records = 2.526.113.375 Number of 16k pages = 53,125,000 Size in GB = 870 Fragment by round robin in 17 dbspaces 3 Indexes detached Thanks in advance, Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
What do you have OPTCOMPIND set to? Are you using PDQPRIORITY > 0? PRQPRIORITY > 1? What are your IDS version and platform? Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, Nov 17, 2009 at 11:08 AM, Celso Cabral Coimbra < ccoimbra@cleartech.com.br> wrote: > Hi all, > > We are having some performance issues running queries in a huge table, > we did some tests. > When we run update statistics with distributions the performance is > worse than without distribution using the default parameters of > resolution and confidence. > We need distribution for the optimizer use light scans. How can I set up > those parameters, resolutions and confidence for huge tables (2,5 > billion records, 53,125,000 16K pages or 870GB) > > Row size = 328 bytes > Number of records = 2.526.113.375 > Number of 16k pages = 53,125,000 > Size in GB = 870 > Fragment by round robin in 17 dbspaces > 3 Indexes detached > > Thanks in advance, > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174c3a6af4495204789556ff
Hi Celso, Are you using MED or HIGH? I suppose MEDIUM, based of the message subject (confidence) How much columns are you specifying? Can you copy here your update statistics? with the parameters used.. and pdq priority,... And the output of the explain for each execution (with light scan and without)... Only to inform, the version 11.50 have this APAR with light scans: http://www-01.ibm.com/support/docview.wss?rs=630&context=SSGU8G&context=SSZ2HS&c ontext=SSP6X2&context=SSVHPS&context=SSHPYE&q1=light+scan&uid=swg1IC60702&loc=en _US&cs=utf-8&lang=en But I don't believed this is your situation because you said the light scan works with the default parameters... My guess is you work with UPD_STAT MEDIUM and probably inform some columns, when you change the confidence , the amount of data change for a value smaller of your buffers, and this way no light scan are used... If your environment is for test.. try set the variable LIGHT_SCANS=FORCE. Tip: After some tests on solaris here, I discovery the I/O block is 64k (no matter the page size of your dbspace), after change the READ AHEAD to a value bigger (to 64), I have a considerable gain over create indexes....
Art, SO: Solaris 9 64 bits IDS 11.50.FC4 OPTCOMPIND=0 Regarding to PDQPRIORITY, running update statistcs high, doesn't matter if I don't use PDQ or pdqpriority high. The test we develop, we ran the follow update statistcs update statistcs low drop distribution update statistcs medium in fields that compose indexes but not in the head update statistcs high in the head of indexes. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art Kagel Enviada em: terça-feira, 17 de novembro de 2009 16:32 Para: ids@iiug.org Assunto: Re: Doubt in Update statistics, resolution and.... [18139] What do you have OPTCOMPIND set to? Are you using PDQPRIORITY > 0? PRQPRIORITY > 1? What are your IDS version and platform? Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Tue, Nov 17, 2009 at 11:08 AM, Celso Cabral Coimbra < ccoimbra@cleartech.com.br> wrote: > Hi all, > > We are having some performance issues running queries in a huge table, > we did some tests. > When we run update statistics with distributions the performance is > worse than without distribution using the default parameters of > resolution and confidence. > We need distribution for the optimizer use light scans. How can I set up > those parameters, resolutions and confidence for huge tables (2,5 > billion records, 53,125,000 16K pages or 870GB) > > Row size = 328 bytes > Number of records = 2.526.113.375 > Number of 16k pages = 53,125,000 > Size in GB = 870 > Fragment by round robin in 17 dbspaces > 3 Indexes detached > > Thanks in advance, > > Celso Cabral Coimbra > Administrador de Banco de Dados > ClearTech Ltda > "Trust at the heart of Communications" > Tel. (11) 3576-4509 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174c3a6af4495204789556ff ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Celso, Just for better understand , what update exactly have the performance problem? med ,high or both? what values of resolution/confidence used? The PDQ make all differ with update statistics high, use more memory and they intend to use light scan and avoid the indexes... Did you check your EXPLAIN output for this executions? Did you test with PDQ high? (and properly configured - DS_* ) I just don't know the effect of OPTCOMPIND over execution of update statistics...
I was referring to the PDQPRIORITY level you use when running the queries
that are slow, not when running the update statistics. Post the table
schema (including the fragmentation scheme and indexes), a typical query
that's giving you trouble, and the output from dbschema ... -hd <table> for
this table.
One problem I see, which may be the whole problem, is the setting for
OPTCOMPIND. Zero tells the optimizer to prefer nested loop joins and
indexed scans over table scans and hash table joins. This is an appropriate
setting in general for an OLTP system. If your system is more of a DSS
server or Data Warehouse server then you should be setting OPTCOMIND to 2
(prefer table scans and hash joins). If it is handling mixed loads you can
either try setting OPTCOMPIND to 1 (let costs decide the join method) or add
an optimizer hint to those queries that are choosing an inappropriate join
method:
SELECT {+ USE_HASH} ....
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Wed, Nov 18, 2009 at 7:39 AM, Celso Cabral Coimbra <
ccoimbra@cleartech.com.br> wrote:
> Art,
>
> SO: Solaris 9 64 bits
> IDS 11.50.FC4
> OPTCOMPIND=0
> Regarding to PDQPRIORITY, running update statistcs high, doesn't matter if
> I
> don't use PDQ or pdqpriority high.
>
> The test we develop, we ran the follow update statistcs
> update statistcs low drop distribution
> update statistcs medium in fields that compose indexes but not in the head
> update statistcs high in the head of indexes.
>
> Celso Cabral Coimbra
> Administrador de Banco de Dados
> ClearTech Ltda
> "Trust at the heart of Communications"
> Tel. (11) 3576-4509
>
> -----Mensagem original-----
> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art
> Kagel
> Enviada em: terça-feira, 17 de novembro de 2009 16:32
> Para: ids@iiug.org
> Assunto: Re: Doubt in Update statistics, resolution and.... [18139]
>
> What do you have OPTCOMPIND set to? Are you using PDQPRIORITY > 0?
> PRQPRIORITY > 1? What are your IDS version and platform?
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. 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 Tue, Nov 17, 2009 at 11:08 AM, Celso Cabral Coimbra <
> ccoimbra@cleartech.com.br> wrote:
>
> > Hi all,
> >
> > We are having some performance issues running queries in a huge table,
> > we did some tests.
> > When we run update statistics with distributions the performance is
> > worse than without distribution using the default parameters of
> > resolution and confidence.
> > We need distribution for the optimizer use light scans. How can I set up
> > those parameters, resolutions and confidence for huge tables (2,5
> > billion records, 53,125,000 16K pages or 870GB)
> >
> > Row size = 328 bytes
> > Number of records = 2.526.113.375
> > Number of 16k pages = 53,125,000
> > Size in GB = 870
> > Fragment by round robin in 17 dbspaces
> > 3 Indexes detached
> >
> > Thanks in advance,
> >
> > Celso Cabral Coimbra
> > Administrador de Banco de Dados
> > ClearTech Ltda
> > "Trust at the heart of Communications"
> > Tel. (11) 3576-4509
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0015174c3a6af4495204789556ff
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0ce02676ec7d400478a72db8
oopps... sorry.. I not read with attention the message ... after the Art answer I see.. the problem isn't in the execution of update statistics ... the issues is in the query *after* update statistics... my bad.. About the original question, I don't dare to answer, for this probably a few tests will need or someone like Art (with knowkledge of the optimizer) Just, complementing the Art post... Can use the SET ENVIRONMENT OPTCOMPIND "2" for the session... Directive {+FULL} to force a full sequencial scan (and probably will include the light scan).. but to have the expected effect, this depend of the query filters and joins... I not sure if force a sequencial scan will force a light scan as you which... maybe Art can answer this. Cesar