Sadly for those who think that a pretty picture is the end result of application development, testing is a huge part of having an application that people can use, rather than just look at. The real problem with testing is that it is not hard, but Oracle has already coded all the easy answers into the products and what is left are questions that require testing. If testing could be done in seconds, and the path was follow steps 1-10, that would in the code, so there is no 1-10 plan but there are plenty of good tools to use. You can dig most of them out of any book by Tom Kyte. The list seems long, but a day or two with the tools (most supplied by Oracle and all free) will get you comfortable with using them to test various forms of SQL and PL/SQL,if not provide all the answers.
Tools you want to use and their source:
SQL* PLUS -- Oracle pkg
EXPLAIN PLAN -- Oracle pkg
AUTOTRACE -- Oracle pkg
TKPROF -- Oracle pkg
Runstats -- Tom Kyte pkg
Statspack -- Oracle pkg, better used for 15-30 min general stats
Runstats -- Tom Kyte pkg; compares two runs of smilar code
Mystat -- Tom Kyte pkg, shows change in selected system generated stat.
Bigtable.sql -- Tom Kyte; generates large table for testing.
dbms_sqltune -- Oracle pkg, demo below.
If you don't know how to use the tools above and want to see some excellent examples, you might want to get Tom's book. I will demo one tool, and give you some idea of how easy it can be. I have to admit though, that testing exposes problems, you still have to come up with alternates to test, and hints that Oracle supplies below may help, or may even make things worse. Keep in mind that if this was easy, and did not require reading, a salesman would be doing your job. If this is a new database, first thing to do, make sure you are not creating tables in the system table space:
hpicot@macie> select username, user_id, created, default_tablespace
2 from dba_users;
USERNAME USER_ID CREATED DEFAULT_TABLESPACE
------------------------------ ---------- --------- ------------------------------
OUTLN 9 22-JUL-08 SYSTEM
ORACLE 30 09-AUG-08 USERS
HPICOT 29 09-AUG-08 USERS
SCOTT 28 09-AUG-08 USERS
SYS 0 22-JUL-08 SYSTEM
SYSTEM 5 22-JUL-08 SYSTEM
DBSNMP 27 22-JUL-08 SYSAUX
TSMSYS 14 22-JUL-08 USERS
DIP 13 22-JUL-08 USERS
ORACLE_OCM 20 22-JUL-08 USERS
10 rows selected.
If this is not correct for you, use alter database to correct it, because we are going to create a large table that may be unwelcome in the system table space. Our table will only fit two rows in one 8k block, so we are going to need to get 5,000 blocks, it may take a few minutes.
hpicot@macie> create table t (id constraint t_pk primary key, pad) AS
2 select rownum, lpad('*', 4000, '*')
3 from all_objects
4 where rownum <=10000;
Table created.
hpicot@macie> variable tn varchar2(30)
hpicot@macie> declare
2 l_sqltext clob := 'select count(*) from t where id+42 = 126';
3 begin
4 :tn := dbms_sqltune.create_tuning_task (sql_text => l_sqltext);
5 dbms_sqltune.execute_tuning_task(:tn);
6 END;
7 /
PL/SQL procedure successfully completed.
First way to get the information:
hpicot@macie> SELECT dbms_sqltune.report_tuning_task(:tn) FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TN)
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_333
Tuning Task Owner : HPICOT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 08/23/2008 03:29:37
Completed at : 08/23/2008 03:29:42
-------------------------------------------------------------------------------
Schema Name: HPICOT
SQL ID : 97nnjbnyk9hvm
SQL Text : select count(*) from t where id+42 = 126
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "HPICOT"."T" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'HPICOT', tabname =>
'T', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 82.3%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HPICOT.IDX$$_014D0001 on HPICOT.T("ID"+42);
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate "T"."ID"+42=126 used at line ID 2 of the execution plan
contains an expression on indexed column "ID". This expression prevents the
optimizer from selecting indices on table "HPICOT"."T".
Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1477688419
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FULL SCAN| T_PK | 108 | 1404 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"+42=126)
2- Using New Indices
--------------------
Plan hash value: 1194693448
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:
01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX RANGE SCAN| IDX$$_014D0001 | 13 | 169 | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T".
-------------------------------------------------------------------------------
Another way to dig out the same (almost) information:
hpicot@macie>
"././afiedt.buf" 16 lines, 427 characters
1 SELECT a.command AS type,
2 f.message AS findings,
3 a.message AS recommendattions,
4 t.message AS rational
5 FROM dba_advisor_actions a,
6 dba_advisor_recommendations r,
7 dba_advisor_findings f,
8 dba_advisor_rationale t
9 WHERE a.task_id = 333 -- from above
10 AND a.task_id = r.task_id
11 AND a.rec_id = r.rec_id
12 AND a.task_id = t.task_id
13 AND a.rec_id = t.rec_id
14 AND f.task_id = r.task_id
15* AND f.finding_id = r.finding_id
hpicot@macie> /
TYPE
----------------------------------------------------------------
FINDINGS
----------------------------------------------------------------------------------------------------
RECOMMENDATTIONS
----------------------------------------------------------------------------------------------------
RATIONAL
----------------------------------------------------------------------------------------------------
REWRITE QUERY
The predicate "T"."ID"+42=126 used at line ID 2 of the execution plan contains an expression on inde
xed column "ID". This expression prevents the optimizer from selecting indices on table "HPICOT"."T"
.
Rewrite the predicate into an equivalent form to take advantage of indices. Alternatively, create a
function-based index on the expression.
The optimizer is unable to use an index if the predicate is an inequality condition or if there is a
n expression or an implicit data type conversion on the indexed column.
CREATE INDEX
The execution plan of this statement can be improved by creating one or more indices.
Consider running the Access Advisor to improve the physical schema design or creating the recommende
d index.
Creating the recommended indices significantly improves the execution plan of this statement. Howeve
r, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to
a single statement. This will allow to get comprehensive index recommendations which takes into acco
unt index maintenance overhead and additional space consumption.
GATHER TABLE STATISTICS
Table "HPICOT"."T" was not analyzed.
Consider collecting optimizer statistics for this table.
The optimizer requires up-to-date statistics for the table in order to select a good execution plan.
No comments:
Post a Comment