Solved

Oracle SQL Query Tunning

Posted on 2012-03-21
6
522 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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 recover a database from a user managed backup

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now