| Performance tuning for select statement [message #439288] |
Fri, 15 January 2010 09:26  |
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 #439305 is a reply to message #439288] |
Fri, 15 January 2010 09:46  |
cookiemonster
Messages: 13920
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 #439537 is a reply to message #439320] |
Mon, 18 January 2010 03:41 |
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 #439556 is a reply to message #439288] |
Mon, 18 January 2010 05:49 |
cookiemonster
Messages: 13920
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 #439565 is a reply to message #439288] |
Mon, 18 January 2010 07:27 |
cookiemonster
Messages: 13920
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 |
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 #439792 is a reply to message #439573] |
Tue, 19 January 2010 07:27 |
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 |
cookiemonster
Messages: 13920
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
