Trouble with Direct path using C++ API

[bangusi]

The problem is likely in my code not occilib. The code below is self contained. It runs successfully if I specify a small array size like 200 but fails if I use say 2000 or 4000. The errors I get are like: ORA-26093: input data column size (62) exceeds the maximum input size (60) Essentially I don't know how to handle situations where the Oracle client is unable to fit the input data into its buffer.

Create a target table CREATE TABLE Z_ALL_TAB_COLUMNS ( TABLE_NAME VARCHAR2(30), COLUMN_NAME VARCHAR2(30) )

void truncate_target(ocilib::Connection& dest_connection,
    const std::wstring& table_name,
    const std::wstring& owner)
{
    std::wstring trunc = L"TRUNCATE TABLE ";
    trunc.append(owner).append(L".").append(table_name);
    ocilib::Statement stmt(dest_connection);
    stmt.Execute(trunc);
}

void bulk_load()
{
    ocilib::Connection src_connection(L"xxxxxx/xxxdomain", L"xxxx", L"xxxxx");
    ocilib::Connection dest_connection(L"xxxxxx/xxxdomain", L"xxxx", L"xxxxx");

    std::vector<std::vector<std::wstring>> dataset;
    std::wstring sql = L"select table_name, column_name from all_tab_columns where rownum <= 4357";
    ocilib::Statement stmt(src_connection);
    stmt.SetFetchSize(100);
    stmt.Execute(sql);
    ocilib::Resultset rset = stmt.GetResultset();
    auto column_count = rset.GetColumnCount();

    std::wstring table_name = L"Z_ALL_TAB_COLUMNS";
    std::wstring owner = L"SALES";

    truncate_target(dest_connection, table_name, owner);

    ocilib::TypeInfo info(dest_connection, table_name, ocilib::TypeInfo::Table);
    const uint32_t array_size = 4000;
    ocilib::DirectPath dpath(info, column_count, array_size);

    dpath.SetBufferSize(64000);
    bool no_logging = true;

    dpath.SetNoLog(no_logging);
    bool disable_parallel_mode = false;
    dpath.SetParallel(disable_parallel_mode);

    dpath.SetColumn(1, L"TABLE_NAME", 30);
    dpath.SetColumn(2, L"COLUMN_NAME", 30);

    dpath.Prepare();

    uint32_t rowcount = 0;
    while (rset++)
    {
        std::vector<std::wstring> row;
        for (int column_index = 0; column_index < column_count; ++column_index)
        {
            auto pos = column_index + 1;
            auto val = rset.Get<std::wstring>(pos);
            row.push_back(val);
        }
        dataset.push_back(row);
        ++rowcount;
        if (rowcount == array_size)
        {
            dpath.Reset();

            uint32_t row_index = 1;
            for (const auto& dv : dataset)
            {
                uint32_t column_index = 1;
                for (const auto& rv : dv)
                {
                    dpath.SetEntry(row_index, column_index, rv, true);
                    ++column_index;
                }
                ++row_index;
            }

            auto state = dpath.Convert();
            if (state == ocilib::DirectPath::ResultError)
            {
                throw std::runtime_error("Conversion failed");
            }
            if (state == ocilib::DirectPath::ResultComplete)
            {
                state = dpath.Load();
            }
            if (state == ocilib::DirectPath::ResultFull)
            {
                throw std::runtime_error("Load failed. Buffer full");
            }
            rowcount = 0;
            dataset.clear();
        }
    }
    if (dataset.size() > 0)
    {
        dpath.SetCurrentRows(dataset.size());

        dpath.Reset();
        uint32_t row_index = 1;
        for (const auto& dv : dataset)
        {
            uint32_t column_index = 1;
            for (const auto& rv : dv)
            {
                dpath.SetEntry(row_index, column_index, rv, true);
                ++column_index;
            }
            ++row_index;
        }

        auto state = dpath.Convert();
        //  auto affected_rows = dpath.GetAffectedRows();

        if (state == ocilib::DirectPath::ResultError)
        {
            throw std::runtime_error("Conversion failed");
        }
        if (state == ocilib::DirectPath::ResultComplete)
        {
            state = dpath.Load();
        }
        if (state == ocilib::DirectPath::ResultFull)
        {
            throw std::runtime_error("Load failed. Buffer full");
        }
    }
    dpath.Finish();
}
int main()
{
    try
    {
        ocilib::Environment::Initialize();
        bulk_load();
    }
    catch (std::exception& p)
    {
        std::cerr << p.what() << std::endl;
    }
}

[vrogier]

Hi,

Just to check something. If you set the column size to 29 instead of 30, is it working fine? Do you have the same issue when running the code in ANSI build of ocilib?

Regards,

Vincent

[bangusi]

I am not getting this error ORA-26093 now but on this machine the setup is slightly different. Client is same 18.5 but the server is 12.c where as the other server is 18c. I will test again on that server later. One thing I have just discovered is that I can get around the Buffer Full issue by adjusting the parameter to DirectPath::SetBufferSize(). I am not sure if this is the idiomatic way to handle this. The C API example ( there is no C++ example afaik) seems to send data in a while loop.

[bangusi]

When I set column size to 29 instead of 30 the error became: ORA-26093: input data column size (60) exceeds the maximum input size (58)

[bangusi]

When I compile the code in ANSI build it works fine. Something else I discovered. If a table has a CLOB column. The WIDE build code fails with this error: ORA-01460: unimplemented or unreasonable conversion requested But it runs fine in ANSI build.

[vrogier]

Can you indicate what call gives you the error ORA-26093 ?

I've tried your code on my local 12c DB (the 2 connections points to the same DB) and cannot reproduce your issue Using small arrays works nicely (2000 is still good) Using an array of 4000, dpath.Convert() return ocilib::DirectPath::ResultFull as the whole content did not fit into the 64k buffer, which is the expected behavior. This I cannot reproduce the issue. Using huge array is not recommended anyway. Oracle recommend to uses same version between client and server when doinf directpath operations.

I will investigate the clob issue.

Regards,

Vincent

[vrogier]

When I define the column Z_ALL_TAB_COLUMNS.COLUMN_NAME as clob, 2 things happens:

1/ I had to reduce the array size (e.g. clob seems to take more space in oci direct path buffers) 2/ It works with clob in ANSI build but fails with the error ORA-01460 in WIDE build. Same as you. I will open an issue about it.

Vincent

[vrogier]

About the ORA-01460, I think it is an Orace bug. When CLOBs columns are involved, it works if the OCI environment is NOT initialized in UTF16 mode. Otherwise an error ORA-01460 is returned by the OCI function OCIDirPathColArrayToStream() when converting user host data array into internal format to be send to the server.

I will see if I can raise the issue to Oracle.

Best regards,

Vincent

[bangusi]

Can you indicate what call gives you the error ORA-26093 ?

I've tried your code on my local 12c DB (the 2 connections points to the same DB) and cannot reproduce your issue Using small arrays works nicely (2000 is still good) Using an array of 4000, dpath.Convert() return ocilib::DirectPath::ResultFull as the whole content did not fit into the 64k buffer, which is the expected behavior.

Vincent

With respect to ORA-26093. Adjusting the array size works as I reported and as you also found out. I was interested in how one can automatically handle buffer full errors (without stopping the program) at run time by adjusting the array size based on repeatedly calling GetMaxRows(). In the C API example that I was attempting to mimic seems like what is being done but I was not successful.

[vrogier]

you have 2 options:

1 - approximation for adjusting row count and buffer size.

By trying to compute max row size prior creating the direct path object by iterating over the table definition

    ocilib::TypeInfo info(dest_connection, table_name, ocilib::TypeInfo::Table);

    auto buffer_size = 64000ul;
    auto row_size = 0ul;
    auto col_count = info.GetColumnCount();
    for (auto i = 1ul; i <= col_count; i++)
    {
        row_size += info.GetColumn(i).GetSize();
    }

    auto array_size = buffer_size / row_size;

    ocilib::DirectPath dpath(info, column_count, buffer_size);

    dpath.SetBufferSize(buffer_size);

This works well for scalar types. For clobs and objects, you have to supply your own user column size. There is no other way to compute array size for far.

2 Adjust load behavior/logic when the buffer is full

When dpath.Convert() returns DirectPath::ResultFull, path.GetAffectedRows() returns you the number of rows that have fitted in the buffer. You can then load these rows and then convert/load agains rows that didn't fit in the first place. Thus, in case of buffer full, you might do the job twice for some rows.
Here is a modifed version of you code that handles buffer full cases :

const auto array_size = 4000ul;
const auto buffer_size = 64000ul;

auto make_row(ocilib::Resultset& rset)
{
    auto column_index = 0ul;
    auto column_count = rset.GetColumnCount();

    std::vector<ocilib::ostring> row;

    for (; column_index < column_count; ++column_index)
    {
        auto pos = column_index + 1;
        auto val = rset.Get<ocilib::ostring>(pos);
        row.push_back(val);
    }

    return row;
}

void load_rows(ocilib::DirectPath& dpath, std::vector<std::vector<ocilib::ostring>>& dataset)
{
    auto rows_toprocess { dataset.size() };
    auto rows_processed{ 0ul };

    dpath.Reset();

    while (rows_processed < rows_toprocess)
    {
        auto row_index = 1ul;
        auto it_start = std::begin(dataset);
        auto it_end = std::end(dataset);
        std::advance(it_start, rows_processed);

        while (it_start != it_end)
        {
            auto column_index = 1ul;
            for (const auto& rv : *it_start)
            {
                dpath.SetEntry(row_index, column_index, rv, true);
                ++column_index;
            }
            ++row_index;
            it_start++;
        }

        dpath.SetCurrentRows(row_index-1);

        auto state = dpath.Convert();

        if (state == ocilib::DirectPath::ResultError)
        {
            throw std::runtime_error("Conversion failed");
        }

        rows_processed = dpath.GetAffectedRows();
        state = dpath.Load();
    }

    dataset.clear();
}

void process_rows(ocilib::Resultset& rset, ocilib::DirectPath& dpath)
{
    dpath.Prepare();

    std::vector<std::vector<ocilib::ostring>> dataset;
    while (rset++)
    {
        dataset.push_back(make_row(rset));
        if (dataset.size() == array_size)
        {
            load_rows(dpath, dataset);
        }
    }

    if (!dataset.empty())
    {
        load_rows(dpath, dataset);
    }

    dpath.Finish();
}

void load_table()
{
    ocilib::Connection conn(OTEXT("db18c"), OTEXT("usr"), OTEXT("pwd"));
    ocilib::Statement stmt(conn);
    stmt.Execute(OTEXT("TRUNCATE TABLE Z_ALL_TAB_COLUMNS"));

    stmt.SetFetchSize(100);
    stmt.Execute(OTEXT("select table_name, column_name from all_tab_columns where rownum <= 4357"));

    ocilib::TypeInfo info(conn, OTEXT("Z_ALL_TAB_COLUMNS"), ocilib::TypeInfo::Table);
    ocilib::DirectPath dpath(info, info.GetColumnCount(), array_size);

    dpath.SetBufferSize(buffer_size);
    dpath.SetNoLog(false);
    dpath.SetParallel(false);

    dpath.SetColumn(1, OTEXT("TABLE_NAME"), 30);
    dpath.SetColumn(2, OTEXT("COLUMN_NAME"), 30);

    process_rows(stmt.GetResultset(), dpath);
}

int main()
{
    try
    {
        ocilib::Environment::Initialize();
        load_table();
    }
    catch (std::exception& p)
    {
        std::cerr << p.what() << std::endl;
    }
}

[bangusi]

This is great and I suspect the code would be a useful add to the C++ API demos.

As far as I am concerned this particular issue can be closed unless you want it remain open for the purpose of tracking ORA-01460

Partially unrelated to this I think there is room for performance improvement in the way ocilib handles clobs. Select from a table with a clob even if the actual clob size is tiny e.g 3 bytes it seems to be much slower using ocilib than should be. When I get a chance I will report with an example.

[vrogier]

about clobs:

• not designed for small contents.
• ocilib does not bring overhead on clob handling, when clobs are used as clobs. The only overhead is when using implicit conversion to string and using strings.

Could you give more info about "is slower thant should be" ?

[bangusi]

I hadn't measured this but now I have. I often switch between using ocilib and OTL depending on the task. Also I use SQL tool called Golden.

My perception has been that when it comes to querying a table that happens to have clobs ocilib tends to be slower than the other two. Often not just a bit slow but much slower.

Often I am not actually interested in the clob itself but when you do select * from tablethat has a clob the differences in response are obvious.

It is possible that the other libs are taking shortcuts somehow and am not comparing apples to apples. Also in the case of Golden have no idea what wrapper library it uses.

Here are two examples. On my machine the OTL version takes 12 secs and the ocilib takes 42 secs

Start with ocilib:

void select_clob(ocilib::Connection& db_connection)
{
    std::chrono::high_resolution_clock::time_point start = std::chrono::high_resolution_clock::now();
    std::wstring sql = L"select table_name, column_name, to_clob('***') as clob_val from all_tab_columns where rownum <= 20000";
    ocilib::Statement stmt(db_connection);
    stmt.Execute(sql);
    ocilib::Resultset rs = stmt.GetResultset();
    while (rs++)
    {
        auto table_name  = rs.Get<std::wstring>(1);
        auto column_name = rs.Get<std::wstring>(2);
        auto clob = rs.Get<std::wstring>(3);
    }
    std::chrono::high_resolution_clock::time_point finish = std::chrono::high_resolution_clock::now();
    auto duration = std::chrono::duration_cast<std::chrono::seconds>(finish - start).count();
    std::cout << "select_clob: elapsed : " << duration << "\n";
}
int main()
{
    try
    {
        ocilib::Environment::Initialize();
        ocilib::Connection db_connection(L"db", L"user", L"pwd");
        select_clob(db_connection);
    }
    catch (std::exception& p)
    {
        std::cerr << p.what() << std::endl;
    }
}

OTL It is a single header library from here http://otl.sourceforge.net/otlv4_h2.zip Don't forget to link with oci.lib

#define OTL_ORA10G_R2
#define OTL_UNICODE
using namespace std;
#define OTL_UNICODE_CHAR_TYPE wchar_t
#define OTL_UNICODE_STRING_TYPE wstring
#include "otlv4.h"
void select_clob(otl_connect& db_connection)
{
    std::chrono::high_resolution_clock::time_point start = std::chrono::high_resolution_clock::now();
    std::string sql = "select table_name, column_name, to_clob('***') as clob_val from all_tab_columns where rownum <= 20000";
    otl_stream stmt(10, sql.data(), db_connection);
    while (!stmt.eof())
    {
        std::wstring table_name, column_name, clob;
        stmt >> table_name >> column_name >> clob;
    }
    std::chrono::high_resolution_clock::time_point finish = std::chrono::high_resolution_clock::now();
    auto duration = std::chrono::duration_cast<std::chrono::seconds> (finish - start).count();
    std::cout << "Elapsed : " << duration << "\n";
}
int main()
{
    try
    {
        otl_connect::otl_initialize();
        otl_connect db_connection("user/host:port/db");
        select_clob(db_connection);
    }
    catch (otl_exception& p)
    {
        std::cerr << p.msg << endl;
    }
}

[vrogier]

Hi,

I have been doing some investigations and yes, in your example, ocilib is much slower when fetching clob as strings. When fetching clob as clob using OCI_GetClob(), OCILIB has no noticeable overhead over plain raw OCI code. When using OCILIB implicit conversion to string via OCI_GetString(), there are some indeed some overhead. There is some room for improvements (such as avoid 2 buffer lenght computation, not using calloc, avoiding calling OCILobGetLength(), ....). I will work on it asap. But there is also given overhead that is linked to some design choices and OCI lob implementation (for example OCILobGetLength() function that has a surprisingly high cost!) The lob object in OTL is not accessible outside the stream and is fully read once pipelined in the string object. It does not care about the Clob State. In OCILIB, you an retrieve many times the CLOB using OCI_GetCLob() or using OCI_GetString(). The Clob object has its own state and is independent from the resultset object (even if it lives in the scope of the resultset object) the OCI_GetString() once returned has to leave the lob object "untouched" and thus needs reset current position at the beginning of the CLOB. It allows call OCI_GetLob() like if nothing happened. Thus OCILIB is doing more stuff to let the clob "clean". I will work on it to reduce the overhead to make it not noticable if possible.

[vrogier]

Thus, I have been doing some change to remove the usage of OCILobGetLength() in OCI_GetString() which is the real source of the issue. Here are the results:

OTL streamlining small clobs as strings

select_clob_otl : 1371 select_clob_otl : 1276 select_clob_otl : 1367 select_clob_otl : 1335 select_clob_otl : 1389

OCILIB fetching small clob as strings

select_clob_ocilib_as_string: elapsed : 3347 select_clob_ocilib_as_string: elapsed : 3335 select_clob_ocilib_as_string: elapsed : 3425 select_clob_ocilib_as_string: elapsed : 3387 select_clob_ocilib_as_string: elapsed : 3396

OCILIB fetching small clob as clobs

select_clob_ocilib_as_clob: elapsed : 1231 select_clob_ocilib_as_clob: elapsed : 1237 select_clob_ocilib_as_clob: elapsed : 1717 select_clob_ocilib_as_clob: elapsed : 1810 select_clob_ocilib_as_clob: elapsed : 1816

OCILIB fetching clobs as strings (OCILIB refactoring NOT using OCILobGetLength at all)

select_clob_ocilib_as_string: elapsed : 1209 select_clob_ocilib_as_string: elapsed : 1209 select_clob_ocilib_as_string: elapsed : 1320 select_clob_ocilib_as_string: elapsed : 1291 select_clob_ocilib_as_string: elapsed : 1365

Anyway, CLOBs are meant to store possibly big data. in most use cases you do not fetch them as string. They are designed for being read/written by chunks, not manipulated into single strings. Thus, using OCI_CLob with ocilib and otl_lob_stream with OTL.

I will refine the changes I have made in order to possibly commit them in a 4.6.4 version

Vincent

[bangusi]

Great response. I am glad my issue will help make ocilib even better.

Going off topic: while you have my attention: BTW I think there are some ideas from OTL that you may want to add to ocilib in some form. One in particular is the ease with which he does array binding. You supply the array size to the constructor of the statement then you start streaming your data. Super easy and convenient. JDBC is more or less similar with the Batch interface

[anthony-tuininga]

@vrogier, in your example above regarding OCILOB fetching clobs as strings (and not using OCILobGetLength at all), are you using dynamic define? That significantly improves performance and is generally about double the performance of fetching the CLOBs and then turning them into strings. You can see that in ODPI-C here and here.

[vrogier]

@anthony-tuininga OCILIB is using dynamic fetch only for piecewise fetching of LONG and LONG RAW columns. OCILIB always fetches LOBs as LobLocators. It was a design choice to not give the ability for the application to decide the datatype to use for the underlying fetch calls. I chose to internally use the real type of the column and provide datatype conversions when possible and feasible. For convenience purposes only, OCILIB provides implicit conversion when user wants to retrieve a LOB column ( internally fetched as LobLocator) as a string (he can do both!). When I added these implicit Lob to string conversions, I did not made performance testing (as the primary use case for LOBs is chunk reads/writes). It was a mistake. I was naive to think that a simple call like OCILobGetLength() was a very expensive call. I will fix soon to provide no overhead :)

[anthony-tuininga]

Sounds good. :-)

FYI, if you don't fetch the LOB locator but instead use piecewise fetch you can get suubstantial performance benefits, as I noted. If you don't want to do that, you can at least avoid the overhead of getting the length of the LOB locator by prefetching the LOB length. You can see that in ODPI-C here. That eliminates one round trip. A round trip is still required for fetching the data, of course!

[vrogier]

@bangusi about array fetch interface, what is wrong with its support in OCILIB ? You can bind vectors to a statement in the C++ API. To be honest, I made some design mistake about 10 years when I added support for both PL/SQL table and Batch array binding within the same interface. I thought many times about fixing this but it would break backward compatibility which I have succeeded to maintain for the past 12 years ! Backward compatibility breaking changes is planned for version 5.0

[vrogier]

@anthony-tuininga You made my day :) If using OCI_ATTR_LOBPREFETCH_LENGTH allows to fix the overhead issue, I could avoid the changes I was working on that require extensive testing prior commit. Thanks a lot :)

[bangusi]

@bangusi about array fetch interface, what is wrong with its support in OCILIB ? You can bind >vectors to a statement in the C++ API.

I was thinking also batch inserts / updates not just fetches. The user of the library does not build the array, the library does based on user supplied array size.

[vrogier]

@bangusi I meant the array interface for batch inserts / update, not array fetch interface. I was a typo. You can bind your own arrays or ask OCILIB to internally allocate arrays and retrieve them. In the C++ API, you only can bind your own vector for batch insert/updates. The internal array allocation in only available in the C API. See here for binding C++ vector for batch insert/updates - Section Binding vectors See here for internal array allocation for C API - section Internal Array interface Example

[bangusi]

I am aware of OCLIB''s vector binding and used it successfully I just find the otl and JDBC design more convenient Think of use case where you are reading from database A and writing database B.

This is OTL

const uint32_t array_size = 200;
std::string sql = "select val from some table";
otl_stream stmt_in(array_size, sql.data(), dbconnection_A);
std::string ins = "insert into target values(:1<char[30]>)";
otl_stream stmt_out(array_size, ins.data(), dbconnection_B);
while (!stmt_in.eof())
{
    std::string val;
    stmt_in >> val;
    stmt_out << val;
}

With OCILIB inside the loop

strs.push_back(val); //populate the vector until array_size
stmt_out.ExecutePrepared() // call when I reach the vec size limit

clear my vector and repeat. When I reach end of resultset, if the vector is not empty, then have to call stmt_out.SetBindArraySize(array_size) before calling final stmt_out.ExecutePrepared()

when I callstmt_out.ExecutePrepared() I assume you have to copy my vector into your internal OCILIB arrays because obviously OCI cannot consume std::vector, correct? There is more copies of of the data in ocilib I think.

I have to copy into my vector and you have to copy from my vector. Not measured yet but I suspect the OTL version would be faster in this scenario.

This got me thinking. Have you considered creating an overload for Resultset.Get<ocilib::ostring> that returns a std::string_view

[vrogier]

Hi,

I have committed some changes that fixed clobs => string issues. You might check it out :)

About bulk arrays and C++ string vectors, whatever ocilib C++ or OTL, it requires copies as nor OTL nor OCILIB can directly bind them to OCI buffers.

About OCILIB C++ API, at the time I released it few years ago, C++11 was not very spread yet. Thus I made the choice to make it C++03 compliant.

At work, I use C++17 and C++20 and it is frustrating to maintain OCILIB as a C++03 library. Version 5.0 is planned to be C++17 based. But It requires quite some time ...

Vincent

[bangusi]

How do I get the change? Here https://github.com/vrogier/ocilib.git ?

You may be right about the relative performance of the array binds. I have't measured.

Unlike you, I don't actually use C++ in my day job. I mean it is not a requirement but do use it for non-production automation to make my work easier. SQL is all I need. C++ is just a hobby thing so I can play with whatever latest language features as I can't break anyone important code

[vrogier]

You have to build from source, branch develop-v4.6.4 : https://github.com/vrogier/ocilib/tree/develop-v4.6.4.

[bangusi]

Confirmed. Now performs similar to OTL. Huge improvement: from 40+ seconds to 12 seconds.

[bangusi]

The ANSI build still performs poorly. Four days ago I only tested the WIDE build today I tried the ANSI build and I am getting the old score of 40+ secs. You may want to revisit.

[vrogier]

Hi,

Are you sure you've also used a recompiled version for ANSI build ? I have just tested both builds (wide/ansi) and I got similar results (same performances between ansi/wide and between ocilib/otl)

[vrogier]

Charset type : ANSI

select_clob_otl : 1860
select_clob_otl : 1880
select_clob_otl : 1828
select_clob_otl : 1664
select_clob_otl : 1739

select_clob_ocilib_as_string: elapsed : 1745
select_clob_ocilib_as_string: elapsed : 1804
select_clob_ocilib_as_string: elapsed : 1827
select_clob_ocilib_as_string: elapsed : 1787
select_clob_ocilib_as_string: elapsed : 1876

Charset type : WIDE

select_clob_otl : 1481
select_clob_otl : 1489
select_clob_otl : 1611
select_clob_otl : 1560
select_clob_otl : 1642

select_clob_ocilib_as_string: elapsed : 1397
select_clob_ocilib_as_string: elapsed : 1407
select_clob_ocilib_as_string: elapsed : 1524
select_clob_ocilib_as_string: elapsed : 1475
select_clob_ocilib_as_string: elapsed : 1552

[bangusi]

Hi,

Are you sure you've also used a recompiled version for ANSI build ? I have just tested both builds (wide/ansi) and I got similar results (same performances between ansi/wide and between ocilib/otl)

Ignore me, I recompiled but had mistakenly cloned v4.6.3

[vrogier]

Issue solved