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 Go to next message](/forum/theme/orafaq/images/down.png) |
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)| | |
--------------------------------------------------------------------------------------------------------------------
|
|
|
|
Re: SQL tuning advisor being retarted [message #624429 is a reply to message #624424] |
Mon, 22 September 2014 12:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
CREATE UNIQUE INDEX "PATRON"."KS_LEDGER_1PK"
ON "PATRON"."KS_LEDGER" ("ID")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
CREATE INDEX "PATRON"."KS_LEDGER_2IX"
ON "PATRON"."KS_LEDGER" ("POSTED_DATE")
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( FREELISTS 1 FREELIST GROUPS 1)
NOLOGGING LOCAL (PARTITION "LEDGER_0604"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "LEDGER_0605"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "LEDGER_0606"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "LEDGER_0607"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "LEDGER_0608"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "LEDGER_0609"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "LEDGER_0610"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "LEDGER_0611"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y06M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M09"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M10"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M11"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y07M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M09"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M10"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M11"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y08M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M09"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M10"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M11"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y09M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M09"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M10"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M11"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y10M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M09"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M10"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M11"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y11M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M09"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M10"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M11"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y12M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M09"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M10"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M11"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y13M12"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M01"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M02"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M03"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M04"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M05"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M06"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M07"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "KS_LEDGER_Y14M08"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "MAX_PART"
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) LOGGING)
/
CREATE UNIQUE INDEX "PATRON"."KS_LEDGER_3UI"
ON "PATRON"."KS_LEDGER" ("PREV_ID")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."KS_LEDGER_4IX"
ON "PATRON"."KS_LEDGER" ("ACCT_NUM")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."KS_LEDGER_5CI"
ON "PATRON"."KS_LEDGER" ("PA_PUR_DET_ID")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."KS_LEDGER_6CI"
ON "PATRON"."KS_LEDGER" ("VPS_ACTIVITY_ID")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."KS_LEDGER_7CI"
ON "PATRON"."KS_LEDGER" ("PA_LANE_TXN_ID")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 160K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."KS_LEDGER_ACCT_LEDG_ID_IND"
ON "PATRON"."KS_LEDGER" ("ACCT_NUM", "ID")
TABLESPACE "SASINDX" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."KS_LEDGER_EVENT_ID_INDEX"
ON "PATRON"."KS_LEDGER" ("TRANSACTION_TYPE")
TABLESPACE "SASDATA1" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE UNIQUE INDEX "PATRON"."PA_ACCT_VEHICLE_1PK"
ON "PATRON"."PA_ACCT_VEHICLE" ("VEHICLE_SEQ")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE UNIQUE INDEX "PATRON"."PA_ACCT_VEHICLE_1PK"
ON "PATRON"."PA_ACCT_VEHICLE" ("VEHICLE_SEQ")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."PA_ACCT_VEHICLE_3IX"
ON "PATRON"."PA_ACCT_VEHICLE" ("ACCT_ACCT_NUM")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
/
CREATE INDEX "PATRON"."PA_ACCT_VEHICLE_4IX"
ON "PATRON"."PA_ACCT_VEHICLE" ("VEH_LIC_NUM")
TABLESPACE "SASINDX" PCTFREE 3 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
[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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: SQL tuning advisor being retarted [message #624626 is a reply to message #624585] |
Wed, 24 September 2014 04:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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/
|
|
|
|
Goto Forum:
Current Time: Sun May 05 20:09:56 CDT 2024
|