I am transitioning into Oracle on RDS (should make it easier than Linux based!) from a SQL Server background and have a few queries;
- Index Maintenance. In SQL Server dependant on maintenance window I reorganise anything over x amount of rows with fragmentation > 10% but < 30%. Anything > 30% gets a rebuild. I can't seem to find any advice on this sort of thing in Oracle
- Integrity check. In SQL Server I do these often as possible, usually every 24 hours. Again struggling to find best practice for Oracle here. I've seen DBVERIFY mentioned but that's about it
- Statistics - I wont do routine maintenance on this (unless done via index rebuild) unless I find an issue. In SQL Server stats are automatically taken care of when a table grows by 500 rows + 20%, is there an Oracle equivalent?
- TABLESPACES - not so much of a maintenance issue, however I believe these are equivalent to databases files in SQL SErver which can grow in a similar fashion, I believe by "AutoExtend". However how do I set what percent or size a TABLESPACE should grow by? And also is it not better practice to presize these to avoid any growth during production hours?
- What are the best "DBA" and performance views to use? i.e. in SQL Server I will use sys.dm_exec_Requests or sp_who2 to see what is hitting the server, sys.dm_os_performance_counters to see performance counters and will log into the box to use perfmon for a graphic interface, what are the rough equivalents of this?
- On the above point, are the key metrics in Oracle the same as SQL Server, roughly? I.e. PLE, buffer hit cache, disk latency etc
Any other links or advice which will give me enough knowledge to "Keep the lights on" and know where to look if an issue arises would be greatly appreciated.