Solved

Oracle SQL Query Tunning

Posted on 2012-03-21
6
539 Views
Last Modified: 2012-04-23
Hi,
I need a help on tuning the below query, i have attached a explain plan also for this query.
Is there any good document or tips to help for how to tune the SQL queries in Oracle 9i DB.
One more question before deletion a bulk record with the below query instead of select if we we use delete then do we need to create the index on the cloumn on AITD table.

SELECT *
FROM TNM S 
where EXISTS (select * 
                                   from AITD A
                                   where not exists (select F.SLT 
                                                     from BID F
                                                     where A.SLT=F.SLT)
    and not exists (select F.TT
			from BID F
			where A.TT=F.TT)
    and A.tdate<= TO_DATE('20050101','yyyy-mm-dd')
    AND S.TN=A.TT)

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

------------------------------------------------------------------------------------------------
| Id  | Operation                       |  Name                        | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |     1 |     6 |   497 |
|   1 |  SORT AGGREAGTE                 |                              |     1 |     6 |       |
|*  2 |   FILTER                        |                              |   219K|       |       |
|   3 |    INDEX FAST FULL SCAN         | PK_T_NO_M		       |   219K|   681K|   496 |
|*  4 |   FILTER                        |                              |  5987 |       |       |
|*  5 |    TABLE ACCESS BY INDEX ROWID  | AITD                         |  5987 |    15 |     2 |
|*  6 |    INDEX RANGE SCAN             | AITD_TTT                     |       |       |     1 |
|*  7 |     INDEX RANGE SCAN            | BID_TT                       |  5987 |    10 |     1 |
|*  8 |    INDEX RANGE SCAN             | BID_SER		       |   431K|    16 |     1 |
------------------------------------------------------------------------------------------------
                                                                                                        
Predicate Information (identified by operation id):                                                     
---------------------------------------------------                                                     
                                                                                                        
   2 - filter(EXISTS (SELECT /*+ */ 0 FROM  'AITD" "SYS_ALIAS_2" WHERE NOT EXISTS (SELECT /*+ */ 0 FROM "BIDL" "F"."SLT"=:B1)        "SYS_ALIAS_2"."TT"=:B2 

AND  "SYS_ALIAS_2"."TDATE"<=TO_DATE('20050101','yyyy-mm-dd') AND NOT EXISTS (SELECT /*+ */ 0 FROM        "BID" "F" WHERE "F"."TT"=:B3)))
   4 - filter ( NOT EXISTS (SELECT /*+ */ 0 FROM "BID" "F" WHERE "F"."SLT"=:B1))
   5 - filter("SYS_ALIAS_2"."TDATE"<=TO_DATE('20050101','yyyy-mm-dd'))
   6 - access("SYS_ALIAS_2"."TT"=:B1)
       filter ( NOT EXISTS (SELECT /*+ */ 0 FROM "BID" "F" WHERE "F"."TT"=:B1))
   7 - access("F"."TT"=:B1)   
   8 - access("F"."SLT"=:B1)

Open in new window

0
Comment
Question by:irsbenz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 

Expert Comment

by:sankhe_dipti
ID: 37751217
Drop will delete the data and the structure of the table, drop will remove all indexes as well.
Delete will just remove data but not structure from Database.

So after deltete no need to create index once again. but the index on the data which is deleted is gone.

One more suggession instead of delete use truncate table, if you are sure you are going to commit after delete.

Now your another Question of fine tuning,

You can use 'IN' instead of Exists,
Exist is comparitivly fast but not fullproof

I just tried ur query

SELECT * FROM TNM S where S.TN IN
(select * from AITD A where A.SLT IN
(select F.SLT from BID F where A.SLT !=F.SLT)
    and not exists
(select F.TT from BID F where A.TT=F.TT)
    and A.tdate<= TO_DATE('20050101','yyyy-mm-dd')
    AND S.TN=A.TT)

ok one more thing I am not aware which table is master table
but always try to scan the table which is having huge data at last and use and conditions.
May be instead of so many joins you can just put a simpkle where caluse with and condition try to change this

SELECT * FROM TNM S where EXISTS
(select * from AITD A where not exists
(select F.SLT from BID F where A.SLT=F.SLT)

to

SELECT * FROM TNM S where s.ForeingKey in ( select * from AITD A where A.that key == s.ForeingKey)
and A.SLT in (select F.SLT from BID F where A.SLT !=F.SLT)

likewise you can modify ur query to keep it simple and fine tuned.

Best luck
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37751335
Author, Expert sankhe_dipti has well answered your question...

regarding the docs which you posted in your question, you can always refer oracle docs for further fine tuning and suggestions from oracle about performance tuning, below i posted the link for the same


http://docs.oracle.com/cd/B19306_01/server.102/b14211.pdf

also you can google around and can find some reference to other external links as well for ex:

http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ

Also would like to emphasize on one of the comments of the experts

"One more suggession instead of delete use truncate table, if you are sure you are going to commit after delete."

you must have to follow this suggestion, simply deleting the data will not free the space that data has been using in that table. this is one of the constraints one should consider,
(Truncation would release the all free unused space and will reset the HWM back to the normal position)


if you cannot truncate the table, then you should atleast do the defragmentation of that tablespace/table at frequent intervals of time to get rid of the unused free space which would eat up performance....
0
 
LVL 27

Expert Comment

by:sujith80
ID: 37753409
Try this -
post the exec plan

 
with A as
(	select /*+ MATERIALIZE */ * 
	from AITD A
        where not exists (	select F.SLT 
                                from BID F
                                where A.SLT=F.SLT)
    	and not exists (	select F.TT
				from BID F
				where A.TT=F.TT)
    and A.tdate<= TO_DATE('20050101','yyyy-mm-dd')
)
SELECT *
FROM TNM S , A
where S.TN=A.TT

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:BigSchmuh
ID: 37765870
Please use the below simplified SQL after checking the "Expected indexes" (cf below)
SELECT S.*
FROM TNM S, AITD A, BID F
WHERE A.tdate<= TO_DATE('20050101','yyyy-mm-dd')
  AND S.TN=A.TT
  AND ( A.TT=F.TT (+) or A.SLT=F.SLT (+) ) AND F.TT is Null AND F.SLT is Null

Expected Access plan
  AITD(TDATE, TT, SLT) index range scan
    BID(TT) index
    BID(SLT) index
      TNM(TN) index
        TNM data pages

Expected indexes
-AITD(TDATE, TT, SLT)
-BID(TT) and BID(SLT)
-TNM(TN)
0
 

Author Comment

by:irsbenz
ID: 37768157
Hi Bigschmuh,
I am not able to execute your query it's. throw me the bellow error.
ora-017019:outer join operator(+) not allowed in operand of OR or IN.
0
 
LVL 18

Accepted Solution

by:
BigSchmuh earned 300 total points
ID: 37770168
Did you check the existing indexes ?
AITD(TDATE, TT, SLT)
BID(TT) and BID(SLT)
TNM(TN)

One of the 2 below syntax should work...
SELECT S.*
FROM TNM S
    INNER JOIN AITD A ON S.TN=A.TT
    LEFT OUTER JOIN BID F ON (A.TT is not Null and A.TT=F.TT) or (A.SLT is not Null and A.SLT=F.SLT)
WHERE A.tdate<= TO_DATE('20050101','yyyy-mm-dd')
    AND F.TT is Null AND F.SLT is Null

SELECT S.*
FROM TNM S, AITD A, BID F
WHERE A.tdate<= TO_DATE('20050101','yyyy-mm-dd')
  AND S.TN=A.TT
  AND ( A.TT is not Null and A.TT=F.TT or A.SLT is not Null and A.SLT=F.SLT ) (+)
  AND F.TT is Null AND F.SLT is Null
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question