We help IT Professionals succeed at work.
Get Started

Query Running Slow

monto1
monto1 asked
on
272 Views
Last Modified: 2012-12-18
11.2.0.1
AIX 6.1

I have following query which is executed from Application,the query comes back within 2 secs for not so common last_name combined with gender but it is taking longer time for common last_names like 'BROWN' or 'JONES' or 'SMITH' with gender 'MALE' .

Execution plan for the SMITH Last_Name and Genderid='2'---which is Male
SQL ID: f9nud0zpm2cac
Plan Hash: 3208566710
select customerin0_.ROW_NUMBER as ROW1_22596_, customerin0_.ADDRESS_ID as
  ADDRESS2_22596_, customerin0_.addressTypeId as addressT3_22596_,
  customerin0_.city as city22596_, customerin0_.CONTACT_DETAILS_CODE_ID as
  CONTACT5_22596_, customerin0_.county as county22596_, customerin0_.countyID
  as countyID22596_, customerin0_.dob as dob22596_, customerin0_.eyecolor as
  eyecolor22596_, customerin0_.eyecolorid as eyecolorid22596_,
  customerin0_.FIRST_NAME as FIRST11_22596_, customerin0_.gender as
  gender22596_, customerin0_.genderid as genderid22596_,
  customerin0_.ID_NUMBER as ID14_22596_, customerin0_.JURISDICTION_CODE as
  JURISDI15_22596_, customerin0_.LAST_NAME as LAST16_22596_,
  customerin0_.LEGAL_ENTITY_ID as LEGAL17_22596_,
  customerin0_.LEGAL_ENTITY_NUMBER as LEGAL18_22596_,
  customerin0_.LEGAL_ENTITY_TYPE as LEGAL19_22596_, customerin0_.MIDDLE_NAME
  as MIDDLE20_22596_, customerin0_.STREET_ADDRESS_1 as STREET21_22596_,
  customerin0_.suffix_value as suffix22_22596_, customerin0_.ZIP_CODE as
  ZIP23_22596_, customerin0_.ZIP_PLUS_4 as ZIP24_22596_
from
 CUSTOMER_IND_SEARCH_VIEW customerin0_ where (customerin0_.LAST_NAME like :1)
  and customerin0_.genderid=:2 and rownum<=200


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch       18      1.88     123.53       8245      12403          0         179
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      1.88     123.53       8245      12403          0         179

Misses in library cache during parse: 0
Parsing user id: 135  (???)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  ges message buffer allocation                 259        0.00          0.00
  gc cr grant 2-way                             259        0.00          0.08
  db file sequential read                     10126        1.40        148.07
  SQL*Net message from client                    19        0.02          0.10
  SQL*Net message to client                      18        0.00          0.00
********************************************************************************

Open in new window


PLAN_TABLE_OUTPUT

SQL_ID  f9nud0zpm2cac, child number 0
-------------------------------------
select customerin0_.ROW_NUMBER as ROW1_22596_, customerin0_.ADDRESS_ID 
as ADDRESS2_22596_, customerin0_.addressTypeId as addressT3_22596_, 
customerin0_.city as city22596_, customerin0_.CONTACT_DETAILS_CODE_ID 
as CONTACT5_22596_, customerin0_.county as county22596_, 
customerin0_.countyID as countyID22596_, customerin0_.dob as dob22596_, 
customerin0_.eyecolor as eyecolor22596_, customerin0_.eyecolorid as 
eyecolorid22596_, customerin0_.FIRST_NAME as FIRST11_22596_, 
customerin0_.gender as gender22596_, customerin0_.genderid as 
genderid22596_, customerin0_.ID_NUMBER as ID14_22596_, 
customerin0_.JURISDICTION_CODE as JURISDI15_22596_, 
customerin0_.LAST_NAME as LAST16_22596_, customerin0_.LEGAL_ENTITY_ID 
as LEGAL17_22596_, customerin0_.LEGAL_ENTITY_NUMBER as LEGAL18_22596_, 
customerin0_.LEGAL_ENTITY_TYPE as LEGAL19_22596_, 
customerin0_.MIDDLE_NAME as MIDDLE20_22596_, 
customerin0_.STREET_ADDRESS_1 as STREET21_22596_, 
customerin0_.suffix_value as suffix22_22596_, customerin0_.ZIP_CODE as 
ZIP23_2
 
Plan hash value: 3208566710
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |       |       |  5751 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID      | REF_COUNTIES                 |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN               | XPK_REF_COUNTIES             |     1 |       |     0   (0)|          |
|   3 |  TABLE ACCESS BY INDEX ROWID      | REF_EYE_COLORS               |     1 |    10 |     1   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN               | XPK_REF_EYE_COLORS           |     1 |       |     0   (0)|          |
|   5 |  TABLE ACCESS BY INDEX ROWID      | REF_GENDER                   |     1 |     9 |     1   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN               | XPK_REF_GENDER               |     1 |       |     0   (0)|          |
|*  7 |  COUNT STOPKEY                    |                              |       |       |            |          |
|   8 |   NESTED LOOPS OUTER              |                              |   201 | 41808 |  5751   (1)| 00:01:10 |
|   9 |    NESTED LOOPS                   |                              |   201 | 34572 |  5160   (1)| 00:01:02 |
|  10 |     NESTED LOOPS                  |                              |   805 | 73255 |  3477   (1)| 00:00:42 |
|  11 |      NESTED LOOPS                 |                              |   805 | 51520 |  1866   (1)| 00:00:23 |
|  12 |       NESTED LOOPS                |                              |   805 | 33005 |   254   (0)| 00:00:04 |
|* 13 |        TABLE ACCESS BY INDEX ROWID| REF_CONTACT_DETAILS          |     1 |    10 |     1   (0)| 00:00:01 |
|* 14 |         INDEX UNIQUE SCAN         | XAK1_REF_CD_VALUE            |     1 |       |     0   (0)|          |
|  15 |        TABLE ACCESS BY INDEX ROWID| PERSON_PROFILE_NAMES         |   805 | 24955 |   253   (0)| 00:00:04 |
|* 16 |         INDEX RANGE SCAN          | IDX$$_D7D50001               |   805 |       |     4   (0)| 00:00:01 |
|* 17 |       TABLE ACCESS BY INDEX ROWID | PERSON_PROFILE               |     1 |    23 |     2   (0)| 00:00:01 |
|* 18 |        INDEX UNIQUE SCAN          | XPK_PERSON_PROFILE           |     1 |       |     1   (0)| 00:00:01 |
|* 19 |      TABLE ACCESS BY INDEX ROWID  | LEGAL_ENTITY                 |     1 |    27 |     2   (0)| 00:00:01 |
|* 20 |       INDEX UNIQUE SCAN           | XPK_LEGAL_ENTITY             |     1 |       |     1   (0)| 00:00:01 |
|* 21 |     TABLE ACCESS BY INDEX ROWID   | LEGAL_ENTITY_ADDRESSES       |     1 |    81 |     3   (0)| 00:00:01 |
|* 22 |      INDEX RANGE SCAN             | LENUM_CODE_ID                |     1 |       |     2   (0)| 00:00:01 |
|* 23 |    TABLE ACCESS BY INDEX ROWID    | LEGAL_ENTITY_IDENTIFICATIONS |     1 |    36 |     3   (0)| 00:00:01 |
|* 24 |     INDEX RANGE SCAN              | XIF1_LEGAL_ENTITY_ID         |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("REFCOUNTIES"."ID"=:B1)
   4 - access("REFEYECOLORS"."ID"=:B1)
   6 - access("REFGENDER"."ID"=:B1)
   7 - filter(ROWNUM<=200)
  13 - filter("REFCONTACTDETAILS"."MODE_TYPE"=4)
  14 - access("REFCONTACTDETAILS"."VALUE"='PRA')
  16 - access("PERSONPROFILENAMES"."LAST_NAME" LIKE :1 AND "PERSONPROFILENAMES"."IS_DELETED"=0)
       filter(("PERSONPROFILENAMES"."LAST_NAME" LIKE :1 AND "PERSONPROFILENAMES"."IS_DELETED"=0))
  17 - filter("SYS_ALIAS_2"."GENDER_ID"=:2)
  18 - access("SYS_ALIAS_2"."PP_LEGALENTITY_NUMBER"="PERSONPROFILENAMES"."PP_LEGALENTITY_NUMBER")
  19 - filter(("LEGALENTITY"."IS_DELETED"=0 OR "LEGALENTITY"."IS_DELETED"=NULL))
  20 - access("LEGALENTITY"."LEGAL_ENTITY_NUMBER"="SYS_ALIAS_2"."PP_LEGALENTITY_NUMBER")
  21 - filter("LEGALENTITYADDRESSES"."IS_DELETED"=0)
  22 - access("LEGALENTITY"."LEGAL_ENTITY_NUMBER"="LEGALENTITYADDRESSES"."LEGAL_ENTITY_NUMBER" AND 
              "REFCONTACTDETAILS"."ID"="LEGALENTITYADDRESSES"."CONTACT_DETAILS_CODE_ID")
  23 - filter(("LEGAL_ENTITY_ID"."END_DATE">=SYSDATE@! AND "LEGAL_ENTITY_ID"."EFFECTIVE_DATE"<=SYSDATE@!))
  24 - access("LEGAL_ENTITY_ID"."LEGAL_ENTITY_NUMBER"="LEGALENTITY"."LEGAL_ENTITY_NUMBER")
 

Open in new window



VIEW
CREATE OR REPLACE FORCE VIEW XMATAPP.CUSTOMER_IND_SEARCH_VIEW (
   ROW_NUMBER,
   LEGAL_ENTITY_NUMBER,
   LEGAL_ENTITY_ID,
   LEGAL_ENTITY_TYPE,
   FIRST_NAME,
   LAST_NAME,
   MIDDLE_NAME,
   ID_NUMBER,
   DOB,
   GENDERID,
   GENDER,
   EYECOLORID,
   EYECOLOR,
   SUFFIX_VALUE,
   CONTACT_DETAILS_CODE_ID,
   ADDRESSTYPEID,
   STREET_ADDRESS_1,
   CITY,
   COUNTYID,
   JURISDICTION_CODE,
   ZIP_CODE,
   ZIP_PLUS_4,
   ADDRESS_ID,
   COUNTY,
   IS_DELETED,
   NAME_TYPE,
   IS_PENDED
)
AS
   SELECT   name_type || legal_entity_number AS ROW_NUMBER,
            legal_entity_number,
            legal_entity_id,
            legal_entity_type,
            first_name,
            last_name,
            middle_name,
            id_number,
            dob,
            gender1 genderid,
            gender,
            eye_color_id eyecolorid,
            eyecolor,
            suffix_value,
            contact_details_code_id,
            addresstypeid,
            street_address_1,
            city,
            county1 countyid,
            jurisdiction_code,
            zip_code,
            zip_plus_4,
            address_id,
            county,
            is_deleted,
            name_type,
            is_pended
     FROM   (SELECT   v2.*,
                      (SELECT   refgender.description
                         FROM   ref_gender refgender
                        WHERE   v2.gender1 = refgender.id)
                         gender,
                      (SELECT   refeyecolors.description
                         FROM   ref_eye_colors refeyecolors
                        WHERE   v2.eye_color_id = refeyecolors.id)
                         eyecolor,
                      (SELECT   refcounties.description
                         FROM   ref_counties refcounties
                        WHERE   refcounties.id = v2.county1)
                         county
               FROM   (SELECT   v1.*
                         FROM   (SELECT   legalentity.legal_entity_number,
                                          legalentity.legal_entity_id,
                                          legalentity.legal_entity_type,
                                          personprofilenames.first_name,
                                          personprofilenames.last_name,
                                          personprofilenames.middle_name,
                                          legal_entity_id.id_number,
                                          personprofile.dob,
                                          legalentityaddresses.contact_details_code_id,
                                          legalentityaddresses.address_type
                                             addresstypeid,
                                          legalentityaddresses.street_address_1,
                                          legalentityaddresses.city,
                                          legalentityaddresses.country_id
                                             countyid,
                                          legalentityaddresses.jurisdiction_code,
                                          legalentityaddresses.zip_code,
                                          legalentityaddresses.zip_plus_4,
                                          legalentityaddresses.id address_id,
                                          legalentity.is_deleted is_deleted,
                                          personprofilenames.name_type_id
                                             name_type,
                                          personprofile.eye_color_id,
                                          personprofilenames.suffix_value
                                             suffix_value,
                                          personprofilenames.transaction_id
                                             ptxnId,
                                          personprofile.gender_id gender1,
                                          legalentityaddresses.county county1,
                                          legalentity.is_pended is_pended
                                   FROM                  legal_entity legalentity
                                                      LEFT OUTER JOIN
                                                         legal_entity_identifications legal_entity_id
                                                      ON legal_entity_id.legal_entity_number =
                                                            legalentity.legal_entity_number
                                                         AND (SYSDATE BETWEEN legal_entity_id.effective_date
                                                                          AND  legal_entity_id.end_date)
                                                   INNER JOIN
                                                      person_profile personprofile
                                                   ON legalentity.legal_entity_number =
                                                         personprofile.pp_legalentity_number
                                                INNER JOIN
                                                   person_profile_names personprofilenames
                                                ON personprofile.pp_legalentity_number =
                                                      personprofilenames.pp_legalentity_number
                                             INNER JOIN
                                                legal_entity_addresses legalentityaddresses
                                             ON legalentity.legal_entity_number =
                                                   legalentityaddresses.legal_entity_number
                                          INNER JOIN
                                             ref_contact_details refcontactdetails
                                          ON refcontactdetails.id =
                                                legalentityaddresses.contact_details_code_id
                                             AND refcontactdetails.VALUE =
                                                   'PRA'
                                             AND refcontactdetails.mode_type =
                                                   4
                                  WHERE   (legalentity.is_deleted = 0
                                           OR legalentity.is_deleted = NULL)
                                          AND legalentityaddresses.is_deleted =
                                                0
                                          AND personprofilenames.is_deleted =
                                                0) v1) v2) v3;

Open in new window



PERSONPROFILENAMES-16 Milion rows
LEGALENTITYADDRESSES-34 Milion rows
PERSONPROFILE-16 Milion rows
LEGALENTITY 17 Milion rows
refcontactdetails 45 rows


Thanks
Comment
Watch Question
IT-specialist
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 10 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE