Home » RDBMS Server » Performance Tuning » Optimize Query using Explain Plan (10g)
Optimize Query using Explain Plan [message #592433] Thu, 08 August 2013 06:26 Go to next message
developer12
Messages: 88
Registered: July 2013
Member
Hi,
I have the following piece of code:

         SELECT                                                 /*+DRIVING_SITE(B)*/
                                                          /*+PARALLEL(A,100)*/
          B.SECURITY_ALIAS,
          '91821XAD5',
          A.SECURITY_ALIAS,
          '91821XAD5',
          'SECURITY_MASTER_DETAIL_HIST',
          'USER_GROUP_CHAR8',
          B.USER_GROUP_CHAR8,
          CAST (A.USER_GROUP_CHAR8 AS VARCHAR2 (100))
     FROM APP_DEVELOPMENT.SEC_MASTER_DTL_HIST_TBL A,
          SECURITYDBO.SECURITY_MASTER_DETAIL_HIST B
    WHERE     A.SRC_INTFC_INST = 140
          AND B.SRC_INTFC_INST = 140
          AND A.SECURITY_ALIAS = 100144129
          AND b.security_alias = 224719
          AND b.effective_date ='25-Apr-2006'
          AND A.USER_GROUP_CHAR8 <> B.USER_GROUP_CHAR8
   UNION ALL
   SELECT /*+DRIVING_SITE(B)*/
                                                          /*+PARALLEL(A,100)*/
          B.SECURITY_ALIAS,
          '91821XAD5',
          A.SECURITY_ALIAS,
          '91821XAD5',
          'SECURITY_MASTER_DETAIL_HIST',
          'USER_GROUP_CHAR8',
          B.USER_GROUP_CHAR8,
          'No Records Found'
     FROM APP_DEVELOPMENT.SEC_MASTER_DTL_HIST_TBL A,
          SECURITYDBO.SECURITY_MASTER_DETAIL_HIST B
    WHERE     A.SRC_INTFC_INST = 140
          AND B.SRC_INTFC_INST = 140
          AND A.SECURITY_ALIAS = 100144129
          AND b.security_alias = 224719
          AND b.effective_date ='25-Apr-2006'
          AND SYS_OP_MAP_NONNULL (A.USER_GROUP_CHAR8) = 'FF'
          AND SYS_OP_MAP_NONNULL (B.USER_GROUP_CHAR8) <> 'FF'


and the explain plan is as follows:

Plan
SELECT STATEMENT CHOOSECost: 10 Bytes: 162 Cardinality: 2
9 UNION-ALL
4 NESTED LOOPS Cost: 5 Bytes: 81 Cardinality: 1
2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE SECURITYDBO.SECURITY_MASTER_DETAIL_HIST Cost: 3 Bytes: 23 Cardinality: 1 Partition #: 3 Partitions accessed #18
1 INDEX UNIQUE SCAN INDEX (UNIQUE) SECURITYDBO.PK_SECURITY_MASTER_DETAIL_HIST Cost: 2 Cardinality: 1
3 TABLE ACCESS FULL TABLE (TEMP) EL_APP_DEVELOPMENT.BNYM_SEC_MASTER_DTL_HIST_TBL Cost: 2 Bytes: 58 Cardinality: 1
8 NESTED LOOPS Cost: 5 Bytes: 81 Cardinality: 1
6 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE SECURITYDBO.SECURITY_MASTER_DETAIL_HIST Cost: 3 Bytes: 23 Cardinality: 1 Partition #: 7 Partitions accessed #18
5 INDEX UNIQUE SCAN INDEX (UNIQUE) SECURITYDBO.PK_SECURITY_MASTER_DETAIL_HIST Cost: 2 Cardinality: 1
7 TABLE ACCESS FULL TABLE (TEMP) EL_APP_DEVELOPMENT.BNYM_SEC_MASTER_DTL_HIST_TBL Cost: 2 Bytes: 58 Cardinality: 1

Can anyone help me read the explain plan and optimize the query??
Re: Optimize Query using Explain Plan [message #592434 is a reply to message #592433] Thu, 08 August 2013 06:31 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
My question would be, how do you expect anyone to read that explain plan?
Re: Optimize Query using Explain Plan [message #592435 is a reply to message #592433] Thu, 08 August 2013 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Optimize Query using Explain Plan [message #592436 is a reply to message #592434] Thu, 08 August 2013 06:33 Go to previous messageGo to next message
developer12
Messages: 88
Registered: July 2013
Member
Let me know how I can format the explain Plan here?
Re: Optimize Query using Explain Plan [message #592437 is a reply to message #592436] Thu, 08 August 2013 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Optimize Query using Explain Plan [message #592569 is a reply to message #592437] Fri, 09 August 2013 11:47 Go to previous message
developer12
Messages: 88
Registered: July 2013
Member
Thanks for all the help guys.
I am finally able to do this with a single SQL script using a NVL in the WHERE clause checking the column values.
Previous Topic: Full Index Scan issue
Next Topic: Please help me to improve the performance of this query.
Goto Forum:
  


Current Time: Sun Nov 23 10:27:28 CST 2014

Total time taken to generate the page: 0.07145 seconds