When you have to troubleshoot blocking locks in your Oracle DB, you might be lucky enough to have a GUI tool handy that helps you with that (e.g. Enterprise Manager or TOAD). Otherwise you’ll likely have to resort to Oracle’s pre-installed script “utllockt.sql”. Unfortunately, this script only shows locks in the DB instance it’s running on. However, locks chains may span multiple instances in a clustered environment.
In this brief post I’d like to propose a RAC-aware alternative to “utllockt.sql” using only SQL so it can be run in any Oracle client.
Picture: Mike Gabelmann / flickr / CC-BY-NC-SA
Tim Hall inspired me with his #ThanksOTN (or “OTN Appreciation Day“) campaign to continue my mini-series on leveraging SQL Developer Reports for DBA tasks. Today with visualizing historical System Statistics from Statspack for performance analyses.
#ThanksOTN I have three favourite Oracle features to rave about today:
- Analytic SQL
- SQL Developer (particularly the reports)!
Today I stumbled across a strange error when I tried to run orachk on a customer’s system:
Enter ORACLE_HOME for PRODDB : /opt/oracle/product/rdbms/12.1.0
Could not login to PRODDB using /opt/oracle/product/rdbms/12.1.0. Try again (3 attempts remaining)
Enter ORACLE_HOME for PRODDB :
A quick internet search revealed nothing useful, so I checked MOS. Document 1989401.1 had the right hint: It was due to a customized “login.sql” that had an “ALTER SESSION …” in it. This leads to incorrect results for orachk’s scripts. Disabling login.sql (or glogin.sql, if you use this one) solved the problem.
login.sql can be so evil!
Side note: The Oracle Docs say that “login.sql” is executed from your current directory or from the directory list in $SQLPATH. Neither of that applied to my environment, but ORACLE_PATH was set and pointed to a directory containing login.sql. Looks like this important detail is missing in the docs.
tl;dr: The option “-ocmrf” is no longer mandatory nor implemented anymore.
Last week, the quarterly PSU came out, and just a few hours later I tried to apply it to a Oracle Restart + DB system under OL 6.7.
This would have run smoothly if it wasn’t for the fact that this was the first patch for this system and therefore I needed to create a OCM response file for silent patching with “opatchauto”.
Too bad that there was no executable “emocmrsp” unter “./ocm/bin” when I tried to use the latest OPatch-Tool (184.108.40.206.5). WTF?!
Up to Oracle 11.2 it was possible to display archived SQL plans from Statspack using DBMS_XPLAN. I make use of this in some of my scripts and SQL Developer Reports since I first saw this in Christian Antognini’s Book “Troubleshooting Oracle Performance“.
But in 12c (here: 220.127.116.11 on Linux), there’s a piece missing now:
select * from table(dbms_xplan.display(
table_name => 'perfstat.stats$sql_plan',
statement_id => null,
format => 'ALL -predicate -note',
filter_preds => 'plan_hash_value = '|| &&phv
ERROR: an uncaught error in function display has happened; please contact Oracle support
Please provide also a DMP file of the used plan table perfstat.stats$sql_plan
ORA-00904: "TIMESTAMP": invalid identifier
So it looks like STATS$SQL_PLAN wasn’t synchronized to the changes in 12c’s PLAN_TABLE. Maybe because the timestamp wouldn’t make much sense there, anyway, maybe simply because Oracle forgot.
==> Quick and most certainly unsupported workaround:
ALTER TABLE perfstat.stats$sql_plan ADD timestamp INVISIBLE AS (cast(NULL AS DATE));
Another workaround could be to create a separate view with an additional dummy timestamp column an reference the view. I chose to stick with the invisible column solution so I won’t have to create new objects in the DB and change scripts to use these objects.
Hopefully, this will be solved in 12.2 – at least, I had filed an SR / Enhancement Request with Oracle Support.
Today here’s a shorter post about my experiments with Oracle SQL Developer’s user-defined reports: A report on all long running operations (“LongOps”) with details on session wait events, explain plans and live SQL monitoring.
“Wait a minute”, you might say, “there’s already the session report in SQL Developer’s standard reports that shows Session_LongOps”! – and you’re right, it’s been integrated in SQLDev for quite some time.
BUT: I wanted to view the LongOps from a different perspective: To find out what’s taking so long in the database generally, it would be better in my humble opinion to start with ALL LongOps and drill down from there.
Here’s how it looks on a live system:
This is the second post in my mini-series on leveraging SQL Developer Reports for DBA tasks, today with visualizing Average Active Sessions (AAS). In this article I’ll cover
- What AAS is and how to interpret it
- How to build a basic line graph in SQL Developer
- How to extend the graph with detailed child reports (Time Model Statistics and Top Events)