The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question.

Asked: November 20, 2007 - 7:32 pm UTC

Last updated: June 12, 2008 - 9:36 am UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

Good morning ,Tom
I have a questions about hint and dblink,I found:
a:insert /* + append */ into t select * from t@dblink,the hint works.
b:insert /* + append */ into t@dblink select * from t ,the hink will be ignored.
Could you give me a explain on this,and if I want B,how can I use hint here?

Another questions:
I know in 10G,there is a schedure called GATHER_DB_STATS in dba_scheduler_jobs,but I can not find when it run and what the internal is from dba_scheduler_jobs,could you give me a way to get it or does it run only when oracle think it should be run?
Thanks so much help form you !
Regards
Alan

and Tom said...

It is a documented restriction

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2191836
"A transaction containing a direct-path INSERT statement cannot be or become distributed."

In the following, we do not have a distributed transaction when we just insert /*+ append */ into local_table select * from remote@table; - we just have a single site transaction. An attempt to make it distributed fails:

ops$tkyte%ORA10GR2> create table t as select * from dual;

Table created.

ops$tkyte%ORA10GR2> create table t2 as select * from dual;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert /*+ APPEND */ into t select * from dual@ora10gr2@loopback;

1 row created.

ops$tkyte%ORA10GR2> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


ops$tkyte%ORA10GR2> insert into t2@ora10gr2@loopback select * from dual;
insert into t2@ora10gr2@loopback select * from dual
                                        *
ERROR at line 1:
ORA-12840: cannot access a remote table after parallel/insert direct load txn

if the table is on the remote site to begin with - that would make the transaction a distributed one by definition and that is not supported.

You should read:

http://docs.oracle.com/cd/B19306_01/server.102/b14211/autostat.htm#i35168
for automatic statistics. You can see the job status via:

ops$tkyte%ORA10GR2> select job_name, job_type, program_name, schedule_name, job_class
  2  from dba_scheduler_jobs
  3  where job_name = 'GATHER_STATS_JOB';

JOB_NAME                       JOB_TYPE
------------------------------ ----------------
PROGRAM_NAME
-------------------------------------------------------------------------------
SCHEDULE_NAME
-------------------------------------------------------------------------------
JOB_CLASS
------------------------------
GATHER_STATS_JOB
GATHER_STATS_PROG
MAINTENANCE_WINDOW_GROUP
AUTO_TASKS_JOB_CLASS


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select  PROGRAM_ACTION
  2  from dba_scheduler_programs
  3  where PROGRAM_NAME = 'GATHER_STATS_PROG';

PROGRAM_ACTION
-------------------------------------------------------------------------------
dbms_stats.gather_database_stats_job_proc

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select *
  2  from DBA_SCHEDULER_WINGROUP_MEMBERS
  3  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP';

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select window_name, repeat_interval, duration
  2  from dba_scheduler_windows
  3  where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW');

WINDOW_NAME
------------------------------
REPEAT_INTERVAL
-------------------------------------------------------------------------------
DURATION
---------------------------------------------------------------------------
WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00

WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00

and you can use the scheduler to change that if you wish.

Rating

(5 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

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

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

job execution status

Thanks a lot ,Mr. Tom!

Behaviour changed from 9i to 10g?

tkprof output

More to Explore

Hints

append hint with dblink

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

job execution status

Thanks a lot ,Mr. Tom!

Behaviour changed from 9i to 10g?

tkprof output

More to Explore

Hints