Devart Forums

hi,
we upgare from 5.x to 8.14 .
Some SQL's have performance problem.
We running SQL in our application (Toraquery component), get result in 7-9 seconds.(returning records around 300 lines in 1 million records).
we test in TOAD and 5.x ODAC, same sql returns recods in 1 sec.
There is immedaitylylock system in query,we remove it, but results is not change.
We check explain by in TOAD , everyting is ok.
But query tme is long in 8.14 ODAC.
I added SQLmonitor results, when SQL running from application by 8.14.
SQL with problem marked as red color.

best regards.


Timestamp: 10:14:51.579

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:14:51.679

SELECT * FROM HASTANE.BOLUM
WHERE (BOLUM BETWEEN 78 AND 89)
OR (BOLUM BETWEEN 64 AND 68)
or (BOLUM=75)
OR (BOLUM BETWEEN 100 AND 500) AND (NVL(AKTIF,'T')'F') AND (NVL(RANDEVUDA_GORUNMESIN,'F')'T')
ORDER BY BOLUM_ADI

----------------------------------
Timestamp: 10:14:51.697

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:14:51.710

SELECT dr_kodu,adi_soyadi,bolum FROM HASTANE.DRADI
WHERE AKTIF='A'
AND BOLUM BETWEEN :BOLUM1 AND :BOLUM2
AND NVL(RANDEVUDA_GORUNMESIN,'F') 'T'

BOLUM1 = 75
BOLUM2 = 75
----------------------------------
Timestamp: 10:14:51.720

SELECT * FROM HASTANE.HASTANEKEY

------------------------------- ---
Timestamp: 10:14:51.730

SELECT dr_kodu,adi_soyadi,bolum FROM HASTANE.DRADI
WHERE AKTIF='A'
AND BOLUM BETWEEN :BOLUM1 AND :BOLUM2
AND NVL(RANDEVUDA_GORUNMESIN,'F') 'T'

BOLUM1 = 75
BOLUM2 = 75
----------------------------------
Timestamp: 10:14:51.740

SELECT * FROM HASTANE.BOLUM
WHERE (BOLUM BETWEEN 80 AND 89)
OR (BOLUM BETWEEN 64 AND 68)
OR (BOLUM BETWEEN 100 AND 999)
ORDER BY BOLUM_ADI

----------------------------------
Timestamp: 10:14:51.761

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:14:51.797

SELECT dr_kodu,adi_soyadi,bolum FROM HASTANE.DRADI
WHERE AKTIF='A'
AND BOLUM BETWEEN :BOLUM1 AND :BOLUM2
AND NVL(RANDEVUDA_GORUNMESIN,'F') 'T'

BOLUM1 = 75
BOLUM2 = 75
----------------------------------
Timestamp: 10:14:51.830

SELECT * FROM HASTANE.HASTANEKEY

------------ ----------------------
Timestamp: 10:14:51.860

SELECT dr_ko du,adi_soyadi,bolum FROM HASTANE.DRADI
WHERE AKTIF='A'
AND BOLUM BETWEEN :BOLUM1 AND :BOLUM2
AND NVL(RANDEVUDA_GORUNMESIN,'F') 'T'

BOLUM1 = 75
BOLUM2 = 75
----------------------------------
Timestamp: 10:14:51.895

SELECT * FROM HASTANE.RANDTIPI
WHERE (BOLUM IS NULL)
OR (BOLUM=:BOLUM1)

BOLUM1 =
----------------------------------
Timestamp: 10:14:52.201

SELECT R.ROWID,LPAD(R.SAAT,5,' ') SAAT,R.*,K.DOGUM_TAR,K.E_MAIL,R.ADI||''||R.SOYADI HASTA_ADSOYAD,KR.KURUM_ADI,RT.RANKKODU,
rownum sno,rownum,EL.RENK_KODU HAS_DURUMU_RENKKODU,PR.GTARIH FROM HASTANE.HASTARND R,HASTANE.PROTOKOL PR,HASTANE.KIMLIK K,
HASTANE.KURUM KR,HASTANE.RANDTIPI RT,HASTANE.EVRAKLIST EL
WHERE R.TARIH=:TARIH AND R.DOSYA_NO=K.DOSYA_NO(+) AND R.KURUM_NO=KR.KURUM_NO(+) AND R.TIPI=RT.KODU(+) AND
R.HASTA_DURUMU=EL.EVRAKNO(+) AND R.PROTOKOL=PR.PROTOKOL_NO(+)
order BY R.ONCELIKLI,R.TARIH,R.SAAT

TARIH = '13.0 4.2012'
----------------------------------
Timestamp: 10:14:59.596

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:14:59.606

SELECT dr_kodu,adi_soyadi,bolum FROM HASTANE.DRADI
WHERE AKTIF='A'
AND BOLUM BETWEEN :BOLUM1 AND :BOLUM2
AND NVL(RANDEVUDA_GORUNMESIN,'F') 'T'

BOLUM1 = 250
BOLUM2 = 250
----------------------------------
Timestamp: 10:14:59.620

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:14:59.630

SELECT dr_kodu,adi_soyadi,bolum FROM HASTANE.DRADI
WHERE AKTIF='A'
AND BOLUM BETWEEN :BOLUM1 AND :BOLUM2
AND NVL(RANDEVUDA_GORUNMESIN,'F') 'T'

BOLUM1 = 250
BOLUM2 = 250
----------------------------------
Timestamp: 10:14:59.648

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:14:59.658

SELECT dr_kodu,adi_soyadi,bolum FR OM HASTANE.DRADI
WHERE AKTIF='A'
AND (BOLUM BETWEEN :BOLU M1 AND :BOLUM2)
AND NVL(RANDEVUDA_GORUNMESIN,'F') 'T'

BOLUM1 = 250
BOLUM2 = 250
----------------------------------
Timestamp: 10:14:59.672

SELECT * FROM HASTANE.RANDTIPI
WHERE (BOLUM IS NULL)
OR (BOLUM=:BOLUM1)

BOLUM1 = 250
----------------------------------
Timestamp: 10:15:13.144

SELECT D.DR_KODU,G.* FROM HASTANE.DRADI D,HASTANE.DRRAND G
WHERE D.DR_KODU=:DR_KODU
AND D.DR_KODU=G.DR_KODU(+)
AND G.GUN(+)=:GUN

DR_KODU = 299
GUN = 6
----------------------------------
Timestamp: 10:15:13.181

SELECT D.*,RT.RANDTIPI FROM HASTANE.RANDEVUSABLON D,HASTANE.RANDTIPI RT
WHERE D.DR_KODU=:DR_KODU
AND D.GUN(+)=:GUN
AND D.TIPI=RT.KODU(+)
ORDER BY D.SAAT,D.TIPI

DR_KODU = 299
GUN = 6
----------------------------------
Timestamp: 10:15:13.247

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:15:13.619

SELECT R.ROWID,LPAD( R.SAAT,5,' ') SAAT,R.*,K.DOGUM_TAR,K.E_MAIL,R.ADI||''||R.SOYADI HASTA_ADSOYAD,KR.KURUM_ADI,RT.RANKKODU,
rownum sno,rownum,EL.RENK_KODU HAS_DURUMU_RENKKODU,PR.GTARIH FROM HASTANE.HASTARND R,HASTANE.PROTOKOL PR,HASTANE.KIMLIK K,
HASTANE.KURUM KR,HASTANE.RANDTIPI RT,HASTANE.EVRAKLIST EL
WHERE R.TARIH=:TARIH AND R.DOSYA_NO=K.DOSYA_NO(+) AND R.KURUM_NO=KR.KURUM_NO(+) AND R.TIPI=RT.KODU(+) AND
R.HASTA_DURUMU=EL.EVRAKNO(+) AND R.PROTOKOL=PR.PROTOKOL_NO(+)
order BY R.ONCELIKLI,R.TARIH,R.SAAT

TARIH = '13.04.2012'
----------------------------------
Timestamp: 10:15:20.335

SELECT D.DR_KODU,G.* FROM HASTANE.DRADI D,HASTANE.DRRAND G
WHERE D.DR_KODU=:DR_KODU
AND D.DR_KODU=G.DR_KODU(+)
AND G.GUN(+)=:GUN

DR_KODU = 299
GUN = 6
----------------------------------
Timestamp: 10:15:20.350

SELECT D.*,RT.RANDTIPI FROM HASTANE.RANDEVUSABLON D,HASTANE.RANDTIPI RT
WHERE D.DR_KODU=:DR_KODU
AND D.GUN(+)=:GUN
AND D.TIPI=R T.KODU(+)
ORDER BY D.SAAT,D.TIPI

DR_KODU = 299
GUN = 6----------------------------------
Timestamp: 10:15:28.169

SELECT R.ROWID,LPAD(R.SAAT,5,' ') SAAT,R.*,K.DOGUM_TAR,K.E_MAIL,R.ADI||''||R.SOYADI HASTA_ADSOYAD,KR.KURUM_ADI,RT.RANKKODU,
rownum sno,rownum,EL.RENK_KODU HAS_DURUMU_RENKKODU,PR.GTARIH FROM HASTANE.HASTARND R,HASTANE.PROTOKOL PR,HASTANE.KIMLIK K,
HASTANE.KURUM KR,HASTANE.RANDTIPI RT,HASTANE.EVRAKLIST EL
WHERE R.TARIH=:TARIH AND R.DOSYA_NO=K.DOSYA_NO(+) AND R.KURUM_NO=KR.KURUM_NO(+) AND R.TIPI=RT.KODU(+) AND
R.HASTA_DURUMU=EL.EVRAKNO(+) AND R.PROTOKOL=PR.PROTOKOL_NO(+)
order BY R.ONCELIKLI,R.TARIH,R.SAAT

TARIH = '12.04.2012'
----------------------------------
Timestamp: 10:15:36.702

SELECT D.DR_KODU,G.* FROM HASTANE.DRADI D,HASTANE.DRRAND G
WHERE D.DR_KODU=:DR_KODU
AND D.DR_KODU=G.DR_KODU(+)
AND G.GUN(+)=:GUN

DR_KODU = 299
GUN = 5
----------------------------------
Timestamp: 10:15:36.755

SELECT D.*,RT.RANDTIPI FROM HASTANE.RANDEVUSAB LON D,HASTANE.RANDTIPI RT
WHERE D.DR_KODU=:DR_KODU
AND D.GUN(+)=:GUN
AND D.TIPI=RT.KODU(+)
ORDER BY D.SAAT,D.TIPI

DR_KODU = 299
GUN = 5
----------------------------------
Timestamp: 10:15:36.805

SELECT * FROM HASTANE.HASTANEKEY

----------------------------------
Timestamp: 10:15:36.826

SELECT COUNT(*) ADET FROM HASTANE.MESAJ M
WHERE
m.KIME=:OWNER and M.DURUMU='F'
AND m.MESAJ_TIPI='I'

OWNER = 'BEDIRHAN'
----------------------------------
Timestamp: 10:15:36.866

SELECT * from HASTANE.GENELMESAJ

----------------------------------
Timestamp: 10:15:36.889

SELECT * FROM HASTANE.HASTANEKEY

Hello,

Please send the dump of the tables participating in the "slow" query to alexp*devart*com in order that we can check this behaviour, specify also your TOraQuery settings if they are different from the default ones.