Clint
3/15/2007 9:14:00 PM
Jim,
Thanks for your reply - I'll post this same thing to the appropriate Oracle
group.
Here's the full Oracle query being run:
select ACTIVITY.TRANS_SEQUENCE_NUMBER, ACCT_CENTER.ACCOUNT_DESCRIPTION,
ACCT_CENTER.ACCOUNT_TYPE
FROM T_ACTIVITY ACTIVITY inner join T_ACCT_CENTER ACCT_CENTER
on (ACTIVITY.GL_ACCOUNT_ID=ACCT_CENTER.GL_ACCOUNT_ID
AND ACTIVITY.BANK_NUMBER=ACCT_CENTER.BANK_NUMBER
AND ACTIVITY.BANK_CENTER=ACCT_CENTER.BANK_CENTER)
where ACCT_CENTER.BANK_NUMBER = '12300'
AND (ACTIVITY.date_entry >= trunc(sysdate)-1) AND (funds_type_code = 'je')
This query causes my problems. As far as times are concerned, here's what I
could get:
TOAD
first row: ~1:30
last row: ~1:35
SQL*Plus
first row: ~1:15
last row: ~1:20
SQL QA
first row: unknown - Oracle Enterprise Manager says the query completes
immediately
last row: unknown - the above query returns about 10,000 records.
Cancelling the query at 20 minutes into it only returned about 2000 records.
Now if I run this query:
select ACTIVITY.TRANS_SEQUENCE_NUMBER, ACCT_CENTER.ACCOUNT_DESCRIPTION,
ACCT_CENTER.ACCOUNT_TYPE
FROM T_ACTIVITY ACTIVITY inner join T_ACCT_CENTER ACCT_CENTER
on (ACTIVITY.GL_ACCOUNT_ID=ACCT_CENTER.GL_ACCOUNT_ID
AND ACTIVITY.BANK_CENTER=ACCT_CENTER.BANK_CENTER)
where ACCT_CENTER.BANK_NUMBER = '12300' and ACTIVITY.BANK_NUMBER = '12300'
AND (ACTIVITY.date_entry >= trunc(sysdate)-1) AND (funds_type_code = 'je')
All applications return the data within 1:30. Note the difference between
the two - On the joining statements, I removed "ACCT_CENTER.BANK_NUMBER =
ACTIVITY.BANK_NUMBER" from the ON clause and put the BANK_NUMBER = '12300'
for both of those tables in the WHERE clause.
What bothers me isn't so much that it's happening in that it is happening
inconsistently. If it was an Oracle problem, wouldn't this delay show itself
in TOAD and SQL*Plus as well? Or could it be more the way it connects via
ODBC?
As far as the explain plan is concerned, the plans are below:
WORKING:
4 SELECT STATEMENT
3 -HASH JOIN
1 --T_ACCT_CENTER TABLE ACCESS [FULL]
2 --T_ACTIVITY TABLE ACCESS [FULL]
NOT WORKING:
5 SELECT STATEMENT
4 -T_ACTIVITY TABLE ACCESS [BY INDEX ROWID]
3 --NESTED LOOPS
1 ---T_ACCT_CENTER TABLE ACCESS [FULL]
2 ---NI1_T_ACTIVITY INDEX [RANGE SCAN]
Mainly the difference is the bad one's looping yet also calling an index.
The working one doesn't.
thanks again!
Clint
"Jim Underwood" wrote:
> You may get better response from an Oracle group, as I expect at least part
> of the problem is with the Oracle optimizer, but my curiosity is peaked.
>
> First, let me make certain I understand the issue:
>
> When you run the stored procedure WITH and WITHOUT the bank number in the
> where clause, how long does it take before you retrieve the first row, and
> how long before you retrieve the last row of the data using each
> application:
> * Toad
> * SQL Plus
> * QA
>
> I think you you are saying that without the bank number you get your first
> and last row at around 8 minutes, in all the apps?
>
> Then, if you add the where clause back in, SQL Plus and Toad return the
> first row immediately, but take how long before the last row is returned?
>
> QA, with the bank number in the where clause, does not appear to return
> anything until you cancel the query (or wait until it finishes) even though
> Oracle is returning the data over time? Is this correct?
>
> I am curious how long it takes to return all of the data, with the bank
> number in the where clause, on each of the 3 apps. The time it takes to
> return the first row may not be relevant.
>
> Also, what explain plan do you get when running the query with and without
> the bank number in the where clause?
>
>
>
>
>
> "Clint" <cmueller@online.nospam> wrote in message
> news:063CE432-6C3F-49DD-9027-F5CB0E6025FE@microsoft.com...
> > Hello all,
> >
> > I'm having a problem with calling a stored procedure via openquery/linked
> > server between SQL Server 2000 and Oracle 9i. Here's an example of the
> query
> > that's being run. In reality, the Oracle SP is returning more rows:
> >
> > SQL Server:
> > select * from openquery(WAREHOUSE,
> > '{CALL
> > OraSchema.pkg_activity.GetActivityForDate(to_char(trunc(sysdate)-1,
> > ''mm/dd/yyyy''), {resultset 75000, o_Trans_Sequence_Number})}')
> >
> > Oracle 9i:
> > type tblTransSequenceNumber is table of
> > ACTIVITY.T_ACTIVITY.TRANS_SEQUENCE_NUMBER%TYPE index by binary_integer;
> >
> > PROCEDURE GetActivityForDate(
> > i_ActivityDate in varchar2,
> > o_Trans_Sequence_Number out tblTransSequenceNumber)
> > is
> > begin
> > select ACTIVITY.TRANS_SEQUENCE_NUMBER
> > bulk collect into
> > o_Trans_Sequence_Number
> > FROM OraSchema.T_ACTIVITY ACTIVITY
> > WHERE ACTIVITY.BANK_NUMBER = '12300' AND (ACTIVITY.date >=
> > to_date(i_ActivityDate, 'mm/dd/yyyy')) AND (type_code = 'je');
> > end GetGLActivityForDate;
> >
> > For referece, there is an index on ACTIVITY.BANK_NUMBER.
> >
> > The problem lies in the "ACTIVITY.BANK_NUMBER = '12300'" part of the WHERE
> > clause. If I remove that part, the query usually takes about 8 minutes to
> > begin returning data, and then the data is immediately returned. This is
> the
> > case from SQL Query Analyzer, TOAD for Oracle, or SQL*Plus.
> >
> > If I add that part of the WHERE clause back into the query, SQL*Plus and
> > TOAD will return data almost immediately, whereas SQL QA will return data
> > extremely slowly.
> >
> > To give an example, I put a packet sniffer between my SQL Server and the
> > Oracle server to see if anything was even happening between the two
> servers,
> > and running the Oracle procedure without the BANK_NUMBER clause had an
> idle
> > connection for ~8 minutes, then returned all the data at once. Putting
> > BANK_NUMBER back in would cause a small trickle of data over a period of
> > time. After waiting 40 minutes, I cancelled the procedure and realized
> only
> > half the data was returned.
> >
> > I'm really not sure what's going on here, but it seems to be focused
> around
> > the linked server. I can connect with Oracle tools and get the data back
> just
> > fine, but once I try going through SQL Server, everything falls apart.
> >
> > Does anyone have any ideas as to what might be happening here? Other
> > procedures return faster than this one, but only return one value and not
> a
> > resultset.
> >
> > Any tips would be greatly appreciated. Also, let me know if anything needs
> > clarification - I'm in a world of hurt if I can't get this figured out :)
> >
> > Clint
>
>
>