on 11-05-2014 8:38 PM
Hi Gurus,
Can someone please advice the SQL command to know the storage quality of a particular index
The storage quality can be known from program "RSORATAD" and DB02 but wanted to know the SQL statement for the same which can be ran from SQL prompt
Thanks in advance
R..../-
Hi
You could use script Space_SegmentFragmentationAndChainedRows_DBMS_SPACE_CommandGenerator.txt from note 1438410 - SQL script collection for Oracle
It can be used to generate a script to analyze storage quality for a set of segments.
Just update the script header to select the segments for which you want to generate the analysis scripts.
You can then run that script to get your result.
Regards
For instance if you want to analyze all segments related to FAGLFLEXA
WITH BASIS_INFO AS
( SELECT
'SAP%' OWNER,
'FAGLFLEXA%' SEGMENT_NAME, /* Name of segment or name pattern */
'%' PARTITION_NAME,
'%' SEGMENT_TYPE, /* TABLE, TABLE PARTITION, INDEX, INDEX PARTITION, LOBSEGMENT */
-1 MIN_SIZE_MB,
'UNUSED' SORT_BY /* UNUSED, QUALITY, SEGMENT, CHAINED_ROWS */
FROM
DUAL
),
OWNER SEGMENT_NAME P SEGME UNUSED_MB QUALITY_% GROSS_MB NET_MB CHAIN_%
SAPSR3 FAGLFLEXA~Z01 INDEX 9.82 96.39 272.00 262.18 0
SAPSR3 FAGLFLEXA~2 INDEX 9.60 96.92 312.00 302.40 0
SAPSR3 FAGLFLEXA~6 INDEX 8.27 96.87 264.00 255.73 0
SAPSR3 FAGLFLEXA~4 INDEX 7.90 97.01 264.00 256.10 0
SAPSR3 FAGLFLEXA~3 INDEX 6.18 99.09 680.00 673.82 0
SAPSR3 FAGLFLEXA TABLE 6.13 99.52 1280.00 1273.87 0
SAPSR3 FAGLFLEXA~0 INDEX 5.70 99.09 624.00 618.30 0
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.