INDEX_STATS
Creates a report showing various calculated statistics about the text index.
This procedure fully scans the text index tables, so it may take a long time to run for large indexes.
Syntax
procedure ctx_report.index_stats(
index_name IN VARCHAR2,
report IN OUT NOCOPY CLOB,
part_name IN VARCHAR2 DEFAULT NULL,
frag_stats IN BOOLEAN DEFAULT TRUE,
list_size IN NUMBER DEFAULT 100,
report_format IN VARCHAR2 DEFAULT FMT_TEXT,
stat_type IN VARCHAR2 DEFAULT NULL
);index_name
Specify the name of the index to describe. This must be a CONTEXT index.
report
Specify the CLOB locator to which to write the report. If report is NULL, a session-duration temporary CLOB will be created and returned. It is the caller’s responsibility to free this temporary CLOB as needed.
The report CLOB will be truncated before report is generated, so any existing contents will be overwritten by this call.
part_name
Specify the name of the index partition. If the index is a local partitioned index, then part_name must be provided. INDEX_STATS will calculate the statistics for that index partition.
frag_stats
Specify TRUE to calculate fragmentation statistics. If frag_stats is FALSE, the report will not show any statistics relating to size of index data. However, the operation should take less time and resources to calculate the token statistics.
list_size
Specify the number of elements in each compiled list. list_size has a maximum value of
1000.
report_format
Specify whether the report should be generated as ‘TEXT’ or as ‘XML’. TEXT is the default. You can also specify the values CTX_REPORT.FMT_TEXT or CTX_REPORT.FMT_XML.
stat_type
Specify the estimated statistics to output. If this parameter is set, then frag_stats is ignored. The possible values are:
| Statistics Type | Description |
|---|---|
EST_FRAG_STATS |
Get the estimated fragmentation stats for the index. When this type is given, list_size is ignored. |
EST_FREQUENT_TOKENS |
Get the estimated frequently queried tokens for the index. You can give a value of up to 100 for list_size. |
EST_TOKENS_TO_OPTIMIZE |
Show best tokens to optimize, based on frequency of querying and fragmentation. You can give a value of up to 100 for list_size. |
EST_SLOWEST_QUERIES |
Show slowest running queries for the index. You can give a value of up to 100 for list_size. |
Note:
The estimated statistics for stat_type is only available if query_stats is enabled and the following privileges must be granted to the user running the report:
grant select, insert, delete, update on ctxsys.dr$slowqrys to <user>;
grant select, insert, delete, update on ctxsys.dr$freqtoks to <user>;Example for CTX_REPORT.INDEX_STATS
create table output (result CLOB);
declare
x clob := null;
begin
ctx_report.index_stats('tdrbprx21',x);
insert into output values (x);
commit;
dbms_lob.freetemporary(x);
end;
/
set long 32000
set head off
set pagesize 10000
select * from output;The following sample output is for INDEX_STATS on a context index. This report has been truncated for clarity. It shows some of the token statistics and all of the fragmentation statistics.
The fragmentation statistics are at the end of the report. It tells you optimal row fragmentation, an estimated amount of garbage data in the index, and a list of the most fragmented tokens. Running CTX_DDL.OPTIMIZE_INDEX cleans up the index.
=================================================================
STATISTICS FOR "DR_TEST"."TDRBPRX21"
=================================================================
indexed documents: 53
allocated docids: 68
$I rows: 16,259
-----------------------------------------------------------------
TOKEN STATISTICS
-----------------------------------------------------------------
unique tokens: 13,445
average $I rows for each token: 1.21
tokens with most $I rows:
telecommunications industry (THEME) 6
science and technology (THEME) 6
EMAIL (FIELD SECTION "SOURCE") 6
DEC (FIELD SECTION "TIMESTAMP") 6
electronic mail (THEME) 6
computer networking (THEME) 6
communications (THEME) 6
95 (FIELD SECTION "TIMESTAMP") 6
15 (FIELD SECTION "TIMESTAMP") 6
HEADLINE (ZONE SECTION) 6
average size for each token: 8
tokens with largest size:
T (NORMAL) 405
SAID (NORMAL) 313
HEADLINE (ZONE SECTION) 272
NEW (NORMAL) 267
I (NORMAL) 230
MILLION (PREFIX) 222
D (NORMAL) 219
MILLION (NORMAL) 215
U (NORMAL) 192
DEC (FIELD SECTION "TIMESTAMP") 186
average frequency for each token: 2.00
most frequent tokens:
HEADLINE (ZONE SECTION) 68
DEC (FIELD SECTION "TIMESTAMP") 62
95 (FIELD SECTION "TIMESTAMP") 62
15 (FIELD SECTION "TIMESTAMP") 62
T (NORMAL) 61
D (NORMAL) 59
881115 (THEME) 58
881115 (NORMAL) 58
I (NORMAL) 55
geography (THEME) 52
token statistics by type:
token type: NORMAL
unique tokens: 6,344
total rows: 7,631
average rows: 1.20
total size: 67,445 (65.86 KB)
average size: 11
average frequency: 2.33
most frequent tokens:
T 61
D 59
881115 58
I 55
SAID 45
C 43
NEW 36
MILLION 32
FIRST 28
COMPANY 27
token type: THEME
unique tokens: 4,563
total rows: 5,523
average rows: 1.21
total size: 21,930 (21.42 KB)
average size: 5
average frequency: 2.40
most frequent tokens:
881115 58
political geography 52
geography 52
United States 51
business and economics 50
abstract ideas and concepts 48
North America 48
science and technology 46
NKS 34
nulls 34The fragmentation portion of this report is as follows:
-----------------------------------------------------------------
FRAGMENTATION STATISTICS
-----------------------------------------------------------------
total size of $I data: 116,772 (114.04 KB)
$I rows: 16,259
estimated $I rows if optimal: 13,445
estimated row fragmentation: 17 %
garbage docids: 15
estimated garbage size: 21,379 (20.88 KB)
most fragmented tokens:
telecommunications industry (THEME) 83 %
science and technology (THEME) 83 %
EMAIL (FIELD SECTION "SOURCE") 83 %
DEC (FIELD SECTION "TIMESTAMP") 83 %
electronic mail (THEME) 83 %
computer networking (THEME) 83 %
communications (THEME) 83 %
95 (FIELD SECTION "TIMESTAMP") 83 %
HEADLINE (ZONE SECTION) 83 %
15 (FIELD SECTION "TIMESTAMP") 83 %Examples for CTX_REPORT.INDEX_STATS with STAT_TYPE
The following sample output is for EST_FRAG_STATS statistics type:
var report clob;
begin
dbms_lob.createtemporary(:report, true);
ctx_report.index_stats(
index_name => 'tdrbps.idx',
report => :report,
report_format => 'XML',
stat_type => 'EST_FRAG_STATS'
);
end;
/
select :report from dual;
:REPORT
------------------------------------------------------------------------------
<CTXREPORT>
<INDEX_STATS>
<STAT_INDEX_NAME>"TDRBPS"."IDX"</STAT_INDEX_NAME>
<STAT_INDEX_STATS>
<STAT_STATISTIC NAME="Estimated Fragmentation Stats">50</STAT_STATISTIC>
</STAT_INDEX_STATS>
</INDEX_STATS>
</CTXREPORT>The following sample output is for EST_FREQUENT_TOKENS statistics type:
begin
dbms_lob.createtemporary(:report, true);
ctx_report.index_stats(
index_name => 'tdrbps.idx',
report => :report,
report_format => 'XML',
stat_type => 'EST_FREQUENT_TOKENS'
);
end;
/
select :report from dual;
:REPORT
------------------------------------------------------------------------------
<CTXREPORT>
<INDEX_STATS>
<STAT_INDEX_NAME>"TDRBPS"."IDX"</STAT_INDEX_NAME>
<STAT_INDEX_STATS>
<STAT_TOKEN_LIST NAME="Most Frequently Queried Tokens">
<STAT_TOKEN>
<STAT_TOKEN_TEXT>ORACLE</STAT_TOKEN_TEXT>
<STAT_TOKEN_TYPE>0:TEXT</STAT_TOKEN_TYPE>
<STAT_TOKEN_STATISTIC>2</STAT_TOKEN_STATISTIC>
</STAT_TOKEN>
<STAT_TOKEN>
<STAT_TOKEN_TEXT>DATABASE</STAT_TOKEN_TEXT>
<STAT_TOKEN_TYPE>0:TEXT</STAT_TOKEN_TYPE>
<STAT_TOKEN_STATISTIC>1</STAT_TOKEN_STATISTIC>
</STAT_TOKEN>
</STAT_TOKEN_LIST>
</STAT_INDEX_STATS>
</INDEX_STATS>
</CTXREPORT>Note: <STAT_TOKEN_STATISTIC> shows the number of times a particular token was queried.
The following sample output is for EST_SLOWEST_QUERIES statistics type:
begin
dbms_lob.createtemporary(:report, true);
ctx_report.index_stats(
index_name => 'tdrbps.idx',
report => :report,
report_format => 'XML',
stat_type => 'EST_SLOWEST_QUERIES'
);
end;
/
select :report from dual;
:REPORT
------------------------------------------------------------------------------
<CTXREPORT>
<INDEX_STATS>
<STAT_INDEX_NAME>"TDRBPS"."IDX"</STAT_INDEX_NAME>
<STAT_INDEX_STATS>
<STAT_QUERY_LIST NAME="Slowest Queries">
<STAT_QUERY>
<STAT_QUERY_FULL>select count(*) from tbl where
contains(txt,'Oracle')>0</STAT_QUERY_FULL>
<STAT_QUERY_TEXT_PART>Oracle</STAT_QUERY_TEXT_PART>
<STAT_QUERY_TIME>114</STAT_QUERY_TIME>
<STAT_QUERY_HASH>2992140927</STAT_QUERY_HASH>
</STAT_QUERY>
<STAT_QUERY>
<STAT_QUERY_FULL>select count(*) from tbl where
contains(txt,'ora%')>0</STAT_QUERY_FULL>
<STAT_QUERY_TEXT_PART>ora%</STAT_QUERY_TEXT_PART>
<STAT_QUERY_TIME>4</STAT_QUERY_TIME>
<STAT_QUERY_HASH>2229259029</STAT_QUERY_HASH>
</STAT_QUERY>
<STAT_QUERY>
<STAT_QUERY_FULL>select count(*) from tbl where
contains(txt,'Database')>0</STAT_QUERY_FULL>
<STAT_QUERY_TEXT_PART>Database</STAT_QUERY_TEXT_PART>
<STAT_QUERY_TIME>2</STAT_QUERY_TIME>
<STAT_QUERY_HASH>1111113040</STAT_QUERY_HASH>
</STAT_QUERY>
</STAT_QUERY_LIST>
</STAT_INDEX_STATS>
</INDEX_STATS>
</CTXREPORT>Note:
-
contains the full query andcontains the Oracle TextCONTAINSclause of the query. -
contains query response times andcontains the hash values of the queries.
The following sample output is for EST_TOKENS_TO_OPTIMIZE statistics type:
begin
dbms_lob.createtemporary(:report, true);
ctx_report.index_stats(
index_name => 'tdrbps.idx',
report => :report,
report_format => 'XML',
stat_type => 'EST_TOKENS_TO_OPTIMIZE'
);
end;
/
select :report from dual;
:REPORT
------------------------------------------------------------------------------
<CTXREPORT>
<INDEX_STATS>
<STAT_INDEX_NAME>"TDRBPS"."IDX"</STAT_INDEX_NAME><STAT_INDEX_STATS>
<STAT_TOKEN_LIST NAME="Best Tokens To Optimize">
<STAT_TOKEN>
<STAT_TOKEN_TEXT>ORACLE</STAT_TOKEN_TEXT>
<STAT_TOKEN_TYPE>0:TEXT</STAT_TOKEN_TYPE>
<STAT_TOKEN_STATISTIC>100</STAT_TOKEN_STATISTIC>
</STAT_TOKEN>
<STAT_TOKEN>
<STAT_TOKEN_TEXT>DATABASE</STAT_TOKEN_TEXT>
<STAT_TOKEN_TYPE>0:TEXT</STAT_TOKEN_TYPE>
<STAT_TOKEN_STATISTIC>50</STAT_TOKEN_STATISTIC>
</STAT_TOKEN>
</STAT_TOKEN_LIST>
</STAT_INDEX_STATS>
</INDEX_STATS>
</CTXREPORT>Note: <STAT_TOKEN_STATISTIC> indicates the fragmentation of a particular token.
Notes
These metadata are available only when QUERY_STATS is turned on for the index: estimated fragmentation stats, estimated frequently queried tokens, estimated most fragmented frequently queried token, and estimated slowest running queries for the specified index.
CTX_REPORT.INDEX_STATS will also output information on dr$indexname$S table, which is the section data, or SDATA, table.
Related Topics