Home » RDBMS Server » Performance Tuning » Performance tuning for select statement
Performance tuning for select statement [message #439288] Fri, 15 January 2010 09:26 Go to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
I need to reduce the time taken for the execution of the below query

SELECT T1.ACCT_SAK,T2.CUST_SAK, T1.VEHICLE_SAK, T1.LINE_ITEM_NUMBER, T1.ASSIGN_DATE,T3.BUNDLE_SAK 
           FROM ACCT_VEH_BUNDLE_OS T1,ACCT_CUST_OS T2, ACCT_VEH_OS T20, VEH_UNIT_OS T9, BUNDLE_OS T3
           WHERE  T1.ACCT_SAK = T20.ACCT_SAK AND
                  T9.VEHICLE_SAK = T20.VEHICLE_SAK  AND
                  T9.VEHICLE_SAK = T1.VEHICLE_SAK AND 
                  T1.BUNDLE_SAK = T3.BUNDLE_SAK


ACCT_VEH_BUNDLE_OS, ACCT_CUST_OS, ACCT_VEH_OS, VEH_UNIT_OS, BUNDLE_OS are the synonyms for the tables ACCT_VEH_BUNDLE, ACCT_CUST, ACCT_VEH, VEH_UNIT, BUNDLE.

All these tables have indexes for the columns mentioned in the above select statement.

When I did EXPLAIN PLAN for the select statement I got the result as

http*://i45.tinypic.com/5anasp.jpg

Note: You can view the explain plan result in the above URL remove '*' from the url and visit it.I couldnt put the image will creating the topic, it displays the error message as "You cannot use links until you have posted more than 5 messages."


I cannot see any index name under the OBJECT_NAME field. But when I removed the synonym BUNDLE_OS from the select statement and then
did a EXPLAIN PLAN for the modified statement and got the result as given below.

MODIFIED STATEMENT:

SELECT T1.ACCT_SAK,T2.CUST_SAK, T1.VEHICLE_SAK, T1.LINE_ITEM_NUMBER, T1.ASSIGN_DATE 
           FROM ACCT_VEH_BUNDLE_OS T1,ACCT_CUST_OS T2, ACCT_VEH_OS T20, VEH_UNIT_OS T9
           WHERE  T1.ACCT_SAK = T20.ACCT_SAK AND
                  T9.VEHICLE_SAK = T20.VEHICLE_SAK  AND
                  T9.VEHICLE_SAK = T1.VEHICLE_SAK


EXPLAIN PLAN Result for this query is

http*://i46.tinypic.com/2nk8foj.jpg

Note: You can view the explain plan result in the above URL remove '*' from the url and visit it. I couldnt put the image will creating the topic, it displays the error message as "You cannot use links until you have posted more than 5 messages."

Here I can see the index name in the OBJECT_NAME field.

Please help me in reducing the time taken for the execution of the above query and also how to check whether the indexes are being used for this select statement execution.
Re: Performance tuning for select statement [message #439299 is a reply to message #439288] Fri, 15 January 2010 09:39 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
Please let me know if you need more information
Re: Performance tuning for select statement [message #439300 is a reply to message #439288] Fri, 15 January 2010 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know for others but for myself I don't click on a link without knowing where I go.

Regards
Michel

[Updated on: Fri, 15 January 2010 09:39]

Report message to a moderator

Re: Performance tuning for select statement [message #439301 is a reply to message #439288] Fri, 15 January 2010 09:41 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
EXPLAIN PLAN RESULT FOR First Select Query

http://i45.tinypic.com/5anasp.jpg

Here I cannot see the index being used.

EXPLAIN PLAN Result for the modified query i.e., removing the BUNDLE_OS

http://i46.tinypic.com/2nk8foj.jpg

In this i can see the index name

Hope this helps...Please let me know if you need more information.
Re: Performance tuning for select statement [message #439302 is a reply to message #439300] Fri, 15 January 2010 09:43 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
Hi Michel Cadot,

The reason I gave the image link is that the system didnt allowed me to provide the img address untill I post atleast five messages...Now i have given the image in the last post(after writing 5 posts]
Re: Performance tuning for select statement [message #439303 is a reply to message #439288] Fri, 15 January 2010 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SELECT clause includes T2.CUST_SAK while T2 does not exist in WHERE clause.

This could be an issue.
Re: Performance tuning for select statement [message #439305 is a reply to message #439288] Fri, 15 January 2010 09:46 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do use a favour and re-run the explain plans in sqlplus using the following syntax:
SQL> set lines 150
SQL> explain plan for select 1 from dual
  2  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1546270724

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

8 rows selected.

SQL> 

It's a lot more readable that way.
Re: Performance tuning for select statement [message #439314 is a reply to message #439305] Fri, 15 January 2010 10:26 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
I did that. But for long query it is unreadable so I took a screenshot and provided it.
Re: Performance tuning for select statement [message #439320 is a reply to message #439288] Fri, 15 January 2010 11:05 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You did it my way in sqlplus and it's unreadable?
I seriously doubt that.
Show us what you did, because I find your screen shot fairly unreadable.
Re: Performance tuning for select statement [message #439537 is a reply to message #439320] Mon, 18 January 2010 03:41 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
I did what you suggested and found the result as below.
Note: This is the first query with BUNDLE_OS included.

QUERY:

EXPLAIN PLAN SET STATEMENT_ID='WITH_BUNDLE' FOR SELECT T1.ACCT_SAK,T2.CUST_SAK, T1.VEHICLE_SAK, T1.LINE_ITEM_NUMBER, T1.ASSIGN_DATE,T3.BUNDLE_SAK 
           FROM ACCT_VEH_BUNDLE_OS T1,ACCT_CUST_OS T2, ACCT_VEH_OS T20, VEH_UNIT_OS T9
           WHERE  T1.ACCT_SAK = T20.ACCT_SAK AND
                  T9.VEHICLE_SAK = T20.VEHICLE_SAK  AND
                  T9.VEHICLE_SAK = T1.VEHICLE_SAK AND 
                  T1.BUNDLE_SAK = T3.BUNDLE_SAK;


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3261960724

--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |  1930 |   237K|  8267   (1)| 00:02:29 |        |      |
|   1 |  MERGE JOIN CARTESIAN|           |  1930 |   237K|  8267   (1)| 00:02:29 |        |      |
|   2 |   NESTED LOOPS       |           |     1 |   113 |  5525   (1)| 00:01:40 |        |      |
|   3 |    REMOTE            |           |  1501 | 91561 |  2443   (1)| 00:00:44 | OCU1AA | R->S |
|   4 |    REMOTE            | BUNDLE    |     1 |    13 |     0   (0)| 00:00:01 |   OCR1 | R->S |
|   5 |   BUFFER SORT        |           |  2015K|    24M|  8267   (1)| 00:02:29 |        |      |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   6 |    REMOTE            | ACCT_CUST |  2015K|    24M|  2741   (1)| 00:00:50 | OCU1AA | R->S |
--------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "A1"."ACCT_SAK","A1"."VEHICLE_SAK","A1"."LINE_ITEM_NUMBER","A1"."ASSIGN_DATE
       ","A1"."ACCT_SAK","A1"."VEHICLE_SAK","A1"."BUNDLE_SAK","A2"."VEHICLE_SAK","A2"."VEHICLE_SA
       K","A3"."ACCT_SAK","A3"."VEHICLE_SAK" FROM "MCS"."ACCT_VEH_BUNDLE" "A1","MCS"."VEH_UNIT"
       "A2","MCS"."ACCT_VEH" "A3" WHERE "A1"."ACCT_SAK"="A3"."ACCT_SAK" AND
       "A2"."VEHICLE_SAK"="A3"."VEHICLE_SAK" AND "A2"."VEHICLE_SAK"="A1"."VEHICLE_SAK"

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
       (accessing 'OCU1AA.ONSTAR.GM.COM' )

   4 - SELECT "BUNDLE_SAK" FROM "BUNDLE" "T3" WHERE :1="BUNDLE_SAK" (accessing
       'OCR1.ONSTAR.GM.COM' )

   6 - SELECT "CUST_SAK" FROM "MCS"."ACCT_CUST" "T2" (accessing 'OCU1AA.ONSTAR.GM.COM' )


29 rows selected.


This is the second query with BUNDLE_OS excluded.

QUERY:

EXPLAIN PLAN SET STATEMENT_ID='WITHOUT_BUNDLE' FOR SELECT T1.ACCT_SAK,T2.CUST_SAK, T1.VEHICLE_SAK, T1.LINE_ITEM_NUMBER, T1.ASSIGN_DATE
           FROM ACCT_VEH_BUNDLE_OS T1,ACCT_CUST_OS T2, ACCT_VEH_OS T20, VEH_UNIT_OS T9
           WHERE  T1.ACCT_SAK = T20.ACCT_SAK AND
                  T9.VEHICLE_SAK = T20.VEHICLE_SAK  AND
                  T9.VEHICLE_SAK = T1.VEHICLE_SAK;



SQL WORKSHEET OUTPUT

SQL> set lines 150
SQL> EXPLAIN PLAN  FOR SELECT T1.ACCT_SAK,T2.CUST_SAK, T1.VEHICLE_SAK, T1.LINE_ITEM_NUMBER, T1.ASSIG
N_DATE
  2             FROM ACCT_VEH_BUNDLE_OS T1,ACCT_CUST_OS T2, ACCT_VEH_OS T20, VEH_UNIT_OS T9
  3             WHERE  T1.ACCT_SAK = T20.ACCT_SAK AND
  4                    T9.VEHICLE_SAK = T20.VEHICLE_SAK  AND
  5                    T9.VEHICLE_SAK = T1.VEHICLE_SAK;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 759048150

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE |                    |  1929 | 94521 |  3097   (3)| 00:00:38 |        |
|   1 |  MERGE JOIN CARTESIAN   |                    |  1929 | 94521 |  3097   (3)| 00:00:38 |        |
|*  2 |   HASH JOIN             |                    |     1 |    43 |  1779   (4)| 00:00:22 |        |
|*  3 |    HASH JOIN            |                    |  1501 | 46531 |   779   (4)| 00:00:10 |        |
|   4 |     INDEX FAST FULL SCAN| PK_ACCT_VEH_BUNDLE |  1501 | 37525 |     7   (0)| 00:00:01 |   OCU1 |
|   5 |     INDEX FAST FULL SCAN| IDX1_VEH_UNIT      |  1603K|  9393K|   759   (3)| 00:00:10 |   OCU1 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   6 |    INDEX FAST FULL SCAN | PK_ACCT_VEH        |  1611K|    18M|   988   (2)| 00:00:12 |   OCU1 |
|   7 |   BUFFER SORT           |                    |  2015K|    11M|  2109   (3)| 00:00:26 |        |
|   8 |    INDEX FAST FULL SCAN | IDX1_ACCT_CUST     |  2015K|    11M|  1318   (2)| 00:00:16 |   OCU1 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A4"."ACCT_SAK"="A2"."ACCT_SAK" AND "A1"."VEHICLE_SAK"="A2"."VEHICLE_SAK")
   3 - access("A1"."VEHICLE_SAK"="A4"."VEHICLE_SAK")


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Note
-----
   - fully remote statement

25 rows selected.


I hope this was useful.
Re: Performance tuning for select statement [message #439539 is a reply to message #439537] Mon, 18 January 2010 03:52 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
avoid the CARTESIAN product which will cause your Query (runs) slow.Run sql advisor for the sql statement it will give the correct results what to do...

sriram Smile
Re: Performance tuning for select statement [message #439556 is a reply to message #439288] Mon, 18 January 2010 05:49 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well I've never seen an explain plan like that before. It appears you are doing a remote query - bundle_os appears to be on a different database, is that correct?
What database are the other tables on - local or remote?

Also will you please copy and paste the sql you really ran to get the explain plans, because this:
EXPLAIN PLAN SET STATEMENT_ID='WITH_BUNDLE' FOR SELECT T1.ACCT_SAK,T2.CUST_SAK, T1.VEHICLE_SAK, T1.LINE_ITEM_NUMBER, T1.ASSIGN_DATE,T3.BUNDLE_SAK 
FROM ACCT_VEH_BUNDLE_OS T1,ACCT_CUST_OS T2, ACCT_VEH_OS T20, VEH_UNIT_OS T9
WHERE  T1.ACCT_SAK = T20.ACCT_SAK AND
T9.VEHICLE_SAK = T20.VEHICLE_SAK  AND
T9.VEHICLE_SAK = T1.VEHICLE_SAK AND 
T1.BUNDLE_SAK = T3.BUNDLE_SAK;

will have just errored out since t3 isn't in the where clause.
Re: Performance tuning for select statement [message #439558 is a reply to message #439556] Mon, 18 January 2010 06:24 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
BUNDLE_OS is a synonym for the table BUNDLE which is present in a remote system.
Re: Performance tuning for select statement [message #439565 is a reply to message #439288] Mon, 18 January 2010 07:27 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
It would have helped if you'd made this clear from the start as I suspect the remote db is the root of the problem.

First some questions that still need answering:
Are the other tables on the local db?
Also is the cartesian join supposed to be in the query? As others have pointed out you haven't joined ACCT_CUST_OS to the other tables in the where clause - often this sort of thing is a bug.

Unfortunately remote dbs are not something I've really played with so I can't make much in the way of suggestions, hopefully someone with more knowledge can sort you out.
However I do have two suggestions to try:
1) use the driving_site hint.
2) Create a materialised view on your local db that selects from the remote table and use that in your query instead.
Re: Performance tuning for select statement [message #439568 is a reply to message #439565] Mon, 18 January 2010 07:43 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
Hi cookiemonster Thank you for your reply....

I will try to do what you have said...

I am fetching data from tables which are in a remote server through synonyms... I am not using a local table in the select query...All are the synonyms for the tables in a remote server.
Re: Performance tuning for select statement [message #439573 is a reply to message #439288] Mon, 18 January 2010 07:58 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are they all on the same remote server?
Because (and I'm guessing here) I imagine the Inst column in your explain plan is the instance name that object lives on and your plans have 3 different instance names.
Re: Performance tuning for select statement [message #439792 is a reply to message #439573] Tue, 19 January 2010 07:27 Go to previous messageGo to next message
benjamin.tl
Messages: 12
Registered: January 2010
Junior Member
Hi cookiemonster,

Can we use a materialize hint in the WITH clause instead of creating a materialized view. What will be the difference between creating a materialized view for loading the data and using the materialize hint in the WITH clause.

Which one of them will take a longer time?
Re: Performance tuning for select statement [message #439797 is a reply to message #439288] Tue, 19 January 2010 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
A materialized view will be faster because it's pre-computed it's answer, that's the whole point of materialized views.
However that doesn't necessarily make them the best answer since if you can't create it as fast-refresh (and I would suspect you can't in your case but I'm just guessing) then you will be querying data that isn't up to date, which may not be acceptable.
If you're not sure how they work I suggest you read up on them in the docs.

As for materialized hint, pass, suggest you try it and see what happens.

As I said before I don't know much about distributed queries but some of the others might, if rleishman is around he might have some ideas.

It might help others to help you if you answered my previous question about the number of instances involved in the query.
Re: Performance tuning for select statement [message #519341 is a reply to message #439797] Thu, 11 August 2011 03:03 Go to previous messageGo to next message
jai_subi
Messages: 7
Registered: May 2007
Location: Chennai
Junior Member
whenever tables are joined, please query the tables with suffucient where clauses.

For eg:
In the above scenario 5 tables are joined, hence atleast 8 join conditions should be specified. T2 and T3 are left as such without any where clauses.

-SD
Re: Performance tuning for select statement [message #519343 is a reply to message #519341] Thu, 11 August 2011 03:33 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
This thread is 18 months old. I would hope the OP has fixed the problem by now. If you want to help people out might I suggest answering more recent threads.
Previous Topic: New Trace File
Next Topic: Need to decrease the cost as much as possible
Goto Forum:
  


Current Time: Fri Dec 02 18:18:43 CST 2016

Total time taken to generate the page: 0.36602 seconds