4 kB read block about ddlfs

[usrecnik]

Probably, the issue here is that ddlfs does not (yet :) ) use prefetch (which is supported by OCI) and so OCI probably makes as many roundtrips to the database as there is rows of pl/sql source code in all_source table.

I did a quick strace out of curiosity and it gives me the following output (fd=4 is OCI database connection).

strace ddlfs -f -o ... 
...
write(4, "\0\25\0\0\6\0\0\0\0\0\3\5*\3\0\0\0\2\0\0\0", 21) = 21
read(4, "\0\325\0\0\6\0\0\0\0\0\6\1\2X\1\0\0\0\0\0\2\0\0\0\0\0\0\0\0\0\0\0"..., 8208) = 213

So it would seem that OCI is reading with max block size of 8208 bytes in this case. Although it usually reads a lot less than this buffer size (213 bytes for example). I took a quick look at OCI documentation and I didn't find any option for setting read block buffer size, although I would assume it doesn't matter - it knows the max size of a row (in bytes) and it knows how many rows it can/must prefetch, thus it should be able to calculate best buffer size.

How did you discover that it's reading 4kb blocks? Did you use the strace the same way I did? (I'm just curious if this buffer size depends on database/platform version or are we just looking at different numbers)

I'll look into implementing prefetch in any case...

[usrecnik]

Another thing came to mind: all_source has text column, which is defined as VARCHAR2(4000). The ~4k number might come from there :)

[pioro]

Hi,

I didn't use strace for my initial test. What I did I was observing a file in /tmp/ddlfs/xxxx
directory and this PL/SQL body files was growing by 4 kB increments. Then I looked into all_source and found out varchar2(4000) and send that question to you ;)

But then I did strace and I have seen similar entry as you did and seen different values.

Am I right with this assumption:

You are setting 4 kB buffor for text line

if (ora_stmt_define_i(o_stm, &o_def, 1, o_sl1, 4*1024*1024*sizeof(char), SQLT_STR, &o_sl1i)) {
    retval = EXIT_FAILURE;
    goto qry_object_all_source_cleanup;
}

and in the loop you are fetching row by row

while (ora_stmt_fetch(o_stm) == OCI_SUCCESS) {

and ora_stmt_fetch is using this

sword r = OCIStmtFetch2(
    stm, g_connection.err, 1, OCI_FETCH_NEXT,

0, OCI_DEFAULT);

so basically there is a round trip per row but I can't see why fwrite is doing 4 kB unless
OCI is doing optimization and actually trying to fill o_sl1 during a fetch.

I will run it again in my local lab with 10046 trace on Oracle to see what is going on.

Do you think it should be possible to fetch 100 rows per execution and put it into array of 4 kB strings ?

regards,
Marcin

[usrecnik]

yes, definitely, OCIStmtDefine (ora_stmt_define is ddlfs's wrapper around it) can also take array of char[4000]. This looks like a nice tutorial on this topic: http://www.sqlines.com/oracle/oci/array_fetch

I plan to implement this although I'm a bit busy these days. Maybe next week. I would also like to rewrite query_*.c files using macros defined in oracle.h (which I added with last commit) and make those macros use prefetch as discussed here.

[usrecnik]

https://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci04sql.htm#LNOCI16355

[usrecnik]

I added prefetch for 1000 rows and 1MB of memory in commit f836e29 . Number of round trips is considerably lower.

It probably won't have any or much effect on VIEW/ and TABLE/ sources though, because the source for those is stored in LONG columns which cannot be prefetched.

Without prefetch:

$ cat x/MYSCHEMA/PACKAGE_BODY/MYPKG.SQL | wc -l
661

# tcpdump -n 'host 123.123.123.123'
...
698 packets captured

With prefetch:

$ cat x/MYSCHEMA/PACKAGE_BODY/MYPKG.SQL | wc -l
661

# tcpdump -n 'host 123.123.123.123'
...
18 packets captured