Aug 10 2010
This is a short note to point out that I just added to the Public Appearances page the next conference organized by the Slovenian Oracle User Group (SIOUG) in Portoroz. It will take place on September 27-29. My talk, entitled “Join Techniques”, is based on chapter 10 of my book. It will be a shorter version of then one I will give at Oracle OpenWorld the week before.
Jul 13 2010
Back from two weeks of vacation I noticed that the schedule of the next OpenWorld is available here.
The detailled information about my session, which is based on chapter 10 of my book, is the following:
| ID# | S316683 |
| Title | Join Techniques |
| Abstract | This presentation explains how the query optimizer joins multiple sets of data to each other. First it explains the operation of the basic join methods (nested loop, hash join, and merge join) and the possibilities we have to influence their performance. Then it presents some more advanced optimization techniques such as the transformations applied to joins, and partition-wise joins. |
| Track | Database |
| Date | 22-SEP-2010 |
| Time | 13:00 – 14:00 |
| Venue | Moscone South |
| Room | Rm 304 |
I’m looking forward to seeing you in San Francisco!
Update 2009-09-01: location was changed from “Rm 200″ to “Rm 304″.
Jun 24 2010
This is just a short note to point out that I just uploaded a new version of the scripts related to TOP. The new ZIP is available through this page.
The change log is the following:
| connect.sql | Added DBM10205, DBA10205, DBM11201 and DBA11201 |
| chapter02\bind_variables.sql | Because of 11g modified/added queries against V$SQL_SHARED_CURSOR |
| chapter02\sharable_cursors.sql | Added SET SERVEROUTPUT OFF in the initialization part |
| chapter03\dbms_hprof.sql | New file |
| chapter03\sql_trace_trigger.sql | New file |
| chapter06\display_awr.sql | Improved query that displays AWR content |
| chapter06\execution_plans.sql | Added example for UNION ALL (RECURSIVE WITH) |
| chapter07\baseline_evolution_delete.sql | New file |
| chapter07\baseline_upgrade_11g.sql | After import added update to set the owner of the SQL tuning set |
| chapter07\opt_estimate.sql | Uncommented 11g query |
| chapter07\outline_with_hj.sql | Script compatible with 10g/11g (set “_hash_join_enabled”) |
| chapter07\tune_last_statement.sql | Added SET SERVEROUTPUT OFF in the initialization part |
| chapter08\client-side_caching.sql | New file |
| chapter09\conditions.sql | Added queries containing NOT IN condition |
| chapter09\hash_cluster.sql | Changed comment related to IN operator because of 11.2 improvement |
| chapter10\hash_join.sql | Fixed typo in description |
| chapter10\join_elimination.sql | Fixed typo in description |
| chapter10\join_elimination2.sql | New file |
| chapter10\pwj.sql | Disabled join-filter pruning |
| chapter10\subquery_unnesting.sql | Cover many more cases |
| chapter11\ArrayInterface.java | Added check for the return value of the executeBatch method |
| chapter11\ArrayInterfacePerf.java | Fixed number of iterations in main method |
| chapter11\atomic_refresh.sql | Changed CTAS to avoid ORA-30009 |
| chapter11\dpi_performance.sql | Changed CTAS to avoid ORA-30009 |
| chapter11\px_auto_dop.sql | New file |
| chapter11\px_ddl.sql | Changed the part displaying the parallel DDL status |
| chapter11\px_dml.sql | Changed the part displaying the parallel DML status |
| chapter11\px_query.sql | Changed the part displaying the parallel query status |
| chapter11\result_cache_plsql.sql | Added comment about invalidation in 11.2 |
| chapter12\data_compression.sql | Changed CTAS to avoid ORA-30009 |
| databases\DBA10205 | New directory containing the files to create the database DBA10205 |
| databases\DBM10205 | New directory containing the files to create the database DBM10205 |
| databases\DBA11201 | New directory containing the files to create the database DBA11201 |
| databases\DBM11201 | New directory containing the files to create the database DBM11201 |
Jun 10 2010
To make easier the interpretation of execution plans, in chapter 6 of TOP I defined three types of operations: standalone operations, unrelated-combine operations, and related-combine operations. For combine operations I also added a list of all operations of each type. Since in 11.2 a new related-combine operation is available, I decided to write this short post as addenda to the content of the book.
The new related-combine operation, named “UNION ALL (RECURSIVE WITH)”, is available to support the new recursive subquery factoring clause. Hence, it is used for hierarchical queries. The following query and its execution plan show an example:
SQL> WITH
2 e (xlevel, empno, ename, job, mgr, hiredate, sal, comm, deptno)
3 AS (
4 SELECT 1, empno, ename, job, mgr, hiredate, sal, comm, deptno
5 FROM emp
6 WHERE mgr IS NULL
7 UNION ALL
8 SELECT mgr.xlevel+1, emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm, emp.deptno
9 FROM emp, e mgr
10 WHERE emp.mgr = mgr.empno
11 )
12 SELECT *
13 FROM e;
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
| 1 | VIEW | | 1 | 14 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | 14 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 1 |
| 4 | NESTED LOOPS | | 4 | 13 |
| 5 | NESTED LOOPS | | 4 | 13 |
| 6 | RECURSIVE WITH PUMP | | 4 | 14 |
|* 7 | INDEX RANGE SCAN | EMP_MGR | 14 | 13 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMP | 13 | 13 |
-------------------------------------------------------------------------------
3 - filter("MGR" IS NULL)
7 - access("EMP"."MGR"="MGR"."EMPNO")
filter("EMP"."MGR" IS NOT NULL)
Notice that there are actually two operations:
As their name suggest, the difference is due to the search clause that you can set to either BREADTH FIRST BY or DEPTH FIRST BY.
Reading an execution plan containing the “UNION ALL (RECURSIVE WITH)” operation is the same as reading one containing the “CONNECT BY WITH FILTERING” operation. As a matter of fact, the purpose of both operations is basically the same. Just notice that also the “PUMP” operation used in the execution plan differs. While in the former it is called “RECURSIVE WITH PUMP”, in the latter it is called “CONNECT BY PUMP”. But the difference, for the purpose of reading the execution plan, does not matter.
You find a full description on how to read such an execution plan in this post.
May 22 2010
In one month I will be in Berlin presenting a two-day seminar based on the chapters 1, 2, 8, 9, 10 and 11 of my book. The event is organized by DOAG. You can read the full description of the seminar (incl. agenda) here. Just be careful that the spoken language will be German (slides will be in English, though). Since I was just informed that there are less than ten free seats, do not wait to long if you want to join us…