insert - select takes quite long

[shockshell]

Hello everybody,

i have a problem with, from my point of view simple insert - select statement. It takes nearly 20 minutes to finish, on an good equipped server. At first i thought the selection would take so long, but this is done in nearly five seconds. The rest of the time is spend for saving the resultsset (7500 rows) into the result table. The table itself has three (row, no function) indexes and one before insert trigger for selecting a sequence index. I also tried oracle hints (+append) but nothing changed. I'm not an oracle crack so if anyone has an idea of what is going wrong please help. For completeness i will post my statement.

insert into 
  tbl_errorcases_ata
(rental_contract_no,type_id,referenced_value,topla_id,region)
select 
  fahrauftragsnummer,errortype,referenced_value,tid,region
from 
  QRY_ATA_ACTUAL_ERROR_CASES errors
where
not exists (select 	
  topla_id 
from 
  TBL_ERRORCASES_ATA 
where 
  rental_contract_no=errors.fahrauftragsnummer and 
  referenced_value=errors.referenced_value and 
  type_id=errors.errortype and
  region=errors.region
);

 

[Dagon]

It's probably the trigger. Can you paste in the PL/SQL code from the trigger.

 

[shockshell]

  CREATE OR REPLACE TRIGGER "AGTABR"."BEFORE_INSERT" BEFORE INSERT ON TBL_ERRORCASES_ATA 
FOR EACH ROW 
begin
	if :old.DATE_ADDED is null then
		:new.DATE_ADDED:=sysdate;
	end if;
	SELECT TBL_ERRORCASES_ATA_TYPE_SEQ.nextval INTO :new.ID   FROM dual;
        SELECT monat,jahr INTO :NEW.ACC_MONTH,:NEW.ACC_YEAR FROM TBL_RECHNUNG_MONAT_JAHR where rownum<2;
end; 

ALTER TRIGGER "AGTABR"."BEFORE_INSERT" ENABLE

The table TBL_RECHNUNG_MONAT_JAHR has only one row...

 

[Dagon]

What are you trying to achieve with this statement ?

SELECT monat,jahr INTO :NEW.ACC_MONTH,:NEW.ACC_YEAR FROM TBL_RECHNUNG_MONAT_JAHR where rownum<2;

Do all rows in TBL_RECHNUM_MONAT_JAHR have the same values for monat and jahr ? If not, you must be assuming that rows will be returned in a particular order, which is very bad practice.

I don't see any reason to use the rownum < 2 method. It would be better simply to declare a cursor on "SELECT monat,jahr FROM TBL_RECHNUNG_MONAT_JAHR", open it and fetch the first row only.

If the values returned are simply the current month and year, you might also be able to get them without any SQL at all e.g.

:new_acc_month := to_char(sysdate, 'MM');
:new_acc_year := to_char(sysdate, 'YYYY');

 

[Dagon]

Just noticed the comment that TBL_RECHNUNG_MONAT_JAHR has only one row. If you are using this just to store the current year and date, that is pointless. Just get them directly using to_char functions.

 

[Dagon]

Also, when you say the "select is done in 5 seconds", do you mean the entire select (fetching all the rows) or are you just talking about 5 seconds to see the first few rows ?

 

[shockshell]

Hi Dagon,

this table is used to store the accounting period which must not be the actual month/year. I'm writing only a small part of the whole accounting algorithm. The where clause is just for safety reasons because the accounting can only be done for one month at once. The problem is that i can not influence the content of this table, or even change the way data is stored there, and i have to used it, because the whole accounting algorithm is based on it (unfortunately). I will try a little bit of manipulating this trigger. Maybe it will help. I will come back later (hopefully with good news...). Thanks for your tips.

Regards,

Marcus

 

[shockshell]

Also, when you say the "select is done in 5 seconds", do you mean the entire select (fetching all the rows) or are you just talking about 5 seconds to see the first few rows ?[/color red]

Oh oh... I think thats the clue... I tried to export the selected data to test this and it seems that i'm a little bit tool blinded (oracle developer) because the export is running quite long as well. If i'm right the tool is fetching the first 50 rows only (this is what is done within five seconds!). When expanding this to 7500 rows we came up to at least more than 12 minutes...

Back to work...

 

[Dagon]

You need to check the plan for the query, especially the subquery:

select
topla_id
from
TBL_ERRORCASES_ATA
where
rental_contract_no=errors.fahrauftragsnummer and
referenced_value=errors.referenced_value and
type_id=errors.errortype and
region=errors.region

This probably needs to be using an index on TBL_ERRORCASES_ATA.

 

[sem]

Are you sure you have no locking issues? Is your session the only writing to that table?

Regards, Dima

http://seminihin.narod.ru

 

[shockshell]

Hi sem,

yes, i'm sure that my session is the only one, because only my application uses this table and the application is a single user one. So locking should not be the problem. I have analyzed the query QRY_ATA_ACTUAL_ERROR_CASES which is actually a union all statement of 15 querys(!).

They together have a cost factor of 2500. Many of these statements (not all) use a databaselink to a database located on a different server. I already have optimized the statements. Before that the cost factor was at 3800.

I'm thinking to use a mat. view, but the data changes frequently and the locale network (rate) is in a poor condition. Could this help? Or do i have to face even more problems?

Regards,

Marcus

 

[Dagon]

Before you do that, make sure it isn't the subquery on TBL_ERRORCASES_ATA which is the problem. Try running the entire query with and without the subquery to see how much extra time it adds.

 

[shockshell]

I've made the mat. view change and it was very usefull. The whole insert just takes only 9 seconds instead of the 20 minutes before. The costs of the whole statement is decreased to 151, which is less than the average cost of just one of the 15 statements before. The subquery was already performant.
Hopefully the whole thing is working in the live system as well.

Thanks for your hints, they brought me a lot.