Statistical checksThe statistics checks validates whether the stats on the table exist and, if so, whether they are representative of the data.
If either of these are false, then you'll get a warning under this section.
You get this in comprehensive mode.
Access path analysisThis looks to see whether there are any indexes you could create to improve the performance.
If there are any that could help, Oracle suggests them here.
You get this in limited mode.
SQL structure analysisThis highlights ways you could improve the performance by changing the SQL. This includes rewrites which could change the output of the query.
For example if you have any cartesian products (cross joins) this will highlight them. The CBO can't do this rewrite automatically because it changes the results. In most cases the cartesian product is a mistake.
Other suggestions could include replacing union with union all. The optimizer can't do this rewrite itself; you'll get different results if query blocks can return the same data.
These are reported in limited mode.
For example, here I'll create a table, put one row in it, gather stats then insert 1,000 more. So the stats are out of date:
create table t (
x integer,
y date
);
insert into t values (1, sysdate);
commit;
exec dbms_stats.gather_table_stats(user, 't');
insert into t
select rownum, sysdate+rownum/1440 from dual connect by level <= 1000;
commit;
I'll then analyze this query:
select count(*) from t t1, t t2
where t1.x = 1
and t2.x = 1
There's three "problems" with it:
- The stats are out of date
- There's a cartesian product
- There's no index on t(x). This returns 2 rows from 1,000. So an index on this would be good.
What does the tuning task tell us?
declare
v_task VARCHAR2(30);
v_sql CLOB;
BEGIN
/* Assign our de-dupe statement... */
v_sql := '
select count(*) from t t1, t t2
where t1.x = 1
and t2.x = 1';
/* Drop the task in case we are re-running... */
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(
task_name => 'sql_tuning_task'
);
EXCEPTION
WHEN OTHERS THEN -- ORA-13605
NULL;
END;
/* Create a SQL Tuning task for our SQL... */
v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => v_sql,
time_limit => 100,
scope => 'COMPREHENSIVE',
task_name => 'sql_tuning_task',
description => 'Demo of DBMS_SQLTUNE'
);
/* Execute the task... */
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name => 'sql_tuning_task'
);
/* We want to run this again... */
ROLLBACK;
END;
/
set long 80000
col recs format a90
select dbms_sqltune.report_tuning_task('sql_tuning_task') as recs
from dual;The findings on the first pass are:
1- Statistics Finding
---------------------
Optimizer statistics for table "CHRIS"."T" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'CHRIS', 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- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 2 of the
execution plan.
Recommendation
--------------
- Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.After gathering stats on the table again:
exec dbms_stats.gather_table_stats(user, 't');
And re-running the analyzer, the findings are now:
1- 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: 66.72%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index CHRIS.IDX$$_01760001 on CHRIS.T("X");
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.
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 2 of the
execution plan.
Recommendation
--------------
- Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.Where do you see that the SQL tuning advisor is doing what the stats collection job does?
The auto tuning task runs in comprehensive mode.
The CBO is separate from the advisor. It's the advisor which has the different modes, not the CBO.