Re: System i Navigator: SQL Plan Cache Snapshots; using Visual Explain for Index Advisor, to improve QZDASOINIT SQL performance

[Jim Oberholtzer <midrangel@xxxxxxxxxx>]

Vern;

The SQE engine has all the improvements in performance and utilization 
of the database in it.  The old query engine, CQE, was stablized and 
does not have the performance the SQE does.  I had one customer running 
a Power 5 550 4 way box pushing three processors fairly hard.  Once we 
built indexes, and got the development staff to create the SQL 
(primarily from .Net applications) to allow the SQE to run the queries, 
we dropped back to using  1 sometimes 2 processors.  With an upgrade to 
V7 (from 5.4) they dropped to 50% of CPU on ONE processor.  Same 
workload on the system, just way more efficient.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 3/8/2012 7:44 AM, Vern Hamberg wrote:
> EVIs have been around at least since V5R1, can you say more about what
> matters about V5R4 and SQE? CQE also uses EVIs where it deems it
> appropriate.
>
> On 3/8/2012 7:10 AM, Jim Oberholtzer wrote:
> > >  It is V5R4 so be sure you are using the SQE instead of the CQE!.
> > >
> > >  Jim Oberholtzer
> > >  Chief Technical Architect
> > >  Agile Technology Architects
> > >
> > >
> > >  On 3/8/2012 6:26 AM, Vern Hamberg wrote:
> > > >>  Eric
> > > >>
> > > >>  Feel free to create the EVI indexes - they are useful for selectivity
> > > >>  and joining - they don't do anything for sorting. The optimizer uses
> > > >>  them for bitmap processing.
> > > >>
> > > >>  Vern
> > > >>
> > > >>  On 3/7/2012 5:30 PM,elehti@xxxxxxxxxxxxxxxxxx   wrote:
> > > > > >>>>     On our V5R4M5 system  (IBM i o POWER6), finally using System i
> > > > > >>>>     Navigator: SQL Plan Cache Snapshots; using Visual Explain for Index
> > > > > >>>>     Advisor.
> > > > > >>>>     When it recommends a Binary Radix index, I am creating one.
> > > > > >>>>     When it recommends an encoded vector index (EVI), I am not doing so yet,
> > > > > >>>>     but am researching the matter in the
> > > > > >>>>     IBM White Paper " IBM DB2 for I indexing methods and strategies - Learn
> > > > > >>>>     how to use DB2 indexes to boost performance"
> > > > > >>>>     Excellent.
> > > > > >>>>
> > > > > >>>>     This white paper lays the foundation for an indexing strategy and design
> > > > > >>>>     that delivers high-performance queries and SQL applications on IBM DB2
> > > > > >>>>     for i. Both, programmers and database administrators can find
> > > > > >>>>     information on indexing to make their jobs easier and improve the
> > > > > >>>>     performance of their DB2 for i servers. This in-depth discussion on DB2
> > > > > >>>>     for i indexing includes a description of the technology along with
> > > > > >>>>     coverage of the DB2 performance tools available to assist with index
> > > > > >>>>     analysis and SQL performance tuning.
> > > > > >>>>
> > > >>  -- 
> --