job execution status
Pierre Forstmann, November 21, 2007 - 2:10 pm UTC
To get job execution status, you need to query DBA_SCHEDULER_JOB_LOG:
SQL> select log_date, status from dba_scheduler_job_log where job_name = 'GATHER_STATS_JOB' order by log_date desc ;
LOG_DATE
---------------------------------------------------------------------------
STATUS
------------------------------
04/11/07 20:09:06,792651 +01:00
SUCCEEDED
Contrary to DBMS_JOB, I have noted that when a DBMS_SCHEDULER job fails, nothing is written in the alert.log.
Thanks a lot ,Mr. Tom!
A reader, November 21, 2007 - 7:43 pm UTC
Behaviour changed from 9i to 10g?
tijmen, June 09, 2008 - 10:16 am UTC
Tom,
Do you know if the behaviour regarding this changed from 9.2(.0.8) to 10.2(0.4)?
We have an application that pushes data from source to destination:
insert /*+ APPEND */ into remote_table@dblink
select * from local_table;
This uses direct-path in 9i, but not in 10g (with bad performance as a result).
The Oracle Administrators Guide states that "a distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database."
Following that, a transaction containing just the statement mentioned above would not necessarily be distributed (because data in only one database is updated).
According to you, such a transaction is distributed "by definition".
So, I'm wondering: what is the correct definition for "distributed transaction"?
June 09, 2008 - 1:59 pm UTC
are you sure,
I run this in 9i and 10g and get the same results
o no direct path first time
o direct path 2nd time (not using dblink)
o direct path 3rd time using dblink to retrieve FROM
define db=ora9ir2
define db=ora10gr2
drop table t2 cascade constraints;
drop table t1 cascade constraints;
create table t1 as select * from all_users;
create table t2 as select * from t1 where 1=0;
insert /*+ append */ into t2@&db.@loopback select * from t1 where rownum = 1;
select * from t2;
select * from t2@&db.@loopback;
commit;
insert /*+ append */ into t2 select * from t1 where rownum = 1;
select * from t2;
select * from t2@&db.@loopback;
commit;
insert /*+ append */ into t2 select * from t1@&db.@loopback where rownum = 1;
select * from t2;
select * from t2@&db.@loopback;
commit;
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
ops$tkyte%ORA9IR2> set echo on
ops$tkyte%ORA9IR2> edit test
ops$tkyte%ORA9IR2> @test
ops$tkyte%ORA9IR2> define db=ora9ir2
ops$tkyte%ORA9IR2> rem define db=ora10gr2
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> drop table t2 cascade constraints;
Table dropped.
ops$tkyte%ORA9IR2> drop table t1 cascade constraints;
Table dropped.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table t1 as select * from all_users;
Table created.
ops$tkyte%ORA9IR2> create table t2 as select * from t1 where 1=0;
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert /*+ append */ into t2@&db.@loopback select * from t1 where rownum = 1;
old 1: insert /*+ append */ into t2@&db.@loopback select * from t1 where rownum = 1
new 1: insert /*+ append */ into t2@ora9ir2@loopback select * from t1 where rownum = 1
1 row created.
ops$tkyte%ORA9IR2> select * from t2;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
USER2 88 16-APR-08
ops$tkyte%ORA9IR2> select * from t2@&db.@loopback;
old 1: select * from t2@&db.@loopback
new 1: select * from t2@ora9ir2@loopback
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
USER2 88 16-APR-08
ops$tkyte%ORA9IR2> commit;
Commit complete.
ops$tkyte%ORA9IR2> insert /*+ append */ into t2 select * from t1 where rownum = 1;
1 row created.
ops$tkyte%ORA9IR2> select * from t2;
select * from t2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
ops$tkyte%ORA9IR2> select * from t2@&db.@loopback;
old 1: select * from t2@&db.@loopback
new 1: select * from t2@ora9ir2@loopback
select * from t2@ora9ir2@loopback
*
ERROR at line 1:
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ops$tkyte%ORA9IR2> commit;
Commit complete.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert /*+ append */ into t2 select * from t1@&db.@loopback where rownum = 1;
old 1: insert /*+ append */ into t2 select * from t1@&db.@loopback where rownum = 1
new 1: insert /*+ append */ into t2 select * from t1@ora9ir2@loopback where rownum = 1
1 row created.
ops$tkyte%ORA9IR2> select * from t2;
select * from t2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
ops$tkyte%ORA9IR2> select * from t2@&db.@loopback;
old 1: select * from t2@&db.@loopback
new 1: select * from t2@ora9ir2@loopback
select * from t2@ora9ir2@loopback
*
ERROR at line 1:
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ops$tkyte%ORA9IR2> commit;
Commit complete.
A reader, June 10, 2008 - 10:34 am UTC
Thanks for the good example.
Indeed, on 9i I can use select after the insert, so apparently no direct-path is used there. I looked at the long runtime in 10g, noticed it didn't use direct-path, and just assumed from the performance in 9i that it did use direct-path there. Yeah, assume, I know.
I'm encountering the following situation, transferring (in this particular case) about 1.7mln records from source to destination, where the destination table has a primary key plus 3 disabled foreign keys (with indexes), and is truncated before starting the insert:
(1) 9i:src> insert /*+ APPEND */ into dst_table@dst select * from src_table;
Runtime: 2 minutes
(2) 9i:dst> insert /*+ APPEND */ into dst_table select * from src_table@src;
Runtime: 4 minutes
(3) 10g:src> insert /*+ APPEND */ into dst_table@dst select * from src_table;
Runtime: 20 minutes
(4) 10g:dst> insert /*+ APPEND */ into dst_table select * from src_table@src;
Runtime: 4 minutes
So, (2) and (4) use direct path, and perform roughly the same in 9i and 10g. (1) and (3) don't use direct path (despite the hint), which is surprisingly faster in 9i, but a lot worse in 10g.
We have several similar tables in the application that are loaded in the same way, and they all show the same behaviour, so in total, the runtime increases from ~40min in 9i to ~500min in 10g.
For now, it looks like the load processing has to be changed (pull instead of push, using direct-path), but I am still wondering if there's something I'm overlooking.
June 10, 2008 - 11:56 am UTC
trace the 9i and 10g with waits included and tkprof it. See what is different between the two.
tkprof output
tijmen, June 12, 2008 - 8:44 am UTC
The difference is in sequential reads:
On 9i:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 76.21 120.54 2356 140043 14438975 1697443
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 76.21 120.54 2356 140043 14438975 1697443
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1697443 REMOTE
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 18334 0.00 0.04
SQL*Net message from client 18334 0.05 4.38
SQL*Net more data from client 55809 0.05 5.08
db file sequential read 2356 0.16 21.70
log buffer space 1027 0.14 14.54
latch free 17 0.01 0.03
log file switch completion 21 0.09 0.40
log file sync 16 0.04 0.31
free buffer waits 4 0.46 1.13
write complete waits 1 0.13 0.13
On 10g:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 120.24 2217.03 193849 141262 14443514 1692166
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 120.24 2217.04 193849 141262 14443514 1692166
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1692166 REMOTE PRS_FCT_ALL_TRANSITIONS (cr=0 pr=0 pw=0 time=22000126 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 625 0.00 0.00
SQL*Net message from client 625 0.16 2.89
SQL*Net more data from client 63822 0.12 5.33
db file sequential read 193797 2.22 2052.17
log buffer space 1136 0.97 33.52
undo segment extension 1 0.05 0.05
latch free 1 0.00 0.00
log file switch completion 14 0.23 1.41
latch: cache buffers chains 5 0.03 0.06
latch: object queue header operation 4 0.01 0.02
In 10g, I can see from the object id's in the tracefile that the sequential reads are on all 4 indexes (Primary Key: 84318 seq reads, Foreign keys: 9923, 24385, 75164). As the execution progresses, the number of sequential reads increases.
June 12, 2008 - 9:36 am UTC
since they both do the same amount of work (logical IO), I'll assume the plans are the same.
Then, ask yourself "why is the data cached in 9i but not 10g - what is different about these two boxes that so much of the data would be in the cache for 9i but not so for 10g"
Perhaps the SGA sizes are radically different.
Perhaps the 9i box is "warm", used by lots of people and they did the IO for us.
The only difference I see here is that 10g has to do more physical IO and that accounts for the runtime differences. I cannot tell you why 10g is doing more - that you have to answer using your knowledge of what could be different between these two