Script to find elapsed time based on a SQL_ID from DBA_HIST Tables

Script to find execution time based on a SQL_ID

SET LINESIZE 120
SET PAGESIZE 1000
COL executions FOR 999,999,999
COL elapsed_time FOR 999,999,999,999
COL avg_ms FOR 999999.99
COL min_ms FOR 999999.99
COL max_ms FOR 999999.99
SELECT
TO_CHAR(TRUNC(snapshot.begin_interval_time + 1/8,'HH24'),'DD-MON-YYYY HH24:MI:SS') || ' EDT' SNAP_BEGIN,
sqlstat.instance_number,
SUM(sqlstat.executions_delta) executions,
MIN(sqlstat.elapsed_time_delta / sqlstat.executions_delta / 1000) min_ms,
MAX(sqlstat.elapsed_time_delta / sqlstat.executions_delta / 1000) max_ms,
SUM(sqlstat.elapsed_time_delta) / SUM(sqlstat.executions_delta) / 1000 avg_Ms
FROM
dba_hist_sqlstat sqlstat,
dba_hist_snapshot snapshot
WHERE
sqlstat.dbid = snapshot.dbid
AND sqlstat.instance_number = snapshot.instance_number
AND sqlstat.snap_id = snapshot.snap_id
AND sqlstat.sql_id = '&1'
AND snapshot.begin_interval_time >= TO_DATE('27-JUL-2009 21:00:00','DD-MON-YYYY HH24:MI:SS')
GROUP
BY TO_CHAR(TRUNC(snapshot.begin_interval_time + 1/8,'HH24'),'DD-MON-YYYY HH24:MI:SS') || ' EDT',
sqlstat.instance_number
ORDER
BY snap_begin
/

Hash-Partitioned Reverse-Key Index

Hash Paritioned global indexes provides higher throughput for applications with large numbers of concurrent insertions. In some applications, new insertions into the indexes are towards the right side of the index, usually this happends when you have an index column that is a monotonically increasing sequence number. Hash Partitioned indexes can improve performance in situations where a small number of nonpartitioned index's leaf blocks are experiencing high contention in an OLTP environment. Queries that use with an equality or IN operator in the WHERE clause can benefit significantly from a hash-partitioned global index.

For monotonically increasing key situatins, reverse keying the index will spread the activity, but only across the highest partition. Hash Partitioning will distribute the workload across all the index partitions, but still with contention at each index's right edge, reverse-key hash partitioning will not only distribute the activity across all the partitions, but also spread it within each partition.

Create Index CUSTOMER_IDX1 on CUSTOMER(ZIP_CODE)
global partition by hash(ZIP_CODE)
(partition P1 tablespace TBS_INDEX_1,
partition P2 tablespace TBS_INDEX_2,
partition P3 tablespace TBS_INDEX_3,
partition P4 tablespace TBS_INDEX_4)
REVERSE
/