--************模拟使用index不能提高效率的情况
Insert Into t1 Select * From t1 --构建大表,百万数据以上
Select Count(*) From t1 --1265792
Alter Table t1 Move --整理表的存储
Select Min(object_id) From t1 --object_id 最小=2 有128个
Update t1 Set object_id = 9000 Where object_id > 2 --卡住不动了
Select * From v$flash_recovery_area_usage --查看归档日志的表空气使用情况 ARCHIVELOG 98.5%
Select * From v$session_wait Where wait_class <> 'Idle' --查看等待的情况
show parameter db_recovery --查看db_recovery_file_dest_size属性值 2G
Alter System Set db_recovery_file_dest_size=5G --属性值改为5G
/*
Select * From v$streams_transaction --stream流,用于多实例互相同步
golden gate 可以实现oracle到异构数据库的同步
*/
Select object_id,Count(*) From t1 Group By object_id --
--执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9889 | 49445 | | 327 (2)| 00:00:04 |
| 1 | HASH GROUP BY | | 9889 | 49445 | 1064K| 327 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T1 | 79112 | 386K| | 218 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
15798 consistent gets
9545 physical reads
0 redo size
516 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
http://tahiti.oracle.com --查看oracle相关文档
select * from v$session_longops --CPU运行超过6秒的动作
Insert Into t1 Select * From t1 --构建大表,百万数据以上
Select Count(*) From t1 --1265792
Alter Table t1 Move --整理表的存储
Select Min(object_id) From t1 --object_id 最小=2 有128个
Update t1 Set object_id = 9000 Where object_id > 2 --卡住不动了
Select * From v$flash_recovery_area_usage --查看归档日志的表空气使用情况 ARCHIVELOG 98.5%
Select * From v$session_wait Where wait_class <> 'Idle' --查看等待的情况
show parameter db_recovery --查看db_recovery_file_dest_size属性值 2G
Alter System Set db_recovery_file_dest_size=5G --属性值改为5G
/*
Select * From v$streams_transaction --stream流,用于多实例互相同步
golden gate 可以实现oracle到异构数据库的同步
*/
Select object_id,Count(*) From t1 Group By object_id --
--执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9889 | 49445 | | 327 (2)| 00:00:04 |
| 1 | HASH GROUP BY | | 9889 | 49445 | 1064K| 327 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T1 | 79112 | 386K| | 218 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
15798 consistent gets
9545 physical reads
0 redo size
516 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
http://tahiti.oracle.com --查看oracle相关文档
select * from v$session_longops --CPU运行超过6秒的动作
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15810196/viewspace-1130239/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15810196/viewspace-1130239/