Solved

Oracle SQL Query Tunning

Posted on 2012-03-21
6
527 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
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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