You Asked

Hello Tom ,

We have been facing a performance issue for some of the sql's in our system .

We have a procedure called "process_xyz" which is being called our batch system and Web application ( OLTP) system . It uses a global temporary table to stage the data.

When called from Batch process stage table is used to store almost 4 plus Million of records, and when called from Web Application it just uses 1 records .

Above leads to two different execution plan . When in the morning while batch process is run and users log in into Web application at same time . Web Application causes batch process to use the execution with lower cardinality ( 1) which causes batch process to run very slow

We have tried to use sql profile with larger cardinality plan but that causes Web application to work slow .

Would you have any suggestion on this ?

Thanks
Ashok Rathi

and Chris said...

So both processes run the same query, but the data volume is vastly different between them?

If so, a SQL profile is unlikely to help. It'll optimize the query assuming either lots or few rows. Which, as you've found, can make the other process slower.

Gathering session level stats on the temporary table beforehand (particularly for the batch process) may help. This enables the optimizer to see there's a big difference in the amount of data and - hopefully - adjust the plan accordingly. Creating two SQL Plan Baselines on the query - one when the GTT has lots of rows, one when it has few - may help too.

The most effective solution may be to ensure that the different processes run different queries. They only need to vary by one character and the optimizer will parse and optimize them as separate statements. This enables you to create one baseline for each to ensure each process has the plan you want.

Rating

(1 rating)

Comments

Ashok Rathi, March 07, 2025 - 2:19 pm UTC

Thanks Chris for the Prompt Reply ,

Yes, both run the same query and data volume is vastly different . Any insight how to optimize the query so it works on high or low volume data ?

Issue occur during the batch process. Batch has multiple query ( about 20 ) and majority of them is merge statements . After batch starts somehow web user comes in and change the plan . Since low volume plan is in the cache batch picks that up . Do you see if I increase the dynamic sampling to more than 4 it's going to help ?

I got that part of putting the space or comment but web user is calling same procedure from package . So the code is shared at the package level

Thx
ashok

March 10, 2025 - 3:45 am UTC

Statistics on global temp tables are private to each session, so as long as you gather/set stats in the session once you have populated the GTT, they will use that.

Session 1
==========

SQL> create global temporary table gtt
  2  ( x int, y int )
  3  on commit preserve rows;

Table created.

SQL>
SQL> insert into gtt
  2  select rownum ,rownum
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','GTT');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from gtt;

Execution Plan
----------------------------------------------------------
Plan hash value: 917624683

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|   976K|    59   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GTT  |   100K|   976K|    59   (2)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - Global temporary table session private statistics used

SQL> set autotrace off

Session 2
===========

SQL> insert into gtt
  2  select rownum ,rownum
  3  from dual
  4  connect by level <= 50;

50 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','GTT');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from gtt;

Execution Plan
----------------------------------------------------------
Plan hash value: 917624683

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    50 |   300 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GTT  |    50 |   300 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - Global temporary table session private statistics used

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

SQL Plan switches to less efficient plan

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs