SQL tuning using Hint

INDEX(table [index])
Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
Existing View on Testbed

CREATE OR REPLACE FORCE VIEW “VWMYCONCURRENTUSERS” (“CUI”, “RADIUS_POLICY”, “DEVICENAME”, “DEVICEPORT”, “USERNAME”, “USER_IDENTITY”, “USER_NAME”, “GROUPNAME”, “NAS_IP_ADDRESS”, “CLASS”, “ACCT_SESSION_ID”, “FRAMED_IP_ADDRESS”) AS
SELECT b.CUI,
b.RADIUSPOLICY,
b.DEVICENAME,
b.DEVICEPORT,
b.USERNAME,
b.USER_IDENTITY,
a.USER_NAME,
a.GROUPNAME,
a.NAS_IP_ADDRESS,
a.CLASS,
a.ACCT_SESSION_ID,
a.FRAMED_IP_ADDRESS
FROM tblmymconcurrentusers a,
tblmyradiuscustomer b
WHERE a.user_name = b.username
ORDER BY a.last_updated_time;

   PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1895085345

--------------------------------------------------------------------------------
| Id  | Operation            | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------
------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     | 12855 |  4280K|       |  1698   (1)| 00:00:21 |       |       |
|   1 |  SORT ORDER BY       |                     | 12855 |  4280K|  4688K|  1698   (1)| 00:00:21 |       |       |
|*  2 |   HASH JOIN          |                     | 12855 |  4280K|       |   758   (1)| 00:00:10 |       |       |
|   3 |    TABLE ACCESS FULL | TBLMYRADIUSCUSTOMER   | 16124 |   818K|       |   239   (1)| 00:00:03 |       |       |
|   4 |    PARTITION HASH ALL|                     | 12855 |  3628K|       |   518   (1)| 00:00:07 |     1 |     8 |
|   5 |     TABLE ACCESS FULL| TBLMYMCONCURRENTUSERS | 12855 |  3628K|       |   5
18   (1)| 00:00:07 |     1 |     8 |

--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."USER_NAME"="B"."USERNAME")

Tuned View on Production

SQL> EXPLAIN PLAN FOR
  2    SELECT /*+ index(b TBLRADIUSCUSTOMER_U01) */
  3    b.CUI,
  4      b.RADIUSPOLICY,
  5      b.DEVICENAME,
  6      b.DEVICEPORT,
  7      b.USERNAME,
  8      b.USER_IDENTITY,
  9      a.USER_NAME,
10      a.GROUPNAME,
11      a.NAS_IP_ADDRESS,
12      a.CLASS,
13      a.ACCT_SESSION_ID,
14      a.FRAMED_IP_ADDRESS,
15      a.last_updated_time
16    FROM  tblmyradiuscustomer b,
17    (select /*+ index(tblmymconcurrentusers IDX_SESSION_USER) */
 18    USER_NAME,
19    GROUPNAME,
20    NAS_IP_ADDRESS,
21    CLASS,
22    ACCT_SESSION_ID,
23    FRAMED_IP_ADDRESS,
24    last_updated_time
 25    from tblmymconcurrentusers )a
26    WHERE b.user_identity = a.user_name
27    ORDER BY a.last_updated_time;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1349771114

--------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows  |Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       | 12786 |4257K|       | 26596   (1)| 00:05:20 |       |       |
|   1 |  SORT ORDER BY                         |                       | 12786 |4257K|  4664K| 26596   (1)| 00:05:20 |       |       |
|   2 |   NESTED LOOPS                         |                       |       |     |       |            |          |       |       |
|   3 |    NESTED LOOPS                        |                       | 12786 |4257K|       | 25659   (1)| 00:05:08 |       |       |
|   4 |     PARTITION HASH ALL                 |                       | 12786 |3608K|       | 12867   (1)| 00:02:35 |     1 |     8 |
|   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| TBLMYMCONCURRENTUSERS   | 12786 |3608K|       | 12867   (1)| 00:02:35 | ROWID | ROWID |
|   6 |       INDEX FULL SCAN                  | IDX_SESSION_USER      | 12786 |     |       |    89   (0)| 00:00:02 |     1 |     8 |
|*  7 |     INDEX UNIQUE SCAN                  | TBLMYRADIUSCUSTOMER_U01 |     1 |     |       |     0   (0)| 00:00:01 |       |       |
|   8 |    TABLE ACCESS BY INDEX ROWID         | TBLMYRADIUSCUSTOMER     |     1 |
    52 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   7 - access("B"."USER_IDENTITY"="USER_NAME")

Ref
http://www.dba-oracle.com/t_sql_hints_tuning.htm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.