Databases Reference
In-Depth Information
How to do it...
The following steps will show how to configure and use SQL Baselines:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create a table
MY_CUSTOMERS
with some test data:
CREATE TABLE sh.MY_CUSTOMERS AS
SELECT * FROM sh.CUSTOMERS NOLOGGING;
3.
Obtain a column
CUST_VALID
containing skewed values (about 1 percent of rows
contain value
'I'
, others containing value
'A'
:
UPDATE sh.MY_CUSTOMERS SET CUST_VALID = 'I'
WHERE CUST_VALID = 'A' AND MOD(CUST_ID,100) <> 0;
COMMIT;
4.
Execute the query over the
MY_CUSTOMERS
data for which we want plan stability:
SELECT /* TEST */ COUNT(*) FROM sh.MY_CUSTOMERS
WHERE CUST_VALID = 'I';
Capture a baseline from the cursor cache for the previous query:
DECLARE
l_sqlid VARCHAR2(13);
l_plan PLS_INTEGER;
BEGIN
SELECT SQL_ID INTO l_sqlid FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT /* TEST */%';
l_plan := dbms_spm.load_plans_from_cursor_cache(
sql_id => l_sqlid);
END;
/
5.
Create an index on the
CUST_VALID
field:
CREATE INDEX MY_CUSTOMERS_IX1 ON sh.MY_CUSTOMERS (CUST_VALID);
6.
Execute the same query in step 4:
SELECT /* TEST */ COUNT(*) FROM sh.MY_CUSTOMERS
WHERE CUST_VALID = 'I';
