ORA-00604 followed by ORA-30036

[it2010]

Hello,

I'm getting the following error:

ORA-00604: error occurred at recursive SQL level 1
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

for the query (in a stored procedure of my local db):

insert /*+APPEND*/ into local_table
SELECT * from remote_table@remote_db_link
WHERE LOWER(aaa) = :bbbb;
COMMIT;

The error ORA-30036 is raised on the remote db?
Can i solve this "locally" or should I contact the remote_db administrator?

Thanks.

 

[Thargy]

It appears to be a local issue, not remote.

On the assumption that 'UNDOTBS1' really is the undo tablespace, then it is trying to increase in size, and failing to do so.

This may be due to lack of disk space, the way the data file is configured or other factors. Investigate whether or not the undo tablespace can autoextend, and if it can't, then enable it. This is obviously subject to the provise that there is sufficient disk capacity to cope with the operation. Don't do this in a production environment until you know precisely how much space, time etc. is required for the operation.

Regards

T

 

[it2010]

Hi,

this is the procedure's code:

EXECUTE IMMEDIATE 'ALTER TABLE local_table NOLOGGING';
EXECUTE IMMEDIATE 'DROP INDEX aaaaaaaa';
EXECUTE IMMEDIATE 'DROP INDEX bbbbbbbb';
EXECUTE IMMEDIATE 'truncate table local_table';

for rec in (select distinct(lower(tipo)) tipo from my_list ) loop

insert /*+APPEND*/ into local_table
SELECT LOWER (field_1) ,field_2,TO_CHAR(field_3,'YYYYMMDD')
FROM remote_view@remote_db_link
WHERE LOWER(field_4) = rec.tipo;

COMMIT;

end loop;

EXECUTE IMMEDIATE 'CREATE INDEX aaaaa ON local_table (xxxx) NOLOGGING TABLESPACE xxxxx';

EXECUTE IMMEDIATE 'CREATE INDEX bbbbb ON local_table (yyyy) NOLOGGING TABLESPACE yyyyy';

DBMS_STATS.gather_table_stats(ownname => 'xxxx' , tabname =>'local_table',estimate_percent => dbms_stats.auto_sample_size ,degree =>'8',cascade=> TRUE);
EXECUTE IMMEDIATE 'ALTER TABLE local_table LOGGING';
During the execution of the procedure there isn't any process querying the table.

I've just checked that the "remote_table" is actually a view.
When the procedure ends without any error it takes 2 hours and the local_table receives only 500.000 records. So I think the actual problem is on the remote_db or on the network.
The DBA of the "local_db" says that the undo segment is big enough.

Thanks for your help.

 

[Thargy]

I may be wrong, but I think you're making life much harder than it need be.

Since you are truncating the table, it is obviously having all its records erased.

Instead of doing all the above, start a sql plus session and try the following:-

DROP TABLE LOCAL_TABLE CASCADE CONSTRAINTS PURGE;
CREATE TABLE LOCAL_TABLE
AS
SELECT LOWER (field_1) ,field_2,TO_CHAR(field_3,'YYYYMMDD') FROM remote_view@remote_db_linkWHERE LOWER(field_4) = rec.tipo;

This code is untested, since I don't have the ability to produce your environment here. I suggest that you do a timing test on this, but your procedure with the FOR LOOP is using row-by-row processing (usually referred to as slow-by-slow). You may also have heard this mentioned in the sql server fraternity as RBAR (row by agonising row).

You need PL/SQL here like you need a hole in the head

Please see the thread thread1662-1541798 which shows that
a) you are not alone
b) significant performance benefits are possible

If a perusal of that thread doesn't help, post again and I'll do what I can.

Oh, and you also appear to be converting a date to a character field, which is usually very bad news. Is there any reason for that?

Regards

T

 

[it2010]

Hi,

It's not a row-by-row loop because the table "my_list" has 2-3 rows. I'm trying to identify the bottleneck of this system/procedure written by others (running in a production system every night), so I can't switch to SQL PLUS or change the output format for data fields.

In the past they also tried a query like the following, but it didn't work:


insert /*+APPEND*/ into local_table
SELECT LOWER (field_1) ,field_2,TO_CHAR(field_3,'YYYYMMDD')
FROM remote_view@remote_db_link
WHERE LOWER(field_4)
in (select distinct(lower(tipo)) tipo from my_list)

Thanks
Regards

 

[carp]

it2010 -

You might want to look into using the PL/SQL profiler to narrow down where you are actually spending the most time in this procedure. You also might consider running the query in sql*plus so you can work on tuning it via the execution plan.

If the remote table is large, I have seen many occasions where it is actually much faster to drag all of the data you will need (in this case, field_1, field_2, field_3, and field_4) into a local table and THEN throw your logic at it.

To thargtheslayer's point, getting the query out of the loop will help your performance by eliminating a lot of context switching. Something like

INSERT INTO local_table
SELECT LOWER (field_1) ,field_2,TO_CHAR(field_3,'YYYYMMDD') 
FROM remote_view@remote_db_link
     INNER JOIN (SELECT DISTINCT(lower(tipo)) tipo 
                   FROM my_list) rec
WHERE LOWER(field_4) = rec.tipo;

will achieve the same results without the loop. However, if, as you say, you are only seeing 2-3 iterations in the loop, this is probably not your bottleneck, and the above query would have you joining tables across the network - which is generally death to performance.

As to your original post - I would ask your DBA what THEY think causes this error if there is "plenty of room". This error comes from a segment not being able to expand when it needs to. The tablespace may have plenty of freespace, but it may be fragmented. On the other hand, what the DBA is calling "plenty of room" may nonetheless be insufficient - at least, the database seems to think so! This error doesn't occur without a reason, and it is one of the more useful error messages Oracle provides.

 

[it2010]

I'm planning to try the following query:

insert /*+APPEND*/ into local_table
SELECT /*+ DRIVING_SITE(remote_view) */ LOWER (field_1) ,field_2,TO_CHAR(field_3,'YYYYMMDD'), my_local_function(field_4)
FROM remote_view
WHERE LOWER(field_5) in (select /*+ full(my_local_list)*/ distinct(lower(tipo)) tipo from my_local_list )

having this execution plan:

INSERT STATEMENT ALL_ROWS
4 LOAD AS SELECT local_table
---- 3 HASH JOIN SEMI
----------1 REMOTE REMOTE remote_view
----------2 TABLE ACCESS FULL TABLE my_local_list


My_local_list usually has 44 records (3-5 distinct).

my_local_function is a (new) stored function on local db doing a number-date conversion.

Regards.