SQLd360 with the AWR Warehouse

Mauro decided to give me another challenge-  run SQLd360 against the AWR Warehouse and let him know how it does straight “out of the box”.  It’s a simpler installation process than SQLTXPLAIN, you simply unzip and run, (no installation of any packages…)

I chose a SQL_ID from one of the source databases loaded and ran it for a 31 day history:

SQL> @sqld360.sql 8ymbm9h6ndphq T 31

I saw a few errors scroll by on the screen as the process to collect the data and zip it up ran.

There isn’t the same log file to see on successful runs to view parameters, but the SQL is very straight forward and right in the SQL directory, so easy enough to see where the DBID is populated as part of the sqld360_0b_pre.sql:

-- get dbid
COL sqld360_dbid NEW_V sqld360_dbid;
SELECT TRIM(TO_CHAR(dbid)) sqld360_dbid FROM v$database;

As this is pulled from the v$ objects, it’s going to populate incorrectly for the AWR Warehouse, which requires a query of the AWR, preferably the DBA_HIST_DATABASE_INSTANCE for this information.

SQL> desc dba_hist_database_instance;
 Name Null? Type
 ----------------------------------------- -------- --------------
 DBID NOT NULL NUMBER
 INSTANCE_NUMBER NOT NULL NUMBER
 STARTUP_TIME NOT NULL TIMESTAMP(3)
 PARALLEL NOT NULL VARCHAR2(3)
 VERSION NOT NULL VARCHAR2(17)
 DB_NAME VARCHAR2(9)
 INSTANCE_NAME VARCHAR2(16)
 HOST_NAME VARCHAR2(64)
 LAST_ASH_SAMPLE_ID NOT NULL NUMBER
 PLATFORM_NAME VARCHAR2(101)
 CON_ID NUMBER

Then the pre run script also queries the plan_table for the SQL_ID:

-- check if SQLD360 is getting called by EDB360
COL from_edb360 NEW_V from_edb360;
SELECT CASE WHEN count(*) > 0 THEN '--' END from_edb360
 FROM plan_table
 WHERE statement_id = 'SQLD360_SQLID' -- SQL IDs list flag
 AND operation = '&&sqld360_sqlid.'
 AND rownum = 1;

We will need it to call the DBA_HIST_PLAN_TABLE if we would want this data, but it does have a flag so that it can continue on as a stand alone run, so it’s not a game stopper.

We then get tripped up again when it looks for the instance number:

-- get instance number
COL connect_instance_number NEW_V connect_instance_number;
SELECT TO_CHAR(instance_number) connect_instance_number FROM v$instance;

Again, with the AWR Warehouse, we need to call upon the DBA_HIST_DATABASE_INSTANCE view after adding in a where clause for the dbname.

SELECT TO_CHAR(instance_number)
from dba_hist_database_instance where db_name=p_dbname;

As with SQLTEXPLAIN, we are shot on get_sdatabase_name_short, get_host_name and get_rdbms_version, too:

-- get database name (up to 10, stop before first '.', no special characters)
COL database_name_short NEW_V database_name_short FOR A10;
SELECT LOWER(SUBSTR(SYS_CONTEXT('USERENV', 'DB_NAME'), 1, 10)) database_name_short FROM DUAL;
SELECT SUBSTR('&&database_name_short.', 1, INSTR('&&database_name_short..', '.') - 1) database_name_short FROM DUAL;
SELECT TRANSLATE('&&database_name_short.',
'abcdefghijklmnopqrstuvwxyz0123456789-_ ''`~!@#$%&*()=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
'abcdefghijklmnopqrstuvwxyz0123456789-_') database_name_short FROM DUAL;

-- get host name (up to 30, stop before first '.', no special characters)
COL host_name_short NEW_V host_name_short FOR A30;
SELECT LOWER(SUBSTR(SYS_CONTEXT('USERENV', 'SERVER_HOST'), 1, 30)) host_name_short FROM DUAL;
SELECT SUBSTR('&&host_name_short.', 1, INSTR('&&host_name_short..', '.') - 1) host_name_short FROM DUAL;
SELECT TRANSLATE('&&host_name_short.',
'abcdefghijklmnopqrstuvwxyz0123456789-_ ''`~!@#$%&*()=+[]{}\|;:",.<>/?'||CHR(0)||CHR(9)||CHR(10)||CHR(13)||CHR(38),
'abcdefghijklmnopqrstuvwxyz0123456789-_') host_name_short FROM DUAL;

-- get rdbms version
COL db_version NEW_V db_version;
SELECT version db_version FROM v$instance;
DEF skip_10g = '';
COL skip_10g NEW_V skip_10g;
SELECT '--' skip_10g FROM v$instance WHERE version LIKE '10%';
DEF skip_11r1 = '';
COL skip_11r1 NEW_V skip_11r1;

You can understand how all of this is going to return the information on the AWR Warehouse repository, (actual database it’s run on) instead of the source database that the SQL_ID is from.

CPU and RAC Info

We have the same issue when it goes to gather the information on CPU, and RAC-

-- get average number of CPUs
COL avg_cpu_count NEW_V avg_cpu_count FOR A3;
SELECT ROUND(AVG(TO_NUMBER(value))) avg_cpu_count FROM gv$system_parameter2 WHERE name = 'cpu_count';

-- get total number of CPUs
COL sum_cpu_count NEW_V sum_cpu_count FOR A3;
SELECT SUM(TO_NUMBER(value)) sum_cpu_count FROM gv$system_parameter2 WHERE name = 'cpu_count';

-- determine if rac or single instance (null means rac)
COL is_single_instance NEW_V is_single_instance FOR A1;
SELECT CASE COUNT(*) WHEN 1 THEN 'Y' END is_single_instance FROM gv$instance;

The first two queries just need to update to use

WRI$_DBU_CPU_USAGE
CPU_COUNT

WRI$_DBU_CPU_USAGE
CPU_CORE_COUNT

And the final one can easily be updated to use the DBA_HIST_DATABASE_INSTANCE after passing in the db_name.

What does make SQLd360 great, is that upon quick inspection, a change to the “pre” script to pass is the DB_ID and query the DBA_HIST vs. the V$/GV$ views is all that is really required to make most of the SQLd360 AWR Warehouse compliant.

SQLd360 Wins

Without any changes-  as is, the product runs and simply provides some wrong info when it comes to stats data, environment and such.  The actual SQL is viable and the code disregards the DBID when it queries the AWR objects.  It will fail on the ASH pull, but that is something that shouldn’t be expected unless they want to hit the DBA_HIST_ACTIVE_SESSION_HISTORY at some point… 🙂

Thanks to Mauro for the great idea to try SQLd360 against the AWR Warehouse and review it for changes to see what would be required.  Hope this helps, guys!