On Tue, 09 Nov 2004 07:32:27 GMT, Jonathan Leffler
<jleffler@earthlink.net> wrote:

>superboer wrote:
>> select
>> {+ ORDERED }
>> pg_partnum + pg_pagenum - 1 partn
>> from
>> sysdbspaces a,
>> syspaghdr
>> where pg_partnum = 1048576 * a.dbsnum + 1
>> and pg_next != 0
>> into temp pp with no log
>>
>> otherwise you will read each and every page in your instance and this
>> may give you grey hair whenever it is finished dependant on
>> how big your instance is...
>
>Without wishing in any way to detract from what Superboer wrote...
>
>Vineet asked this question during the outage at the IIUG, and I
>responded on one of the IIUG mailing lists with the attached Perl script.
>
>The poor performing query was doing a sequential scan on the temp
>table created by the query - and that it is conceivable that either an
>index or an update statistics (or both) on the temp table would
>improve performance.
>
>The output of 'oncheck -pT' contains the information about IPAs (in
>amongst a lot of other information). The attached Perl script (which
>has been tested with Perl 5.5.3, 5.6.1, 5.8.5 on output from IDS 7.31,
>9.30, 9.40 and 9.50 - fragmented and non-fragmented tables) diagnoses
>partitions (fragments) of tables with outstanding IPAs quite handily.
> (Vineet expressed satisfaction with it.)
>
>I sent the message to software@iiug.org for inclusion in the IIUG
>Software Archive, but it has not made it there yet -- I suspect the
>team is still a bit busy recovering from the carnage.
>

And many thanks for the volunteer effort . . . sometimes it's easy to
forget . . .

JWC