Printable View
I am running a query selecting well over 150 columns of data out of our database. Hence it takes forever to run. I am getting the 'unable to extend rollback segment'...... and therefore it never completes. I have set the rollback space to unlimited and even added datafiles and still nothing. I know the suggestion has been to do commits (ie when inserting or updating) but what about on a single select statement?
See Note 1066542.6 on MetaLink.
You are performing SELECT and you are getting "unable to extend rollback segment"? That sounds quite impossible! Can you give us some more detail about your problem? Does your select use some user created functions that might be writting something to the database?
And BTW, the number of columns you are selecting has practicaly no influence on the execution time of your query. You select 1 or 10 or 150 columns - the execution time will be (roughly) the same.
Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them. These are bank accounts so I am wondering if that could be causing the problem, since they are updated as transactions are performed. But I am not sure of a way around them. Since we are online 24 x 7 I could be running into this problem constantly so there isn't a time when I can run this where there might not be a conflict. Any ideas how to get around this?
How long it takes before the SELECT fails ? Have you optimised the SELECT statement itself ? If not, that should be the first thing.
Whats the reason behind doing this huge SELECT as ONE query ? Can you do it in separate SELECTs ?
Is your rollback tablespace and the segments sized adequately ?
Belive it or not, this is simply impossible. Can you post exact ORA-n error messsage you are getting, with exact error text?Quote:
Originally posted by bfunky
Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them.
I belive you are hitting the infamous ORA-1555 "snapshot too old, rollback segment to small" error, but not the one that says "unable to extend rollback segment" as you specified in your first post. Those are two different errors that have two fundamentaly different causes. The first one happens only to selects but never to inserts/updates/deletes, while the other one happens only to inserts/updates/deletes, but never to selects.
Do u have (Manuel) auditing (For SELECT statement) enabled. For more details read "Oracle One-on-One". But this is not an Oracle generaic behavior. Can be a site specific implementation of auditing on SELECT statements and the auditing table may hitting the error. Are you selecting from a 'View'..? Check for any "INSTEAD OF" triggers on the view. Also check the alert log, on which table is getting the error. The query table and the error hitting table must be different.Quote:
Originally posted by bfunky
Believe it or not it's on a select statement. The only thing I can think of is that there are some changes being made to the rows while I am selecting them. These are bank accounts so I am wondering if that could be causing the problem, since they are updated as transactions are performed. But I am not sure of a way around them. Since we are online 24 x 7 I could be running into this problem constantly so there isn't a time when I can run this where there might not be a conflict. Any ideas how to get around this?
keep in mind that a SELECT statement cann't produce this error.
Tom Kyte explaind, how you can implement auditing on "SELECT"
Regards
Thomas
Hi All,
Thanks for all the help I have since figured this out. Did a little tweaking with the sql and got it to stop looping and running up my temp database file.
Ah! So it was nothing about 'unable to extend rollback segment', right? You were in fact running out of temp space, not rollback segments, yes?Quote:
Originally posted by bfunky
Hi All,
Thanks for all the help I have since figured this out. Did a little tweaking with the sql and got it to stop looping and running up my temp database file.
Yes it was about the 'unable to extend rollback segments' on a select statement. But I think that once the database file had grown to an unimagineable size that my extents maxed out on that file and caused the error to appear. All is good now.
This interests me. I've used DB2 & this isn't the case with DB2 - it uses a different execution path over (I can't remember the exact figure 22? maybe) 16 columns. That was after raising a call with IBM.Quote:
You select 1 or 10 or 150 columns - the execution time will be (roughly) the same.
I've tried to find out what how Oracle works, do you have any links?
TIA
The implementation of 1000 columns in a table needs to be studied carefully. Oracle cannot read 1000 columns data in a single operation, even if it needs only one data block to store for one row.
Oracle needs 4 I/O operations on a table that has 1000 columns.
It reads first 255 columns, then next 255 columns, then 255 columns and finally 235 columns. So the read performance is going to be hit badly when the table has 1000 columns.
Tamil
Hi tamilselvan,Quote:
Originally posted by tamilselvan
The implementation of 1000 columns in a table needs to be studied carefully. Oracle cannot read 1000 columns data in a single operation, even if it needs only one data block to store for one row.
Oracle needs 4 I/O operations on a table that has 1000 columns.
It reads first 255 columns, then next 255 columns, then 255 columns and finally 235 columns. So the read performance is going to be hit badly when the table has 1000 columns.
Tamil
Can you elaborate on this? Or any links to support this statement?
First of all, 1 row of 1000 column cannot fit into 1 block of memory unit as Tamil pointed, provided all the Columns have data.
Range of BLOCK_SIZE is 2K to 32K. Assume Maximum(32K)...
PS below demo with block size = 8K.
I have used the datatype Number(1) which uses least memory on hardisk.Code:TEST_ABHAY> set trimspool on
TEST_ABHAY> set autotrace off
TEST_ABHAY> ed
Wrote file afiedt.buf
1* create table thousand_col_tab (id1 number(1)) storage (initial 8K next 8K pctincrease 0 minextents 1 maxextents unlimited)
TEST_ABHAY> /
Table created.
Elapsed: 00:00:01.00
TEST_ABHAY> ed
Wrote file afiedt.buf
1 begin
2 for i in 2..1000 loop
3 execute immediate ('alter table thousand_col_tab add id'||i||' number(1)');
4 end loop;
5* end;
TEST_ABHAY> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:51.02
TEST_ABHAY> analyze table thousand_col_tab compute statistics;
Table analyzed.
Elapsed: 00:00:02.08
TEST_ABHAY> select * from dba_tables where table_name=upper('thousand_col_tab');
OWNER TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ------------------------------ -------------- -------------------- -------- -------------------- --------------------
ABHAY THOUSAND_COL_TAB USERS_TS_DICT_MANAGED 16384 8192 0 0 1
Elapsed: 00:00:02.06
TEST_ABHAY>
TEST_ABHAY> Insert into thousand_col_tab(id1) values(1);
1 row created.
Elapsed: 00:00:01.01
TEST_ABHAY> begin
2 for i in 2..1000 loop
3 execute immediate ('update thousand_col_tab set id'||i||'=id1');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:05.01
TEST_ABHAY> commit;
Commit complete.
Elapsed: 00:00:00.00
TEST_ABHAY> analyze table thousand_col_tab compute statistics;
Table analyzed.
Elapsed: 00:00:01.02
TEST_ABHAY> select * from dba_tables where table_name=upper('thousand_col_tab');
OWNER TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ------------------------------ -------------- -------------------- -------- -------------------- --------------------
ABHAY THOUSAND_COL_TAB USERS_TS_DICT_MANAGED 16384 8192 1 746 0
Elapsed: 00:00:00.02
TEST_ABHAY>
TEST_ABHAY> select (blocks)*8192/1024 "Size of tab in KB" from dba_tables where table_name=upper('thousand_col_tab');
Size of tab in KB
--------------------
5968
Elapsed: 00:00:00.05
TEST_ABHAY> select count(*) from dba_extents where segment_name=upper('thousand_col_tab');
COUNT(*)
--------------------
747
Elapsed: 00:00:04.07
TEST:ABHAY> select count(1) from thousand_col_tab;
COUNT(1)
--------------------
1
TEST_ABHAY> spool off
So to have one row with all columns filled with data...i see there are 746 datablocks ( 5968 KB )....
so if we take the data to range in 5000 KB for eack row on harddisk..
& assuming we have max value for the db_block_size(32KB)...we will still have around 150+ data_blocks for one row.
Assuming we have DB_FILE_MULTIBLOCK_READ_COUNT to be max (32)..
So to fetech one row into DB_CACHE from harddisk, the number of I/O will be 150/32=4.6...
I think this is the reason why tamil was mentioning 4 I/O min.
Rgds
Abhay.
Abhay,
Once again you are driving (wrong) conclusions from bad/inappropriate tests.... Let's see:
Wrong. Not that it realy matter that much for the sake of the results you've gotten from your tests, I just like to mention that datatype NUMBER(1) by no means uses least memory on harddisk. Value 0 will indeed use only 1 byte of oracle datafile, but any other number that can fit into this datatype will use 2 or 3 bytes! While on the other hand, any non-null value stored in CHAR(1) or VARCHAR(1) column will require only 1 byte of disk storage. So generaly data stored in CHAR(1) consume less space than that stored in NUMBER(1).Quote:
Originally posted by abhaysk
I have used the datatype Number(1) which uses least memory on hardisk.
Now about your test. I did rerun them and have gotten the same results. However that doesn't mean that you can realy drive any conclusions from them. Why? Because you can easily perform some other tests that perform virtually the same actions on that 1000-column row, yet they result in totaly different storage consmuption for that same row.
You have inserted your row into a table with only the first column having some value, all other collumns were null. Then you performed 999 updates to that row to fill all other columns with the same value. This realy results in quite excessive space consumption (and I'm not quite sure how to explain this).
But what if you do it the other way? What if you simply perform a single insert with all columns populated values?
Your query against table statistics will show that this row consumes only one single block! With the same data as yours, which consumed about 750 blocks!Code:DECLARE
v_cmd VARCHAR2(32000) := 'insert into thousand_col_tab values(1';
BEGIN
FOR i IN 2 .. 1000 LOOP
v_cmd := v_cmd || ',1';
END LOOP;
v_cmd := v_cmd || ')';
EXECUTE IMMEDIATE (v_cmd);
END;
/
But it can get even stranger! Let's do it the same way you did (with one insert and 999 updates), but this time so that the initial insert will put data into the last column instead into the first one.
Again, your query against DBA_TABLES will show that this column was stored in a single block, not into 750 blocks as in your case. And all that was different was the column into which the inserted value was stored!Code:INSERT INTO thousand_col_tab (id1000) VALUES (1);
BEGIN
FOR i IN 1..999 LOOP
EXECUTE IMMEDIATE ('update thousand_col_tab set id'||i||'=id1000');
END LOOP;
END;
/
I hope that my two examples have shown that the above conclusions of yours are wrong because your test is far from being the representative one.Quote:
I have used the datatype Number(1) which uses least memory on hardisk.
So to have one row with all columns filled with data...i see there are 746 datablocks ( 5968 KB )....
so if we take the data to range in 5000 KB for eack row on harddisk..
& assuming we have max value for the db_block_size(32KB)...we will still have around 150+ data_blocks for one row.
Assuming we have DB_FILE_MULTIBLOCK_READ_COUNT to be max (32)..
So to fetech one row into DB_CACHE from harddisk, the number of I/O will be 150/32=4.6...
I think this is the reason why tamil was mentioning 4 I/O min.
Now the fact which tamilsevan has pointed out is true and the correct one, but not for the reasons you have assumed. Even if the 1000-column row is stored in a single block, oracle will still need to perform 4 I/O operations to get it, using CONTINUED ROW FETCH mechanism as if the row was chained into 4 sepparate blocks! This anomaly was first pointed out by Dave Ensor, and Jonathan Lewis is describing this phenomenon in his book "Practical Oracle 8i" on pages 74 and 75.
Jurij :
All I thought was Update would be expensive than a single insert, but to this extent - I never new.Quote:
Originally posted by jmodic
You have inserted your row into a table with only the first column having some value, all other collumns were null. Then you performed 999 updates to that row to fill all other columns with the same value. This realy results in quite excessive space consumption (and I'm not quite sure how to explain this).
Instead of one block thru single insert and ending up with 700+ blocks thru Updates is unimaginable...
How on earth have Oracle designed for updates..which will lead to such exagareted figures..
Can you please get me some info / links / books to have detailed explanation?
Again, How on earth have Oracle designed for updates..which will lead to just one Block if Last Col is inserted & restQuote:
Originally posted by jmodic
But it can get even stranger! Let's do it the same way you did (with one insert and 999 updates), but this time so that the initial insert will put data into the last column instead into the first one.
Again, your query against DBA_TABLES will show that this column was stored in a single block, not into 750 blocks as in your case. And all that was different was the column into which the inserted value was stored!Code:INSERT INTO thousand_col_tab (id1000) VALUES (1);
BEGIN
FOR i IN 1..999 LOOP
EXECUTE IMMEDIATE ('update thousand_col_tab set id'||i||'=id1000');
END LOOP;
END;
/
updated.
Can we draw some conclusions ( generic ) here like
* Inserts are always better then updates & try avoiding updates as much as possible - Expected.
* If have to go for only Updates ( If no go in any application ), then is it recomended to DELETE the recs & then
Insert from source..provided number if records for processing is less say less than a million?
* If updates unavoidable then can we design our application in such a way that insertable Cols ( initially ) be last?
Regards
Abhay.
well set high pctfree and you wont end up using 750 blocks
Even after i set Pctfree=50, i ended up using 740+ blocks.Quote:
Originally posted by pando
well set high pctfree and you wont end up using 750 blocks
Isn't it more than evident that this kind of anomaly is happening only on tables with more than 255 columns. You won't be able to observe such behavior on "normal" table with not such large number of columns....Quote:
Originally posted by abhaysk
All I thought was Update would be expensive than a single insert, but to this extent - I never new.
Instead of one block thru single insert and ending up with 700+ blocks thru Updates is unimaginable...
How on earth have Oracle designed for updates..which will lead to such exagareted figures..
As I said, this is an exceptional behavior on tables with large number of columns, it's not a general behavior. About what is different if non-null value is inserted in the last column instead of in the first column: read in Concepts manual how null values are stored in tables when there is any non-null value in columns following it or when there is no non-null value in any trailing columns.Quote:
Again, How on earth have Oracle designed for updates..which will lead to just one Block if Last Col is inserted & rest
updated.
This would be very ridiculous conclusion. The general rule should be: use whatever DML (insert/update/delete) is appropriate and efficient for the task. Use INSERT when you need new record, use UPDATE when you must change some values of the existing record. Noone in his right mind will create a record with INSERT listing only some of the columns, followed by a bunch of updates on the same record to populate other columns - if those other column values were known in the insert time, why not use them then? And it's not because of this strange behavior you've encountered with 1000-column table, it's because of "why would you want to do it with N I/O operations when you can do it with single I/O".Quote:
Can we draw some conclusions ( generic ) here like
* Inserts are always better then updates & try avoiding updates as much as possible - Expected.
I'm not quitre sure what you mean here, it sounds very confusing to me.Quote:
* If have to go for only Updates ( If no go in any application ), then is it recomended to DELETE the recs & then
Insert from source..provided number if records for processing is less say less than a million?
Only if you have table with 1000 columns......Quote:
* If updates unavoidable then can we design our application in such a way that insertable Cols ( initially ) be last?
Jurij :Quote:
Originally posted by jmodic
This would be very ridiculous conclusion. The general rule should be: use whatever DML (insert/update/delete) is appropriate and efficient for the task. Use INSERT when you need new record, use UPDATE when you must change some values of the existing record. Noone in his right mind will create a record with INSERT listing only some of the columns, followed by a bunch of updates on the same record to populate other columns - if those other column values were known in the insert time, why not use them then? And it's not because of this strange behavior you've encountered with 1000-column table, it's because of "why would you want to do it with N I/O operations when you can do it with single I/O".
Well, it seems ridiculous, but I meant this for 2 reasons.
* When one can insert all values in one go, then why insert some cols at time X and then update rest at time Y....as you mentioned.
I have seen many of the posts here where PPL try to insert some values and rest update, even when it can be done thru single
Inserts thru sub queries or so.
* I belive Updates will be costlier than Inserts, due to 3 reasons
1) An extra I/O required if Data Block where the row is candidate for Update dosent exist in Data Buffer Cache.
2) Updates candidate for Row Chaining/Row Migration.
3) Response time for Updates will usually be more/higher than Inserts..
So, in general, I said Insert score better marks over Updates...
If you see application wise, then use Any DML which will be efficent to achive Business.
Say, you have N records in source table which needs to be updated in Destination table, by joining PK cols.Quote:
I'm not quitre sure what you mean here, it sounds very confusing to me.
Then is it better to DELETE recs in Destination Table the records candidate for Update and then DO bulk Insert.
Assume no FKs
Abhay.