473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Minimizing The Buffer Gets

Please help me in tuning the below query
I have also attached the Explain paln and Statistics

SELECT CA.CA_ID_CONTR_ATTEST CA_CONTR_ATTEST FROM CDL_MA_ASSEGN_CONTR AC, CDL_MA_CONTROLLI CO, CDL_TR_CONTR_ATTEST CA
WHERE CA.CA_ID_CONTR_ASSEGN = AC.AC_ID_ASSEGN_CONTR AND AC.AC_ID_CONTROLLO = CO.CO_ID_CONTROLLO
AND AC.AC_CDR IN (SELECT COLUMN_VALUE FROM TABLE (cdl_pa_internal_interface.CDL_FN_GET_IN_LIST(1514 876 ) ) )
AND AC.AC_FINE_VALID IS NULL
AND CO.CO_TYPE IN (SELECT COLUMN_VALUE FROM TABLE (cdl_pa_internal_interface.CDL_FN_GET_IN_LIST( '10012 ,10010,10469,10011,10013') ) )
AND ( CA.CA_ULTIMA_DATA_ATTEST = Trunc(TO_DATE('28/02/2007','DD/MM/YYYY')) )





Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1131 Card=1 Bytes=
54)

1 0 HASH JOIN (SEMI) (Cost=1131 Card=1 Bytes=54)
2 1 NESTED LOOPS (Cost=1028 Card=1 Bytes=52)
3 2 HASH JOIN (SEMI) (Cost=1027 Card=1 Bytes=42)
4 3 NESTED LOOPS (Cost=924 Card=911 Bytes=36440)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'CDL_TR_CONTR_ATT
EST' (TABLE) (Cost=11 Card=911 Bytes=16398)

6 5 INDEX (RANGE SCAN) OF 'CDL_CA_ULTIMA_DATA_IN_100
3' (INDEX) (Cost=3 Card=911)

7 4 TABLE ACCESS (BY INDEX ROWID) OF 'CDL_MA_ASSEGN_CO
NTR' (TABLE) (Cost=1 Card=1 Bytes=22)

8 7 INDEX (UNIQUE SCAN) OF 'CDL_AC_ID_ASSEGN_CONTR_P
K_1001' (INDEX (UNIQUE)) (Cost=0 Card=1)

9 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'CDL_FN_GET_I
N_LIST' (PROCEDURE)

10 2 TABLE ACCESS (BY INDEX ROWID) OF 'CDL_MA_CONTROLLI' (T
ABLE) (Cost=1 Card=1 Bytes=10)

11 10 INDEX (UNIQUE SCAN) OF 'CDL_CO_ID_CONTROLLO_PK_1001'
(INDEX (UNIQUE)) (Cost=0 Card=1)

12 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'CDL_FN_GET_IN_LI
ST' (PROCEDURE)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40938 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
283 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
Mar 7 '07 #1
1 3811
Dave44
153 100+
what is the issue? is it taking too long?
is it maxing out the memory and doing some swapping?

you have no physical reads which is quite good. and you are hitting indexes which is good.

having 2 in lists is unfortunate but they are small in this query and they are the pickler fetches which is just a fancy way of saying an in memory structure we are treating as a table row source
.
it is doing a lot of in memory work which is what you want.
Mar 8 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: Ben Collingsworth | last post by:
Anyone have some efficient source code for implementing a ring buffer?
9
by: Sathyaish | last post by:
I noticed that gets() reads into the buffer even if the you've not allocated enough memory. For instance, if you do: char *str=(char*)malloc(sizeof(char)); printf("Enter something about...
2
by: Bill Sun | last post by:
Hi, I have a quetion about to refresh the ostringstream buffer: like this. ostringstream buffer; buffer << 245; // then the buffer.str() = "245"; ......
1
by: Jawahar | last post by:
All, I have an ASP.net application from which I allow users to run reports that are crystal reports and exported to PDF format, in addition there are other static documents that users can view in...
7
by: toton | last post by:
Hi, I want a circular buffer or queue like container (queue with array implementation). Moreover I want random access over the elements. And addition at tail and remove from head need to be low...
28
by: bwaichu | last post by:
Is it generally better to set-up a buffer (fixed sized array) and read and write to that buffer even if it is larger than what is being written to it? Or is it better to allocate memory and...
3
by: =?Utf-8?B?WFNsZXI=?= | last post by:
I have some code that works fine, except for the fact that it has a lot of embedded if statements. I don't think that this would be practice code, but I wanted to tap into the knowledge of this...
15
by: raashid bhatt | last post by:
#include <stdio.h> #include <string.h> #include <stdlib.h> void func(char *p) { char i; strcpy(i, p); }
4
by: AAaron123 | last post by:
trying to understand the below shown code. After this is run the browser opens a file-save dialog box for saving the file. I wonder how it knows I want the file saved? But more important, the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.