search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
1.02k stars 343 forks source link

after last changes the view migration is extra slow #584

Closed okbob closed 6 years ago

okbob commented 6 years ago

The view migration was about 1hour. Now, it is more than 1 day. Last commit 2d1db01868ae2ce0bd8c30987fd0f6a535e6a9bb

darold commented 6 years ago

Ok, I will look on how to optimize this code tomorrow.

okbob commented 6 years ago

Looks so speed is ok, but the commit 2dc9a95587e19093ff9ae416924639a04bd15b59 enforce extra long cycle on some views:

CREATE OR REPLACE VIEW IDES_JMMAJ_AKT.V_VNAK_PRUV
(ID_VNAK_PRUV,NAZEV,ID_CCLE,PLATNOST_OD,PLATNOST_DO,POZNAMKA,IDENT,BLOKOVANO,CVZNIKRADK,ID_ASPRAVCE,ID_APROVOZNA,NAZEV_CSKUPINA,ID_SKUP_CISEL,CAS_DATVYR,DATVYR,LIDENT_JMENO,LIDENT,CAS_DATZMENY,DATZMENY,LIDENT_ZMENA_JMENO,LIDENT_ZMENA,POC_ZAZ_DAVKA,POC_ZAZ_BEZUZI,POC_NE_DEF_TEP,POC_NE_DEF_HAS,POC_NE_DEF_TUV,POC_NE_DEF_VOD,POC_NE_VYNAK_TEP,POC_NE_VYNAK_HAS,POC_NE_VYNAK_TUV,POC_NE_VYNAK_VOD,SUM_ZAOT,SUM_SPOT,SUM_ZAOTSPOT,SUM_NAOT,SUM_ZATUV,SUM_SPTUV,SUM_OHREVTUV_SAM,SUM_SASVTV,SUM_SPSVTV,SUM_VODTUV_SAM,SUM_NASV,SUM_OSTNAK,SUM_OSTNAK1,SUM_OSTNAK2,TEP_MIMO_NAOT,HAS_MIMO_OHREVTUV_SAM,TUV_MIMO_VODTUV_SAM,VOD_MIMO_NASV,OST_MIMO_OSTNAK,OST1_MIMO_OSTNAK1,OST2_MIMO_OSTNAK2)
AS
select  MT.ID_VNAK_PRUV, MT.NAZEV, MT.ID_CCLE
, MT.PLATNOST_OD, MT.PLATNOST_DO, MT.POZNAMKA
, MT.IDENT, MT.BLOKOVANO, MT.CVZNIKRADK
, MT.ID_ASPRAVCE, MT.ID_APROVOZNA, A2.J1_NAZEV as NAZEV_CSKUPINA
, MT.ID_SKUP_CISEL, TO_CHAR(MT.DATVYR,'HH24:MI:SS') as CAS_DATVYR, MT.DATVYR
, DECODE(A3.ID_SSSLOGIN,NULL,MT.IDENT,A3.JMENO ) as LIDENT_JMENO
, MT.LIDENT, TO_CHAR(MT.DATZMENY,'HH24:MI:SS') as CAS_DATZMENY
, MT.DATZMENY, A4.JMENO as LIDENT_ZMENA_JMENO, MT.LIDENT_ZMENA
,( select count(*) from VNAK_ISTA B1 where B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV ) AS POC_ZAZ_DAVKA
,( select count(*) from VNAK_ISTA B1 where B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
   AND B1.ID_NAJVZTAH IS NULL AND (NAKLAD <>0)) AS POC_ZAZ_BEZUZI
,( select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
        AND ( ID_VYROK_TEP IS NULL
        AND ( ZAOT IS NOT NULL OR SPOT IS NOT NULL )
        AND ( ZAOT <> 0 OR SPOT <> 0 ))  ) AS POC_NE_DEF_TEP,
  (select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
       AND( ID_VYROK_HAS IS NULL
       AND ( ZATUV IS NOT NULL OR SPTUV IS NOT NULL )
       AND ( ZATUV <> 0 OR SPTUV <> 0 )) ) AS POC_NE_DEF_HAS,
  (select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
       AND ( ID_VYROK_TUV IS NULL
       AND ( SASVTV IS NOT NULL OR SPSVTV IS NOT NULL )
       AND ( SASVTV <> 0 OR SPSVTV <> 0 )) ) AS POC_NE_DEF_TUV,
  (select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
       AND ( ID_VYROK_VOD IS NULL
       AND ( NASV IS NOT NULL ) AND ( NASV <> 0 ) )) AS POC_NE_DEF_VOD,
  (select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
       AND ( ID_VYROK_TEP IS NOT  NULL
       AND ( ID_VYNAKBYT_TEP IS  NULL )
        AND ( ZAOT IS NOT NULL OR SPOT IS NOT NULL )
        AND ( ZAOT <> 0 OR SPOT <> 0 ))) AS POC_NE_VYNAK_TEP,
  (select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
       AND ( ID_VYROK_HAS IS NOT  NULL
       AND ( ID_VYNAKBYT_HAS IS  NULL )
       AND ( ZATUV IS NOT NULL OR SPTUV IS NOT NULL )
       AND ( ZATUV <> 0 OR SPTUV <> 0 ) )) AS POC_NE_VYNAK_HAS,
  (select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
       AND ( ID_VYROK_TUV IS NOT  NULL
       AND ( ID_VYNAKBYT_TUV IS  NULL )
       AND (( SASVTV IS NOT NULL OR SPSVTV IS NOT NULL )
       AND ( SASVTV <> 0 OR SPSVTV <> 0 )) )) AS POC_NE_VYNAK_TUV,
  (select count(*) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        AND B1.ID_NAJVZTAH IS NOT NULL
       AND ( ID_VYROK_VOD IS NOT  NULL
       AND ( ID_VYNAKBYT_VOD IS  NULL ) AND ( NASV <> 0 ) )) AS POC_NE_VYNAK_VOD,
 (select sum(coalesce(zaot,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_ZAOT,
 (select sum(coalesce(spot,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_SPOT,
 (select sum(coalesce(zaot,0)+coalesce(spot,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_ZAOTSPOT,
 (select sum(coalesce(NAOT,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_NAOT,
 (select sum(coalesce(ZATUV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_ZATUV,
 (select sum(coalesce(SPTUV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_SPTUV,
 (select sum(coalesce(ZATUV,0)+coalesce(SPTUV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_OHREVTUV_SAM,
 (select sum(coalesce(SASVTV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_SASVTV,
 (select sum(coalesce(SPSVTV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_SPSVTV,
 (select sum(coalesce(SASVTV,0)+coalesce(SPSVTV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_VODTUV_SAM,
 (select sum(coalesce(NASV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_NASV,
 (select sum(coalesce(OSTNAK,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_OSTNAK,
 (select sum(coalesce(OSTNAK1,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_OSTNAK1,
 (select sum(coalesce(OSTNAK2,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        ) AS SUM_OSTNAK2,
 (select sum(coalesce(NAOT,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        and upper(B1.TEP_MIMO) LIKE 'A%'
        ) AS TEP_MIMO_NAOT,
 (select sum(coalesce(ZATUV,0)+coalesce(SPTUV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        and upper(B1.HAS_MIMO) LIKE 'A%'
        ) AS HAS_MIMO_OHREVTUV_SAM,
 (select sum(coalesce(SASVTV,0)+coalesce(SPSVTV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        and upper(b1.TUV_MIMO) LIKE 'A%'
        ) AS TUV_MIMO_VODTUV_SAM,
 (select sum(coalesce(NASV,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        and upper(B1.VOD_MIMO) LIKE 'A%'
        ) AS VOD_MIMO_NASV,
 (select sum(coalesce(OSTNAK,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        and upper(B1.OST_MIMO) LIKE 'A%'
        ) AS OST_MIMO_OSTNAK,
 (select sum(coalesce(OSTNAK1,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        and upper(B1.OST1_MIMO) LIKE 'A%'
        ) AS OST1_MIMO_OSTNAK1,
 (select sum(coalesce(OSTNAK2,0)) from VNAK_ISTA B1 where
        B1.ID_VNAK_PRUV=MT.ID_VNAK_PRUV
        and upper(B1.OST2_MIMO) LIKE 'A%'
        ) AS OST2_MIMO_OSTNAK2
 from VNAK_PRUV MT , CLEOSOBY A1,
      CSKUPINA A2, SSSLOGIN A3,
      SSSLOGIN A4
 where MT.ID_CCLE= A1.ID_CCLE(+)
 AND MT.ID_SKUP_CISEL= A2.ID_SKUP_CISEL(+)
 AND MT.LIDENT= A3.ID_SSSLOGIN(+)
 AND MT.LIDENT_ZMENA= A4.ID_SSSLOGIN(+)


Attached profile (execution was cancelled after 40 min)

[nytprof.zip](https://github.com/darold/ora2pg/files/1443869/nytprof.zip)
darold commented 6 years ago

Commit f4927f1 fixes possible infinite loop introduce with commit 2dc9a95.

okbob commented 6 years ago

It is fixed, thank you