Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

slow select while a long write transaction is running

liviocFeb 15 2019 — edited Feb 18 2019

hi, i'm working on a 12c EE Extreme Perf Release 12.2.0.1.0 instance.

I've a table TABLE1 with about 1 millions of records and a procedure, executed on request by a job, that deletes and inserts about 300k of that table's records in about 10 minutes.

Than the SP executes about 10 millions of insert, for about other 90 minutes, in another table TABLE2 with a FK to TABLE1.

I don't have any explicit transaction management within the SP.

I have an external app that must frequently execute this query "select max(COLUMN1) from TABLE1",

the query usually is immediate but, when the SP is running, it has always his duration increased up to 30/60 seconds that isn't acceptable for my requirements.

Note: it hasn't any lock, i can execute it multiple times while SP is running and it uses the default read-commit behavior so it is correctly returning the value of the data without the SP updates.

I'm pretty new to oracle so i don't know if i can do something to make it faster, i cannot understand why it's impacted by a concurrent not-committed transaction.

thanks for your suggestions
This post has been answered by Jonathan Lewis on Feb 15 2019
Jump to Answer

Comments

BEDE

select distinct status,count(*) col1
,sum(
case
when reviwe_status='Y' then 1
else 0
end
) number_reviewed
from temp1
group by status
;

Frank Kulash

Hi,
SUM (CASE ...), as Bede showed, will work. You could also use COUNT (CASE ...), like this:

SELECT    status              -- or  LOWER (status) AS status
,         COUNT (*)  AS col1  -- or a more descriptive name, like number_total
,	  COUNT ( CASE
	  	      WHEN  review_status = 'Y'
		      THEN  'OK'
	  	  END
	  	)    AS number_reviewed
FROM      temp1
GROUP BY  status
ORDER BY  status  -- or whatever
;

If you really want to display 'open' and 'close' even though the table has 'Open' and 'Close', as you said, then you just need to change the first line.
In any event, you don't need "SELECT DISTINCT". The GROUP BY clause guarantees that status will be distinct in the output

1 - 2

Post Details

Added on Feb 15 2019
#transaction
9 comments
613 views