Home » RDBMS Server » Performance Tuning » Oracle sequence performance (Oracle 10.2.0.4.0, sun solaris )
Oracle sequence performance [message #545251] Tue, 28 February 2012 02:30 Go to next message
a_oracle
Messages: 98
Registered: November 2010
Member
Hi All,

I have been facing issues with an insert statement which has been written like:
declare
cursor c is 
<QUERY1>;
begin
open c;
loop
fetch c bulk collect into a object type limit 2000 (initially the limit was 200, later changed to 2000 to see if any performance improvement is there)
inert into ALD_LOAN_EXTRACT
values
nest_table(i);
commit;
end loop;



Indexes on source table [b]STAGE_DML_LOAN_EOD[/b]:
STAGE_DML_LOAN_EOD_NU1	Normal	LNC_BORROWER_ID
STAGE_DML_LOAN_EOD_NU2	Normal	LNC_ALLOC_FLAG, BAD_REC_FLG
STAGE_DML_LOAN_EOD_NU3	Normal	LNC_SEQ_NO
STAGE_DML_LOAN_EOD_NU4	Normal	RESIDENCE_CTRY_CD

Indexes on target table [b]ALD_LOAN_EXTRACT[/b]:
ALD_LOAN_EXTRACT_NU1	Normal	CALENDAR_DT, ALD_ENTITY_ID
ALD_LOAN_EXTRACT_PK	Unique	CALENDAR_DT, REC_TYPE_CD, LOAN_ID, REC_SQ


explain plan for QUERY1:

SELECT [b]ALD_RECORD_SQ.NEXTVAL[/b] AS REC_SQ,
       rec_type_cd,
       loan_id,
       loan_alloc_id,
       open_dt,
       '00000997' AS sending_firm_id,
       CUSIP_ID,
       SEDOL_ID,
       ISIN_ID,
       QUICK_ID,
       LENDER_DTC_ID,
       LOCAL_TAX_CTRY_CD,
       LOCAL_TAX_ID,
       client_entity_id,
       ald_entity_id,
       ctpy_settle_cd,
       ALD_DLF_COLL_TYPE_ID,
       asset_id,
       asset_id_type_cd,
       disclosed_flg,
       shares_qty,
       contract_amt,
       coll_ccy_id,
       market_val_amt,
       market_value_dt,
       sec_settle_status_cd,
       coll_settle_status_cd,
       rebate_rt,
       lender_dividend_rt,
       term_dt,
       allocation_ct,
       reallocated_flg,
       marked_flg,
       returned_flg,
       mark_amt,
       bad_rec_flg,
       USER AS last_mod_signon_id,
       SYSDATE AS last_mod_date_time
  FROM (SELECT rec_type_cd,
               shares_qty,
               contract_amt,
               ald_entity_id,
               CUSIP_ID,
               SEDOL_ID,
               ISIN_ID,
               QUICK_ID,
               LENDER_DTC_ID,
               LOCAL_TAX_CTRY_CD,
               LOCAL_TAX_ID,
               client_entity_id,
               loan_id,
               open_dt,
               ctpy_settle_cd,
               loan_alloc_id,
               ALD_DLF_COLL_TYPE_ID,
               asset_id,
               asset_id_type_cd,
               disclosed_flg,
               coll_ccy_id,
               market_val_amt,
               market_value_dt,
               sec_settle_status_cd,
               coll_settle_status_cd,
               rebate_rt,
               lender_dividend_rt,
               term_dt,
               allocation_ct,
               reallocated_flg,
               marked_flg,
               returned_flg,
               mark_amt,
               bad_rec_flg,
               USER,
               SYSDATE
          FROM (SELECT '1' AS rec_type_cd,
                       lnc_unit_on_loan AS shares_qty,
                       DECODE(ald_dlf_coll_type_id,
                              'N',
                              0,
                              DECODE(lnc_local_collat_amt,
                                     0.0,
                                     0.01,
                                     lnc_local_collat_amt)) AS contract_amt,
                       ald_entity_id,
                       LNC_CUSIP AS CUSIP_ID,
                       SEDOL_ID,
                       ISIN_ID,
                       QUICK_ID,
                       NULL AS LENDER_DTC_ID,
                       NULL AS LOCAL_TAX_CTRY_CD,
                       NULL AS LOCAL_TAX_ID,
                       NULL AS client_entity_id,
                       lnc_area_id || lnc_seq_no AS loan_id,
                       lnc_settle_date AS open_dt,
                       DECODE(residence_ctry_cd,
                              'US',
                              DECODE(lnc_settlement_code,
                                     'USD',
                                     DECODE(lnc_business_line,
                                            'G',
                                            'FEDW',
                                            SUBSTR(lnc_borrower_id, 3, 4)),
                                     'INTL'),
                              lnc_borrower_id) AS ctpy_settle_cd,
                       NULL AS loan_alloc_id,
                       ald_dlf_coll_type_id,
                       asset_id,
                       asset_id_type_cd,
                       NULL AS disclosed_flg,
                       iso_ccy_cd AS coll_ccy_id,
                       NULL AS market_val_amt,
                       NULL AS market_value_dt,
                       DECODE(lnc_settled_flag, 'Y', 'S', 'P') AS sec_settle_status_cd,
                       'S' AS coll_settle_status_cd,
                       DECODE(NVL(lnc_reb_prem_rate, 0),
                              0,
                              ((-1) * lnc_commission_rate),
                              lnc_reb_prem_rate) AS rebate_rt,
                       NULL AS lender_dividend_rt,
                       NULL AS term_dt,
                       NULL AS allocation_ct, -- alloation count ONLY applicable for Omni. May need to update later.
                       NULL AS reallocated_flg,
                       NULL AS marked_flg,
                       NULL AS returned_flg,
                       NULL AS mark_amt,
                       'N' AS bad_rec_flg
                  FROM STAGE_DML_LOAN_EOD sdle1
                 WHERE sdle1.lnc_alloc_flag = 2
                   AND sdle1.bad_rec_flg = 'N'
                   AND sdle1.ald_entity_id IS NOT NULL
                UNION
                -- aggregate all allocation records....
                SELECT OFD.rec_type_cd,
                       SUM(lnc_unit_on_loan) AS shares_qty,
                       SUM(DECODE(sdle.ald_dlf_coll_type_id,
                                  'N',
                                  0,
                                  DECODE(lnc_local_collat_amt,
                                         0.0,
                                         0.01,
                                         lnc_local_collat_amt))) AS contract_amt,
                       ald_entity_id,
                       OFD.CUSIP_ID,
                       OFD.SEDOL_ID,
                       OFD.ISIN_ID,
                       OFD.QUICK_ID,
                       LENDER_DTC_ID,
                       LOCAL_TAX_CTRY_CD,
                       LOCAL_TAX_ID,
                       client_entity_id,
                       lnc_area_id || lnc_seq_no AS loan_id,
                       OFD.open_dt,
                       OFD.ctpy_settle_cd,
                       OFD.loan_alloc_id,
                       OFD.ald_dlf_coll_type_id,
                       OFD.asset_id,
                       OFD.asset_id_type_cd,
                       SUBSTR(MAX(DECODE(lnc_exclusive_flag, 'Y', 'E', 'N')),
                              1,
                              1) AS disclosed_flg,
                       OFD.coll_ccy_id,
                       OFD.market_val_amt,
                       OFD.market_value_dt,
                       OFD.sec_settle_status_cd,
                       OFD.coll_settle_status_cd,
                       OFD.rebate_rt,
                       OFD.lender_dividend_rt,
                       OFD.term_dt,
                       OFD.allocation_ct,
                       SUBSTR(MAX(lnc_realloc_flag), 1, 1) AS reallocated_flg,
                       DECODE(SIGN(SUM(lnc_mark_amt)), 0, 'N', 'Y') AS marked_flg,
                       SUBSTR(MAX(lnc_return_flag), 1, 1) AS returned_flg,
                       SUM(lnc_mark_amt),
                       sdle.bad_rec_flg
                  FROM STAGE_DML_LOAN_EOD sdle,
                       (SELECT '2' AS rec_type_cd,
                               NULL AS open_dt, --NULL AS marked_flg,
                               NULL AS ctpy_settle_cd,
                               NULL AS allocation_ct,
                               NULL AS CUSIP_ID,
                               NULL AS SEDOL_ID,
                               NULL AS ISIN_ID,
                               NULL AS QUICK_ID,
                               NULL AS loan_alloc_id,
                               NULL AS ald_dlf_coll_type_id,
                               NULL AS asset_id,
                               NULL AS asset_id_type_cd,
                               NULL AS disclosed_flg,
                               NULL AS market_val_amt,
                               NULL AS coll_ccy_id,
                               NULL AS market_value_dt,
                               NULL AS sec_settle_status_cd,
                               NULL AS coll_settle_status_cd,
                               NULL AS rebate_rt,
                               NULL AS lender_dividend_rt,
                               NULL AS term_dt
                          FROM dual) OFD
                 WHERE sdle.lnc_alloc_flag = 3
                   AND sdle.bad_rec_flg = 'N'
                   AND sdle.ald_entity_id IS NOT NULL
                 GROUP BY OFD.rec_type_cd,
                          ald_entity_id,
                          LENDER_DTC_ID,
                          LOCAL_TAX_CTRY_CD,
                          LOCAL_TAX_ID,
                          client_entity_id,
                          (lnc_area_id || lnc_seq_no),
                          OFD.open_dt,
                          OFD.ctpy_settle_cd,
                          OFD.loan_alloc_id,
                          OFD.ald_dlf_coll_type_id,
                          OFD.asset_id,
                          OFD.asset_id_type_cd,
                          OFD.CUSIP_ID,
                          OFD.SEDOL_ID,
                          OFD.ISIN_ID,
                          OFD.QUICK_ID,
                          iso_ccy_cd,
                          OFD.market_val_amt,
                          OFD.market_value_dt,
                          OFD.sec_settle_status_cd,
                          OFD.coll_settle_status_cd,
                          rebate_rt,
                          OFD.lender_dividend_rt,
                          OFD.term_dt,
                          bad_rec_flg) aggregated_data
         ORDER BY ald_entity_id, loan_id, rec_type_cd)


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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3028225301
--------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    | 41192 |  7803K|       |
|   1 |  SEQUENCE                 | ALD_RECORD_SQ      |       |       |       |
|   2 |   VIEW                    |                    | 41192 |  7803K|       |
|   3 |    SORT ORDER BY          |                    | 41192 |  7803K|    21M|
|   4 |     VIEW                  |                    | 41192 |  7803K|       |
|   5 |      SORT UNIQUE          |                    | 41192 |  5520K|    15M|
|   6 |       UNION-ALL           |                    |       |       |       |
|*  7 |        TABLE ACCESS FULL  | STAGE_DML_LOAN_EOD | 25031 |  3911K|       |
|   8 |        SORT GROUP BY      |                    | 16161 |  1609K|  5696K|
|   9 |         NESTED LOOPS      |                    | 25031 |  2493K|       |
|  10 |          FAST DUAL        |                    |     1 |       |       |
|* 11 |          TABLE ACCESS FULL| STAGE_DML_LOAN_EOD | 25031 |  2493K|       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   7 - filter("SDLE1"."ALD_ENTITY_ID" IS NOT NULL AND TO_NUMBER("SDLE1"."LNC_ALL
              "SDLE1"."BAD_REC_FLG"='N')
  11 - filter("SDLE"."ALD_ENTITY_ID" IS NOT NULL AND TO_NUMBER("SDLE"."LNC_ALLOC
              "SDLE"."BAD_REC_FLG"='N')


the query fetches approximately 1 million data and load into table2 but takes around 50 minutes to do so. Both the target as well as the source table are truncated on daily basis
so it has the current businiess day data only and no historical data. Now, i got the AWR report generated for this proecssing and noticed that its not the insert process that's taking time
but rather the select query which has been facing performance degrade. Our database is a RAC instance and in the target table, the primary key ALD_LOAN_EXTRACT_PK is on column "rec_sq" which is inerted with
sequence values. The sequence is fetched in the query as "ALD_RECORD_SQ.NEXTVAL" and its metadata is:

 
create sequence ALD.ALD_RECORD_SQ
minvalue 0
maxvalue 9999999
start with 5726014
increment by 1
nocache
cycle;


In the AWR report, the top 5 events show contain "row cache lock" and so i queried the below and fetched the values for parameter dc_sequences:

SELECT parameter,
       sum(gets),
       sum(getmisses),
       100 * sum(gets - getmisses) / sum(gets) pct_succ_gets,
       sum(modifications) updates
  FROM V$ROWCACHE
 WHERE gets > 0
 GROUP BY parameter;
 
PARAMETER	    SUM(GETS)	SUM(GETMISSES)	PCT_SUCC_GETS	UPDATES
dc_sequences	    11114726	83875	        99.24537051	  11114723


Also, the SQl query stastistics show the SQL ID "08w3thdwdc6j9" as the SQL which have been time consuming.
Now, my inital findings led me to the conclusion that the culprit may be the sequence, because it's a RAC database and this sequence doesnt even have a CACHE.
Don't have much expertise on AWR report but seems the worst hit indexes does include the prmary key index on the target table which is being updated by sequence values.

Finally, i need your expert advice on the above scenario.
1. If i intend to CACHE the sequence considering the fact that it has to generate unique numbers for each row insert, how much should i give the cache size? I intend to keep
an optimal value yet the maximum allowed. A few thousand is what i get in the manuals or references, but was just wondering how much exactly.

2. Though I analyzed the AWR report and the explain plan, am i missing something out here apart from the above?

Please excuse that i have floughted the guidelines and uploaded a document which many people won't like to download but seeing the size of this post I though that was abetter one.


Re: Oracle sequence performance [message #545252 is a reply to message #545251] Tue, 28 February 2012 02:43 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
Ok. I tried uploading the html version of the AWR report and seems we can't do that. Now, i have uploaded the same in csv format. Don't know how can i convert the html format to txt format so had to just copy paste the one from html document to csv. I hope that would readable.
  • Attachment: AWR.csv
    (Size: 161.99KB, Downloaded 1780 times)
Re: Oracle sequence performance [message #545253 is a reply to message #545251] Tue, 28 February 2012 02:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I would set up the sequence as
CACHE 100000 NOORDER
and also reverse key the index. These are examples of something that you should do single instance, and you must do in a cluster: normal best practice, not necessarily relevant to your problem. Post the AWR report up here, and we can have a look.
Re: Oracle sequence performance [message #545254 is a reply to message #545252] Tue, 28 February 2012 02:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Don't know how can i convert the html format to txt format
Generate the report with ?/rdbms/admin/spreport.sql and when prompted enter TEXT tather than HTML.
Re: Oracle sequence performance [message #545261 is a reply to message #545254] Tue, 28 February 2012 03:25 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
Hey John,
Thanks for the prompt reply. I have attached the text format of the AWR report now for reference. I have a composite primary key on the target table which also contains the column "rec_sq". Now, I believe we cannot reverse key the whole index..right?? If the case then can you please also advice how do I just include only the sequence based column as having a reverse key index?
  • Attachment: awrrpt_1.txt
    (Size: 309.07KB, Downloaded 1847 times)

[Updated on: Tue, 28 February 2012 03:39]

Report message to a moderator

Re: Oracle sequence performance [message #545269 is a reply to message #545261] Tue, 28 February 2012 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why have you got a column populated from a sequence as part of a composite key?
Normally sequence columns are used as keys on their own.
Re: Oracle sequence performance [message #545272 is a reply to message #545269] Tue, 28 February 2012 03:57 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
@cookiemonster: I believe it has been done to make sure that the column is never null and moreover we cant have more than one primary key at table level should it might have included in this composite key. So are you advising to remove this from the existing composite key and make a seperate reverse key index? Then how would i handle the not null constraint?
Re: Oracle sequence performance [message #545276 is a reply to message #545272] Tue, 28 February 2012 04:02 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't understand what not null has to do with anything. The question is what logically defines a row in the table.
Normally you have a key consisting of values entered by the user and then a seperate key on a sequence column so you can use that to link to other tables.
Re: Oracle sequence performance [message #545310 is a reply to message #545276] Tue, 28 February 2012 05:30 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
The sequence column used here is not used to create links to other tables but it is just for some process wherein this target table is checked for some exceptions and for the rows which have exceptions, an entry is made taking the primary key columns from the target table into the user created exception table having columns similar to the ones here. Don't know why such an extra step has been done for this process.
Anyways, the doubt with creating reverse key index remains still the same Confused
Re: Oracle sequence performance [message #545312 is a reply to message #545310] Tue, 28 February 2012 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The whole point of having unique keys is to allow you to uniquely identify records and prevent duplicates.
You have a key which includes a column populated by a sequence.
Sequences always give unique values.
So if you took the current key of CALENDAR_DT, REC_TYPE_CD, LOAN_ID, REC_SQ
and replaced it with a key of just REC_SQ every record would still be exactly as unique as it was before.
So as far as I can see having those other three columns on the key is completely pointless.
Re: Oracle sequence performance [message #545316 is a reply to message #545312] Tue, 28 February 2012 05:51 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
Agreed, but what if a user has to fetch a specific record from the table which holds data on daily basis. I believe the user wont be aware of the sequence number rather he would query by the other attributes for sure. The table holds nearly million records, so doesn't it require some indexes?
Moreover, there are lots of updates and inserts happening over on the target table so thinking in that perspective also it requires indexes to be present

[Updated on: Tue, 28 February 2012 05:54]

Report message to a moderator

Re: Oracle sequence performance [message #545322 is a reply to message #545316] Tue, 28 February 2012 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not suggesting you get rid of indexes.
I'm not suggesting that CALENDAR_DT, REC_TYPE_CD, LOAN_ID shouldn't be indexed.
I'm suggesting that they shouldn't be part of the primary key. Nothing more.
Re: Oracle sequence performance [message #545384 is a reply to message #545322] Wed, 29 February 2012 00:21 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
Anyways, Thanks for the advice all.
A few more doubts. I would be trying to alter the sequence to cache 100000 with noorder option. Any specific system configuration I have to do a check prior to doing this change?
Re: Oracle sequence performance [message #545432 is a reply to message #545251] Wed, 29 February 2012 07:20 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

declare
cursor c is
<QUERY1>;
begin
open c;
loop
fetch c bulk collect into a object type limit 2000 (initially the limit was 200, later changed to 2000 to see if any performance improvement is there)
inert into ALD_LOAN_EXTRACT
values
nest_table(i);
commit;
end loop;


I don't think the sql really needs to be processed in small batches unless you are hiding the complex business logic from us. If not try converting this to plain insert select and oracle will optimizer it for you. Commiting in batches is not a good solution. Check this link

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:760210800346068768

Also this

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4951966319022

Quote:

create sequence ALD.ALD_RECORD_SQ
minvalue 0
maxvalue 9999999
start with 5726014
increment by 1
nocache
cycle;

Is the sequence set to cycle deliberately? If so any particular reason for it ?

Quote:

select '1' AS rec_type_cd,
...
from STAGE_DML_LOAN_EOD sdle1
WHERE sdle1.lnc_alloc_flag = 2
AND sdle1.bad_rec_flg = 'N'
AND sdle1.ald_entity_id IS NOT NULL
UNION
SELECT OFD.rec_type_cd,
...
STAGE_DML_LOAN_EOD sdle,
(SELECT '2' AS rec_type_cd,
...
ORDER BY ald_entity_id, loan_id, rec_type_cd

I don't think you need the UNION there as the output from these two queries will be unique as the rec_Type_Cd is unique and the "order by" in the select could be removed unless you want to insert the record in a particular order.

By removing these steps you can potentially avoid the following steps in your execution plan
Quote:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3028225301
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
--------------------------------------------------------------------------------
| 3 | SORT ORDER BY | | 41192 | 7803K| 21M|
| 5 | SORT UNIQUE | | 41192 | 5520K| 15M|
--------------------------------------------------------------------------------

Also, is it really needed to scan the table twice? I could see it can be achieved by using analytics. By doing that you can avoid scanning the table twice. Since I don't have the test data I cannot really prove it but a quick glance I have a feeling it is doable and still worth a try.

I am not trying to say sequence cache is not your problem. It's also one of your problem.

These are all the few things I can think of could attribute to your performance.

Last but not least, don't rely on the explain plan. Check the execution plan.

Regards

Raj
Re: Oracle sequence performance [message #547408 is a reply to message #545432] Wed, 14 March 2012 03:43 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
Hi All,

Back for the existing issue again, though haven't tried anything yet advised.
Just wanted to ask your advice on the issue again. I have the AWRSQLRPT.SQL now for the above sql.
I expected it show something related to sequence issue as discussed above, but could find issue with the same except union and the order by clause only (as it seems to me, please correct me if I am wrong).

Please advice what should be my approach now.

Thanks.

[Updated on: Wed, 14 March 2012 03:49]

Report message to a moderator

Re: Oracle sequence performance [message #547416 is a reply to message #547408] Wed, 14 March 2012 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please post awrs as text in code tags. A lot of people here can't/won't download attachments.
Here's the execution plan:
Execution Plan
--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |       |       |       | 19414 (100)|          |
|   1 |  SEQUENCE                 | ALD_RECORD_SQ      |       |       |       |            |          |
|   2 |   VIEW                    |                    | 40190 |  7614K|       | 19414   (3)| 00:03:53 |
|   3 |    SORT ORDER BY          |                    | 40190 |  7614K|    20M| 19414   (3)| 00:03:53 |
|   4 |     VIEW                  |                    | 40190 |  7614K|       | 17705   (3)| 00:03:33 |
|   5 |      SORT UNIQUE          |                    | 40190 |  5411K|    15M| 17705  (52)| 00:03:33 |
|   6 |       UNION-ALL           |                    |       |       |       |            |          |
|   7 |        TABLE ACCESS FULL  | STAGE_DML_LOAN_EOD | 24857 |  3883K|       |  7963   (3)| 00:01:36 |
|   8 |        SORT GROUP BY      |                    | 15333 |  1527K|  5560K|  8862   (3)| 00:01:47 |
|   9 |         NESTED LOOPS      |                    | 24857 |  2475K|       |  7984   (4)| 00:01:36 |
|  10 |          FAST DUAL        |                    |     1 |       |       |     2   (0)| 00:00:01 |
|  11 |          TABLE ACCESS FULL| STAGE_DML_LOAN_EOD | 24857 |  2475K|       |  7982   (4)| 00:01:36 |
--------------------------------------------------------------------------------------------------------


Your biggest problems there are the ones already identified - order by and Union.
You definitely don't need the order by.
You probably don't need union. If you change it to union all do you get more rows back? If not use union all.
Re: Oracle sequence performance [message #547418 is a reply to message #547416] Wed, 14 March 2012 04:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looking at the select the two subselects that are unioned together get different values for rec_type_cd.
So you can definitely use union all.
Re: Oracle sequence performance [message #547438 is a reply to message #547418] Wed, 14 March 2012 05:43 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
@cookiemosnter: Thanks for the advice. So does the idea of altering the sequence to be cached with 100000 with noorder option still imply for this query?
Re: Oracle sequence performance [message #547441 is a reply to message #547438] Wed, 14 March 2012 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It couldn't hurt, but I'd concentrate on the order by and union, they seem to be causing most of the pain.
Re: Oracle sequence performance [message #547442 is a reply to message #547441] Wed, 14 March 2012 05:51 Go to previous messageGo to next message
a_oracle
Messages: 98
Registered: November 2010
Member
ok so, i can finally make the changes will test out.
Thanks again and will come back with the outcome.
Re: Oracle sequence performance [message #547450 is a reply to message #547442] Wed, 14 March 2012 06:03 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Get rid of that select from dual as well, it serves no purpose. Just move the values up a level.
Previous Topic: Sql statement taking very time
Next Topic: Need to know Sql ID and text
Goto Forum:
  


Current Time: Thu Mar 28 09:51:38 CDT 2024