oracle insert 表空间,Oracle system系统表空间和数据表空间的insert性能测试

SQL> show user

USER is "ZHONGWC"

SQL>

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

Elapsed: 00:00:00.00

SQL> select username,default_tablespace from dba_users where username in('SYS','SYSTEM');

USERNAME      DEFAULT_TABLESPACE

------------------------------ ------------------------------

SYS          SYSTEM

SYSTEM      SYSTEM

Elapsed: 00:00:00.04

SQL>

SQL> create table t_users(id number) tablespace users;

Table created.

Elapsed: 00:00:00.07

SQL> create table t_system(id number) tablespace system;

Table created.

Elapsed: 00:00:00.01

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

T_SYSTEM                       SYSTEM

T_USERS                        USERS

Elapsed: 00:00:00.11

SQL>

SQL> select statistic#,name from v$statname where name='CPU used by this session';

STATISTIC# NAME

---------- ----------------------------------------------------------------

13 CPU used by this session

Elapsed: 00:00:00.00

SQL> select * from v$mystat where statistic#=13;

SID STATISTIC#      VALUE

---------- ---------- ----------

860         13         18

Elapsed: 00:00:00.01

SQL>

SQL> begin

2  for i in 1..200000 loop

3  insert into t_users values(i);

4  end loop;

5  end;

6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.87                                                    --执行时间4.87s

SQL> select * from v$mystat where statistic#=13;

SID STATISTIC#      VALUE

---------- ---------- ----------

860         13        482                                        --CUP消耗482-18=464

Elapsed: 00:00:00.00

SQL>

SQL>

SQL> begin

2  for i in 1..200000 loop

3  insert into t_system values(i);

4  end loop;

5  end;

6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.95                                                    --执行时间10.95s

SQL> select * from v$mystat where statistic#=13;

SID STATISTIC#      VALUE

---------- ---------- ----------

860         13       1546                                        --CUP消耗1546-482=1064

Elapsed: 00:00:00.01