Does the SQL Tuning Advisor auto task tune DML statements?
Sort of; sometimes. Read on.
The documentation says:
Oracle Database automatically runs the SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates…..
<skip some lines>
….. Oracle Database analyzes statistics in the AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the system. Only SQL statements that have an execution plan with a high potential for improvement will be tuned. Recursive SQL and statements that have been tuned recently (in the last month) are ignored, as are parallel queries, DMLs, DDLs, and SQL statements with performance problems that are caused by concurrency issues.
However, I was alarmed to see statements like this being run by the SQL Tuning autotasks:
/* SQL Analyze(186,1) */ INSERT INTO TABLE_A SELECT * FROM ...
What would the implications be? Consider locking, segment growth, redo, triggers, etc.
I could see the execution plan in the SGA started with:
0 INSERT STATEMENT 1 LOAD TABLE CONVENTIONAL
The session also had a DML lock on TABLE_A.
I tested with dbms_sqltune, trying to replicate the situation, but after opening the cursor a couple of times, the report just said:
Type of SQL statement not supported.
I logged a Service Request with Oracle Support who said:
…the sql tuning advisor will check the select part only and not the DML part,yes technically it is considered DML but actually internally it will not use the DML but will use the SELECT part…
Another clarification is for such DMLs, the SELECT parts are only used i,e for execution internally and not the DML part and therefore there will be no redo generation or any data update/delete/insert …
I can’t find this treatment of DMLs documented anywhere, so I suggested to Oracle that they correct their documentation and I created this post.