custom metrics via select.

[man-at-home]

Hi,

I would like to add a custom query (sql select read from config) that generates a gauge per row. I have some backend devs here who could like to formulate a query/view best.

will be my first go code :-) , i nearly got lost compiling the thing on windows (the oracle driver parts).

my idea would be like:

content, err := ioutil.ReadFile(*configFile)
err = yaml.Unmarshal(content, &config)

// ScrapeCustomSQL collects a custom query wiith name/value columns in its rows.
func ScrapeCustomSQL(db *sql.DB, ch chan<- prometheus.Metric, config.sqlQuery string) error 
..
 rows.Scan(&name, &value)

ch <- prometheus.MustNewConstMetric(
     prometheus.NewDesc(prometheus.BuildFQName(namespace, "custom", name),
        "Generic counter metric from sql in Oracle.", []string{}, nil),
    prometheus.GaugeValue,
    value,

[iamseth]

What sort of Oracle metrics are you looking to add? Perhaps it might be best to add everything into the binary instead of adding a config file.

[man-at-home]

That is the problem with the most expensive db with its own care-takers ...

I do not claim to understand its tuning, but those who do... want to collect metrics - and my intention is to get it into a monitoring/alarming solution.

I am pretty sure other dba would disagree about the metrics and have their own...

Perhaps I show part of the code, got it fresh from source control. Seems to be a mix as preaggregated and dynamic values...

So I fetched you code and got it working with an executed this first query in it (read by a config file) to get a list of gauge metrics:

select source as name, val as value 
from ( select currentSnap.snap_id, sessions, sessions_lc_req_wait , sessions_lc_holding, max_wait_time, max_loaded_versions,  processes,lc_locks_req_wait,lc_locks_held,
        max_children, cum_parse_calls  from v_monitor_snapshots currentSnap, (select max(snap_id) as msnap_id from v_monitor_snapshots) maxSnap 
       where currentSnap.snap_id = maxSnap.msnap_id and CURRENTSNAP.SNAP_TS > sysdate - 1 )  
UNPIVOT INCLUDE NULLS ( VAL FOR( SOURCE ) IN (snap_id AS 'snap_id', sessions as 'sessions', sessions_lc_req_wait as 'sessions_lc_req_wait' , 
                        sessions_lc_holding as 'sessions_lc_holding', max_wait_time as 'max_wait_time', max_loaded_versions as 'max_loaded_versions', 
                        processes AS 'oracle_processes', lc_locks_req_wait AS 'lc_locks_req_wait', lc_locks_held AS 'lc_locks_held', max_children AS 'max_children', 
                        cum_parse_calls AS 'cum_parse_calls' )) 
order by source"

but that works only on one database on earth (ours), since it is using this existing view, which is based again on some presampled data it seems...

 SELECT s.snap_id, s.snap_ts, s.startup_time,
        p.processes, p.sessions, p.sessions_active, p.sessions_lc_req_wait, sessions_lc_holding,
        p.lc_locks_req_wait, p.lc_locks_held,
        c.max_children,
        c.max_wait_time,
        c.max_parse_calls - LAG(c.max_parse_calls) OVER (ORDER BY c.snap_id) AS diff_parse_calls,
        c.max_parse_calls AS cum_parse_calls,
        c.max_loaded_versions,
        NVL(sql.captured_statements,0) AS captured_statements
   FROM moni_snapshots s,
        moni_processes p,
        (
            SELECT snap_id,
                   MAX(children) AS max_children,
                   ROUND( MAX(concurrency_wait_time)/1000 ) AS max_wait_time,
                   MAX(parse_calls) AS max_parse_calls,
                   COUNT(loaded_versions) AS max_loaded_versions
              FROM moni_childcursors c
             GROUP BY snap_id
        ) c,
        ( SELECT snap_id, COUNT(*) AS captured_statements FROM moni_lp_statements GROUP BY snap_id ) sql
  WHERE s.snap_id=c.snap_id
    AND s.snap_id=p.snap_id
    AND s.snap_id=sql.snap_id(+)
  ORDER BY s.snap_id DESC;

    INSERT INTO moni_snapshots (snap_id,snap_ts,startup_time)
       VALUES (v_snap_id,SYSDATE,(SELECT startup_time FROM v$instance));

  -- Prozess- und Session-Details einsammeln
  SELECT COUNT(*) INTO v_processes FROM v$process;
  SELECT COUNT(*) INTO v_sessions FROM v$session;
  SELECT COUNT(*) INTO v_sessions_active FROM v$session WHERE status='ACTIVE';
  SELECT COUNT(*) INTO v_sessions_lc_req_wait FROM (SELECT DISTINCT addr FROM sys.x_$kglpn WHERE kglpnmod = 0);
  SELECT COUNT(*) INTO v_sessions_lc_holding FROM (SELECT DISTINCT addr FROM sys.x_$kglpn WHERE kglpnmod <> 0);
  SELECT COUNT(*) INTO v_lc_locks_req_wait FROM sys.x_$kglpn WHERE kglpnmod = 0;
  SELECT COUNT(*) INTO v_lc_locks_held FROM sys.x_$kglpn WHERE kglpnmod <> 0;
  INSERT INTO moni_processes (snap_id,processes,sessions,sessions_active,sessions_lc_req_wait,sessions_lc_holding,
              lc_locks_req_wait, lc_locks_held)
       VALUES (
         v_snap_id,v_processes,v_sessions,v_sessions_active,v_sessions_lc_req_wait,v_sessions_lc_holding,
         v_lc_locks_req_wait, v_lc_locks_held
       );

  -- Child Cursor Details einsammeln
  FOR rec IN (
    SELECT COUNT(DISTINCT child_address) AS children, sql_id, MAX(sql_text) AS sql_text,
           SUM(concurrency_wait_time) AS concurrency_wait_time, SUM(parse_calls) AS parse_calls,
           SUM(executions) AS executions, SUM(loaded_versions) AS loaded_versions
      FROM v$sql
     WHERE (module = 'DDTek.Oracle.dll' OR LOWER(module) LIKE 'APP%')
     GROUP BY sql_id
    HAVING COUNT(DISTINCT child_address) >= i_min_children
     ORDER BY COUNT(DISTINCT child_address) DESC
  ) LOOP
    INSERT INTO moni_childcursors (snap_id,children,sql_id,concurrency_wait_time,parse_calls,executions,loaded_versions)
    VALUES (v_snap_id,rec.children,rec.sql_id,rec.concurrency_wait_time,rec.parse_calls,rec.executions,rec.loaded_versions);
    SELECT COUNT(*) INTO v_rows FROM moni_sql_statements WHERE sql_id = rec.sql_id;
    IF v_rows = 0 THEN
      INSERT INTO moni_sql_statements (sql_id,sql_text) VALUES (rec.sql_id,rec.sql_text);
    END IF;
  END LOOP;

  -- All done