Creating SQL Tuning Sets
In this recipe, we will see how we can store a group of SQL statements along with their execution context and statistics, obtaining a so-called SQL Tuning Set.
Getting ready
To create a SQL Tuning Set, we need the ADMINISTER SQL TUNING SET
privilege, so we grant this privilege to SH
user, which will be used in this recipe.
CONNECT / AS SYSDBA GRANT ADMINISTER SQL TUNING SET TO sh;
How to do it...
The following steps will demonstrate how to create and use SQL Tuning Sets:
- Connect to
SH
schema:CONNECT sh@TESTDB/sh
- Execute some queries to populate the cursor cache:
SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY FROM CUSTOMERS ORDER BY CUST_CITY; SELECT * FROM ( SELECT CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH ...
Get Oracle Database 11gR2 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.