[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle SQL Query Tunning

Posted on 2012-03-21
6
Medium Priority
?
551 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 600 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

656 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