ODP.Net Stored Procedure performance issues on large datasets.

Question

Hey guys,

We are having some major performance issues with SELECT queries out one of our databases. Please see the simple procedure and associated code below.

In the code the ExecuteReader() method is executing in around 10 seconds on a query returning 30K records. Iterating the Reader takes 2 minutes (even when I am not pumping the data into any other object). 2 minutes for a 30k row data set is unacceptable for us as we are expecting datasets in the millions.

Is there anything here that stands out to any of you? Hoping that your experience with ODP.NET and PL/SQL might help out.

    create or replace PROCEDURE              TRACKING_FETCH (
                p_tracking_id             IN NUMBER,
                p_parent_id               IN NUMBER,
                p_media_id                IN NUMBER,
                p_custodian_id            IN NUMBER,
                p_return_cursor           OUT SYS_REFCURSOR)
AS
BEGIN
     OPEN p_return_cursor FOR
            SELECT 
                  * 
            FROM
                  tracking
            WHERE
                  (tracking_id = p_tracking_id OR p_tracking_id = 0)
            AND   (parent_id = p_parent_id OR p_parent_id = 0) 
            AND   (media_id = p_media_id OR p_media_id = 0)
            AND  (custodian_id = p_custodian_id OR p_custodian_id = 0);
END TRACKING_FETCH;

--

using (DataFactory command 
       = new DataFactory(dbConnection, 
                         DatabaseType.Oracle, 
                         CommandType.StoredProcedure, 
                         "TRACKING_FETCH"))
{
       command.AddInParameter("p_tracking_id", DbType.Int32, trackingid);
       command.AddInParameter("p_parent_id", DbType.Int32, parentid);
       command.AddInParameter("p_media_id", DbType.Int32, mediaid);
       command.AddInParameter("p_custodian_id", DbType.Int32, custodianid);
       using (var dr = command.ExecuteReader())
       {
              while (dr.Read())
              {
                  //Do Things...
              }
       }
}

Any guidance will be greatly appreciated.

Answer 1

Have you tried running an EXPLAIN PLAN on the stored procedure? I don't see any immediate problems with either your code or your stored procedure, but full table scans would seriously kill the execution time on your query. An explain plan would tell you if there are table scans, and then you can tune your query to speed it up.

Answer 2

Worth studying up on the Oracle Wait Interface. I'd suspect network latency is killing you. The procedure is returning a pointer to the result set. At some point in your loop I would guess you are fetching the rows (even if they are being dumped).

Checking v$sql would tell you how many fetches are being done and how many rows are processed. Divide one by the other and you'll see how many rows per fetch. If you are doing 1 row/fetch or even 10-20, that's thousands of network waits. You ideally want thousands of rows per fetch if you are going to be pulling back millions of records, though that may cost you in memory.

Depending on what you are doing with those millions of rows, it may be worth rethinking the architecture. For example, if they are being dumped to a file, then maybe generate the file on the DB server, zip it, move the file over the network, then unzip it.

Answer 3

It is not a problem of your odp.net program. The reason is in the SELECT. If the table contains lots of records it can be that the optimizer decides to run a full table scan, depending on your parameters. Check with explain plan how the statement runs. If you see nothing helpful. Try to trace the statement to see physical reads.