Home » RDBMS Server » Performance Tuning » Query tuning with INDEX hint.
Query tuning with INDEX hint. [message #181002] Thu, 06 July 2006 04:43 Go to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Here are different results I am getting when I execute the explain plan. Can anybody please let me know the how I can tune this one. Thanks in advance.

Following is the relevent information:

1. Both table contain few million rows.

2. Index - LOAD_COR_CREW_ACCOUNTS_PK on LOAD_COR_CREW_ACCOUNTS
Index Columns - PERIOD_END_DT, BACC_ORG_ID

Index LOAD_COR_CREW_POSN_PK on LOAD_COR_CREW_POSN
Index Columns - PERIOD_END_DT, POSN_D, FI_ID, ACTY_N

3. Optimizer mode = CHOOSE

4. Both table and indexes analyzed recently.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

**** With INDEX hint ***

EXPLAIN PLAN
SET statement_id = 'my_id' INTO plan_table FOR
SELECT /*+ index(load_cor_crew_accounts_pk) */
DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_ACCOUNTS,
LOAD_COR_CREW_POSN
WHERE LOAD_COR_CREW_POSN.BACC_ORG_ID = LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID AND
LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y' AND
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN ('F2J', '0ZX', '0ZF', '0ZS') AND
LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00' AND
LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00'
;

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id and statement_id = 'my_id'
;

Operation Object
------------------------------ ----------------------
SELECT STATEMENT () --
SORT (UNIQUE) --
HASH JOIN () --
PARTITION RANGE (SINGLE) --
TABLE ACCESS (FULL) LOAD_COR_CREW_ACCOUNTS
PARTITION RANGE (SINGLE) --
INDEX (FAST FULL SCAN) LOAD_COR_CREW_POSN_PK

=================================================================

*** Without INDEX hint. Result same as previous ***

EXPLAIN PLAN
SET statement_id = 'my_id' INTO plan_table FOR
SELECT DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_ACCOUNTS,
LOAD_COR_CREW_POSN
WHERE LOAD_COR_CREW_POSN.BACC_ORG_ID = LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID AND
LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y' AND
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN ('F2J', '0ZX', '0ZF', '0ZS') AND
LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00' AND
LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00'
;

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id and statement_id = 'my_id'
;

Operation Object
------------------------------ ----------------------
SELECT STATEMENT () --
SORT (UNIQUE) --
HASH JOIN () --
PARTITION RANGE (SINGLE) --
TABLE ACCESS (FULL) LOAD_COR_CREW_ACCOUNTS
PARTITION RANGE (SINGLE) --
INDEX (FAST FULL SCAN) LOAD_COR_CREW_POSN_PK

=================================================================

*** Query converted to use subquery instead of join. Result same as previous ***

EXPLAIN PLAN
SET statement_id = 'my_id' INTO plan_table FOR
SELECT /*+ index(load_cor_crew_accounts_pk) */
DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_ACCOUNTS
WHERE LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00' AND
LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID IN (SELECT LOAD_COR_CREW_POSN.BACC_ORG_ID
FROM LOAD_COR_CREW_POSN
WHERE LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00') AND
LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y' AND
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN ('F2J', '0ZX', '0ZF', '0ZS')
;

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id and statement_id = 'my_id'
;

Operation Object
------------------------------ -----------------------
SELECT STATEMENT () --
SORT (UNIQUE) --
HASH JOIN () --
PARTITION RANGE (SINGLE) --
TABLE ACCESS (FULL) LOAD_COR_CREW_ACCOUNTS
PARTITION RANGE (SINGLE) --
INDEX (FAST FULL SCAN) LOAD_COR_CREW_POSN_PK

=================================================================

*** Subquery changed to select first 100 rows. Index LOAD_COR_CREW_ACCOUNTS_PK on LOAD_COR_CREW_ACCOUNTS is being used ***

EXPLAIN PLAN
SET statement_id = 'my_id' INTO plan_table FOR
SELECT /*+ INDEX (LOAD_COR_CREW_ACCOUNTS_PK) */
DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_ACCOUNTS
WHERE LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00' AND
LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID IN (SELECT LOAD_COR_CREW_POSN.BACC_ORG_ID
FROM LOAD_COR_CREW_POSN
WHERE LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00' AND
ROWNUM < 101) AND
LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y' AND
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN ('F2J', '0ZX', '0ZF', '0ZS')
;

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id and statement_id = 'my_id'
;

Operation Object
------------------------------ --------------------------
SELECT STATEMENT () --
SORT (UNIQUE) --
NESTED LOOPS () --
VIEW () VW_NSO_1
COUNT (STOPKEY) --
PARTITION RANGE (SINGLE) --
INDEX (FAST FULL SCAN) LOAD_COR_CREW_POSN_PK
PARTITION RANGE (SINGLE) --
TABLE ACCESS (BY LOCAL IND LOAD_COR_CREW_ACCOUNTS
INDEX (UNIQUE SCAN) LOAD_COR_CREW_ACCOUNTS_PK

=================================================================

*** Subquery changed to select first 1000001 rows. Full table scan of LOAD_COR_CREW_ACCOUNTS ***

EXPLAIN PLAN
SET statement_id = 'my_id' INTO plan_table FOR
SELECT /*+ INDEX (LOAD_COR_CREW_ACCOUNTS_PK) */
DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_ACCOUNTS
WHERE LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00' AND
LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID IN (SELECT LOAD_COR_CREW_POSN.BACC_ORG_ID
FROM LOAD_COR_CREW_POSN
WHERE LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00' AND
ROWNUM < 1000001) AND
LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y' AND
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN ('F2J', '0ZX', '0ZF', '0ZS')
;

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id and statement_id = 'my_id'
;

Operation Object
------------------------------ ----------------------
SELECT STATEMENT () --
SORT (UNIQUE) --
HASH JOIN () --
PARTITION RANGE (SINGLE) --
TABLE ACCESS (FULL) LOAD_COR_CREW_ACCOUNTS
VIEW () VW_NSO_1
COUNT (STOPKEY) --
PARTITION RANGE (SINGLE) --
INDEX (FAST FULL SCAN) LOAD_COR_CREW_POSN_PK

=================================================================

Report message to a moderator

Re: Query tuning with INDEX hint. [message #181034 is a reply to message #181002] Thu, 06 July 2006 07:19 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
This is a shot in the dark, but does performance improve if you use TO_DATE on those strings?
SELECT /*+ index(load_cor_crew_accounts_pk) */
DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_ACCOUNTS,
LOAD_COR_CREW_POSN
WHERE LOAD_COR_CREW_POSN.BACC_ORG_ID = LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID AND
LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y' AND
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN ('F2J', '0ZX', '0ZF', '0ZS') AND
LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = TO_DATE('05/31/2006 00:00:00','MM/DD/YYYY HH24:MI:SS')
LOAD_COR_CREW_POSN.PERIOD_END_DT = TO_DATE('05/31/2006 00:00:00','MM/DD/YYYY HH24:MI:SS')
;

Report message to a moderator

Re: Query tuning with INDEX hint. [message #181116 is a reply to message #181034] Thu, 06 July 2006 21:45 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The INDEX hint requires a table name or alias. Even if it uses the index, it may still choose a FULL or FAST FULL scan and a hash join. In order to get it to use Nested Loops, you may also need a USE_NL hint. With some alder versions of Oracle, you needed an ORDERED hint to make the CBO take notice of a join method hint like USE_NL. To use ORDERED, you need put the tables in the FROM clause in order of access.

If the code below works, you can try removing the ordered and use_nl hints.

SELECT /*+ ordered 
  use_nl(LOAD_COR_CREW_ACCOUNTS)
  index(LOAD_COR_CREW_ACCOUNTS.load_cor_crew_accounts_pk) */
  DISTINCT 
  LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
  LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
  LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
  LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
  LOAD_COR_CREW_ACCOUNTS.ACC_CL_C 
FROM LOAD_COR_CREW_POSN ,
  LOAD_COR_CREW_ACCOUNTS,   
WHERE LOAD_COR_CREW_POSN.BACC_ORG_ID =
      LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID 
AND   LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y' 
AND   LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN ('F2J', '0ZX', '0ZF', '0ZS') 
AND   LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00'
AND   LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00'


Ross Leishman

Report message to a moderator

Re: Query tuning with INDEX hint. [message #181136 is a reply to message #181002] Fri, 07 July 2006 00:44 Go to previous messageGo to next message
ramesh_samane
Messages: 63
Registered: May 2006
Member
Please check the queries below:

Both uses now index on LOAD_COR_CREW_ACCOUNTS, but still does full scan of LOAD_COR_CREW_POSN.

How can I formulate a query to use indexes on both tables?


SELECT
/*+ ordered
use_nl(LOAD_COR_CREW_ACCOUNTS)
index(LOAD_COR_CREW_ACCOUNTS.load_cor_crew_accounts_pk) */
DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_POSN,
LOAD_COR_CREW_ACCOUNTS
WHERE LOAD_COR_CREW_POSN.BACC_ORG_ID =
LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID
AND LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y'
AND LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN
('F2J', '0ZX', '0ZF', '0ZS')
AND LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00'
AND LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00'
;

Operation Object
------------------------------ --------------------------
SELECT STATEMENT () --
SORT (UNIQUE) --
NESTED LOOPS () --
PARTITION RANGE (SINGLE) --
TABLE ACCESS (FULL) LOAD_COR_CREW_POSN
PARTITION RANGE (SINGLE) --
TABLE ACCESS (BY LOCAL IND LOAD_COR_CREW_ACCOUNTS
INDEX (UNIQUE SCAN) LOAD_COR_CREW_ACCOUNTS_PK

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT
/*+ ordered
use_nl(LOAD_COR_CREW_ACCOUNTS)
index(LOAD_COR_CREW_ACCOUNTS.load_cor_crew_accounts_pk)
index(LOAD_COR_CREW_POSN.LOAD_COR_CREW_POSN_PK)*/
DISTINCT LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID,
LOAD_COR_CREW_ACCOUNTS.LD_BASE_C,
LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C,
LOAD_COR_CREW_ACCOUNTS.PRD_LV_C,
LOAD_COR_CREW_ACCOUNTS.ACC_CL_C
FROM LOAD_COR_CREW_POSN ,
LOAD_COR_CREW_ACCOUNTS
WHERE LOAD_COR_CREW_POSN.BACC_ORG_ID =
LOAD_COR_CREW_ACCOUNTS.BACC_ORG_ID
AND LOAD_COR_CREW_ACCOUNTS.OPEN_ACC_I = 'Y'
AND LOAD_COR_CREW_ACCOUNTS.LD_SBRX_C NOT IN
('F2J', '0ZX', '0ZF', '0ZS')
AND LOAD_COR_CREW_ACCOUNTS.PERIOD_END_DT = '05/31/2006 00:00:00'
AND LOAD_COR_CREW_POSN.PERIOD_END_DT = '05/31/2006 00:00:00'
;

Operation Object
------------------------------ --------------------------
SELECT STATEMENT () --
SORT (UNIQUE) --
NESTED LOOPS () --
PARTITION RANGE (SINGLE) --
TABLE ACCESS (FULL) LOAD_COR_CREW_POSN
PARTITION RANGE (SINGLE) --
TABLE ACCESS (BY LOCAL IND LOAD_COR_CREW_ACCOUNTS
INDEX (UNIQUE SCAN) LOAD_COR_CREW_ACCOUNTS_PK

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Report message to a moderator

Re: Query tuning with INDEX hint. [message #181406 is a reply to message #181136] Sun, 09 July 2006 21:56 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think you should double-check the structure of the index LOAD_COR_CREW_ACCOUNTS.load_cor_crew_accounts_pk. It doesn't include the columns you think it does.

My reasons:
- You had a previous SQL and Explain Plan that used load_cor_crew_accounts_pk, but did not lookup the table. This means that all columns used in the SQL could be satisfied by the index. But the SQL uses LOAD_COR_CREW_POSN.BACC_ORG_ID, which is not in the index. It seems likely that (PERIOD_END_DT, POSN_D, FI_ID, ACTY_N) are not the columns in the index.
- If the index does indeed contain PERIOD_END_DT and BACC_ORG_ID, then - if PERIOD_END_DT was the first column in the index - then the earlier SQL that used that index would have performed a RANGE SCAN, not a FAST FULL SCAN. It seems likely that this index may well contain PERIOD_END_DT and BACC_ORG_ID, but not in that order.

Check the index and see if that explains your problem. If PERIOD_END_DT is not the leading column of the index, then there is no benefit to scanning on that index, therefore Oracle ignores the hint.

Also make sure that the tables, ALL partitions, indexes, and ALL index partitions have current statistics gethered with DBMS_STATS.
Ross Leishman

Report message to a moderator

Previous Topic: Performance issue with Analytical function
Next Topic: Help with explain plans
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon May 06 09:21:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.