Oracle 10g with its AWR (Automatic Workload Repository) for automatic collection of performance statistics and with ADDM (Automatic Database Diagnostic Monitor) for identifying root causes of problems, is a self-healing and self-tuning getting wiser every day and very soon will start to think on its own database.
There are features in Oracle 10g which help you to quickly find out what is wrong with the database. Features like ASH (Active Session History) to peak into what your active sessions are waiting for and Server Generated Alerts with Metrics and Thresholds are all tools which tell you what is really going on in your database. The OEM (Oracle Enterprise Manager) Database control is a brilliant interface you can use to learn and find out more about all these smart enhancements. All in the name of pro-active database management.
What is database well-being all about anyway? Isn't it high throughput(OLTP) and response time(DSS)? Aren't all databases working to achieve these?
Oracle 10g has also an
Advisory Framework which based on workload, will also try to advise you on configuration issues like Buffer Cache, Library Cache, PGA, Undo and Segments used. There are also Tuning-Related Advisors which deal with SQL Tuning and these are:
I - SQL Tuning Advisor
II - SQL Access Advisor
In this post I will try to explain the usage of these two advisors in the job of tuning bad and nasty SQL statements.
All automation is good, self healing databases are very good. But what happens in the development arena when a developer turns up to your desk pointing to an email he/she just sent to you and says
'...what's wrong with this SQL statement? it takes ages? Why is Oracle so slow?...' I am particularly fond of the last comment.
How can we quickly look at what is wrong with the SQL statement using the SQL Tuning Advisor? Here is how.
I - SQL Tuning Advisor
Using the DBMS_SQLTUNE package to tune SQL
Assuming the suspect query is something like
"SELECT * FROM SESSIONS, SOURCE_EVENTS", two very large tables, and your schema has the ADVISOR privilege granted, you would put this query into the Oracle SQL Tuning Advisor engine using the PL/SQL package
DBMS_SQLTUNE like this:
1. Create the tuning task
In SQL*Plus and in the schema where objects live, use the procedure
dbms_sqltune.create_tuning_task to create a tuning task.
SQL>
declare tname varchar2(30);
begin
tname := dbms_sqltune.create_tuning_task(sql_text=>'select * from sessions, source_events');
end;
/
PL/SQL procedure successfully completed.
Then you can check to make sure that your task is created and get the TASK_NAME value as you will need this in the next step. I could name the task, just being lazy...
SQL> select task_name from user_advisor_log;
TASK_NAME
------------------------------
TASK_51415
Execute the tuning task you created in step 1 with the procedure
dbms_sqltune.execute_tuning_task
SQL> exec dbms_sqltune.execute_tuning_task('TASK_51415');
Get the tuning report/recommendation with the following SQL.
SQL> set long 50000
SQL> select dbms_sqltune.report_tuning_task('TASK_51415') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_51415')
(CLOB) GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_51415
Tuning Task Owner : ACHILLES
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 12/20/2009 18:44:05
Completed at : 12/20/2009 18:44:14
Number of SQL Restructure Findings: 1
-------------------------------------------------------------------------------
Schema Name: ACHILLES
SQL ID : 8qsdf0a6qv4g7
SQL Text : select * from sessions, source_events
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 3 of the
execution plan.
Recommendation
--------------
- Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.
Rationale
---------
A cartesian product should be avoided whenever possible because it is an
expensive operation and might produce a large amount of data.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3883039950
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33T| 4158T| 4367M (2)|999:59:59 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 33T| 4158T| 4367M (2)|999:59:59 | | | Q1,01 | P->S | QC (RAND) |
| 3 | MERGE JOIN CARTESIAN | | 33T| 4158T| 4367M (2)|999:59:59 | | | Q1,01 | PCWP | |
| 4 | SORT JOIN | | | | | | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 8439K| 829M| 3175 (2)| 00:00:39 | | | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 8439K| 829M| 3175 (2)| 00:00:39 | | | Q1,00 | P->P | BROADCAST |
| 7 | PX BLOCK ITERATOR | | 8439K| 829M| 3175 (2)| 00:00:39 | 1 | 16 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| SESSIONS | 8439K| 829M| 3175 (2)| 00:00:39 | 1 | 16 | Q1,00 | PCWP | |
| 9 | BUFFER SORT | | 4013K| 122M| 4367M (2)|999:59:59 | | | Q1,01 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 4013K| 122M| 532 (4)| 00:00:07 | 1 | 16 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL | SOURCE_EVENTS | 4013K| 122M| 532 (4)| 00:00:07 | 1 | 16 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------
1 rows selected
Well done SQL Tuning Advisor, a good answer. It immediately spotted the Cartesian product and it recommends with a rationale that it would be expensive!
II - SQL Access Advisor
SQL Access advisor is another Advisory Framework tool which provides primarily advice on the creation of indexes, materialized views, materialized view logs to improve query performance and response time.
Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatcially from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads. Look at the Oracle documentation to find out how all this is possible under OEM Database Control.
What I focus on here is how to quickly pass through these tools ad-hoc SQL that the developer will bring to you and will demand to be tuned there and then.
So using the
QUICK_TUNE procedure from the
DBMS_ADVISOR package here is how you would tune a nasty single SQL statement for better data access paths with SQL Access Advisor.
1. Create tuning task
In the schema where the table is run the following
VARIABLE tname VARCHAR2(30);
VARIABLE sql_stmt VARCHAR2(30);
EXECUTE :sql_stmt := 'select count(*) from sessions where short_code=''abracadambra'' ';
EXECUTE :tname := 'myquicktunetask';
EXECUTE DBMS_ADVISOR.quick_tune(dbms_advisor.sqlaccess_advisor, :tname, :sql_stmt);
Again to see the task
myquicktunetask which you just created run in the schema
SQL> select task_name from user_advisor_log
TASK_NAME
------------------------------
TASK_51415
myquicktunetask
2 rows selected
Now, to see the advice you get you will have to look in the DBA dictionary view
DBA_ADVISOR_ACTIONS. So login as DBA and filter for the task_name='myquicktunetask' in the DBA_ADVISOR_ACTIONS dictionary view. Well, you can view the advice in the USER_ADVISOR_ACTIONS dictionary view as well.
SELECT command,
attr2 ,
attr3 ,
attr4 ,
attr5
FROM dba_advisor_actions
WHERE TASK_NAME='myquicktunetask'
TASK_NAME||'|'||COMMAND||'|'||ATTR2||'|'||ATTR3||'|'||ATTR4||'|'||ATTR5
(CLOB) myquicktunetask|CREATE MATERIALIZED VIEW LOG||ROWID, SEQUENCE| INCLUDING NEW VALUES|("SHORT_CODE")
(CLOB) myquicktunetask|CREATE MATERIALIZED VIEW||REFRESH FAST WITH ROWID|ENABLE QUERY REWRITE|SELECT ACHILLES.SESSIONS.SHORT_CODE C1, COUNT(*) M1 FROM ACHILLES.SESSIONS WHERE (ACHILLES.SESSIONS.SHORT_CODE = 'tl') GROUP BY ACHILLES.SESSIONS.SHORT_CODE
(CLOB) myquicktunetask|GATHER TABLE STATISTICS||-1||
Conclusion
Well I find both tools extremely useful. Both are good starting points in resolving SQL tuning issues and discussions. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.
