A working example
Many ad-hoc examples found on Web sites, blogs,
and other books tend to use the
ALL_OBJECTS data dictionary view
as a source for generating test data. Therefore,
for the sake of consistency, so will this
example.
As mentioned, the “tool” is the
DBMS_SQLPA package. Its
subprograms are shown below.
CANCEL_ANALYSIS_TASK
CREATE_ANALYSIS_TASK
DROP_ANALYSIS_TASK
EXECUTE_ANALYSIS_TASK
INTERRUPT_ANALYSIS_TASK
REPORT_ANALYSIS_TASK
RESET_ANALYSIS_TASK
RESUME_ANALYSIS_TASK
SET_ANALYSIS_TASK_PARAMETER
SET_ANALYSIS_DEFAULT_PARAMETER
The three analysis tasks of interest here are
the create, execute and report subprograms. Use
the SCOTT schema, create a replica of
ALL_OBJECTS and populate the new
table with a dump of SELECT * from
ALL_OBJECTS. A relatively easy
test case or “what if” scenario concerns adding
an index to the new objects table. An index on
what, though?
If using 11g out of the box, the SCOTT account
must be unlocked and have the password reset. To
get a feel for the amount of data, there are
around 53,750 objects and 24 object types.
Synonyms and Java classes account for just over
90%, so in terms of selectivity, the next most
populous object (views) accounts for 3% and
everything else is less than that. Object type
seems like a good candidate for being indexed
given that most of the queries are looking names
of tables, views, packages, functions,
procedures and triggers.
Use CTAS (Create Table As) to create the table
and then gather table statistics using
DBMS_STATS.GATHER_TABLE_STATS. For
the SCOTT schema, one could also use
GATHER_SCHEMA_STATS because it is
a relatively small schema.
The target table, in keeping with examples
elsewhere, is named
MY_OBJECTS.
create table my_objects
as select * from all_objects;
exec
dbms_stats.gather_table_stats('scott','my_objects');
Execute a variety of SQL statements (all
queries).
SELECT object_type,
count(*) FROM my_objects
GROUP by object_type
ORDER BY 2 desc;
SELECT object_name FROM
my_objects
WHERE object_type =
'VIEW';
SELECT object_name FROM
my_objects
WHERE object_type like
'PACKAGE%';
SELECT count(*) FROM
my_objects
WHERE object_type NOT IN
('SYNONYMS','JAVA CLASS');
SELECT object_name FROM
my_objects
WHERE object_type =
'EDITION';
Now create an SQL set using
DBMS_TUNE.CREATE_SQLSET, load up a
SQLSET cursor, and view what is in
DBA_SQLSET_STATEMENTS.
The ADMINISTER SQL TUNING SET
privilege will need to be
granted to SCOTT beforehand.
|
set serveroutput on
EXEC
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'my_obj_sqlset');
DECLARE
v_cursor
DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN v_cursor
FOR
SELECT VALUE(x)
FROM TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter
=> 'sql_text LIKE ''%my_objects%''
and
parsing_schema_name = ''SCOTT''',
attribute_list
=> 'ALL')) x;
DBMS_SQLTUNE.LOAD_SQLSET
(sqlset_name =>
'my_obj_sqlset',
populate_cursor => v_cursor);
END;
/
As confirmation that the tuning set is loaded,
use the following query.
SQL> SELECT sql_text
2
FROM
dba_sqlset_statements
3
WHERE
sqlset_name = 'my_obj_sqlset';
SQL_TEXT
--------------------------------------------------
SELECT object_name FROM
my_objects
WHERE object_type =
'VIEW'
SELECT object_name FROM
my_objects
WHERE object_type =
'EDITION'
SELECT count(*) FROM
my_objects
WHERE object_type NOT IN
('SYNONYMS','JAVA CLASS')
SELECT object_type,
count(*) FROM my_objects
GROUP by object_type
ORDER BY 2 desc
SELECT object_name FROM
my_objects
WHERE object_type like
'PACKAGE%'
