Home » RDBMS Server » Performance Tuning » Query running slow (11.2.0.2.5 , Solaris 10)
Query running slow [message #559737] Thu, 05 July 2012 15:53 Go to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
HI Friends,

The below query is run by a developer. Please cna you help me re-write this? This is running to slow and doing a lot of full table scans


SELECT   a.id_member,
         a.code_hotel
FROM    tmp_enroll_cur_mth a
        LEFT OUTER JOIN tmp_enroll_pri_mth b
        ON (a.first_name = b.first_name
           AND a.last_name = b.last_name
           AND (a.email_addr = b.email_addr
                OR a.code_postal = b.code_postal))
;



and the explain plan for it is



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2907925857

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |  1711K|   226M|  1382M  (8)|999:59:59 |
|   1 |  NESTED LOOPS OUTER |                    |  1711K|   226M|  1382M  (8)|999:59:59 |
|   2 |   TABLE ACCESS FULL | TMP_ENROLL_CUR_MTH | 77806 |    10M|   189   (5)| 00:00:02 |
|   3 |   VIEW              |                    |    22 |       | 17772   (8)| 00:02:46 |
|*  4 |    TABLE ACCESS FULL| TMP_ENROLL_PRI_MTH |    22 |  2574 | 17772   (8)| 00:02:46 |
------------------------------------------------------------------------------------------

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

   4 - filter("A"."FIRST_NAME"="B"."FIRST_NAME" AND
              "A"."LAST_NAME"="B"."LAST_NAME" AND ("A"."EMAIL_ADDR"="B"."EMAIL_ADDR" OR
              "A"."CODE_POSTAL"="B"."CODE_POSTAL"))

Note
-----
   - dynamic sampling used for this statement (level=2)

22 rows selected.



Thx!
Re: Query running slow [message #559742 is a reply to message #559737] Thu, 05 July 2012 17:08 Go to previous message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
>ON (a.first_name = b.first_name
> AND a.last_name = b.last_name

do indexes exist on all 4 columns above?
Are statistics current for all tables & indexes involved?

http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof

Previous Topic: How to find out who is the locking SQL statement?
Next Topic: DB_BLOCK_SIZE
Goto Forum:
  


Current Time: Tue Sep 16 19:20:06 CDT 2014

Total time taken to generate the page: 0.08848 seconds