Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Archive for the ‘Errors’ Category

Displaying and fixing compilation errors on a SQLTXPLAIN package

leave a comment »

If you are installing SQLTXPLAIN and for some reason you get a PL/SQL compilation error in a SQLT log like the one below, chances are the SHOW ERRORS command on SQL*Plus won’t show the actual error. This is because the installation connects as SYS and the packages are owned by SQLTXADMIN. In most cases the cause of the error is missing a GRANT or a SYNONYM in libraries called by SQLT like DBMS_METADATA or UTL_FILE.

... creating package body for SQLT$R
Warning: Package Body created with compilation errors.
No errors.
...
SELECT column_value libraries FROM TABLE(SQLTXADMIN.sqlt$r.libraries_versions)
ERROR at line 1:
ORA-04063: package body "SQLTXADMIN.SQLT$R" has errors

How to display PL/SQL package compilation error on SQLTXPLAIN

You may need to unlock the schema owner of SQLT packages and compile the invalid object connecting as this SQLTXADMIN account, then lock it back. By following steps below you will get to see the actual compile error. Then proceed to fix it and recompile the invalid package body. If it is a missing GRANT/SYNONYM you may want to create the GRANT EXECUTE of the SYS library to SQLTXADMIN then create a SYNONYM with same name (not a PUBLIC SYNONYM). Ex: GRANT EXECUTE ON SYS.DBMS_METADATA TO SQLTXADMIN; CREATE SYNONYM SQLTXADMIN.DBMS_METADATA FOR SYS.DBMS_METADATA;

$ cd sqlt/install
$ sqlplus / AS SYSDBA
--
SQL> SELECT object_name, object_type FROM dba_objects WHERE owner = 'SQLTXADMIN' AND object_type LIKE 'PACKAGE%' AND status = 'INVALID';
SQL> START sqcommon1.sql
SQL> GRANT CREATE SESSION TO sqltxadmin;
SQL> ALTER USER sqltxadmin IDENTIFIED BY &&password. ACCOUNT UNLOCK;
SQL> CONN sqltxadmin/&&password.;
--
-- compile invalid packages as per query output above
--
SQL> ALTER PACKAGE sqlt$m COMPILE;
SQL> ALTER PACKAGE sqlt$r COMPILE;
--
-- fix root cause of error
--
SQL> CONN / AS SYSDBA
SQL> REVOKE CREATE SESSION FROM sqltxadmin;
SQL> ALTER USER sqltxadmin PASSWORD EXPIRE ACCOUNT LOCK;
Advertisements

Written by Carlos Sierra

June 19, 2013 at 11:50 am

Understanding SQLTXPLAIN Health-Checks

with 2 comments

SQLTXPLAIN (SQLT) collects diagnostics details about a SQL statement that either performs poorly or it produces unexpected results (also known as wrong results). In addition to that, it also performs over a hundred health-checks around the SQL statement being analyzed. The results of these so called health-checks are displayed on the main HTML report under the “Observations” section. Within each observation there is description of the meaning and in some cases a pointer to known bugs or notes. Since there are so many health-checks, it happens that every once in a while we want to dig further into WHY we get this “observation”. If that is your case and you understand SQL and PL/SQL, I invite you to “hack” into the SQLT source code and see how this health-check is triggered.

Finding WHY SQLT triggered a particular Health-Check

Assume you get this “observation” in your main HTML report “Table contains 2 column(s) referenced in predicates where the number of distinct values does not match the number of buckets.“.

Open sqlt/install/sqcpkgh.pkb (this h is for health-checks) and search for a portion of this text “number of distinct values does not match the number of buckets”. You will find a piece of code that looks like this:

 -- 10174050 frequency histograms with less buckets than ndv
 BEGIN
 SELECT COUNT(*)
 INTO l_count
 FROM sqlt$_dba_all_table_cols_v
 WHERE statement_id = p_tab_rec.statement_id
 AND owner = p_tab_rec.owner
 AND table_name = p_tab_rec.table_name
 AND histogram = 'FREQUENCY'
 AND num_distinct <> num_buckets
 AND in_predicates = 'TRUE';

IF l_count > 0 THEN
 s_obs_rec.type_id := E_TABLE;
 s_obs_rec.object_type := 'TABLE';
 s_obs_rec.object_name := p_tab_rec.owner||'.'||p_tab_rec.table_name;
 s_obs_rec.observation := 'Table contains '||l_count||' column(s) referenced in predicates where the number of distinct values does not match the number of buckets.';
 s_obs_rec.more := 'Review <a href="#tab_cols_cbo_'||p_tab_rec.object_id||'">column statistics</a> for this table and look for "Num Distinct" and "Num Buckets". If there are values missing from the frequency histogram you may have Bug 10174050. If you are referencing in your predicates one of the missing values the CBO can over estimate table cardinality, and this may produce a sub-optimal plan. As a workaround: alter system/session "_fix_control"=''5483301:off'';';
 ins_obs;
 END IF;
 END;

In this case the health-check derives from view sqlt$_dba_all_table_cols_v. You can find all view definitions inside file sqlt/install/sqcvw.sql. This file shows that view sqlt$_dba_all_table_cols_v selects from tables sqlt$_dba_tab_cols and sqlt$_dba_nested_table_cols.

There is a predicate on the health-check that reads “num_distinct <> num_buckets“. So this condition is what triggered this particular health-check.

In some cases, the column driving the health-check is an addition to the base DBA or GV$ views, so in such cases you may have to search for that column in one of these two packages: sqlt/install/sqcpkgd.pkb or sqlt/install/sqcpkgt.pkb. Where the “d” stands for Diagnostics data collection and the “t” for Transformation of diagnostics data.

Conclusion

When you get a SQLT health-check that is of your concern and you need to find out WHY it was raised, you may need to look into SQLT source code. Always start on the “h” module and continue either in view definitions or in the two packages that seed special columns. Those two packages “d” and “t” are the ones responsible for data collection and transformations.

SQLT source code is transparent. It is written in an easy to follow style. Feel free to reverse engineer any part of it. No mysteries, no hidden pieces, no black magic…

Written by Carlos Sierra

April 19, 2013 at 10:43 am

Fixing ORA-19384 from DBMS_SQLTUNE.PACK_STGTAB_SQLSET on SQLT

leave a comment »

If you see in the main html SQLTXPLAIN (SQLT) the following error you can execute the commands below connected as SYS:

sqlt$a: *** d:ORA-19384: cannot pack into staging table from previous version
sqlt$a: *** d:DBMS_SQLTUNE.PACK_STGTAB_SQLSET

DROP TABLE sqltxplain.sqli$_stgtab_sqlset;
BEGIN
 SYS.DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (
 table_name => 'SQLI$_STGTAB_SQLSET',
 schema_name => 'SQLTXPLAIN');
END;
/
DROP TABLE sqltxplain.sqlt$_stgtab_sqlset;
BEGIN
 SYS.DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (
 table_name => 'SQLT$_STGTAB_SQLSET',
 schema_name => 'SQLTXPLAIN');
END;
/

Written by Carlos Sierra

April 2, 2013 at 1:04 pm