troubleshooting Question

Tuning the query based on the explain plan

Avatar of gattu007
gattu007 asked on
Oracle Database
3 Comments2 Solutions1098 ViewsLast Modified:
I need to tune this below query

 select icl.facility_code, icl.document_tsn, icl.image_server_id, icl.path, ims.server_name, ims.path, ims.port, dt.patient_id, dt.file_format, dt.document_type, dt.document_extension, icl.CONTENT_TYPE, icl.ADMISSION_NUM, to_char(DT.SERVICE_DATE_TIME, 'MM/DD/YYYY'), ims.LOCAL_MOUNT_PATH, nvl(icl.zipped_y, 'N'), icl.master_exists from image_copy_location icl, image_server ims, document_txn dt where nvl(icl.deleted_yn, 'N') = 'N' and ims.server_name = '10.85.0.21' and ims.protocol = 'http' and ims.image_server_id = icl.image_server_id and icl.master_exists = 'N' and dt.document_tsn = icl.document_tsn and dt.facility_code = icl.facility_code and dt.file_format=25

Current explain plan
----------------------------
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=25651      Cardinality=103      Bytes=16892
 NESTED LOOPS                  Cost=25651      Cardinality=103      Bytes=16892
  NESTED LOOPS                  Cost=16758      Cardinality=4405      Bytes=577055
   TABLE ACCESS FULL      Object owner=NNA      Object name=IMAGE_SERVER      Cost=5      Cardinality=1      Bytes=62
   TABLE ACCESS BY INDEX ROWID      Object owner=NNA      Object name=IMAGE_COPY_LOCATION      Cost=16753      Cardinality=8830      Bytes=609270
    INDEX RANGE SCAN      Object owner=NNA      Object name=ICL_MASTER_IDX      Cost=249      Cardinality=35689      
  TABLE ACCESS BY INDEX ROWID      Object owner=NNA      Object name=DOCUMENT_TXN      Cost=2      Cardinality=1      Bytes=33
   INDEX UNIQUE SCAN      Object owner=NNA      Object name=XPKDOCUMENT_TXN      Cost=1      Cardinality=1      

Desired explain plan shown by EM Tuning adviser.  Can you pls let know what are the changes required to attain the below query
-------------------------
SELECT STATEMENT 0
 
 8 1 0.160 78234 939 4072929792 73802
HASH JOIN 1
 
 7 1 0.160 78234 939 4072929792 73802
TABLE ACCESS FULL 2 DOCUMENT_TXN TABLE 1 143982 4,640.045 35218 423 2640646656 32344
TABLE ACCESS BY INDEX ROWID 3 IMAGE_COPY_LOCATION TABLE 6 10278 692.561 9725 117 137806336 9575
NESTED LOOPS 4
 
 5 4672678 597,774.250 9727 117 137821056 9577
TABLE ACCESS BY INDEX ROWID 5 IMAGE_SERVER TABLE 3 1 0.061 2 1 14718 2
INDEX RANGE SCAN 6 IMG_SERV_IDX1 INDEX 2 1
 1 1 7321 1
INDEX RANGE SCAN 7 IMG_COPY_LOC_IDX1 INDEX 4 77353
 176 3 16596638 158
ASKER CERTIFIED SOLUTION
Mark Geerlings
Database Administrator, retired

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros