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  |
 |
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 #545253 is a reply to message #545251] |
Tue, 28 February 2012 02:43  |
John Watson
Messages: 8962
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 |
John Watson
Messages: 8962
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 |
|
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 2078 times)
[Updated on: Tue, 28 February 2012 03:39] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle sequence performance [message #545276 is a reply to message #545272] |
Tue, 28 February 2012 04:02 |
cookiemonster
Messages: 13962
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 #545312 is a reply to message #545310] |
Tue, 28 February 2012 05:38 |
cookiemonster
Messages: 13962
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 |
|
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 |
cookiemonster
Messages: 13962
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 #545432 is a reply to message #545251] |
Wed, 29 February 2012 07:20 |
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 |
|
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 |
cookiemonster
Messages: 13962
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 16:11:16 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
