Oracle SQL Query Tunning

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

irsbenzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sankhe_diptiCommented:
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
Wasim Akram ShaikCommented:
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
SujithData ArchitectCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BigSchmuhCommented:
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
irsbenzAuthor Commented:
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
BigSchmuhCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.