Oracle® Business Intelligence Server Administration Guide > Administering the Oracle BI Server Query Environment > Administering Usage Tracking >
Setting Up Direct Insertion to Collect Information for Usage Tracking
This is the recommended method for setting up usage tracking.
To set up direct insertion for usage tracking, use the guidelines in this section. For more information, refer to Oracle Business Intelligence Infrastructure Installation and Configuration Guide. To set up and administer direct insertion, use the following topics:
Enabling Direct Insertion
In the Usage Tracking section of the NQSConfig.INI file, the DIRECT_INSERT parameter determines whether the query statistics are inserted directly into a database table or are written to a file for subsequent loading. The DIRECT_INSERT and ENABLE parameters must be set to YES to enable direct insertion.
NOTE: It is strongly recommended that you enable direct insertion.
Database Table Configuration
Inserting query statistic information into a table requires the configuration of the name of the table and the connection pool used to access the table.
The fully qualified physical table name consists of up to four components (database name, catalog name, schema name, and table name). Each component is surrounded by double quotes (") and separated by a period (.). The physical table name must be fully qualified. This fully qualified physical table name must match a table name in the physical layer of the loaded repository. The following is an example of a physical table name for the Usage Tracking table in the Oracle BI repository:
PHYSICAL_TABLE_NAME = "Oracle BI Usage"."Catalog"."dbo"."S_NQ_ACCT" ;
In this example, Oracle BI Usage represents the database component, Catalog represents the catalog component, dbo represents the schema component, and S_NQ_ACCT represents the table name.
Connection Pool Configuration
The fully-specified connection pool name has two parts, database name and connection pool name. Each part is surrounded by double quotes (") and separated by a period (.). The fully qualified connection pool name should match a connection pool name in the physical layer of the loaded repository. For an example, refer to the following connection pool name in the Oracle BI repository:
CONNECTION_POOL = "Oracle BI Usage"."Connection Pool" ;
In this example, Oracle BI Usage represents the database component and Connection Pool represents the connection pool name proper.
For Usage Tracking inserts to succeed, the connection pool must be configured with a user ID that has write access to the back-end database.
NOTE: It is recommended that the connectivity type supports international data.
Buffer Size Configuration Parameter
The BUFFER_SIZE configuration parameter indicates how much memory the Oracle BI Server should allocate for buffering the insert statements. Such a buffer allows the Oracle BI Server to submit multiple insert statements as part of a single transaction, improving Usage Tracking insert throughput. It also means that ordinary query requests do not have to wait on Usage Tracking inserts, improving average query response time. You may want to adjust this value based on available memory and memory utilization on the server machine.
Buffer Time Limit Configuration Parameter
The BUFFER_TIME_LIMIT_SECONDS configuration parameter indicates the maximum amount of time an insert statement will remain in the buffer before the Usage Tracking subsystem attempts to issue it. This time limit ensures that the Oracle BI Server will issue the insert statements in a timely manner even during periods of extended quiescence.
Number of Insert Threads Configuration Parameter
The NUM_INSERT_THREADS configuration parameter indicates the number of threads that will remove insert statements from the buffer and issue them to the Usage Tracking database. Assuming separate connection pools for readers and inserters, the number of insert threads should typically equal the Maximum Connections setting in the connection pool.
Max Inserts Per Transactions Configuration Parameter
The MAX_INSERTS_PER_TRANSACTION configuration parameter indicates the maximum number of insert statements the Usage Tracking subsystem attempts to issue as part of a single transaction. The larger this number, the greater potential throughput for Usage Tracking inserts. However a larger number also increases the likelihood of transactions failing due to deadlocks. Note that a small value for BUFFER_TIME_LIMIT_SECONDS may limit the number of inserts per transaction.