Checking most heavy queries every snapshot and system statistics

Most important system statiscts/performance overview:

SELECT begin_time,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN 'SQL Service Response
Time (secs)'
    WHEN 'Response Time Per Txn'
    THEN 'Response Time Per Txn
(secs)'
    ELSE METRIC_NAME
  END METRIC_NAME,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MINVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MINVAL / 100),2)
    ELSE MINVAL
  END MININUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MAXVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MAXVAL / 100),2)
    ELSE MAXVAL
  END MAXIMUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((AVERAGE / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((AVERAGE / 100),2)
    ELSE AVERAGE
  END AVERAGE
FROM SYS.DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME IN ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec','User Commits Per Sec')
AND BEGIN_TIME BETWEEN sysdate -1 and sysdate
ORDER BY 1;

Output:

Most heavy queries:

SELECT t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  t.PARSING_SCHEMA_NAME username,
  t.executions_delta exec_count,
  begin_interval_time,
  ROUND(SUM(t.elapsed_time_delta/1000000)/SUM(t.executions_delta),4) time_exec
FROM dba_hist_sqlstat t,
  dba_hist_snapshot s,
  DBA_HIST_SQLTEXT q
WHERE t.snap_id           = s.snap_id
AND t.dbid                = s.dbid
AND q.sql_id              =t.sql_id
AND t.instance_number     = s.instance_number
AND t.executions_delta   IS NOT NULL
AND t.elapsed_time_delta IS NOT NULL
AND t.executions_delta    > 0
AND s.begin_interval_time BETWEEN TRUNC(sysdate)-1 AND TRUNC(sysdate)
AND t.PARSING_SCHEMA_NAME NOT                  IN ('SYS','SYSTEM','DBSNMP') -- yesterday's stats
GROUP BY t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  PARSING_SCHEMA_NAME,
  t.executions_delta,
  s.begin_interval_time
ORDER BY 5,6 DESC;

Output:

 

SQL profile and invisible index

A few days ago we had a problem with the performance of several queries and we found that the problem is with queries which take different index than should be for some not bind variables.

So we decide set invisible parameter for this index. Before that I checked what queries use this index using v$sql_plan and I ran:

alter index index_owner.index_name INVISIBLE;

The list of queries that used this index there was one important query and after setting invisible for this index execution plan for this query start use different index and elapsed time increased almost 100 times. 

There was no possibility to add hint USE_INVISIBLE_INDEXES to this query directly, so I hat to use sql profile:

DECLARE
  clsql_text CLOB;
BEGIN
  SELECT sql_fulltext INTO clsql_text FROM v$sqlarea WHERE sql_id = '&SQL_ID';
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE( sql_text => clsql_text, profile => sqlprof_attr('USE_INVISIBLE_INDEXES'), name => 'PROFILE_&SQL_ID', force_match => TRUE );
END;
/