Sorted Hash Clusters

Page 63

TRACE_TYPE     PRIMARY_ID WAITS BINDS --------------------- ---------- ----- ----- CLIENT_ID      PROD_USER TRUE FALSE

You can disable session-level monitoring by using the procedure dbms_ monitor.client_id_trace_disable, as shown here:

 Exec - dbms_monitor.client_id_trace_disable(client_id=>'PROD_USER');

Also, the dbms_monitor package allows you to enable tracing based on a combination of service name, module name (optional), instance name (optional), and action name (optional) via the serv_mod_act_trace_enable procedure.

For any specific session that is actively tracing, you can use the procedure dbms_monitor.serv_mod_act_trace_disable to turn off tracing for that session. You provide the session ID and serial number of that session to the procedure as shown in this example:

 Exec dbms_monitor.session_trace_disable(22,2044);

If your application is a high-velocity OLTP application and it tends to access data in the same order consistently, you might want to look at sorted hash clusters. Previous to Oracle Database 10g, there was no way to control the order in which data within a heap table was stored. As a result, unless your SQL statement included the order by clause, you would find your rows returned in an unordered fashion. Oracle Database 10g offers a sorted hash cluster, which ensures rows will be stored within the hash cluster in the order defined when you created the hash cluster. This can eliminate the overhead associated with sort operations that might otherwise be required, because data will be returned in a guaranteed order.

If your SQL statements against the sorted hash cluster should contain an order by clause that defines a sort operation that requires a different ordering of the rows than the cluster is configured for, you will lose the benefit of the sorted hash cluster, because the order by clause will force a sort operation. Therefore, take care when deciding how to order the columns of a sorted hash cluster. As with normal hash clusters, performance of a sorted hash cluster is very dependent on correctly configuring the cluster table. The hash function, number and size of hash keys, and the sort columns that the cluster is built on all require careful consideration.

When deciding on using a sorted hash cluster, you should make sure that the data lends itself to the creation of a hash key that will lead to few collisions (that is, more than one row sharing the same key), and that there are a fixed number of hash keys.

Example of Using a Sorted Hash Cluster

Let's look at an example of the use of a sorted hash cluster. In this example, we have a customer support center that is receiving calls from customers. Within the