Sql-server – Oracle 12c (RDS) Questions from a SQL Server DBA

oraclesql server

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.

Best Answer

  • Index Maintenance: Generally speaking you don't need to rebuilt b-tree indexes because they are self-managed.

    When to rebuild Indexes?

    Index Internals- Rebuilding The Truth

  • Integrity Check: Oracle has DBVERIFY(dbv) command-line utility for physical data structure integrity check. But you can't use this tool on Amazon RDS as it doesn't provide shell access to database server.

    DBVERIFY: Offline Database Verification Utility

  • Statistics: The preferred method for gathering statistics in Oracle is to use the supplied automatic statistics gathering job. Or you can gather it manually using DBMS_STATS package. Details are given in the following white paper.

    Best Practices for Gathering Optimizer Statistics

    In SQL Server stats are automatically taken care of when a table grows by 500 rows + 20%, is there an Oracle equivalent?

    How often we should gather statistics is depend upon how much changes are being made in the tables which affect the query plan. In other words when statistics become stale we need to gather statistics. You can set stale percentage threshold for a table using STALE_PERCENT(default is 10%) parameter of SET_TABLE_PREFS procedure under DBMS_STATS package. For details:

    DBMS_STATS

  • Tablespaces: You can define the increment size using AUTOEXTEND ON NEXT clause either in CREATE TABLESPACE or ALTER TABLESPACE statements.

    And also is it not better practice to presize these to avoid any growth during production hours?

    Auto extending doesn't affect the performance noticeably as it doesn't happen frequently unless we specify very small size on NEXT clause.

    CREATE TABLESPACE

  • Important DBA and Performance Views: Some the of the important dynamic performance views are given below(v$ views).

    V$SESSION: Currently active sessions.

    V$SQL: SQL command recently executed and its related information.

    V$SGA: Statistics related to Oracle's shared memory called SGA.

    Dynamic Performance View Description

  • Key Metrics

    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.

    Generally speaking, these are same just Oracle has different terminologies to address them. For example, Page is equivalent to Data Block in Oracle and life expectancy of a block is determined by touch count algorithm similar to LRU(Least Frequently Used) algorithm.

    • Related Question