Help - Search - Members - Calendar
Full Version: sql tunning
Oracle DBA Forums > Oracle > Oracle Forum
poratips
Hi,
I have following query which is running slow.
Could you please guide me that how can tune the query to make it faster?
I have Oracle 9i R2.

SELECT
DISTINCT a.EADDR,
a.MID
FROM MEM a
WHERE ( a.Lst='gbby'
AND a.MType='normal'
AND a.SbType_='mail'
AND a.EADDR IS NOT NULL )
AND ( ((a.DTJOINED < '23-JUN-08') AND
(a.C_BHDAY1 IS NULL))
AND
(NOT EXISTS (SELECT /*+ INDEX(clltrk_IX_clltrk_TmeClk) */ NULL FROM clktrck b
WHERE a.MID = b.MID ))


I have currently following index on this table:


PK_MEM MID UNIQUE
MEM_USR_IX1 Lst, MTYPE, SbTYPE, EADDR, DtJOINED, C_BDAY1 NONUNIQUE
IX_MEM_MTYPE MTYPE, LST, DtJOINED NONUNIQUE



My Explain plan is following

TABLE ACCESS(BY INDEX ROWID) MEM ANALYZED 369 120 7920
INDEX(RANGE SCAN) IX_MEM_MTYPE ANALYZED 30 2781
INDEX(RANGE SCAN) IX_ClkTrk2 ANALYZED 3 4 24

Total cost is 754

Other join table has following index:

IX_Clktrk2 MID, URLID NONUNIQUE
IX_Clktrk_TmeClk TMECLCK, MID, CLKID NONUNIQUE

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--------

| Id | Operation | Name | Rows | Bytes |
Cost |

--------------------------------------------------------------------------------
--------

| 0 | SELECT STATEMENT | | 120 | 7920 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
754 |

| 1 | SORT UNIQUE | | 120 | 7920 |
394 |

| 2 | FILTER | | | |
|

| 3 | TABLE ACCESS BY INDEX ROWID| MEMB | 120 | 7920 |
369 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | INDEX RANGE SCAN | IX_MEM_MTYPE | 2781 | |
30 |

| 5 | INDEX RANGE SCAN | IX_ClkTrack2 | 4 | 24 |
3 |

--------------------------------------------------------------------------------
--------



13 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'




Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
38 consistent gets
0 physical reads
52 redo size
1233 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
13 rows processed

In table MEM - 2507803 Rows
In Table clktrck - 1881306 Rows


SELECT COUNT(*)
FROM MEM a
WHERE a.Lst='gbby'
AND a.MType='normal'
AND a.SbType_='mail'
AND a.EADDR IS NOT NULL
AND a.DTJOINED < to_date('23-JUN-2008', 'DD-MON-YYYY')
AND a.C_BHDAY1 IS NULL


Returns - 538493 Rows


Thanks so much!
SteveC
Quantify what "slow" means. Current stats on the tables? Re-write not using the NOT IN clause helps too.
poratips
QUOTE (SteveC @ Jul 11 2008, 01:15 PM) *
Quantify what "slow" means. Current stats on the tables? Re-write not using the NOT IN clause helps too.

Thanks Steve.
Its taking - Elapsed: 00:02:35.89 (From sql*plus)
We have current stats updated.
Please let me know if you need any further information.

Number of Rows" "Blocks" "Average Row Length" "Sample Size" "Last Analyzed" "Last Analyzed Since"
"499" "306" "217" "499" "07-JULY-08" "07-JULY-08"




Thanks,
SteveC
Re-write the query, don't use not exists
http://www.dba-oracle.com/sql/t_subquery_not_in_exists.htm

And please enclose code/output with [ code ] and [ /code ] tags (remove the space next to each bracket).
HAL9000
The query runs slow because of the embedded subquery.

Here are tips for tuning "not exists" SQL:

http://www.dba-oracle.com/oracle_news/2005...s_minus_sql.htm

Can you re-wite it to replace the NOT EXISTS with a MINUS?

*************************

Also, did you try a concantnated index?

create index textme on mem (lst, mtype, sbtype);


a.Lst='gbby'
AND a.MType='normal'
AND a.SbType_='mail'
AND a.EADDR IS NOT NULL )
AND ( ((a.DTJOINED < '23-JUN-08') AND
(a.C_BHDAY1 IS NULL))
poratips
QUOTE (SteveC @ Jul 11 2008, 04:43 PM) *
Re-write the query, don't use not exists
http://www.dba-oracle.com/sql/t_subquery_not_in_exists.htm

And please enclose code/output with [ code ] and [ /code ] tags (remove the space next to each bracket).


Thanks much, i will check it out and let you know.
poratips
QUOTE (poratips @ Jul 12 2008, 12:21 PM) *
Thanks much, i will check it out and let you know.



Thanks Steve.
So if i replace the NOT EXISTS
CODE
AND
(NOT EXISTS (SELECT /*+ INDEX(clltrk_IX_clltrk_TmeClk) */ NULL FROM clktrck b
WHERE a.MID = b.MID ))

it will be like following?
CODE
AND
a.MID != b.MID ))



Thanks for your help!
HAL9000
Hi,

"So if i replace the NOT EXISTS"

Is it right? Only you can answer that question, because we are not privy to the rationale behind your table and system design.

Try it and see if you get the same results back, although that's no guarantee that it is fully equivalent.

To me this logic is unclear, but it's puzzling because the INDEX hint makes it look like it was done on purpose this way:

--- AND
--- (NOT EXISTS (SELECT /*+ INDEX(clltrk_IX_clltrk_TmeClk) */ NULL FROM clktrck b
--- WHERE a.MID = b.MID ))

How would you say this statement, in plain English?


"select from mem where there are no matching rows in the clktrck table"

So, I don't think that "a.MID != b.MID" is equivalent.

Could you say this with an outer join?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.