improve performance

[cleberz]

Good morning,

I'm doing the Select below and would like to improve performance because I'm using it in a package and it's taking an absurd. Does anyone have a tip for improving the select performance below?

Select all

              SELECT TABLE_NAME, COLUMN_NAME 
                FROM ALL_CONS_COLUMNS 
               WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
                                           FROM ALL_CONSTRAINTS 
                                          WHERE OWNER = 'USER_1' 
                                            AND CONSTRAINT_TYPE = 'P' 
                                            AND TABLE_NAME LIKE ('TABLE_CUST_%'));

[spernega]

Good morning Cleberz,

attempts to compare more columns

Select all

SELECT TABLE_NAME, COLUMN_NAME 
FROM   ALL_CONS_COLUMNS 
WHERE  (owner, table_name, CONSTRAINT_NAME) IN (SELECT owner, table_name, CONSTRAINT_NAME 
                                                FROM ALL_CONSTRAINTS 
                                                WHERE OWNER = 'USER_1' 
                                                AND CONSTRAINT_TYPE = 'P' 
                                                AND TABLE_NAME LIKE ('TABLE_CUST_%'));

may be improved.

[cleberz]

Improved not.

[cleberz]

The select below is a cursor that generates a query. The execution of the block below is that it takes so much soooo, absurdly, 12 hours for 22 tables.

Select all

declare 
  queryresult     number(38) := 0; 
  cursor C_1 is 
              SELECT TABLE_NAME, COLUMN_NAME 
                FROM ALL_CONS_COLUMNS 
               WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
                                           FROM ALL_CONSTRAINTS 
                                          WHERE OWNER = 'USER1' 
                                            AND CONSTRAINT_TYPE = 'P' 
                                            AND TABLE_NAME LIKE ('TABLE_CUST%')); 
begin 
    for rec in c_1 loop 
     
        execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE  NOT EXISTS (SELECT 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult; 
  
    end loop; 
end;

[spernega]

Is the bottleneck in this same query?

I am running on my base and returns right, without delay.

Want to put the package so we can take a look?

[cleberz]

The select below is a cursor that generates a query. The execution of the block below is that it takes a lot, absurdly, 12 hours for 22 tables.

Select all

declare 
queryresult number(38) := 0; 
cursor C_1 is 
SELECT TABLE_NAME, COLUMN_NAME 
FROM ALL_CONS_COLUMNS 
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME 
FROM ALL_CONSTRAINTS 
WHERE OWNER = 'USER1' 
AND CONSTRAINT_TYPE = 'P' 
AND TABLE_NAME LIKE ('TABLE_CUST%')); 
 
begin 
for rec in c_1 loop 
 
execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult; 
 
end loop; 
end;

[spernega]

I'm going to test with what you sent, but by doubts, run just the query to see if it's very slow.

[spernega]

Select in the Immediate execute that is running may not be using no index, in addition to the link being slow.

But I bet on the first hypothesis, no index.

Are you trying to compare missing records at the remote base?

If your PKs are composed of more than one column, almost certainty is index.

[cleberz]

Are you trying to compare missing records at the remote base? Yes

If it is index, how do I solve? Use a HINT?

[spernega]

I do not know if a hint would solve ...

I'll do some tests here.

[cleberz]

See the HINT that I placed on the second Select of Execute Immediate.
Improved a lot:

Select all

execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT /*+ parallel */ 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult;

[spernega]

Well ...

I tried otherwise, see if it helps a little more.
I used Minus instead not existing, easier to ride.

Select all

declare 
 -- 
 v_owner  varchar2(40) := 'USER1'; 
 v_link   varchar2(40) := '@dblink_siv001'; 
 v_table_name  varchar2(40) := 'TABLE_CUST'; 
 queryresult number(38) := 0; 
 -- 
 cursor c is 
  select constraint_name, table_name from all_constraints  
  where  owner = v_owner 
  and    table_name like v_table_name||'%'; 
  and    constraint_type = 'P'; 
 c_r c%rowtype; 
 -- 
 cursor C1 is 
  select column_name, count(*) over (partition by table_name) qt_colu 
  from   all_cons_columns 
  where  owner = v_owner 
  and    table_name = c_r.table_name  
  and    constraint_name = c_r.constraint_name 
  order by position ; 
 c1_r c1%rowtype; 
 -- 
 v_comando varchar2(2000); 
 v_colunas varchar2(2000); 
 v_contador number := 0; 
 -- 
begin 
 open c; 
  loop 
   fetch c into c_r; 
    exit when c%notfound; 
    -- 
    v_Comando  := null; 
    v_contador := 1; 
    v_colunas  := null; 
    open c1; 
     loop  
      fetch c1 into c1_r; 
       exit when c1%notfound; 
       -- 
       if v_contador = c1_r.qt_colu then 
          v_colunas := v_colunas||c1_r.column_name; 
       else  
          v_colunas := v_colunas||c1_r.column_name||','; 
       end if; 
       v_contador := nvl(v_contador,0) + 1; 
       -- 
     end loop; 
    close c1; 
    -- 
    v_Comando := 'Select count(1) from ( '; 
    v_comando := v_Comando||'Select '||v_Colunas||' from '||c_r.table_name||' minus '||'Select '||v_Colunas||' from '||c_r.table_name||v_link||')'; 
    --v_comando := v_Comando||'Select '||v_Colunas||' from '||c_r.table_name||v_link||' minus '||'Select '||v_Colunas||' from '||c_r.table_name||')'; 
    -- 
    dbms_output.put_line(v_Comando); 
    execute immediate v_Comando into queryresult; 
    dbms_output.put_line(queryresult); 
  end loop; 
 close c; 
end; 
/

[cleberz]

OK. I'll test this.

[spernega]

Just do not forget to change the name of Dblink, I left mine

[spernega]

And I forgot to take one;

Select all

and table_name like v_table_name||'%';

[cleberz]

Testing still, hold on, Brother.

[cleberz]

Brother, the way you put this delaying 11,000 seconds and with the Hint / * + Parallel * / I put it taking 9,000 seconds, a little faster. Still, can not we get better?

[spernega]

Try to do it using the not exist, I'm not sure if one is faster than the other, but it does not cost to try.

Meanwhile I'm thinking about here.

[spernega]

I think I got something, try so

Select all

declare 
 -- 
 v_owner  varchar2(40) := 'USER_1'; 
 v_link   varchar2(40) := '@DB_LINK'; 
 v_table_name  varchar2(40) := 'TABLE_CUST_'; 
 -- 
 queryresult number(38) := 0; 
 v_qt_regi_base_loca number(38) := 0; 
 v_qt_regi_base_remo number(38) := 0; 
 -- 
 cursor c is 
  select constraint_name, table_name from all_constraints  
  where  owner = v_owner 
  and    table_name like v_table_name||'%' 
  and    constraint_type = 'P'; 
 c_r c%rowtype; 
 -- 
 cursor C1 is 
  select column_name, count(*) over (partition by table_name) qt_colu 
  from   all_cons_columns 
  where  owner = v_owner 
  and    table_name = c_r.table_name  
  and    constraint_name = c_r.constraint_name 
  order by position ; 
 c1_r c1%rowtype; 
 -- 
 v_comando varchar2(2000); 
 v_colunas varchar2(2000); 
 v_contador number := 0; 
 -- 
begin 
 open c; 
  loop 
   fetch c into c_r; 
    exit when c%notfound; 
    -- 
    -- pesquisa a quantidade de registros na base local 
    -- 
    v_qt_regi_base_loca := 0; 
    execute immediate 'select count(1) from '||c_r.table_name into v_qt_regi_base_loca; 
    -- 
    v_contador := 1; 
    v_colunas  := null; 
    v_Comando := 'Select count(1) from '||c_r.table_name||' a where exists (select 1 from '||c_r.table_name||v_link||' b '; 
    open c1; 
     loop  
      fetch c1 into c1_r; 
       exit when c1%notfound; 
       -- 
       if v_contador = 1 then 
          v_Comando := v_Comando||' where '; 
       else 
          v_Comando := v_Comando||' and '; 
       end if; 
       -- 
       v_Comando := v_Comando||' a.'||c1_r.column_name||' = b.'||c1_r.column_name; 
       v_contador := nvl(v_contador,0) + 1; 
       -- 
     end loop; 
    close c1; 
    -- 
    v_Comando := v_Comando||')'; 
    -- 
    dbms_output.put_line(v_Comando); 
    execute immediate v_Comando into v_qt_regi_base_remo; 
    -- 
    dbms_output.put_line('Local  :'||nvl(v_qt_regi_base_loca,0)); 
    dbms_output.put_line('Remota :'||nvl(v_qt_regi_base_Remo,0)); 
    dbms_output.put_line( nvl(v_qt_regi_base_loca,0) - nvl(v_qt_regi_base_remo,0) ); 
  end loop; 
 close c; 
end; 
/

[cleberz]

With HINT / * + Parallel (5) * / I managed to knock down 9,000 seconds to 6,000 seconds.

Now testing your block.

[cleberz]

My friend, took 13,000 seconds. With Parallel gets faster.

[spernega]

Beauty, at least you have already subsided a lot from the beginning

[cleberz]

Yes, thank you brother. But I still try to move on. It cost!