Home » RDBMS Server » Performance Tuning » SQL tuning advisor being retarted
SQL tuning advisor being retarted [message #624422] Mon, 22 September 2014 11:53 Go to next message
aliyesami
Messages: 28
Registered: October 2005
Location: Florida
Junior Member
I ran sql tuning advisor on the following query and its recommending a plan to enable 384 parallel processes on it which is absurd .
Quote:

Executing this query parallel with DOP 384 will improve its response time
99.67% over the original plan.


what are my other options ? 

select V.VEH_LIC_NUM, V.ACCT_ACCT_NUM,V.STATE_STATE_CODE_ABBR
from  ks_ledger l, pa_acct_vehicle v, 
        ( select l.acct_num, max(l.id) as id   from ks_ledger l group by l.acct_num ) mx 
         where  MX.id = l.id and L.BALANCE < 0  and V.ACCT_ACCT_NUM = L.ACCT_NUM

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                            |  4737K|   216M|       |  2507K  (6)|       |       |
|   1 |  HASH JOIN               |                            |  4737K|   216M|   159M|  2507K  (6)|       |       |
|   2 |   HASH JOIN              |                            |  3900K|   115M|    92M|  2457K  (7)|       |       |
|   3 |    VIEW                  |                            |  3900K|    48M|       |   949K (11)|       |       |
|   4 |     HASH GROUP BY        |                            |  3900K|    48M|  6181M|   949K (11)|       |       |
|   5 |      INDEX FAST FULL SCAN| KS_LEDGER_ACCT_LEDG_ID_IND |   269M|  3335M|       |   399K  (4)|       |       |
|   6 |    PARTITION RANGE ALL   |                            |   204M|  3518M|       |  1200K  (3)|     1 |   102 |
|   7 |     TABLE ACCESS FULL    | KS_LEDGER                  |   204M|  3518M|       |  1200K  (3)|     1 |   102 |
|   8 |   TABLE ACCESS FULL      | PA_ACCT_VEHICLE            |    10M|   176M|       | 26690   (4)|       |       |
--------------------------------------------------------------------------------------------------------------------

Report message to a moderator

Re: SQL tuning advisor being retarted [message #624424 is a reply to message #624422] Mon, 22 September 2014 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post CREATE INDEX statements for all indexes on tables in SELECT.

consider to use different table alias to avoid confusion about which table is being accessed ("l" exists twice)

Report message to a moderator

Re: SQL tuning advisor being retarted [message #624429 is a reply to message #624424] Mon, 22 September 2014 12:24 Go to previous messageGo to next message
aliyesami
Messages: 28
Registered: October 2005
Location: Florida
Junior Member
here are all the indexes on the two tables in select query
List of indexes


[EDITED by LF: applied [spoiler] tags to cut a long message short]

[Updated on: Mon, 22 September 2014 15:04] by Moderator

Report message to a moderator

Re: SQL tuning advisor being retarted [message #624430 is a reply to message #624429] Mon, 22 September 2014 12:39 Go to previous messageGo to next message
aliyesami
Messages: 28
Registered: October 2005
Location: Florida
Junior Member
and i changed the query to :
select V.VEH_LIC_NUM, V.ACCT_ACCT_NUM,V.STATE_STATE_CODE_ABBR
from  ks_ledger l, pa_acct_vehicle v, 
        ( select ks.acct_num, max(ks.id) as id   from ks_ledger ks group by ks.acct_num ) mx 
         where  MX.id = l.id and L.BALANCE < 0  and V.ACCT_ACCT_NUM = L.ACCT_NUM

Report message to a moderator

Re: SQL tuning advisor being retarted [message #624585 is a reply to message #624430] Tue, 23 September 2014 14:38 Go to previous messageGo to next message
aliyesami
Messages: 28
Registered: October 2005
Location: Florida
Junior Member
any updates on this anyone ?

Report message to a moderator

Re: SQL tuning advisor being retarted [message #624626 is a reply to message #624585] Wed, 24 September 2014 04:11 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The thing to remember about sql tuning advisor is it's not cognisant of how often a query will be run or by how many users. It aims to make that single query as fast as possible in isolation.

The other options are to do it the old fashioned way and tune it yourself. Find where the time is being spent and see if that can be cut down within reasonable economic bounds.

This link covers the basics: http://www.orafaq.com/forum/t/84315/

Report message to a moderator

Re: SQL tuning advisor being retarted [message #626239 is a reply to message #624626] Thu, 23 October 2014 05:08 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Isn't it supposed to be
"... AND MX.ACCT_ACCT_NUM = L.ACCT_NUM" as well?

Report message to a moderator

Previous Topic: Table creation with nologging clause is stopped in between
Next Topic: Query in Loop causing performance issue
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 02 08:36:03 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.