This chapter describes how to use the CTX_REPORT package to create reports on indexing and querying. These reports can help you troubleshoot problems or fine-tune your applications.
This chapter contains the following topics:
For an overview of the CTX_REPORT package and how you can use the various procedures described here, see the Oracle Text Application Developer's Guide.
The CTX_REPORT package contains the following procedures:
| Name | Description |
|---|---|
| DESCRIBE_INDEX | Creates a report describing the index. |
| DESCRIBE_POLICY | Creates a report describing a policy. |
| CREATE_INDEX_SCRIPT | Creates a SQL*Plus script to duplicate the named index. |
| CREATE_POLICY_SCRIPT | Creates a SQL*Plus script to duplicate the named policy. |
| INDEX_SIZE | Creates a report to show the internal objects of an index, their tablespaces and used sizes. |
| INDEX_STATS | Creates a report to show the various statistics of an index. |
| QUERY_LOG_SUMMARY | Creates a report showing query statistics |
| TOKEN_INFO | Creates a report showing the information for a token, decoded. |
| TOKEN_TYPE | Translates a name and returns a numeric token type. |
Some of the procedures in the CTX_REPORT package have function versions. You can call these functions as follows:
select ctx_report.describe_index('MYINDEX') from dual;
In SQL*Plus, to generate an output file to send to support, you can do:
set long 64000
set pages 0
set heading off
set feedback off
spool outputfile
select ctx_report.describe_index('MYINDEX') from dual;
spool off
Creates a report describing the index. This includes the settings of the index metadata, the indexing objects used, the settings of the attributes of the objects, and index partition descriptions, if any.
You can call this operation as a procedure with an IN OUT CLOB parameter or as a function that returns the report as a CLOB.
procedure CTX_REPORT.DESCRIBE_INDEX( index_name IN VARCHAR2, report IN OUT NOCOPY CLOB, report_format IN VARCHAR2 DEFAULT FMT_TEXT ); function CTX_REPORT.DESCRIBE_INDEX( index_name IN VARCHAR2, report_format IN VARCHAR2 DEFAULT FMT_TEXT ) return CLOB;
Specify the name of the index to describe.
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.
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.
Creates a report describing the policy. This includes the settings of the policy metadata, the indexing objects used, the settings of the attributes of the objects.
You can call this operation as a procedure with an IN OUT CLOB parameter or as a function that returns the report as a CLOB.
procedure CTX_REPORT.DESCRIBE_POLICY( policy_name IN VARCHAR2, report IN OUT NOCOPY CLOB, report_format IN VARCHAR2 DEFAULT FMT_TEXT ); function CTX_REPORT.DESCRIBE_POLICY( policy_name IN VARCHAR2, report_format IN VARCHAR2 DEFAULT FMT_TEXT ) return CLOB;
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.
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.
Specify the name of the policy to describe
Creates a SQL*Plus script which will create a text index that duplicates the named text index.
The created script will include creation of preferences identical to those used in the named text index. However, the names of the preferences will be different.
You can call this operation as a procedure with an IN OUT CLOB parameter or as a function that returns the report as a CLOB.
procedure CTX_REPORT.CREATE_INDEX_SCRIPT( index_name in varchar2, report in out nocopy clob, prefname_prefix in varchar2 default null ); function CTX_REPORT.CREATE_INDEX_SCRIPT( index_name in varchar2, prefname_prefix in varchar2 default null ) return clob;
Specify the name of the index.
Specify the CLOB locator to which to write the script.
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.
Specify optional prefix to use for preference names.
If prefname_prefix is omitted or NULL, index name will be used. The prefname_prefix follows index length restrictions.
Creates a SQL*Plus script which will create a text policy that duplicates the named text policy.
The created script will include creation of preferences identical to those used in the named text policy.
You can call this operation as a procedure with an IN OUT CLOB parameter or as a function that returns the report as a CLOB.
procedure CTX_REPORT.CREATE_POLICY_SCRIPT( policy_name in varchar2, report in out nocopy clob, prefname_prefix in varchar2 default null ); function CTX_REPORT.CREATE_POLICY_SCRIPT( policy_name in varchar2, prefname_prefix in varchar2 default null ) return clob;
Specify the name of the policy.
Specify the locator to which to write the script.
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.
Specify the optional prefix to use for preference names. If prefname_prefix is omitted or NULL, policy name will be used. prefname_prefix follows policy length restrictions.
Creates a report showing the internal objects of the text index or text index partition, and their tablespaces, allocated, and used sizes.
You can call this operation as a procedure with an IN OUT CLOB parameter, or as a function that returns the report as a CLOB.
procedure CTX_REPORT.INDEX_SIZE( index_name IN VARCHAR2, report IN OUT NOCOPY CLOB, part_name IN VARCHAR2 DEFAULT NULL, report_format IN VARCHAR2 DEFAULT FMT_TEXT ); function CTX_REPORT.INDEX_SIZE( index_name IN VARCHAR2, part_name IN VARCHAR2 DEFAULT NULL, report_format IN VARCHAR2 DEFAULT FMT_TEXT ) return clob;
Specify the name of the index to describe
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
Specify the name of the index partition (optional). If part_name is NULL, and the index is a local partitioned text index, then all objects of all partitions will be displayed. If part_name is provided, then only the objects of a particular partition will be displayed.
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.
Creates a report showing various calculated statistics about the text index.
This procedure will fully scan the text index tables, so it may take a long time to run for large indexes.
procedure 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 );
Specify the name of the index to describe. This must be a CONTEXT index.
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.
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.
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.
Specify the number of elements in each compiled list. list_size has a maximum value of 1000.
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.
Here's an example of using 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 is sample output 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 34
The 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 %
Obtain a report of logged queries.
QUERY_LOG_SUMMARY enables you to analyze queries you have logged. For example, suppose you have an application that searches a database of large animals, and your analysis of queries against it shows that users are continually searching for the word mouse; this analysis might induce you to rewrite your application so that a search for mouse redirects the user to a database for small animals instead of simply returning an unsuccessful search.
With query analysis, you can find out
which queries were made
which queries were successful
which queries were unsuccessful
how many times each query was made
You can combine these factors in various ways, such as determining the 50 most frequent unsuccessful queries made by your application.
Query logging is begun with CTX_OUTPUT.START_QUERY_LOG and terminated with CTX_OUTPUT.END_QUERY_LOG.
|
Note: You must connect asCTXSYS to use CTX_REPORT.QUERY_LOG_SUMMARY. |
procedure CTX_REPORT.QUERY_LOG_SUMMARY( logfile IN VARCHAR2, indexname IN VARCHAR2 DEFAULT NULL, result_table IN OUT NOCOPY QUERY_TABLE, row_num IN NUMBER, most_freq IN BOOLEAN DEFAULT TRUE, has_hit IN BOOLEAN DEFAULT TRUE );
Specify the name of the logfile that contains the queries.
Specify the name of the context index for which you want the summary report. If you specify NULL, the procedure provides a summary report for all context indexes.
Specify the name of the in-memory table of type TABLE OF RECORD where the results of the QUERY_LOG_SUMMARY are to go. The default is the location specified by the system parameter LOG_DIRECTORY.
The number of rows of results from QUERY_LOG_SUMMARY to be reported into the table named by restab. For example, if this is number is 10, most_freq is TRUE, and has_hit is TRUE, then the procedure returns the 10 most frequent queries that were successful (that is, returned hits).
Specify whether QUERY_LOG_SUMMARY should return the most frequent or least frequent queries. The default is most frequent queries. If most_freq is set to FALSE, the procedure returns the least successful queries.
Specify whether QUERY_LOG_SUMMARY should return queries that are successful (that is, that generate hits) or unsuccessful queries. The default is to count successful queries; set has_hit to FALSE to return unsuccessful queries.
The following example shows how a query log can be used.
First connect as CTXSYS. Then create and populate two tables, and then create an index for each:
create table qlogtab1 (tk number primary key, text varchar2(2000)); insert into qlogtab1 values(1, 'The Roman name for France was Gaul.'); insert into qlogtab1 values(2, 'The Tour de France is held each summer.'); insert into qlogtab1 values(3, 'Jacques Anatole Thibault took the pen name Anatole France.'); create index idx_qlog1 on qlogtab1(text) indextype is ctxsys.context; create table qlogtab2 (tk number primary key, text varchar2(2000)); insert into qlogtab2 values(1, 'The Great Wall of China is about 2400 kilometers long'); insert into qlogtab2 values(2, 'Soccer dates back at least to 217 C.E.'); insert into qlogtab2 values(3, 'The Corn Palace is a tourist attraction in South Dakota.'); create index idx_qlog2 on qlogtab2(text) indextype is ctxsys.context;
Turn on query logging, creating a log called query_log:
exec ctx_output.start_query_log('query.log');
Now make some queries (some of which will be unsuccessful):
select text from qlogtab1 where contains(text, 'France',1)>0; select text from qlogtab1 where contains(text, 'cheese',1)>0; select text from qlogtab1 where contains(text, 'Text Wizard',1)>0; select text from qlogtab2 where contains(text, 'Corn Palace',1)>0; select text from qlogtab2 where contains(text, 'China',1)>0; select text from qlogtab1 where contains(text, 'Text Wizards',1)>0; select text from qlogtab2 where contains(text, 'South Dakota',1)>0; select text from qlogtab1 where contains(text, 'Text Wizard',1)>0; select text from qlogtab2 where contains(text, 'China',1)>0; select text from qlogtab1 where contains(text, 'Text Wizard',1)>0; select text from qlogtab2 where contains(text, 'company',1)>0; select text from qlogtab1 where contains(text, 'Text Wizard',1)>0; select text from qlogtab1 where contains(text, 'France',1)>0; select text from qlogtab1 where contains(text, 'database',1)>0; select text from qlogtab2 where contains(text, 'high-tech',1)>0; select text from qlogtab1 where contains(text, 'database',1)>0; select text from qlogtab1 where contains(text, 'France',1)>0; select text from qlogtab1 where contains(text, 'Japan',1)>0; select text from qlogtab1 where contains(text, 'Egypt',1)>0; select text from qlogtab1 where contains(text, 'Argentina',1)>0; select text from qlogtab1 where contains(text, 'Argentina',1)>0; select text from qlogtab1 where contains(text, 'Argentina',1)>0; select text from qlogtab1 where contains(text, 'Japan',1)>0; select text from qlogtab1 where contains(text, 'Egypt',1)>0; select text from qlogtab1 where contains(text, 'Air Shuttle',1)>0; select text from qlogtab1 where contains(text, 'Argentina',1)>0;
With the querying over, turn query logging off:
exec ctx_output.end_query_log;
Use QUERY_LOG_SUMMARY to get query reports. In the first instance, you ask to see the three most frequent queries that return successfully. First declare the results table (the_queries).
set serveroutput on;
declare
the_queries ctx_report.query_table;
begin
ctx_report.query_log_summary('query.log', null, the_queries,
row_num=>3, most_freq=>TRUE, has_hit=>TRUE);
dbms_output.put_line('The 3 most frequent queries returning hits');
dbms_output.put_line('number of times query string');
for i in 1..the_queries.count loop
dbms_output.put_line(the_queries(i).times||' '||the_queries(i).query);
end loop;
end;
/
This returns the following:
TThe 3 most frequent queries returning hits number of times query string 3 France 2 China 1 Corn Palace
Next, look for the three most frequent queries on idx_qlog1 that were successful.
declare
the_queries ctx_report.query_table;
begin
ctx_report.query_log_summary('query.log', 'idx_qlog1', the_queries,
row_num=>3, most_freq=>TRUE, has_hit=>TRUE);
dbms_output.put_line('The 3 most frequent queries returning hits for index idx_qlog1');
dbms_output.put_line('number of times query string');
for i in 1..the_queries.count loop
dbms_output.put_line(the_queries(i).times||' '||the_queries(i).query);
end loop;
end;
/
Because only the queries for France were successful, ctx_report.query_log_summary returns the following:
The 3 most frequent queries returning hits for index idx_qlog1 number of times query string 3 France
Lastly, ask to see the three least frequent queries that returned no hits (that is, queries that were unsuccessful and called infrequently). In this case, you are interested in queries on both context indexes, so you set the indexname parameter to NULL.
declare
the_queries ctx_report.query_table;
begin
ctx_report.query_log_summary('query.log', null, the_queries, row_num=>3,
most_freq=>FALSE, has_hit=>FALSE);
dbms_output.put_line('The 3 least frequent queries returning no hit');
dbms_output.put_line('number of times query string');
for i in 1..the_queries.count loop
dbms_output.put_line(the_queries(i).times||' '||the_queries(i).query);
end loop;
end;
/
This returns the following:
The 3 least frequent queries returning no hit number of times query string 1 high-tech 1 company 1 cheese
Argentina and Japan do not make this list, because they are queried more than once, while Corn Palace does not make this list because it is successfully queried.
Creates a report showing the information for a token, decoded. This procedure will fully scan the info for a token, so it may take a long time to run for really large tokens.
You can call this operation as a procedure with an IN OUT CLOB parameter or as a function that returns the report as a CLOB.
procedure CTX_REPORT.TOKEN_INFO( index_name IN VARCHAR2, report IN OUT NOCOPY CLOB, token IN VARCHAR2, token_type IN NUMBER, part_name IN VARCHAR2 DEFAULT NULL, raw_info IN BOOLEAN DEFAULT FALSE, decoded_info IN BOOLEAN DEFAULT TRUE, report_format IN VARCHAR2 DEFAULT FMT_TEXT ); function CTX_REPORT.TOKEN_INFO( index_name IN VARCHAR2, token IN VARCHAR2, token_type IN NUMBER, part_name IN VARCHAR2 DEFAULT NULL, raw_info IN VARCHAR2 DEFAULT 'N', decoded_info IN VARCHAR2 DEFAULT 'Y', report_format IN VARCHAR2 DEFAULT FMT_TEXT ) return clob;
Specify the name of the index.
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 token may be case-sensitive, depending on the passed-in token type.
Specify the token text.
Specify the token type. You can use a number returned by the TOKEN_TYPE function. THEME, ZONE, ATTR, PATH, and PATH ATTR tokens are case-sensitive.
Everything else gets passed through the lexer, so if the index's lexer is case-sensitive, the token input is case-sensitive.
Specify the name of the index partition.
If the index is a local partitioned index, then part_name must be provided. TOKEN_INFO will apply to just that index partition.
Specify TRUE to include a hex dump of the index data. If raw_info is TRUE, the report will include a hex dump of the raw data in the token_info column.
Specify decode and include docid and offset data. If decoded_info is FALSE, CTX_REPORT will not attempt to decode the token information. This is useful when you just want a dump of data.
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.
This is a helper function which translates an English name into a numeric token type. This is suitable for use with token_info, or any other CTX API which takes in a token_type.
function token_type( index_name in varchar2, type_name in varchar2 ) return number; TOKEN_TYPE_TEXT constant number := 0; TOKEN_TYPE_THEME constant number := 1; TOKEN_TYPE_ZONE_SEC constant number := 2; TOKEN_TYPE_ORIG constant number := 3, TOKEN_TYPE_ATTR_TEXT constant number := 4; TOKEN_TYPE_ATTR_SEC constant number := 5; TOKEN_TYPE_PREFIX constant number := 6; TOKEN_TYPE_PATH_SEC constant number := 7; TOKEN_TYPE_PATH_ATTR constant number := 8; TOKEN_TYPE_STEM constant number := 9;
Specify the name of the index.
Specify an English name for token_type. The following strings are legal input. All input is case-insensitive.
| Input | Meaning | Type Returned |
|---|---|---|
| TEXT | Normal text token. | 0 |
| THEME | Theme token. | 1 |
| ZONE SEC | Zone token. | 2 |
| ORIGINAL | Original form token | 3 |
| ATTR TEXT | Text that occurs in attribute. | 4 |
| ATTR SEC | Attribute section. | 5 |
| PREFIX | Prefix token. | 6 |
| PATH SEC | Path section. | 7 |
| PATH ATTR | Path attribute section. | 8 |
| STEM | Stem form token. | 9 |
| FIELD <name> TEXT | Text token in field section <name> | 16-79 |
| FIELD <name> PREFIX | Prefix token in field section <name> | 616-916 |
| FIELD <name> STEM | Stem token in field section <name> | 916-979 |
| TOKEN_TYPE_ATTR_TXT_PFIX | Attribute text prefix. | 604 |
| TOKEN_TYPE_ATTR_TXT_STEM | Attribute text stem. | 904 |
For FIELD types, the index metadata needs to be read, so if you are going to be calling this a lot for such things, you might want to consider caching the values in local variables rather than calling token_type over and over again.
The constant types (0 - 9) also have constants in this package defined.
To get token types for MDATA tokens, do not use CTX_REPORT.TOKEN_TYPE; use the MDATA operator instead. (See "MDATA".) The syntax to use is 'MDATA fieldname'.
typenum := ctx_report.token_type('myindex', 'field author text');