Conference orarep::nomahs::dbintegrator_public_public

    
    Hi John,
    
    Can you post the partitioned view definition and explain output from a
    query (no need to let it run though).
    
    Thanks very much,
    
    Dan

    Dan,
    
    The HP view definition is the same as 1065.0.
    
    Basically, the application starts out by retrieving a list of dbkeys
    from a range of fiscal dates.
    
    SELECT DBKEY
    	FROM DETAIL
    	WHERE (FYR_FQTR_FWK BETWEEN 1996101 and 1996102)
    	and DTL_XPLOD_INDCR IN ('B', 'M', 'D', 'I');
    
    This part of the application works ok, and uses the HP view correctly,
    loading all the dbkeys into memory (can be over 3 million dbkeys). It
    only takes about 20 minutes to fetch and load all dbkeys in memory.
    
    The second part of this application cycles through each dbkey and uses
    dynamic sql to create a cursor and fetch the row.
    
    SELECT FIELDS,...
    	FROM DETAIL WHERE DBKEY = ???
    	LIMIT TO 1 ROW
    
    It then takes fields off of this returned row and does it's own mapping
    into reference tables in a separate database. (This was an inherited
    application that the author tried to write his own optimizer to do
    this type of joined selection)
    
    We are not sure why this worked so much faster under a single database
    structure in RDB V4.0, because if you ask me it is junk code that
    should never have been written just because they didn't like the way
    the RDB V4 optimizer was doing a zig-zag strategy. Unfortunately, we
    are not at the liberty to go and change this code, we are expected to
    go into production with the dbi solution next month.
    
    Anyway, we believe that the increase in dbkey size due to the HP view
    may have to do with slowing down this application, and the fact that
    it has to break apart the dbkey to do multiple dbkey selections to the
    underlying databases.
    
    I wish I could supply you more information at this time, but nobody
    here *really* understands this C code, it is very complex, which the
    author got his masters degree for writing. At the time, under V4.0
    of RDB, this code actually ran faster than the RDB optimizer.
    
    Thanks,
    	John
    

    
    Hi John,
    
    I just did some testing here that showed me that we are doing partition
    elimination on dbkey values, but I would like to make sure that you are
    getting the same thing.  This might be difficult for you to do (maybe
    you can't, or will have to spawn jobs or whatever), but if you can will 
    you try the following:
    
    before running the program 
    
    $define/job dbi_trace_flags reset_flags
    $define dbi_trace_output "file.ext"
    
    then run the program.  Once all the dbkeys have been collected then
    
    $define/job dbi_trace_flags "reset_flags sdi_brief explain"
    
    and let it run for a short while so that at least some of the values
    are fetched by dbkey.  If you could send us the trace flags then I can
    make sure we are only sending the dbkey values down to the right
    partition.
    
    BTW - the reason for the convolution is that I'm not sure how big a 9
    million + trace line trace file is but that is what would be generated
    if the tracing was on from the start.
    
    Thanks,
    
    dan
    

    Hi Dan,
    
    Just want you to know that I am planning on doing this test with a
    smaller set of records (a 1 week range of fiscal dates), I have just
    been fighting too many other fires at the moment to get away to do
    this.
    
    Thanks,
    	John

    
    Thanks John,
    
    That will be a doubly useful test.  I'm wondering whether there is an
    overhead factor from each of the cursor operations or whether the 4X
    (from 6 -> 24 hours) is a standard multiplier for whatever operation is
    beig done.  By subsetting the data we'll (hopefully) be able to answer
    that question.

    DAN,
    
    I Just copied the log to ORAREP::TEST_DBKEY.LOG
    
    
    Thanks,
    	John

Hi Dan,

I sent the log with DBKEY fetches, but while that remains a problem, I am
trying to rewrite that code to do a single dynamic cursor and fetch through
that instead. It has what I think is a DBI buffering problem.

When I define dbi_optimization_flags "Fast First" this is what happens...

I open and fetch through the cursor displaying a message every 100 rows fetched.

Open cursor takes about 10 seconds 
It whips through the first 300 records - less that 1 second
It stalls at 300 records fetched - for 12 seconds
It whips through the next 300 records - again less than 1 second
It stalls at 600 records fetched - for 22 seconds
It whips through the next 1100 records - 1 to 2 seconds
It stalls at 1700 records - for 1 minute
It whips through the next 6800 records - around 1000 recs/second
It stalls at 8500 records for 1.5 minutes
It whips through the next 600 records - around 1 second


It then cycles through this pattern of records/stalls until it reaches the end
of cursor. Then there is a final 1 minute stall after all records have been
fetched. The whole process of fetching through around 15000 records is
about 5 minutes.

With "Fast First" off, it stalls for the full 5 minutes just opening the cursor,
then proceeds to fetch with NO stalls.

No, the funny part. It depends on the amount of records you are going after
in the cursor. For instance, we increased the fiscal period in the select
statement for the cursor so now it is going after, say 60,000 records. This
time it opens the cursor and has a couple of short stalls at the beginning,
(I put in a display for every 5000 records on this one), then whips from
5000 up to around 55,000 - around 1000 records/second, then stalled for
around 1/2 hour before it was killed.


Thanks,
	John

    
    Hi John,
    
    
>I sent the log with DBKEY fetches, but while that remains a problem, I am
>trying to rewrite that code to do a single dynamic cursor and fetch through
>that instead. It has what I think is a DBI buffering problem.

    We haven't got the log mentioned above.
    
>When I define dbi_optimization_flags "Fast First" this is what happens...
>I open and fetch through the cursor displaying a message every 100 rows fetched.
>
>Open cursor takes about 10 seconds 
>It whips through the first 300 records - less that 1 second
>It stalls at 300 records fetched - for 12 seconds
>It whips through the next 300 records - again less than 1 second
>It stalls at 600 records fetched - for 22 seconds
>It whips through the next 1100 records - 1 to 2 seconds
>It stalls at 1700 records - for 1 minute
>It whips through the next 6800 records - around 1000 recs/second
>It stalls at 8500 records for 1.5 minutes
>It whips through the next 600 records - around 1 second

>It then cycles through this pattern of records/stalls until it reaches the end
>of cursor. Then there is a final 1 minute stall after all records have been
>fetched. The whole process of fetching through around 15000 records is
>about 5 minutes.

    Could you turn on some trace flags for us John and supply us with the
    trace?   We would like to see "explain,sdi_brief".  We aren't sure why
    the stalls are happening either - we should be just getting data and
    feeding it to your application.
    
>With "Fast First" off, it stalls for the full 5 minutes just opening the cursor,
>then proceeds to fetch with NO stalls.

    This would sound right - when you open a cursor with fast first off
    then all the data is fetched at open time - fetches from an application
    then go directly against DBI.  In this case though DBI will build a
    data collection, which might take some time.  In the previous case no
    collection should have been built which is one of the reasons why the
    stalls don't make sense to us.
    
>No, the funny part. It depends on the amount of records you are going after
>in the cursor. For instance, we increased the fiscal period in the select
>statement for the cursor so now it is going after, say 60,000 records. This
>time it opens the cursor and has a couple of short stalls at the beginning,
>(I put in a display for every 5000 records on this one), then whips from
>5000 up to around 55,000 - around 1000 records/second, then stalled for
>around 1/2 hour before it was killed.

    I assume based on the access pattern you are seeing that this is with
    fast first enabled.  I don't think DBI knows a priori how many records
    will be fetched, so I don't think this is related to the amount of data
    coming back.  Something doesn't seem right though and hopefully we'll
    get a clue from some tracing.
    
    Thanks John,
    
    Dan

    Hi Dan,
    
    John's out today, but we have been working closely together on this
    problem. I'll set up the DBI flags and send you the log.
    
    Thanks ,
    Geoff Westropp
    

    re: -2
    
    Dan,
    
    I had copied the log to ORAREP::TEST_DBKEY.LOG, it is quite large at
    around 2,000 blocks though.
    
    Was Geoff able to get you the correct dbi flags into a logfile?
    
    Thanks,
    	John

    
    Hi John,
    
    Yup, we actually discussed both of the problems documented here over
    the past week or so.The problem with the fetch by DBKEY of the 3
    million rows taking so much more time than Rdb I think at this point in
    time we are leaning towards believing a large chunk of the extra time
    is due to the extra pathlength in DBI required to open a cursor (dbi
    sets up context and does other processing for every cursor open), to
    fetch from the cursor by dbkey value (dbi looks at the dbkey value and
    does partition elimination based on the dbkey value), and to close the
    cursor (dbi has to tear down all the context stuff it had to set up for
    the cursor).  I asked Goeff to look into rewriting the code so it isn't
    doing 3 million open/fetch/closes.
    
    The other problem mentioned was relating to the stalls in DBI while
    doing a join between separate Rdb databases.  After looking at the
    explain and sdi traces I had Geoff use the logical
    dbi_query_index_join_degree (he set it to 50) and I think this improved
    performance significantly.  The use of the logical is documented in the
    Improving Performance section of the Product Family User's Guide.
    
    Best regards,
    
    Dan
    
    
    

    Hi Dan,
    
    There seems to be a little confusion here as to the last thing Geoff
    was doing before he left for vacation. He mentioned to my boss that
    you had told him he was opening the cursor for each record, but I
    believe that was for the dbkey fetches, which we already knew was
    happening. That is why we decided to go ahead and try to re-write the
    code to create one large cursor up front. But my boss thinks you may 
    have told him he was opening the cursor for each record still in the 
    new code which doesn't use dbkeys (where we had the stalls). Do you
    happen to recall any of these events?
    
    Thanks,
    	John

    Dan,
    
    From what I have figured out, there is really nothing we can do about
    the dbkey fetching, and your opinion is to re-write the code, right?
    If so, that's ok because we came to the same conclusion. :-)
    
    Now, for the new problem, it appears that the logical
    DBI_QUERY_INDEX_JOIN_DEGREE set to 50 has controlled the stalling
    patterns we were seeing, correct? Do you think that declaring the
    cursor as read only up front would also increase performance?
    
    Thanks again,
    
    John

    
    Hi John,
    
    
>    From what I have figured out, there is really nothing we can do about
>    the dbkey fetching, and your opinion is to re-write the code, right?
>    If so, that's ok because we came to the same conclusion. :-)
    
    Yes, that is accurate.  I understand the need to open/fetch/close (to
    reposition the cursor), but through DBI you are paying a price for
    those three calls for each of 3 million records (about 22
    milliseconds per record).
    
>    Now, for the new problem, it appears that the logical
>    DBI_QUERY_INDEX_JOIN_DEGREE set to 50 has controlled the stalling
>    patterns we were seeing, correct? 
    
    It is for the query you guys sent last week.  If you are going to be
    working with a lot more records from the query of type:
    
    SELECT BU_SEGMT_RT.BU_SEGMT_CD
        FROM   BU_SEGMT_RT T002, BU_RT T003
        WHERE  BU_RT.BU_CD = BU_SEGMT_RT.BU_CD
        AND    BU_RT.BU_CD = '819'
        OR     BU_RT.BU_CD = '187'
        OR     BU_RT.BU_CD = '800';
    
    (i.e. if that query statrs retuning a lot more records) you may want to
    kick the value up further.
    
    Hope this helps,
    
    dan

    
    Oh, and setting the transaction read only might help with performance
    but I'm not sure.
    
    Sorry,
    
    Dan

    Thanks Dan, that helps me understand things more clearly.
    
    John

>> Do you think that declaring the cursor as read only up
>> front would also increase performance?

I do not have the background that Dan does on your particular
problem, but in general it is always better to explicitly declare
a cursor (note I said cursor as opposed to transaction) read-only
if it really is just to be used for reading.  For non read-only
cursors SQL must always fetch a dbkey so as to be prepared
to process any CURRENT OF CURSOR update requests.  In read
only cursors, there are no updates, thus no need to fetch the
dbkey.  When the underlying database is Rdb, the cost of fetching
a dbkey is probably small, but there is a cost.  When the
underlying database is something other then Rdb, the cost
will tend to be greater, particularly depending on the dbkey
mode and/or specifics of the dbkey.

Bottom line: Where ever it makes sense to do so, explicitly
declare cursors as read-only.