How I can get statistics by indexes using?

[bughunter]

For example - very large table with a lot indexes and updates work very
slow. I'd like found unused or rarely used indexes. How? Yes, for
static I found all sql in packages and, after analyse, drop/recreate
some indexes. But for dynamic I have no idea. Only drop all and monitor
RR :-) Inhumanely, IMHO

Andy

[Rhino]

"bughunter@ru" <a.********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

For example - very large table with a lot indexes and updates work very
slow. I'd like found unused or rarely used indexes. How? Yes, for
static I found all sql in packages and, after analyse, drop/recreate
some indexes. But for dynamic I have no idea. Only drop all and monitor
RR :-) Inhumanely, IMHO

Dynamic SQL is, in effect, SQL that is generated live at the moment the
query is being run. For instance, if you go to a command line and write a
SELECT, INSERT, UPDATE, or DELETE statement, then press Enter, you are doing
dynamic SQL.

I don't see how you can possibly anticipate which indexes dynamic queries
will need before you've written them.

If you have somehow kept a large proportion of the dynamic SQL that you and
your users have written in the past, you might be able to analyze it and see
patterns that would tell you what indexes are likely to be used by those
queries, assuming that the queries written in the future are similar to the
ones written in the past. But dynamic SQL queries are often not saved and,
even if they are, future queries might differ substantially from queries in
the past. After all, if a dynamic query starts getting used heavily, it is
probably a good candidate to be made into static SQL, perhaps in an
application or stored procedure. Therefore, the most popular dynamic queries
probably become static queries over time and then dynamic queries will do
different things than they did when the database was newer.

It may be best to handle this problem reactively, i.e. drop all indexes that
aren't heavily used by your static queries and then wait for the users to
start screaming about dynamic queries that are suddenly much slower. Those
queries will give you big clues about where you need additional indexes - or
where dynamic queries should be made static by putting them in applications
or stored procedures.

Just my two cents worth....

Rhino

[Pierre Saint-Jacques]

I hope you are using DB2 UDB V8.
Fromm your production environment, you can define a larger package cache
size to run your snapshots.
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Default - Siz in pages.
Update your db cfg to kmake it larger.
Let your system run for a while.
The package cache will hold as much as it can of the most recent dynamic and
static SQL stmts executed.
Then issue: db2 get snapshot for dynamic sql on <dbname>
Using that Snaphot info and DB2 Design Advisor, you can capture the sql and
get advisor to recommend/evaluate indexes.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"bughunter@ru" <a.********@gmail.com> a écrit dans le message de news:
11**********************@g44g2000cwa.googlegroups. com...

For example - very large table with a lot indexes and updates work very
slow. I'd like found unused or rarely used indexes. How? Yes, for
static I found all sql in packages and, after analyse, drop/recreate
some indexes. But for dynamic I have no idea. Only drop all and monitor
RR :-) Inhumanely, IMHO

Andy

[Phil Sherman]

I was told in an IBM class some time ago that UDB will store a maximum
of ONE dynamic query that DOES NOT USE PARAMETER MARKERS in the cache.

Is this still a valid statement?

Phil Sherman

Pierre Saint-Jacques wrote:

I hope you are using DB2 UDB V8.
Fromm your production environment, you can define a larger package cache
size to run your snapshots.
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Default - Siz in pages.
Update your db cfg to kmake it larger.
Let your system run for a while.
The package cache will hold as much as it can of the most recent dynamic
and static SQL stmts executed.
Then issue: db2 get snapshot for dynamic sql on <dbname>
Using that Snaphot info and DB2 Design Advisor, you can capture the sql
and get advisor to recommend/evaluate indexes.
HTH, Pierre.

[Serge Rielau]

Phil Sherman wrote:

I was told in an IBM class some time ago that UDB will store a maximum
of ONE dynamic query that DOES NOT USE PARAMETER MARKERS in the cache.
Is this still a valid statement?

No. Parameter markers are of no meaning to the cache. Given that many
apps don't use parameter markers (unfortunately) keeping only one in the
cache would wreck performance. I don't see any upsaide to such a
behaviour, actually.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[bughunter]

with static - yes. and I already do it. Drop all indexes on
productive... On very large table... Me will dismiss without the
severance pay :-)

Package cache snapshot for some cases very useful. But for tables
accessed through numerous views it will not help me - no pattern for
search. Everything, that is necessary for me, is the elementary
counter, how many times the index has been used. Exactly also me does
not suffice for the analysis of the elementary counter on RR from the
concrete table.

M.b. IBM can create new monitor type - snapshot monitor for table?
Which will be collect statistics like rr/rw, indexes use.