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