Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 188

For every join operation Oracle computes the cost of accessing the rows for a nested loop join (NL join),
sort merge join (SM join), and hash join (HA join) operation. From these three access methods Oracle selects
the operation that takes the least cost for execution. This is illustrated by the best so far after each join order cost
computation.

***********************
Best so far: Table#:0 cost:5458.2261 card:4299997.0000 bytes:47299967
Table#:1 cost:259483.2873 card:42592862.7173 bytes:1405564479
***********************

For the query used in this illustration, Oracle created 14 different join order checks (not all join orders have been
illustrated for formatting and easy understanding purposes). However, if the best cost computed for a specific join
order is not better than the next, Oracle does not calculate the best cost for the specific join order.

■ in complex queries where there are several tables being joined, oracle has to compute join orders for all
possible table orders, which can be time-consuming. if queries are not being reused, which happens, for example, when
literals instead of bind variables are used, oracle will have to compute these join orders every single time and could
create more severe performance problems.

Note

Similarly to avoiding several join orders in a complex query with several table joins, the /*ORDERED*/ hint
becomes very helpful. This hint will enforce the join order, and the optimizer, based on the hint, will use the
user-specified join order for query execution.

Service-Module-Action
Oracle allows statistics collection at the following different levels:

• At the system level : Statistics from the time the instance was started can be collected and
viewed from V$SYSSTAT or GV$SYSSTAT views.

• At the session level : Statistics for the active sessions can be collected and viewed from
V$SESSTAT or GV$SESSSTAT views. Due to the volatile nature of the sessions, the life of statistics
in these views is retained only for the life of the session. Statistics for the expired sessions can
be viewed from V$ACTIVE_SESSION_HISTORY .

• At the service level : Sessions performing a similar kind of functionality can be grouped into
database services. A service can be a subset of an application. This grouping provides a more
granular instrumentation into the performance of these sessions when compared to the entire
application. Statistics at the service level can be collected and viewed from the
V$SERVICE_STATS view.
Oracle provides an additional level of data collection by defining modules within services or actions within
modules. This helps in easy identification of performance areas within the application. Module and action level
monitoring can be enabled using the following PL/SQL definition:

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE (<SERVICE_NAME>, <MODULE NAME>)