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秒