search

GoogleCloudPlatform / database-assessment

Database Migration Assessment
https://googlecloudplatform.github.io/database-assessment/
Apache License 2.0
62 stars 35 forks source link

ER: Need workaround for DBA_HIST_SYSMETRIC_SUMMARY.standard_deviation issue #172

Closed wpuziewicz closed 2 years ago

wpuziewicz commented 2 years ago

Enhancement Request Details

As an end user I want to handle cases where DBA_HIST_SYSMETRIC_SUMMARY.standard_deviation returns NULL. There have been cases where DBA_HIST_SYSMETRIC_SUMMARY.standard_deviation displays as NULL. yet a numeric comparison has it between (0.9 repeating) and 1. In these cases, assign 0. Observed in 11.2.0.4 and 19.3.

Version

1.0.2 (Default)

What oracle database version are you seeing the problem on?

All

Which sql script version ?

2.0.4

Relevant log output

No response

wpuziewicz commented 2 years ago

To find such data in a database:

SELECT
    begin_time,
    metric_id,
    metric_name,
    metric_unit,
    num_interval,
    minval,
    average,
    maxval,
    standard_deviation,
    CASE
        WHEN s.standard_deviation >.9999999999999999999999999999999999999999
             AND s.standard_deviation < 1 THEN
            'STANDARD_DEVIATION is invalid'
        ELSE
            'Ok'
    END AS new_dev
FROM
    dba_hist_sysmetric_summary s
WHERE
        average = maxval
    AND average > minval
ORDER BY 
    10 DESC,
    standard_deviation,
    begin_time,
    metric_name;

The output will look like:


"BEGIN_TIME"                  "METRIC_ID"                   "METRIC_NAME"                 "METRIC_UNIT"                 "NUM_INTERVAL"                "MINVAL"                      "AVERAGE"                     "MAXVAL"                      "STANDARD_DEVIATION"          "NEW_DEV"                     
"02-SEP-22"                   "2119"                        "Session Limit %"             "% Sessions/Limit"            "10"                          "0"                           "8.67430441898527"            "8.67430441898527"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2105"                        "User Limit %"                "% Sessions/License_Limit"    "10"                          "0"                           "0.00000195577740714787"      "0.00000195577740714787"      ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2119"                        "Session Limit %"             "% Sessions/Limit"            "10"                          "0"                           "8.67430441898527"            "8.67430441898527"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2105"                        "User Limit %"                "% Sessions/License_Limit"    "10"                          "0"                           "0.00000195577740714787"      "0.00000195577740714787"      ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2119"                        "Session Limit %"             "% Sessions/Limit"            "10"                          "0"                           "9.00163666121113"            "9.00163666121113"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2114"                        "Shared Pool Free %"          "% Free/Total"                "10"                          "0"                           "47.2136461509849"            "47.2136461509849"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2114"                        "Shared Pool Free %"          "% Free/Total"                "10"                          "0"                           "44.6667500261991"            "44.6667500261991"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2114"                        "Shared Pool Free %"          "% Free/Total"                "10"                          "0"                           "44.6644603081469"            "44.6644603081469"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2114"                        "Shared Pool Free %"          "% Free/Total"                "10"                          "0"                           "44.6644603081469"            "44.6644603081469"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2114"                        "Shared Pool Free %"          "% Free/Total"                "10"                          "0"                           "44.6797730787745"            "44.6797730787745"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2114"                        "Shared Pool Free %"          "% Free/Total"                "10"                          "0"                           "44.6797730787745"            "44.6797730787745"            ""                            "STANDARD_DEVIATION is invalid"
"02-SEP-22"                   "2114"                        "Shared Pool Free %"          "% Free/Total"                "10"                          "0"                           "44.6797730787745"            "44.6797730787745"            ""                            "STANDARD_DEVIATION is invalid"```

Notice that STANDARD_DEVIATION displays as NULL, though it evaluates as greater than ( .9 repeating) and less than 1.
wpuziewicz commented 2 years ago

Extract code will be modified to identify these cases and use 0 for the standard deviation.