|
| Hash join performance | 12 May 1999 |
| Which type of join is faster - hash join, or nested loops - and why? | ||
 | Hash join uses the smaller input to build a hash table and the larger to probe it. Its performance is highly sensitive to the hash_area_size and the hash_multiblock_io_count parameters. The number of partitions into which the hash table is divided is inversely proportional to the I/O size. If any of the partitions do not fit into the hash_area_size the performance will degrade badly. If memory is scarce you must use a small I/O count, despite that that makes the I/O less efficient. But assuming you don't make these mistakes, hash join will outperform nested loops every time in both the number of I/Os required and the number of join key comparisons. Nested loops will however win in terms of the FIRST_ROWS response time. |
|
| When you say partitions, what exactly are you referring to. Also, is there a recommended size for the hash_area_size and hash_multiblock_io_count? |
|
The partitions in this case are just parts of the dynamically constructed hash table for the hash join.
If Oracle cannot keep the whole hash table in memory, then it will write all the partitions that do not fit to a temp segment,
and similarly the probe input data that needs to be joined thereto.
Where it really goes badly is when a single partition will not fit, because then it needs to be repartitioning and recursively joined.
However, there is lots of hidden intelligence working for you here.
For example, probe inputs are checked against a bitmap of hash values and only retained if they will be able to be joined.
Also, when revisiting a hash table partition and the corresponding dynamic partition of the probe input,
Oracle will dynamically reverse the role of the build and probe inputs if better performance can be obtained that way.
There is a really helpful trace to show you exactly what is going on with a hash join so you can see all this happening.
You can enable it with:
alter session set events '10104 trace name context forever'; Your hash area size must take lots of factors into account: how much memory you have, how much data you are joining, how critical the query response time is relative to other work on the server, and so on. But, if you want a hash join to fly, make your hash area size slightly larger than the size of the smaller input and use a multiblock I/O count equivalent to 64K. |
|
| Performance impact of sql_trace | 7 July 1999 |
| Is the use of sql_trace likely to lead to the increased occurrence of certain types of wait events that we can monitor in one or more of the V$ tables, or can the impact only be monitored at OS level? | ||
| There is no wait event for trace file writes, because Oracle does not sync them. There is an impact on overall system performance, but if your udump directory has I/O separation, and if your pageout daemon or syncer process is not overworked, then the impact will be trivial but difficult to measure. |
|
| Copying optimizer statistics | 5 October 1999 |
| I think I've read somewhere that in Oracle 8.x it is possible to copy the statistics from analyzed tables on one system to another system without copying the data also. | ||
| You can do this in 8.1 with DBMS_STATS. If you just want a non-production database to run EXPLAIN PLAN against, then you can clone the database using just the SYSTEM and rollback segment tablespaces. |
|
| Wait for credit - send blocked | 28 October 1999 |
| Can you give me a description of the oracle wait event Wait for credit - send blocked? I was not able to find any information about the event and I have the system waiting for it. This is Oracle 8.0.5 on Solaris 2.6. | ||
| The credit is a synchronization mechanism used to control the flow of messages between parallel query slaves. Use the V$PQ_TQSTAT view to investigate. |
|
| alter session set sql_trace | 5 November 1999 |
| V$PARAMETER still shows sql_trace as being set to FALSE, even after it has been explicitly set to TRUE using ALTER SESSION. Why? | ||
| The answer is that it does not really change the parameter. It sets an event that is equivalent. If you use the which_events.sql script, you should see that event 10046 is set at level 1. |
|
| CBO index usage | 10 November 1999 |
| I was trying to get CBO (the cost-based optimizer) to use a certain index. Building a histogram worked, but interestingly, deleting the statistics on the index worked too. So, does this mean that Oracle's default is to use the index, and the statistics may simply rule it out due to Oracle believing that the cardinality is too low? Obviously the histogram is the better way to go here, but the technique might be useful in other circumstances if I know why it worked here. | ||
| You can see what the CBO was doing and why, by setting event 10053 and looking in the trace file. In this case you will no doubt find that in the absence of statistics, CBO used its default assumptions about indexes, namely only 1 level and 25 leaf blocks. These numbers are release specific, but you get the idea. By assuming that the index is small, CBO intentionally biases itself towards index based access paths, because that is safer. |
|
| Implicit datatype conversion | 19 November 1999 |
|
We have found that when quotes are placed around certain constants it makes a difference in how the optimizer processes the query.
For example, if id is a VARCHAR2 column
select * from my_table where id = '44713'; is a lot faster than select * from my_table where id = 44713; | ||
| If you mix a character datatype with a numeric or date datatype in an expression or predicate, then there is an implicit conversion of the character datatype to the numeric or date datatype that disables the use of an index just would be the case if you had used an explicit to_number() or to_date() function on the character column. |
|
| Which sessions have tracing enabled? | 28 December 1999 |
| When you enable tracing using DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, does Oracle record this information in any of the X$ tables? If not, how can we work out which sessions have tracing enabled, so that we can stop them? | ||
| Other than the existence of trace files (which is not entirely reliable) there is no way of determining which sessions have tracing enabled. To explain why: this trace is actually implemented by sending a message to the session that causes it to set event 10046 at level 1. However, event settings such as this go into the UGA for the session, which is generally not visible to other sessions. DBMS_SYSTEM has a procedure to read the event settings in the current session (READ_EV) but there is nothing to send a message to another session to ask it to return the equivalent information. |
|
| Trace files | 1 March 2000 |
| Executing DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace another session only works intermittently. Often it says the PL/SQL executed successfully, but then there isn't a trace file in the udump directory. | ||
| Are you sure those sessions are doing anything that would be written to a trace file? Check their user calls statistic in V$SESSTAT. Oracle will not create a trace file if this statistic is static. Also, remember that Oracle appends to an existing trace file if an old file of the same name exists. |
| Copyright © Ixora Pty Ltd |
|