By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

How I can get statistics by indexes using?

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"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
Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

P: n/a
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.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.