在puber上看到的幾道面試題列舉如下:

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秒