Devart Forums

Posted: **Tue 04 Sep 2018 07:57**

Добрый день! Тестирую возможность перехода от компонент DOA на ODAC. Столкнулся с такой проблемой:
при открытии запроса вида
select поля, поля... поля from вьюха t where t.bank_n = :PBankN
при выборке в несколько миллионов записей очень долго выполняется запрос.
Свойства FetchAll=False FetchRows=300. Судя по документации, должен отфетчиться первый блок, остальные блоки должны подтягиваться по мере обращения пользователя к ним (например, листаем cxGrid). Не могли бы вы объяснить, почему при объеме выборки в 6 миллионов записей OraQuery.Open отрабатывает более 5 минут. Заранее благодарен.

Posted: **Tue 04 Sep 2018 13:31**

Пожалуйста, попробуйте выполнить интересующий Вас запрос OraQuery без использования каких-либо визуальных компонентов и сообщите нам о результатах

Posted: **Tue 04 Sep 2018 13:44**

Пробовал выполнить этот запрос в PL\SQL Developer. Выполняется практически моментально, появляется первая порция данных. Буквально полторы секунды.

Posted: **Tue 04 Sep 2018 13:47**

MaximG wrote: ↑Tue 04 Sep 2018 13:31 Пожалуйста, попробуйте выполнить интересующий Вас запрос OraQuery без использования каких-либо визуальных компонентов и сообщите нам о результатах

Вот часть трассы этого запроса, но возвращает 400 тысяч записей.

Code: Select all

*** 2018-09-03 17:41:46.428
FETCH #139868340145904:c=3613450,e=3621363,p=0,cr=44795,cu=0,mis=0,r=1,dep=1,og=1,plh=543621469,tim=1535985706428819
STAT #139868340145904 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=44797 pr=0 pw=0 time=3621383 us)'
STAT #139868340145904 id=2 cnt=420002 pid=1 pos=1 obj=0 op='FILTER  (cr=44797 pr=0 pw=0 time=3431799 us)'
STAT #139868340145904 id=3 cnt=420002 pid=2 pos=1 obj=0 op='HASH JOIN  (cr=44795 pr=0 pw=0 time=3367405 us cost=2452 size=2185662 card=24558)'
STAT #139868340145904 id=4 cnt=18 pid=3 pos=1 obj=266194 op='TABLE ACCESS FULL MOP_STANS_DICT (cr=6 pr=0 pw=0 time=51 us cost=3 size=72 card=18)'
STAT #139868340145904 id=5 cnt=420002 pid=3 pos=2 obj=0 op='HASH JOIN RIGHT OUTER (cr=44789 pr=0 pw=0 time=3045028 us cost=2448 size=2087430 card=24558)'
STAT #139868340145904 id=6 cnt=67 pid=5 pos=1 obj=379242 op='TABLE ACCESS FULL MOP_AGENT (cr=6 pr=0 pw=0 time=17 us cost=3 size=396 card=66)'
STAT #139868340145904 id=7 cnt=420002 pid=5 pos=2 obj=0 op='HASH JOIN RIGHT OUTER (cr=44783 pr=0 pw=0 time=2874147 us cost=2445 size=1940082 card=24558)'
STAT #139868340145904 id=8 cnt=67 pid=7 pos=1 obj=379242 op='TABLE ACCESS FULL MOP_AGENT (cr=6 pr=0 pw=0 time=22 us cost=3 size=396 card=66)'
STAT #139868340145904 id=9 cnt=420002 pid=7 pos=2 obj=0 op='NESTED LOOPS  (cr=44777 pr=0 pw=0 time=2672792 us cost=2442 size=1792734 card=24558)'
STAT #139868340145904 id=10 cnt=420002 pid=9 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=8733 pr=0 pw=0 time=547912 us cost=2438 size=27140100 card=417540)'
STAT #139868340145904 id=11 cnt=1 pid=10 pos=1 obj=308350 op='TABLE ACCESS FULL MOP_BANK (cr=6 pr=0 pw=0 time=58 us cost=3 size=7 card=1)'
STAT #139868340145904 id=12 cnt=420002 pid=10 pos=2 obj=0 op='BUFFER SORT (cr=8727 pr=0 pw=0 time=483495 us cost=2435 size=24217320 card=417540)'
STAT #139868340145904 id=13 cnt=420002 pid=12 pos=1 obj=0 op='PARTITION LIST SINGLE PARTITION: KEY KEY (cr=8727 pr=0 pw=0 time=244590 us cost=2435 size=24217320 card=417540)'
STAT #139868340145904 id=14 cnt=420002 pid=13 pos=1 obj=265962 op='TABLE ACCESS FULL MOP_D_ PARTITION: KEY KEY (cr=8727 pr=0 pw=0 time=183793 us cost=2435 size=24217320 card=417540)'
STAT #139868340145904 id=15 cnt=420002 pid=9 pos=2 obj=0 op='PARTITION LIST SINGLE PARTITION: KEY(AP) KEY(AP) (cr=36044 pr=0 pw=0 time=1912465 us cost=1 size=8 card=1)'
STAT #139868340145904 id=16 cnt=420002 pid=15 pos=1 obj=266845 op='INDEX UNIQUE SCAN U_MOP_Z_ZID PARTITION: KEY(AP) KEY(AP) (cr=36044 pr=0 pw=0 time=751422 us cost=1 size=8 card=1)'
STAT #139868340145904 id=17 cnt=1 pid=16 pos=1 obj=0 op='NESTED LOOPS  (cr=4 pr=0 pw=0 time=21 us cost=2 size=20 card=1)'
STAT #139868340145904 id=18 cnt=1 pid=17 pos=1 obj=296734 op='TABLE ACCESS BY INDEX ROWID MOP_ATOM_ (cr=2 pr=0 pw=0 time=12 us cost=1 size=9 card=1)'
STAT #139868340145904 id=19 cnt=1 pid=18 pos=1 obj=391149 op='INDEX UNIQUE SCAN P_MOP_ATOM_ (cr=1 pr=0 pw=0 time=3 us cost=1 size=0 card=1)'
STAT #139868340145904 id=20 cnt=1 pid=17 pos=2 obj=296736 op='INDEX UNIQUE SCAN P_USER_RIGHT (cr=2 pr=0 pw=0 time=7 us cost=1 size=11 card=1)'
STAT #139868340145904 id=21 cnt=1 pid=2 pos=2 obj=296736 op='INDEX UNIQUE SCAN P_USER_RIGHT (cr=2 pr=0 pw=0 time=20 us cost=1 size=11 card=1)'
CLOSE #139868340145904:c=0,e=2,dep=1,type=3,tim=1535985706429826
EXEC #139868339903768:c=3810421,e=3859817,p=13,cr=44952,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1535985706429869
XCTEND rlbk=0, rd_only=1, tim=1535985706429903
CLOSE #139868339903768:c=0,e=18,dep=0,type=3,tim=1535985706434283
PARSE #139868339774328:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706436538
EXEC #139868339774328:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706436614
FETCH #139868339774328:c=0,e=20,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706436656
CLOSE #139868339774328:c=0,e=10,dep=1,type=3,tim=1535985706436686
PARSE #139868339774328:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706437053
EXEC #139868339774328:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706437100
FETCH #139868339774328:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706437153
CLOSE #139868339774328:c=0,e=8,dep=1,type=3,tim=1535985706437180
PARSE #139868339774328:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706437454
EXEC #139868339774328:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706437500
FETCH #139868339774328:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706437530
CLOSE #139868339774328:c=0,e=9,dep=1,type=3,tim=1535985706437560
PARSE #139868339774328:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706437761
EXEC #139868339774328:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706437807
FETCH #139868339774328:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706437837
CLOSE #139868339774328:c=0,e=9,dep=1,type=3,tim=1535985706437863
PARSE #139868339774328:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706438106
EXEC #139868339774328:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706438152
FETCH #139868339774328:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706438182
CLOSE #139868339774328:c=0,e=8,dep=1,type=3,tim=1535985706438207
=====================
PARSING IN CURSOR #139868339775376 len=994 dep=0 uid=153 oct=3 lid=204 tim=1535985706440915 hv=3963136235 ad='1de1ce180' sqlid='2bz65hrq3ja7b'
select t.CLIENT_ID,
       t.FULL_NAME,
       t.ID_CODE,
       t.DOC_NUM,
       t.DOC_DATE,
       t.DOC_AUTH,
       t.ADR_REGION,
       t.ADR_DISTR,
       t.ADR_CITY,
       t.ADR_ADR,
       t.BANK_N,
       t.BANK_NAME,
       t.ACN_N,
       t.ACN_ID,
       t.Z_NO,
       t.D_NO,
       t.SUM_FULL,
       t.SUM_DEPO,
       t.SUM_BLOCK,
       t.SUM_NOT,
       t.WHY_NAME,
       t.WHY_NOT,
       t.UPDATE_ST,
       t.STAN_D,
       t.TR_ID,
       t.CF_ID,
       t.BANK_REE,
       t.BANK_PAY,
       t.DATE_PAY,
       t.SUM_PAY,
       t.DEAL_END,
       t.DEAL_NUM,
       t.ACN_NUM,
       t.ACN_SUM,
       t.PROC_NUM,
       t.PROC_SUM,
       t.VIRT,
       t.Z_NO_OLD,
       t.D_NO_OLD,
       t.TMP_BLCK,
       t.DEAL_START,
       t.PROC,
       t.CURR,
       t.ZOB,
       t.SUM_FULL_K,
       t.SUM_DEPO_K,
       t.SUM_BLOCK_K,
       t.SUM_NOT_K,
       t.SUM_PAY_K,
       t.ACN_SUM_K,
       t.PROC_SUM_K
  from fgv0.mop_205_d t 
where t.bank_n = :doa_bankn
END OF STMT
PARSE #139868339775376:c=3999,e=4798,p=0,cr=10,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1535985706440914
PARSE #139868339774328:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706441515
EXEC #139868339774328:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706441567
FETCH #139868339774328:c=0,e=11,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706441599
CLOSE #139868339774328:c=0,e=10,dep=1,type=3,tim=1535985706441630
PARSE #139868339774328:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706441909
EXEC #139868339774328:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706441954
FETCH #139868339774328:c=0,e=11,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706442011
CLOSE #139868339774328:c=0,e=9,dep=1,type=3,tim=1535985706442042
PARSE #139868339774328:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706442299
EXEC #139868339774328:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706442344
FETCH #139868339774328:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706442373
CLOSE #139868339774328:c=0,e=8,dep=1,type=3,tim=1535985706442399
PARSE #139868339774328:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706442568
EXEC #139868339774328:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706442613
FETCH #139868339774328:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706442647
CLOSE #139868339774328:c=0,e=9,dep=1,type=3,tim=1535985706442673
PARSE #139868339774328:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706442894
EXEC #139868339774328:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1535985706442939
FETCH #139868339774328:c=0,e=9,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1535985706442968
CLOSE #139868339774328:c=0,e=9,dep=1,type=3,tim=1535985706443024

*** 2018-09-03 17:41:46.688
EXEC #139868339775376:c=245962,e=247132,p=0,cr=12,cu=0,mis=1,r=0,dep=0,og=1,plh=55685339,tim=1535985706688354

*** 2018-09-03 17:41:51.289
WARNING:io_submit failed due to kernel limitations MAXAIO for process=128 pending aio=111
WARNING:asynch I/O kernel limits is set at AIO-MAX-NR=1048576 AIO-NR=43264

*** 2018-09-03 17:41:55.393
FETCH #139868339775376:c=6884953,e=8698914,p=36585,cr=121849,cu=0,mis=0,r=300,dep=0,og=1,plh=55685339,tim=1535985715393801

Posted: **Wed 05 Sep 2018 08:29**

Hi,

it can be because of cxGrid. Try setting <GridView>.DataController.DataModeController.GridMode := true. Should go as fast as in PL/SQL Developer.

Regards
Adam

Posted: **Wed 05 Sep 2018 10:19**

On the form there is a TOraQuery component, in which there is a query of the specified type. FetchAll = false and FetchRows = 300. Also there is a cxGrid with <GridView> .DataController.DataModeController.GridMode: = true and <GridView> .DataController.DataModeController.GridModeBufferCount = 300. The described case occurred with such settings. But even without a grid, if Dataset is not tied to anyone, the braking described above is observed.

Posted: **Thu 06 Sep 2018 08:59**

Can not add trace file. How can I do it?

Posted: **Thu 06 Sep 2018 12:05**

*** 2018-09-05 17:14:44.900
PARSING IN CURSOR #139773342180664 len=994 dep=0 uid=153 oct=3 lid=196 tim=1536156885176601 hv=3963136235 ad='1de1ce180' sqlid='2bz65hrq3ja7b'
select t.CLIENT_ID,
t.FULL_NAME,
t.ID_CODE,
t.DOC_NUM,
t.DOC_DATE,
t.DOC_AUTH,
t.ADR_REGION,
t.ADR_DISTR,
t.ADR_CITY,
t.ADR_ADR,
t.BANK_N,
t.BANK_NAME,
t.ACN_N,
t.ACN_ID,
t.Z_NO,
t.D_NO,
t.SUM_FULL,
t.SUM_DEPO,
t.SUM_BLOCK,
t.SUM_NOT,
t.WHY_NAME,
t.WHY_NOT,
t.UPDATE_ST,
t.STAN_D,
t.TR_ID,
t.CF_ID,
t.BANK_REE,
t.BANK_PAY,
t.DATE_PAY,
t.SUM_PAY,
t.DEAL_END,
t.DEAL_NUM,
t.ACN_NUM,
t.ACN_SUM,
t.PROC_NUM,
t.PROC_SUM,
t.VIRT,
t.Z_NO_OLD,
t.D_NO_OLD,
t.TMP_BLCK,
t.DEAL_START,
t.PROC,
t.CURR,
t.ZOB,
t.SUM_FULL_K,
t.SUM_DEPO_K,
t.SUM_BLOCK_K,
t.SUM_NOT_K,
t.SUM_PAY_K,
t.ACN_SUM_K,
t.PROC_SUM_K
from fgv0.mop_205_d t
where t.bank_n = :doa_bankn
END OF STMT
PARSE #139773342180664:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=55685339,tim=1536156885176599
BINDS #139773342180664:
Bind#0
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=171 siz=32 off=0
kxsbbbfp=7f1f845a6d48 bln=32 avl=06 flg=05
value="303333"
EXEC #139773342180664:c=0,e=179,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=55685339,tim=1536156885177083
WAIT #139773342180664: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536156885177108
WAIT #139773342180664: nam='SQL*Net message from client' ela= 751 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536156885177876
WAIT #139773342180664: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536156885177944
WAIT #139773342180664: nam='SQL*Net message from client' ela= 5598 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536156885183646
*** 2018-09-05 17:14:46.636
WAIT #139773342180664: nam='asynch descriptor resize' ela= 3 outstanding #aio=0 current aio limit=128 new aio limit=513 obj#=296736 tim=1536156886636665

*** 2018-09-05 17:14:49.119
WAIT #139773342180664: nam='direct path write temp' ela= 13418 file number=201 first dba=1290112 block cnt=31 obj#=296736 tim=1536156889119069

------------------------------------------------
here the repetition of "WAIT # 139773342180664: nam = 'direct path write temp'..." and "WAIT # 139773342180664: nam = 'direct path read temp'..."
------------------------------------------------
*** 2018-09-05 17:18:06.766
WAIT #139773342180664: nam='SQL*Net more data to client' ela= 6 driver id=1413697536 #bytes=4034 p3=0 obj#=296736 tim=1536157087219564
FETCH #139773342180664:c=93226828,e=202035899,p=531309,cr=7058693,cu=0,mis=0,r=300,dep=0,og=1,plh=55685339,tim=1536157087219628
WAIT #139773342180664: nam='SQL*Net message from client' ela= 104663 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087333252
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087333329
WAIT #0: nam='SQL*Net message from client' ela= 437 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087333780
PARSE #139773342157624:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1536157087333894
WAIT #139773342157624: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087334018
WAIT #139773342157624: nam='SQL*Net message from client' ela= 598 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087334640
BINDS #139773342157624:
Bind#0
oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=01 csi=171 siz=32 off=0
kxsbbbfp=7f1f84670d40 bln=32 avl=19 flg=05
value="FGV0.MOP.AU_START_0"
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835d4c30 bln=22 avl=00 flg=05
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835d3f08 bln=22 avl=00 flg=05
Bind#3
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835d31e0 bln=22 avl=00 flg=05
Bind#4
oacdty=01 mxl=32(31) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=171 siz=32 off=0
kxsbbbfp=7f1f835d1f70 bln=31 avl=00 flg=05
Bind#5
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835d0e30 bln=22 avl=00 flg=05
Bind#6
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835cfcf0 bln=22 avl=00 flg=05
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835cebb0 bln=22 avl=00 flg=05
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835cda70 bln=22 avl=00 flg=05
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835cc930 bln=22 avl=00 flg=05
Bind#10
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835cb7f0 bln=22 avl=00 flg=05
Bind#11
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835ca6b0 bln=22 avl=00 flg=05
Bind#12
oacdty=02 mxl=22(22) mxlc=00 mal=150 scl=00 pre=00
oacflg=41 fl2=1000000 frm=01 csi=00 siz=24 off=0
kxsbbbfp=7f1f835c9570 bln=22 avl=00 flg=05
EXEC #139773342196928:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=415205717,tim=1536157087335212
FETCH #139773342196928:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=415205717,tim=1536157087335298
CLOSE #139773342196928:c=0,e=2,dep=1,type=3,tim=1536157087335332
BINDS #139773342193744:
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f1f8483f100 bln=22 avl=04 flg=09
value=296743
EXEC #139773342193744:c=1000,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1218588913,tim=1536157087335436
FETCH #139773342193744:c=0,e=21,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1218588913,tim=1536157087335472
CLOSE #139773342193744:c=0,e=1,dep=1,type=3,tim=1536157087335500
BINDS #139773342189208:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=56 off=0
kxsbbbfp=7f1f845e9660 bln=22 avl=04 flg=05
value=296743
Bind#1
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=171 siz=0 off=24
kxsbbbfp=7f1f845e9678 bln=32 avl=10 flg=01
value="AU_START_0"
EXEC #139773342189208:c=0,e=97,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2459890116,tim=1536157087335619
FETCH #139773342189208:c=0,e=78,p=0,cr=5,cu=0,mis=0,r=3,dep=1,og=1,plh=2459890116,tim=1536157087335729
CLOSE #139773342189208:c=0,e=2,dep=1,type=3,tim=1536157087335789
WAIT #139773342157624: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087335828
EXEC #139773342157624:c=1000,e=1906,p=0,cr=10,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1536157087335848
WAIT #139773342157624: nam='SQL*Net message from client' ela= 571 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087336456
CLOSE #139773342157624:c=0,e=18,dep=0,type=3,tim=1536157087336513
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087336532
WAIT #0: nam='SQL*Net message from client' ela= 648 driver id=1413697536 #bytes=1 p3=0 obj#=296736 tim=1536157087337192

Posted: **Thu 06 Sep 2018 12:06**

this is only part of the trace, I can not fully lay out

Posted: **Thu 06 Sep 2018 12:38**

Thank you for providing the information. Please provide the information about the structure of the tables used in the query that you are executing. Perhaps you will be able to create and send us a small test application that demonstrates the behavior of your code when working with an SQL query executed for an unacceptable time. This is convenient to do this via the e-support form (https://www.devart.com/company/contactform.html)

Posted: **Thu 06 Sep 2018 12:57**

The select is made from a view, which in turn is collected from 8 different tables. I can not provide the structure of all these tables, since this is a trade secret. At the request of the customer, we can not provide the structure to third parties. I can only say that the view is working quickly on other components (originally developed on DOA components in a 32-bit application). Now we are trying the 64-bit application and your components (without using the Oracle client)

Posted: **Thu 06 Sep 2018 13:02**

In this query all fields of the view are used, I can set type and dimension for each field, if this is important

Code: Select all

select t.CLIENT_ID,
t.FULL_NAME,
t.ID_CODE,
t.DOC_NUM,
t.DOC_DATE,
t.DOC_AUTH,
t.ADR_REGION,
t.ADR_DISTR,
t.ADR_CITY,
t.ADR_ADR,
t.BANK_N,
t.BANK_NAME,
t.ACN_N,
t.ACN_ID,
t.Z_NO,
t.D_NO,
t.SUM_FULL,
t.SUM_DEPO,
t.SUM_BLOCK,
t.SUM_NOT,
t.WHY_NAME,
t.WHY_NOT,
t.UPDATE_ST,
t.STAN_D,
t.TR_ID,
t.CF_ID,
t.BANK_REE,
t.BANK_PAY,
t.DATE_PAY,
t.SUM_PAY,
t.DEAL_END,
t.DEAL_NUM,
t.ACN_NUM,
t.ACN_SUM,
t.PROC_NUM,
t.PROC_SUM,
t.VIRT,
t.Z_NO_OLD,
t.D_NO_OLD,
t.TMP_BLCK,
t.DEAL_START,
t.PROC,
t.CURR,
t.ZOB,
t.SUM_FULL_K,
t.SUM_DEPO_K,
t.SUM_BLOCK_K,
t.SUM_NOT_K,
t.SUM_PAY_K,
t.ACN_SUM_K,
t.PROC_SUM_K
from fgv0.mop_205_d t 
where t.bank_n = :doa_bankn

Posted: **Thu 06 Sep 2018 13:48**

It's already getting interesting. In my query, I used a bind variable. But as soon as I replaced it directly with the value, as a result of execution it became several seconds. Why is that?

Posted: **Tue 11 Sep 2018 10:34**

The information about the type of the fields used in the provided query is important for us. Therefore, you can provide it
using the e-support form (https://www.devart.com/company/contactform.html) . To investigate the issue with using a bind variable, please obtain and compare query execution plans on the Oracle server when using a bind variable and explicitly indicating the parameter value

Devart Forums

Долго открывается OraQuery

Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery

Re: Долго открывается OraQuery