CODEWITHSUNDEEP
oracle-databaseoraclereports
snso
snso

Reputation: 329

Oracle report with long running SQL

I have a report generate using Oracle Report Builder. The report has 82 SQL queries. Almost every queries perform heavy calculation. I'm doing financial report that has double entry for accounting. Sometimes, when I generate the report, the entries is not tally. And sometimes it's good. It looks like it's not doing it in "transactional way". Because it seems the data is keep running while generating the report.

I'm curious how the report execute the SQL query? Is it one by one or the whole report? How can I debug or see what query is executing?

Upvotes: 0

Views: 6185

Answers (3)

Thomas
Thomas

Reputation: 366

maybe you could try the "longops"

SELECT s.sid, 
       s.serial#,          
       sl.target,  sl.OPNAME, sl.SQL_PLAN_OPERATION as OPERATION, sl.SQL_PLAN_OPTIONS as options,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/decode(sl.totalwork,0, decode(sl.sofar, 0,1), sl.totalwork  )*100, 2) progress_pct, s.INST_ID , s.machine
FROM   gv$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#(+)
and sl.elapsed_seconds(+) <> 0
ORDER BY   ROUND(sl.sofar/decode(sl.totalwork,0, decode(sl.sofar, 0,1), sl.totalwork  )*100, 2)

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17944

Oracle Reports will issue your 82 different queries, as needed, according to the relationships between them in your Oracle Reports data model.

By default, in Oracle, you only get read consistency within a single SQL statement -- and that is your problem.

For example, suppose you have query Q_ACCOUNTS, which lists your chart of accounts, and query Q_JOURNAL_ENTRIES, which summarizes the journal entries made to a given account. In your Oracle Reports data model, suppose Q_JOURNAL_ENTRIES is linked to Q_ACCOUNTS.

In this case, Oracle reports will run Q_ACCOUNTS and then run Q_JOURNAL_ENTRIES once for each account. And here is the key point: there is no read consistency between the multiple executions of Q_JOURNAL_ENTRIES (nor is there consistence with Q_ACCOUNTS, for that matter).

So, if an accounting entry is made to debit account A and credit account B, and that entry is made after Q_JOURNAL_ENTRIES has run for A and before it has run for B, your report will only include the credit to B. And, so, your report will not add up.

I have never done it, but you might try to run a SET TRANSACTION READ ONLY SQL command in your "Before Report" trigger. This can give you transaction-level read consistency, which is what you need, but it comes with limitations (mainly, you cannot perform any database writes, as the name implies).

Upvotes: 1

nitin.sharma0180
nitin.sharma0180

Reputation: 481

Try using below query to check the active SQl running

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece

Upvotes: 2

Related Questions