insert into "000".tbtcyang select * from dba_objects where rownum<=10000;
10000 rows created.
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 2273262418
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10000 | 2021K| 896 (5)| 00:00:06 |
| 1 | LOAD TABLE CONVENTIONAL | TBTCYANG | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | DBA_OBJECTS | 88487 | 17M| 896 (5)| 00:00:06 |
| 4 | UNION-ALL | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 31 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | HASH JOIN | | 108K| 12M| 893 (5)| 00:00:06 |
| 11 | TABLE ACCESS FULL | USER$ | 89 | 1513 | 2 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 108K| 11M| 888 (4)| 00:00:06 |
| 13 | INDEX FULL SCAN | I_USER2 | 89 | 2047 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | OBJ$ | 108K| 8824K| 884 (4)| 00:00:05 |
|* 15 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 9 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 29 | 3 (0)| 00:00:01 |
|* 18 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | LINK$ | 1 | 18 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10000)
5 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
6 - access("S"."OBJ#"=:B1)
8 - access("EO"."OBJ#"=:B1)
9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
"O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
"O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
"O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
"U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))
OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE
"O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND
"U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
)))
10 - access("O"."SPARE3"="U"."USER#")
12 - access("O"."OWNER#"="U"."USER#")
14 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
15 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
16 - access("I"."OBJ#"=:B1)
18 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
edition_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
edition_id')))
19 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
23 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
728 recursive calls
1958 db block gets
798 consistent gets
1 physical reads
1129628 redo size
1119 bytes sent via SQL*Net to client
1317 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
sys@SGERP5> alter table "000".tbtcyang nologging;
Table altered.
Elapsed: 00:00:00.01
sys@SGERP5> insert into "000".tbtcyang
2 select * from dba_objects where rownum<=10000;
10000 rows created.
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
Plan hash value: 2273262418
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10000 | 2021K| 896 (5)| 00:00:06 |
| 1 | LOAD TABLE CONVENTIONAL | TBTCYANG | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | DBA_OBJECTS | 88487 | 17M| 896 (5)| 00:00:06 |
| 4 | UNION-ALL | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 31 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | HASH JOIN | | 108K| 12M| 893 (5)| 00:00:06 |
| 11 | TABLE ACCESS FULL | USER$ | 89 | 1513 | 2 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 108K| 11M| 888 (4)| 00:00:06 |
| 13 | INDEX FULL SCAN | I_USER2 | 89 | 2047 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | OBJ$ | 108K| 8824K| 884 (4)| 00:00:05 |
|* 15 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 9 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 29 | 3 (0)| 00:00:01 |
|* 18 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | LINK$ | 1 | 18 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10000)
5 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
6 - access("S"."OBJ#"=:B1)
8 - access("EO"."OBJ#"=:B1)
9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
"O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
"O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
"O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
"U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))
OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE
"O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND
"U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
)))
10 - access("O"."SPARE3"="U"."USER#")
12 - access("O"."OWNER#"="U"."USER#")
14 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
15 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
16 - access("I"."OBJ#"=:B1)
18 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
edition_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
edition_id')))
19 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
23 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
225 recursive calls
1186 db block gets
623 consistent gets
0 physical reads
1080296 redo size
1127 bytes sent via SQL*Net to client
1317 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
10000 rows processed
sys@SGERP5> rollback;
Rollback complete.
Elapsed: 00:00:00.00
sys@SGERP5> select * from "000".tbtcyang;
10000 rows selected.
Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 3200461411
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12956 | 2619K| 60 (4)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TBTCYANG | 12956 | 2619K| 60 (4)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
29 recursive calls
1 db block gets
1068 consistent gets
0 physical reads
536 redo size 产生这个
1075281 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
sys@SGERP5> rollback;
Rollback complete.
Elapsed: 00:00:00.00
sys@SGERP5> select * from "000".tbtcyang;
10000 rows selected.
Elapsed: 00:00:00.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3200461411
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12956 | 2619K| 60 (4)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TBTCYANG | 12956 | 2619K| 60 (4)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
986 consistent gets
0 physical reads
0 redo size
1075281 bytes sent via SQL*Net to client
7846 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
sys@SGERP5> set autotrace traceonly;
sys@SGERP5> insert /*+ append */ into "000".tbtcyang select * from dba_objects where rownum<=10000;
10000 rows created.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
81 recursive calls
259 db block gets
367 consistent gets
0 physical reads
9944 redo size
1116 bytes sent via SQL*Net to client
1331 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed
sys@SGERP5> rollback;
Rollback complete.
Elapsed: 00:00:00.00
对比没有append:
sys@SGERP5> insert into "000".tbtcyang select * from dba_objects where rownum<=10000
2 ;
10000 rows created.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 2273262418
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10000 | 2021K| 896 (5)| 00:00:06 |
| 1 | LOAD TABLE CONVENTIONAL | TBTCYANG | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | DBA_OBJECTS | 88487 | 17M| 896 (5)| 00:00:06 |
| 4 | UNION-ALL | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 31 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 3 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | HASH JOIN | | 108K| 12M| 893 (5)| 00:00:06 |
| 11 | TABLE ACCESS FULL | USER$ | 89 | 1513 | 2 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 108K| 11M| 888 (4)| 00:00:06 |
| 13 | INDEX FULL SCAN | I_USER2 | 89 | 2047 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | OBJ$ | 108K| 8824K| 884 (4)| 00:00:05 |
|* 15 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 9 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 29 | 3 (0)| 00:00:01 |
|* 18 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | LINK$ | 1 | 18 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10000)
5 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
6 - access("S"."OBJ#"=:B1)
8 - access("EO"."OBJ#"=:B1)
9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
"O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
"O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
"O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
"U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))
OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE
"O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND
"U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
)))
10 - access("O"."SPARE3"="U"."USER#")
12 - access("O"."OWNER#"="U"."USER#")
14 - filter(BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)
15 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
16 - access("I"."OBJ#"=:B1)
18 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
edition_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_
edition_id')))
19 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
23 - access("L"."OWNER#"="U"."USER#")
Statistics
----------------------------------------------------------
15 recursive calls
997 db block gets
614 consistent gets
0 physical reads
1067528 redo size
1132 bytes sent via SQL*Net to client
1324 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10000 rows processed