search

iamseth / oracledb_exporter

Prometheus Oracle database exporter.
MIT License
479 stars 236 forks source link

Incorrect Tablespace values #431

Closed img0043 closed 5 months ago

img0043 commented 5 months ago

Oracle_exporter metrics are working, but it seems that something is wrong with them. because dashboards panels display incorrect information: Annotation 2024-04-08 134437 Annotation 2024-04-08 134634

Query: 100*(1-oracledb_tablespace_free{instance="$host"}/oracledb_tablespace_bytes{instance="$host"})

Metric values: # TYPE oracledb_tablespace_free gauge oracledb_tablespace_free{tablespace="SPACE_DATA_D",type="PERMANENT"} 3.4233761792e+10 oracledb_tablespace_free{tablespace="SPACE_DATA_LX",type="PERMANENT"} 3.411959808e+10 oracledb_tablespace_free{tablespace="SPACE_DATA_M",type="PERMANENT"} 3.4358673408e+10 oracledb_tablespace_free{tablespace="SPACE_DATA_TS",type="PERMANENT"} 1.9180404736e+10 oracledb_tablespace_free{tablespace="SPACE_INDEX_D",type="PERMANENT"} 3.4231402496e+10 oracledb_tablespace_free{tablespace="SPACE_INDEX_LX",type="PERMANENT"} 3.4166521856e+10 oracledb_tablespace_free{tablespace="SPACE_INDEX_M",type="PERMANENT"} 3.4358673408e+10 oracledb_tablespace_free{tablespace="SPACE_INDEX_TS",type="PERMANENT"} 2.120671232e+10 oracledb_tablespace_free{tablespace="SYSAUX",type="PERMANENT"} 3.2852721664e+10 oracledb_tablespace_free{tablespace="SYSTEM",type="PERMANENT"} 3.3443069952e+10 oracledb_tablespace_free{tablespace="TEMP",type="TEMPORARY"} 3.4359721984e+10 oracledb_tablespace_free{tablespace="UNDOTBS1",type="UNDO"} 3.4352119808e+10 oracledb_tablespace_free{tablespace="USERS",type="PERMANENT"} 3.4357952512e+10 oracledb_tablespace_free{tablespace="X3DCOMMENT_TS",type="PERMANENT"} 3.4351202304e+10 oracledb_tablespace_free{tablespace="X3DDASHADMIN_TS",type="PERMANENT"} 3.4347008e+10 oracledb_tablespace_free{tablespace="X3DNOTIF_TS",type="PERMANENT"} 3.4349432832e+10 oracledb_tablespace_free{tablespace="X3DPASSADMIN_TS",type="PERMANENT"} 3.4175762432e+10 oracledb_tablespace_free{tablespace="X3DPASSTOKENS_TS",type="PERMANENT"} 3.3854046208e+10 oracledb_tablespace_free{tablespace="X3DSWYM_MEDIA_TS",type="PERMANENT"} 3.4349498368e+10 oracledb_tablespace_free{tablespace="X3DSWYM_SOCIAL_TS",type="PERMANENT"} 3.4343600128e+10 oracledb_tablespace_free{tablespace="X3DSWYM_WIDGET_TS",type="PERMANENT"} 3.4358673408e+10

# TYPE oracledb_tablespace_bytes gauge oracledb_tablespace_bytes{tablespace="SPACE_DATA_D",type="PERMANENT"} 1.25960192e+08 oracledb_tablespace_bytes{tablespace="SPACE_DATA_LX",type="PERMANENT"} 2.40123904e+08 oracledb_tablespace_bytes{tablespace="SPACE_DATA_M",type="PERMANENT"} 1.048576e+06 oracledb_tablespace_bytes{tablespace="SPACE_DATA_TS",type="PERMANENT"} 1.5179317248e+10 oracledb_tablespace_bytes{tablespace="SPACE_INDEX_D",type="PERMANENT"} 1.28319488e+08 oracledb_tablespace_bytes{tablespace="SPACE_INDEX_LX",type="PERMANENT"} 1.93200128e+08 oracledb_tablespace_bytes{tablespace="SPACE_INDEX_M",type="PERMANENT"} 1.048576e+06 oracledb_tablespace_bytes{tablespace="SPACE_INDEX_TS",type="PERMANENT"} 1.3153009664e+10 oracledb_tablespace_bytes{tablespace="SYSAUX",type="PERMANENT"} 1.50700032e+09 oracledb_tablespace_bytes{tablespace="SYSTEM",type="PERMANENT"} 9.16652032e+08 oracledb_tablespace_bytes{tablespace="TEMP",type="TEMPORARY"} 0 oracledb_tablespace_bytes{tablespace="UNDOTBS1",type="UNDO"} 7.602176e+06 oracledb_tablespace_bytes{tablespace="USERS",type="PERMANENT"} 1.769472e+06 oracledb_tablespace_bytes{tablespace="X3DCOMMENT_TS",type="PERMANENT"} 8.51968e+06 oracledb_tablespace_bytes{tablespace="X3DDASHADMIN_TS",type="PERMANENT"} 1.2713984e+07 oracledb_tablespace_bytes{tablespace="X3DNOTIF_TS",type="PERMANENT"} 1.0289152e+07 oracledb_tablespace_bytes{tablespace="X3DPASSADMIN_TS",type="PERMANENT"} 1.83959552e+08 oracledb_tablespace_bytes{tablespace="X3DPASSTOKENS_TS",type="PERMANENT"} 5.05675776e+08 oracledb_tablespace_bytes{tablespace="X3DSWYM_MEDIA_TS",type="PERMANENT"} 1.0223616e+07 oracledb_tablespace_bytes{tablespace="X3DSWYM_SOCIAL_TS",type="PERMANENT"} 1.6121856e+07 oracledb_tablespace_bytes{tablespace="X3DSWYM_WIDGET_TS",type="PERMANENT"} 1.048576e+06

What could be the problem? Has anyone encountered it?

Yannig commented 5 months ago

What is the version you are using? Could you try to run this sql instruction:

SELECT
    dt.tablespace_name as tablespace,
    dt.contents as type,
    dt.block_size * dtum.used_space as bytes,
    dt.block_size * dtum.tablespace_size as max_bytes,
    dt.block_size * (dtum.tablespace_size - dtum.used_space) as free,
    dtum.used_percent
    FROM  dba_tablespace_usage_metrics dtum, dba_tablespaces dt
    WHERE dtum.tablespace_name = dt.tablespace_name
    ORDER by tablespace
aneeraj commented 5 months ago

We had similar issue after upgrading from 0.2.9 to 0.5.2. Changing oracledb_tablespace_bytes to oracledb_tablespace_max_bytes provided the correct values.

100*(1-oracledb_tablespace_free{instance="$host"}/oracledb_tablespace_max_bytes{instance="$host"})

You may also use the metric: oracledb_tablespace_used_percent to get the used percentage.

img0043 commented 5 months ago

@aneeraj That's what me need! Thank you so much, for some reason I didn't find a description about changing metrics anywhere.