Question

Oracle SqL Query stuck time to time

  • 21 May 2020
  • 1 reply
  • 2349 views
A
Rank
Userlevel 3
Badge +4

Hi All,

I am trying to replicate customer issue internally with the customer db dump. Program sends series of sql statements to the database ( i.e inserts ,updates delete ).

If the program completed successfully, I just refresh the tables with customer db dump and started testing again. I have completed couple of test runs yesterday successfully.(it takes around 10 mins to complete all sql statements )

When I ran test today, it stuck on particular statement (even after 10 mins that particular sql statement didn’t get completed ), where as yesterdays run it took less than one minute to complete and sends back results to the program, on same data.

I am testing the program each time on exact the same data. No changes in database, data, and no time outs , no network issues, noted. No deadlocs or table blocks observed.

Why the oracle sometimes takes long time (some times no response at all ) on sql statements which sends results quickly on previous tests, exactly on same data)? 

Not cleared shared pool or buffer cache?

Only me working on this database , no others are connected, when I am testing. no scheduled jobs are running. Database server memory-60%.cpu-20%), is fine

Appreciate you help on to find cause for this.

1 reply

S
Rank
Userlevel 7
Badge +21

Hi,

Most common reason why this would happen can be attributed to the execution plans that Oracle CBO produces for specific SQL’s on specific times. When there is a reasonable change in the data and /or statistics, based on the available statistics on how skewed the data is, Oracle can produce sub optimal plans from time to time. The difference of the performance over night can pretty much be a result of automated stats gathering jobs that generally run in the night time window. 

A place to start with would be to query the Oracle V$ views to check the execution plan that is actually been used for your running SQL. Keep in mind that the explain plan that you see when you manually generate it (let’s say using PL/SQL developer) might not be the same  one that is actually been used. This is specially true if you are using literals for your parameters for testing. Oracle always generate the initial plans based on binds and hard coded literals that we use for testing can give you drastically different results.

v$sqlarea and v$sql_plan are two great places to get an understanding on what’s going on under the hood. The two queries below can help you determine if something is totally off.

 

SELECT *
FROM v$sqlarea
WHERE address = 
(
SELECT sql_address
FROM v$session
WHERE username='IFSAPP' -- user running the sql
AND status='ACTIVE'
AND SID = 277 -- this would be the session runnig the sql's
);

SELECT *
FROM v$sql_plan
WHERE sql_id ='7kc7vpbsukv5t'; -- this would be sql_id from previous query;

 

you can also use the following query to make sure that your SQL’s are actually not waiting on some weird wait event. Probably worth using this first before getting in to more details with the ones above.

 

SELECT SID,serial#,MODULE,program,event,blocking_session
FROM v$session
WHERE username='IFSAPP' -- user running the sql
AND status='ACTIVE';

 

Cheers,

Reply


Terms & ConditionsCookie settings