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

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


Other related posts: