SAP - 9.2.0.7 - and bind variables

From: Robert Pegram <pegramrg_at_yahoo.com>
Date: Wed, 16 Nov 2005 05:33:13 -0800 (PST)
Message-ID: <20051116133313.68323.qmail@web50801.mail.yahoo.com>

After upgrading the test database from 9.2.0.5 to 9.2.0.7 (on AIX 5200-06), we are experiencing very slow performance. I was able to isolate one of the queries. In a nutshell, it looks like SAP is declaring the bind variables as char, and Oracle is handling them differently in 9.2.0.7 (i.e. using an inefficient index). The database is a copy of production and stats are up to date. I was able to create a test case (below), which I forwarded to Oracle. If I define the bind variables as varchar2 instead of char, the efficient path is taken. Anyone else seen this behavior?

Summary of test cases (details below)
Test case 1:
  9.2.0.5 (char bind variables - index HRP1001~2) Test case 2:
  9.2.0.7 (char bind variables - index HRP1001~0) Test case 3:
  9.2.0.7 (varchar2 bind variables - index HRP1001~2)

Rob Pegram
Oracle DBA
Duke University

TEST Case 1 - ran against 9.2.0.5 database:

alter session set events '10046 trace name context forever, level 12';

variable v1 char(3);
variable v2 char(2);
variable v3 char(10);
variable v4 char(4);
variable v5 char(1);
variable v6 char(8);
variable v7 char(8);

exec :v1 := 'XXX';
exec :v2 := 'XX';
exec :v3 := 'X XXXXXXXX';
exec :v4 := 'XXX';
exec :v5 := 'X';

exec :v6 := 'XXXXXXXX';
exec :v7 := 'XXXXXXXX';

SELECT * FROM sapr3.HRP1001
WHERE MANDT = :v1

AND PLVAR = :v2
AND OTJID = :v3
AND SUBTY = :v4
AND ISTAT = :v5

AND BEGDA <= :v6
AND ENDDA >= :v7
/

alter session set events '10046 trace name context off';

Explain Plan from trace file:

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=5130 op='TABLE ACCESS BY INDEX ROWID HRP1001 '
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=5133 op='INDEX RANGE SCAN HRP1001~2 '

TEST Case 2 - ran against 9.2.0.7 database:

alter session set events '10046 trace name context forever, level 12';

variable v1 char(3);
variable v2 char(2);
variable v3 char(10);
variable v4 char(4);
variable v5 char(1);
variable v6 char(8);
variable v7 char(8);

exec :v1 := 'XXX';
exec :v2 := 'XX';
exec :v3 := 'X XXXXXXXX';
exec :v4 := 'XXXX';
exec :v5 := 'X';

exec :v6 := 'XXXXXXXX';
exec :v7 := 'XXXXXXXX';

SELECT * FROM sapr3.HRP1001
WHERE MANDT = :v1

AND PLVAR = :v2
AND OTJID = :v3
AND SUBTY = :v4
AND ISTAT = :v5

AND BEGDA <= :v6
AND ENDDA >= :v7
/

alter session set events '10046 trace name context off';

Explain Plan from trace file:

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=5130 op='TABLE ACCESS BY INDEX ROWID HRP1001 '
STAT #1 id=2 cnt=1354668 pid=1 pos=1 obj=5131 op='INDEX RANGE SCAN HRP1001~0 '

TEST Case 3 - ran against 9.2.0.7 database:

alter session set events '10046 trace name context forever, level 12';

variable v1 varchar2(3);
variable v2 varchar2(2);
variable v3 varchar2(10);
variable v4 varchar2(4);
variable v5 varchar2(1);
variable v6 varchar2(8);
variable v7 varchar2(8);

exec :v1 := 'XXX';
exec :v2 := 'XX';
exec :v3 := 'X XXXXXXXX';
exec :v4 := 'XXXX';
exec :v5 := 'X';

exec :v6 := 'XXXXXXXX';
exec :v7 := 'XXXXXXXX';

SELECT * FROM sapr3.HRP1001
WHERE MANDT = :v1

AND PLVAR = :v2
AND OTJID = :v3
AND SUBTY = :v4
AND ISTAT = :v5

AND BEGDA <= :v6
AND ENDDA >= :v7
/

alter session set events '10046 trace name context off';

Explain Plan from trace file:

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=5130 op='TABLE ACCESS BY INDEX ROWID HRP1001 '
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=5133 op='INDEX RANGE SCAN HRP1001~2 '

Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 16 2005 - 07:35:19 CST