Saturday, May 17, 2014

Granting Developers Access to Dynamic Performance Views




CREATE ROLE perf_viewer;

GRANT SELECT ON V_$LOCK TO perf_viewer;
GRANT SELECT ON V_$LOCKED_OBJECT TO perf_viewer;
GRANT SELECT ON USER_OBJECT_USAGE TO perf_viewer;
GRANT SELECT ON V_$SESSION TO perf_viewer;
GRANT SELECT ON V_$SESSION_LONGOPS TO perf_viewer;
GRANT SELECT ON V_$SESSMETRIC TO perf_viewer;
GRANT SELECT ON V_$SESSTAT TO perf_viewer;
GRANT SELECT ON V_$SQL TO perf_viewer;
GRANT SELECT ON V_$SQLAREA TO perf_viewer;
GRANT SELECT ON V_$SQLSTATS TO perf_viewer;
GRANT SELECT ON V_$SQLTEXT TO perf_viewer;
GRANT SELECT ON V_$SQLTEXT_WITH_NEWLINES TO perf_viewer;
GRANT SELECT ON V_$SQL_PLAN TO perf_viewer;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS TO perf_viewer;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO perf_viewer;
GRANT SELECT ON V_$SQL_WORKAREA TO perf_viewer;
GRANT SELECT ON V_$STATNAME TO perf_viewer;
GRANT SELECT ON V_$SYSSTAT TO perf_viewer;
GRANT SELECT ON V_$TRANSACTION TO perf_viewer;

GRANT perf_viewer TO <<user name>>;



Monday, May 12, 2014

E-R Diagram for Sample University Database in My Pluralsight Course

I always like to have an E-R diagram handy whenever I am dealing with a database, and maybe some of you do as well.  If that is the case, there is the E-R diagram that I use in my Pluralsight course "Oracle Performance Tuning for Developers".  It should print on legal paper, so you shouldn't have too much trouble if you want a hard copy.

E-R Diagram for University Schema

Sunday, May 11, 2014

Finding Unused Indexes in Oracle

One area I think SQL Server really does a better job than Oracle is in the DMV's around indexes.  SQL Server makes it very easy to see what indexes it thinks are missing and also gives you the DMV dm_db_index_usage_stats which reports how often an index has been used and how often it has been updated.  Of course the point here is to find indexes that are not used and drop them and evaluate indexes that are only infrequently used and see if there is really a justification for those indexes to exist.

Oracle does make a lot more information available via AWR, but AWR requires you to have a diagnostics pack and tuning pack license.  So not everyone is going to have those.

We can use regular V$ views though to recover some of this functionality.  The following query uses the V$sql_plan table to find all the usages of an index, and then joins that information to the all_indexes view.  So we can see what indexes are getting used, but what statements and what indexes aren't getting used with this query.


 WITH index_usage AS
(
    SELECT pl.sql_id, pl.object_owner, pl.object_name, pl.operation,
            pl.options, count(1) as use_count
        FROM v$sql_plan pl
        WHERE pl.operation = 'INDEX'
        GROUP BY pl.sql_id, pl.object_owner, pl.object_name, pl.operation, pl.options
)
SELECT
        ix.table_owner, ix.table_name, ix.index_name, iu.operation,
        iu.options, ss.sql_text, ss.executions
    FROM all_indexes ix
    LEFT OUTER JOIN index_usage iu
             ON ix.owner = iu.object_owner
             AND ix.index_name = iu.object_name  
    LEFT OUTER JOIN v$sqlstats ss
        ON iu.sql_id = ss.sql_id
    WHERE ix.owner = '<<owner name>>'
    ORDER BY ix.table_name, ix.index_name;

Now this query is not perfect.  Since it is using v$sql_plan, you are limited to what statements are in the shared sql area right now.  But it does give you a start.  And you could always schedule this query to run on an hourly basis and dump those results to a table.  After a few days, you would have a pretty good idea of your index usage patterns.