Re: Session PGA memory max exceeded 2 GB and crashes.

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Wed, 20 Dec 2006 13:31:29 -0800 (PST)
Message-ID: <20061220213130.87519.qmail@web58703.mail.re1.yahoo.com>

Norman

See this AskTom thread - the number of executions of the user function depends on the SQL execution plan: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6978972926020.

I reproduced (on Oracle XE, using SQL Developer:

script

select * from v$version;

CREATE OR REPLACE FUNCTION TESTUSER.TEST_FUNCTION (param IN VARCHAR2)
RETURN NUMBER AS
BEGIN
counter_pkg.increment_counter;
RETURN 190;
END;
/

drop index th_amt_i;
begin counter_pkg.reset_counter; end;
/

select * from trade_history where amount > test_function('X');

begin

counter_pkg.print_counter('FTS access'); counter_pkg.reset_counter;
end;
/

select * from trade_history where amount > (select test_function('X') from dual); begin

counter_pkg.print_counter('FTS access with subquery'); counter_pkg.reset_counter;
end;
/

create index th_amt_i on trade_history(amount); select count(*) from trade_history;
select * from trade_history where amount > test_function('X'); begin

counter_pkg.print_counter('Index range scan'); counter_pkg.reset_counter;
end;
/

script output

BANNER

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

PL/SQL Release 10.2.0.1.0 - Production                           
CORE 10.2.0.1.0 Production                                         
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production          
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected

FUNCTION TESTUSER.TEST_FUNCTION Compiled. drop index th_amt_i succeeded.
anonymous block completed

TRADE_DATE                AMOUNT                 TICKER 
------------------------- ---------------------- ------ 
12-SEP-06                 199                    ORCL   
11-SEP-06                 198                    ORCL   
10-SEP-06                 197                    ORCL   
09-SEP-06                 196                    ORCL   
08-SEP-06                 195                    ORCL   
07-SEP-06                 194                    ORCL   
06-SEP-06                 193                    ORCL   
05-SEP-06                 192                    ORCL   
04-SEP-06                 191                    ORCL

9 rows selected

anonymous block completed
FTS access: execution count = 365

TRADE_DATE                AMOUNT                 TICKER 
------------------------- ---------------------- ------ 
12-SEP-06                 199                    ORCL   
11-SEP-06                 198                    ORCL   
10-SEP-06                 197                    ORCL   
09-SEP-06                 196                    ORCL   
08-SEP-06                 195                    ORCL   
07-SEP-06                 194                    ORCL   
06-SEP-06                 193                    ORCL   
05-SEP-06                 192                    ORCL   
04-SEP-06                 191                    ORCL

9 rows selected

anonymous block completed
FTS access with subquery: execution count = 1 create index succeeded.
COUNT(*)

365
1 rows selected

TRADE_DATE                AMOUNT                 TICKER 
------------------------- ---------------------- ------ 
04-SEP-06                 191                    ORCL   
05-SEP-06                 192                    ORCL   
06-SEP-06                 193                    ORCL   
07-SEP-06                 194                    ORCL   
08-SEP-06                 195                    ORCL   
09-SEP-06                 196                    ORCL   
10-SEP-06                 197                    ORCL   
11-SEP-06                 198                    ORCL   
12-SEP-06                 199                    ORCL

9 rows selected

anonymous block completed
Index range scan: execution count = 1

That still doesn't explain how your call stack was blown, of course...

Regards

Nigel

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

Received on Wed Dec 20 2006 - 15:31:29 CST