This example uses multiple query logging requests to log and retrieve request information.
Create the following table in user_1:
CREATE TABLE employee ( emp_code INTEGER, emp_name VARCHAR(30), dept_code INTEGER, salary FLOAT);
Assume the employee table is populated with 30,000 rows and statistics have been collected on column dept_code using this request:
COLLECT STATISTICS COLUMN (dept_code) ON employee;
Log queries in XML format:
BEGIN QUERY LOGGING WITH VERBOSE XMLPLAN ON user_1;
Log Object, SQL, Step Details, and Verbose EXPLAIN text into the plan data for this request:
SELECT * FROM employee WHERE dept_name = 'sys_mgt';
This request returns verbose and XMLPLAN rules as being TRUE:
SHOW QUERY LOGGING ON user_1;
Replace the existing BEGIN QUERY LOGGING rule and collect detailed statistics usage data in XML format on user_1:
REPLACE QUERY LOGGING WITH DETAILED STATSUSAGE ON user_1;
Log Object, SQL, Step Details, and Statistics Usage information into the plan data for this request:
SELECT * FROM employee WHERE dept_name = 'testing';
This request returns Detailed Statistics and Statistics Usage rules as being TRUE:
SHOW QUERY LOGGING ON user_1;
Terminate the logging on user_1:
END QUERY LOGGING ON user_1;