Apr 29 2010
Almost one year ago Iggy Fernandez asked me to write a short text for the Ask the Oracles column of the NoCOUG Journal. The topic was “Bind Variable Peeking: Bane or Boon?”. My text along with the ones of Wolfgang Breitling, Dan Tow and Jonathan Lewis were published in the August issue. For some (unknown) reasons I never published that text on this site. Today, I correct that oversight. The text can be downloaded from this page.
Apr 28 2010
Even though the utilization of an Exadata Storage Server should be transparent for the query optimizer, when you look at execution plans generated in an environment using it you might notice slight differences. The purpose of this series of post is to summarize the differences I was able to observe.
Disclaimer: I do not have access to an Exadata Storage Server (cell, from now on…). All tests I did, i.e. generation of execution plans, were performed with an 11gR2 database using “regular” storage. All I did is to set the CELL_OFFLOAD_PLAN_DISPLAY initialization parameter to ALWAYS. As a result, this post contains untested assumptions based on what I was able to observe in such an environment. It goes without saying that any correction is highly welcome!
Smart scan is one of the key performance features available only when a cell is used. Its purpose is to offload to the storage layer part of the work that would usually be performed by a database instance. As a result, not only the amount of work performed by a database instance might be (much) lower, but also the amount of data transferred from a cell to a database instance might be strongly reduced. When smart scan is used a cell, instead of sending back to the database engine “regular” database blocks (as a “regular” storage layer would do), sends back packets of data containing only the relevant information (I guess they are similar to the messages exchanged during PX operations).
Three are the basic smart scan techniques: projection, restriction and join filtering. As of Oracle Database 11g Release 2 three additional improvements are available: storage indexing, hybrid columnar compression and encryption. But, honestly, I consider them “simple” enhancements (that might have a major impact, though) of the previous ones. In other words, they add nothing to the basic concepts.
In this first post I cover projection. In the next posts I will cover the other two.
Whenever a query does not reference all columns belonging to the tables referenced in the FROM clause AND smart scan is used, it makes no sense that a cell sends back to a database instance the data belonging to the unreferenced columns. Since it makes no sense, it’s not done. Instead, a cell is able to extract only the data of the referenced columns. In this way, especially when queries reference few columns from wide tables, the amount of data received by a database instance is much smaller. As far as I know this technique is not explicitly externalized in execution plans (I mean, you don’t see which columns are actually sent back to the database instance). But, I guess, if you see one of the new operations added to support Exadata (they all contains the keyword “STORAGE” in them), you take advantage of this technique. Here is an example with a FTS (notice the keyword “STORAGE”):
------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS STORAGE FULL| T | ------------------------------------------
The full list of new “operations” that I was able to observe is the following:
So, it seems that for the most common access paths there is a “smart scan version”.
Apr 27 2010
As of Oracle Database 11g the DBMS_SQLTUNE package provides the SELECT_SQL_TRACE function. Its purpose is to load the content of a SQL trace file into a SQL tuning set. But, as it often happens, a feature can be (mis)used for another purpose. The aim of this post is to show how to take advantage of this function to display through SQL statements the content of a SQL trace file.
Note that the SELECT_SQL_TRACE function is not available in version 11.1.0.6. Refer to the MOS note 790806.1 for additional information. Hence, the code shown in this post works as of 11.1.0.7 only.
First of all, let’s setup the scene.
SQL> CREATE TABLE t
2 AS
3 SELECT rownum AS id, rpad('*',1000,'*') AS pad
4 FROM dual
5 CONNECT BY level <= 10000
6 ORDER BY dbms_random.value;
SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
SQL> BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 't',
5 estimate_percent => 100,
6 method_opt => 'for all columns size 1'
7 );
8 END;
9 /
SQL> execute dbms_monitor.session_trace_enable(binds => TRUE, plan_stat => 'ALL_EXECUTIONS')
SQL> EXECUTE :id := 10;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SQL> EXECUTE :id := 990;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
SQL> EXECUTE :id := 20;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
19
SQL> SELECT sum(id) FROM t;
SUM(ID)
----------
50005000
SQL> execute dbms_monitor.session_trace_disable SQL> SELECT value 2 FROM v$diag_info 3 WHERE name = 'Default Trace File'; VALUE ------------------------------------------------------------------- /u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_26790.trc
SQL> CREATE DIRECTORY trace AS '/u00/app/oracle/diag/rdbms/dba112/DBA112/trace/';
Now that we have a SQL trace file, let’s see how we can read its content with some simple queries and what kind of information we can extract.
SQL> SELECT sql_id, 2 sum(elapsed_time) AS elapsed_time, 3 sum(executions) AS executions, 4 round(sum(elapsed_time)/sum(executions)) AS elapsed_time_per_execution 5 FROM table(dbms_sqltune.select_sql_trace( 6 directory => 'TRACE', 7 file_name => 'DBA112_ora_26790.trc', 8 select_mode => 2 -- all executions 9 )) t 10 WHERE parsing_schema_name = user 11 GROUP BY sql_id 12 ORDER BY elapsed_time DESC; SQL_ID ELAPSED_TIME EXECUTIONS ELAPSED_TIME_PER_EXECUTION ------------- ------------ ---------- -------------------------- asth1mx10aygn 249757 4 62439 6tgnxwpymddqc 4200 1 4200
SQL> SELECT sql_text 2 FROM table(dbms_sqltune.select_sql_trace( 3 directory => 'TRACE', 4 file_name => 'DBA112_ora_26790.trc', 5 select_mode => 1 -- only first execution 6 )) t 7 WHERE sql_id = 'asth1mx10aygn'; SQL_TEXT --------------------------------------- SELECT count(pad) FROM t WHERE id < :id
SQL> SELECT plan_hash_value, executions, fetches, elapsed_time, cpu_time, disk_reads, buffer_gets, rows_processed
2 FROM table(dbms_sqltune.select_sql_trace(
3 directory => 'TRACE',
4 file_name => 'DBA112_ora_26790.trc',
5 select_mode => 2 -- all executions
6 )) t
7 WHERE sql_id = 'asth1mx10aygn'
8 ORDER BY elapsed_time DESC;
PLAN_HASH_VALUE EXECUTIONS FETCHES ELAPSED_TIME CPU_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
--------------- ---------- ---------- ------------ ---------- ---------- ----------- --------------
4294967295 1 2 129056 127981 731 992 1
4294967295 1 2 113667 112982 691 1434 1
4294967295 1 2 5993 6999 11 11 1
4294967295 1 2 1041 1000 0 21 1
SQL> SELECT elapsed_time,
2 value(b).gettypename() AS type,
3 value(b).accessnumber() AS value
4 FROM table(dbms_sqltune.select_sql_trace(
5 directory => 'TRACE',
6 file_name => 'DBA112_ora_26790.trc',
7 select_mode => 2 -- all executions
8 )) t,
9 table(bind_list) b
10 WHERE sql_id = 'asth1mx10aygn'
11 ORDER BY elapsed_time DESC;
ELAPSED_TIME TYPE VALUE
------------ ---------- -----
129056 SYS.NUMBER 990
113667 SYS.NUMBER 990
5993 SYS.NUMBER 10
1041 SYS.NUMBER 20
Even though everything seems fine, there is an error in the output of one of the queries. Specifically, the hash value of the execution plan is not always the right one (the same value is used for all executions). In fact, because of extended cursor sharing (a.k.a. adaptive cursor sharing) several execution plans were used. This can be confirmed by analyzing the trace file with TKPROF (or another analyzer like TVD$XTAT). In this case the TKPROF output, generated with the AGGREGATE option set to “NO” provides the following information (notice that the hash value of the third execution is different):
... SQL ID: asth1mx10aygn Plan Hash: 4270555908 SELECT count(pad) FROM t WHERE id < :id ... SQL ID: asth1mx10aygn Plan Hash: 4270555908 SELECT count(pad) FROM t WHERE id < :id ... SQL ID: asth1mx10aygn Plan Hash: 2966233522 SELECT count(pad) FROM t WHERE id < :id ... SQL ID: asth1mx10aygn Plan Hash: 4270555908 SELECT count(pad) FROM t WHERE id < :id ...
The other information that can be retrieved through the SELECT_SQL_TRACE function is the execution plan. Unfortunately, extracting it directly through the function is inconvenient. The reason is that you should do the formatting yourself. Much easier is to load the information into a SQL tuning set and, then, to use the DBMS_XPLAN package to show its content. The following example illustrates this:
SQL> DECLARE
2 c sys_refcursor;
3 BEGIN
4 dbms_sqltune.create_sqlset('TEST');
5 OPEN c FOR
6 SELECT value(t)
7 FROM table(dbms_sqltune.select_sql_trace(
8 directory => 'TRACE',
9 file_name => 'DBA112_ora_26790.trc',
10 select_mode => 2 -- all executions
11 )) t;
12 dbms_sqltune.load_sqlset('TEST', c);
13 CLOSE c;
14 END;
15 /
SQL> SELECT *
2 FROM table(dbms_xplan.display_sqlset(
3 sqlset_name => 'TEST',
4 sql_id => 'asth1mx10aygn'
5 ));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL Tuning Set Name: TEST
SQL Tuning Set Owner: CHA
SQL_ID: asth1mx10aygn
SQL Text: SELECT count(pad) FROM t WHERE id < :id
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| UNKNOWN_OBJECT_90222 | 9045 | 11 |
| 3 | INDEX RANGE SCAN | UNKNOWN_OBJECT_90223 | | 2 |
-----------------------------------------------------------------------------
Notice that also in this case only one execution plan is shown.
All in all, this is an interesting feature. For sure it does not replace a profiler (mainly because the wait events are not shown), but it might be useful in some situations.
Feb 28 2010
Even though a number of articles and blog posts have already been written on this topic (e.g. on Pete Finnigan’s site I found references dating back from 2003), from time to time I’m still asked “How to trace predicates generated by VPD?”. Hence, here’s yet another blog post about this topic…
Let’s setup the scene before explaining how you can do it:
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> CREATE OR REPLACE FUNCTION emp_restrict (p_schema IN VARCHAR2, p_table IN VARCHAR2) RETURN VARCHAR2 AS
2 BEGIN
3 RETURN '''' || sys_context('userenv','session_user') || ''' = ename';
4 END emp_restrict;
5 /
SQL> execute dbms_rls.add_policy('CHA','EMP','EMP_POLICY','CHA','EMP_RESTRICT');
SQL> connect scott
SQL> SELECT * FROM cha.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
SQL> connect clark
SQL> SELECT * FROM cha.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
If the function used for the VPD policy is simple and does generate a predicate that can be correctly parsed, to view the predicate it is enough to give a look to the output of the dbms_xplan package. The following SQL statements illustrate this:
SQL> SELECT * FROM table(dbms_xplan.display_cursor(sql_id=>'dmc3z4t0u57y1', format=>'basic predicate'));
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM cha.emp
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='SCOTT')
Another possibility, if you have access to the V$VPD_POLICY view, is to execute a query like the following one:
SQL> SELECT predicate FROM v$vpd_policy WHERE sql_id = 'dmc3z4t0u57y1'; PREDICATE ---------------------- 'SCOTT' = ename
However, in case you want to see the whole SQL statement or the predicate generated by the VPD policy leads to an ORA-28113 (policy predicate has error), there is no documented way I’m aware of to display the generated predicate. One of the undocumented ways to do it is to use the event 10730. Note that the event generates a trace file containing the information we are looking for in such situations. Here is an example:
SQL> ALTER SESSION SET events '10730 trace name context forever, level 1';
SQL> SELECT * FROM cha.emp;
SELECT * FROM cha.emp
*
ERROR at line 1:
ORA-28113: policy predicate has error
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
---------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
SQL> host tail -10 /u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
-------------------------------------------------------------
Error information for ORA-28113:
Logon user : SCOTT
Table/View : CHA.EMP
Policy name : EMP_POLICY
Policy function: CHA.EMP_RESTRICT
RLS view :
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "CHA"."EMP" "EMP" WHERE ('SCOTT' = enamee)
ORA-00904: "ENAMEE": invalid identifier
-------------------------------------------------------------
As you can see, the trace file contains not only the whole SQL statement but also the reason for the ORA-28113 error.
Jan 26 2010
The short answer to this question is “yes”, it does. Unfortunately, the distribution costs are not externalized through the execution plans and, as a result, this limitation (yes, it is really a limitation in the current implementation, not a bug) confuses everyone that carefully look at the information provided in an execution plan of a SQL statement executed in parallel. Hence, let’s remove some confusion…
To illustrate what the problem is, let’s have a look to a simple query that joins two tables:
SELECT * FROM master m JOIN detail d ON (m.id = d.id)
Now, let’s have a look at two parallel executions. If the two tables are equipartitioned, the following execution plan (which takes advantage of partition-wise join) is probably the most effective for such a query. Note that thanks to the partition-wise join not only there is a single set of parallel slaves (Q1,00), but, in addition, the parallel slaves do not communicate with each other (they only communicate with the query coordinator). As a result, the communication costs are equal to zero (this is because the query optimizer does not compute the costs of the communication towards the query coordinator).
---------------------------------------------------------------------------------------------- | Id | Operation | Name | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16G| 162524 (1)| | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 16G| 162524 (1)| Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION HASH ALL| | 16G| 162524 (1)| Q1,00 | PCWC | | | 4 | HASH JOIN | | 16G| 162524 (1)| Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | MASTER | 125M| 1422 (1)| Q1,00 | PCWP | | | 6 | TABLE ACCESS FULL | DETAIL | 15G| 161052 (1)| Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------
If the two tables are not equipartitioned, the following execution plan might be chosen by the query optimizer. Since it does not take advantage of a partition-wise join, several set of parallel slaves are used. The first one (Q1,00) scans the MASTER table, the second one (Q1,01) scans the DETAIL table, and both of them send the data to the third one (Q1,02) that performs the join of the two tables and sends the data to the query coordinator. Since all data (about 15GB; yes, the estimations are good) is sent through the PX channels, the cost should not be zero. However, as you can see, the cost is exactly the same as the one of the previous execution plan.
---------------------------------------------------------------------------------------------- | Id | Operation | Name | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16G| 162524 (1)| | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 16G| 162524 (1)| Q1,02 | P->S | QC (RAND) | | 3 | HASH JOIN BUFFERED | | 16G| 162524 (1)| Q1,02 | PCWP | | | 4 | PX RECEIVE | | 125M| 1422 (1)| Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 125M| 1422 (1)| Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 125M| 1422 (1)| Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| MASTER | 125M| 1422 (1)| Q1,00 | PCWP | | | 8 | PX RECEIVE | | 15G| 161052 (1)| Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 15G| 161052 (1)| Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 15G| 161052 (1)| Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| DETAIL | 15G| 161052 (1)| Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------
For completeness, let’s compare the cost of several distribution methods (“none-none” is the one of the first execution plan above, “hash-hash” of the second one). As you can see the cost is always the same!
SQL> EXPLAIN PLAN SET STATEMENT_ID 'none-none' FOR SELECT /*+ pq_distribute(d none none) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'hash-hash' FOR SELECT /*+ pq_distribute(d hash hash) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'broadcast-none' FOR SELECT /*+ pq_distribute(d broadcast none) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'none-broadcast' FOR SELECT /*+ pq_distribute(d none broadcast) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'partition-none' FOR SELECT /*+ pq_distribute(d partition none) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'none-partition' FOR SELECT /*+ pq_distribute(d none partition) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> SELECT statement_id, cost FROM plan_table WHERE id = 0; STATEMENT_ID COST ------------------------------ ---------- none-none 162524 hash-hash 162524 broadcast-none 162524 none-broadcast 162524 partition-none 162524 none-partition 162524
As I wrote before, the problem is not that the costs are not computed. The problem is that they are not externalized. In fact, by giving a look to a trace file generated through the event 10053 the costs are available. Here’s the relevant part (the lines starting with “---- cost” contain the most important information). As you can see there are two costs associated with every distribution method: one with the distribution costs (w/ dist) and one without them (w/o dist).
Enumerating distribution method for join between M[MASTER] and D[DETAIL]
-- Using join method #Hash Join:
---- cost NONE = 0.00
Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 513 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162524.05 (w/ dist)
---- cost VALUE = 278.52
---- cost with slave mapping = Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 2 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162802.57 (w/ dist)
---- cost PARTITION-RIGHT = 271.40
Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 576 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162795.46 (w/ dist)
---- cost PARTITION-LEFT = 7.12
Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 544 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162531.17 (w/ dist)
---- cost BROADCAST-RIGHT = 920.78
---- cost with slave mapping = Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 8 #groups: 4
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162755.25 (w/ dist)
---- cost BROADCAST-LEFT = 7.22
---- cost with slave mapping = Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 16 #groups: 4
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162526.86 (w/ dist)
Since the cost are (correctly) computed, the query optimizer is able to choose the optimal plan. However, it would be nice to have the actual costs in the execution plans.