ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在puber上看到的几道面试题列举如下:

在puber上看到的几道面试题列举如下:

原创 Linux操作系统 作者:paulyibinyi 时间:2007-12-18 10:56:32 0 删除 编辑

1、如何知道数据库目前正在执行的事务有多少,以当前时间点来说

select * from v$transaction where addr in (select taddr from v$session  where status='active' )

2.如何知道数据库目前已结束,未提交的事务有多少,以当前时间点来说

select * from v$transaction where addr in (select taddr from v$session  where status='inactive' )

3、请列举出发生了锁的争用,但是系统不会HANG住,以报错退出的例子(请列举不少于三个)
a. ddl+ddl
b.ddl(非online)+dml
c. update全表+select * from table for update nowait;

5、update 无条件全表, select count(*) 无条件全表,请考虑是否有优化的余地

采用update 分批提交

select count(*)  在非空字段上加索引 ,走index fast full scan

要是不加的走全表扫描,速度慢
测试如下:

SQL> select count(*) from tb_game_bet_log;

  COUNT(*)
----------
  43319931

Elapsed: 00:01:23.00

SQL> alter table TB_GAME_BET_LOG
  2    add constraint pk_tb_game_bet_log primary key (ID);

Table altered.

Elapsed: 00:11:45.03
SQL> select count(*) from tb_game_bet_log;

  COUNT(*)
----------
  43319931

Elapsed: 00:00:13.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PK_TB_GAME_BET_LOG' (UNIQUE)
          (Cost=3 Card=43389931)

 

 

Statistics
----------------------------------------------------------
        361  recursive calls
          0  db block gets
     121020  consistent gets
     120707  physical reads
          0  redo size
        310  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=22524 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (FULL) OF 'TB_GAME_BET_LOG' (Cost=22524 C
          ard=43389931)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     581845  consistent gets
     367827  physical reads
          0  redo size
        310  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter table TB_GAME_BET_LOG
  2    add constraint pk_tb_game_bet_log primary key (ID);

Table altered.

Elapsed: 00:11:45.03
SQL> select count(*) from tb_game_bet_log;

  COUNT(*)
----------
  43319931

Elapsed: 00:00:13.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PK_TB_GAME_BET_LOG' (UNIQUE)
          (Cost=3 Card=43389931)

 

 

Statistics
----------------------------------------------------------
        361  recursive calls
          0  db block gets
     121020  consistent gets
     120707  physical reads
          0  redo size
        310  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

由1分多钟降到13秒

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-791/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
学习数据库

注册时间:2007-12-11

  • 博文量
    902
  • 访问量
    6543220