Yesterday I posted a note about querying dba_optstat_operations to get a quick report of how long calls to dbms_stats had been taking but said I had another script that helped to fill some of the gaps it left. One of my readers points out fairly promptely that 12c enhances the feature considerably, with a view dba_optstat_operation_tasks that (for example) lists all the tables processed during a single call to gather_schema_stats.
Well, I wrote my script (more...)
I don’t really remember how long it’s been since Oracle created an automatic log of how long a call to the dbms_stats package took, though it was probably some time in the 10g time-line. It wasn’t until it had been around for several years, though before I wrote little script (possibly prompted by a comment from Martin Widlake) that I’ve used occasionally since to see what’s been going on in the past, how variable (more...)
A script hacked together a couple of years ago from a clone of a script I’d been using for checking space usage in the older types of segments. Oracle Corp. eventually put together a routine to peer inside securefile LOBs:
rem Script: dbms_space_use_sf.sql
rem Author: Jonathan Lewis
rem Dated: Dec 2013
rem Last tested
rem Not tested
rem 11.1. (more...)
Here’s a simple script that I’ve used for many years to check space usage inside segments. The comment about freelist groups may be out of date – I’ve not had to worry about that for a very long time. There is a separate script for securefile lobs.
rem Script: dbms_space_use.sql
rem Author: Jonathan Lewis
rem Dated: Nov 2002
rem Last tested
rem 11.2. (more...)
I got a call earlier today from the Tech Support Scammers. You’ve probably heard of this horribly unethical practice already, but the premise is that they cold-call seemingly randomly and try to convince you that there is a problem with your PC/router, and then attempt to get you to allow them remote access to your PC to ‘fix it’. Some then claim problems are due to expired warranties on the computer and demand payment, others (more...)
There are few ways to see the execution plan of a SQL statement. One of these ways is the autotrace option in sqlplus. It is a very easy-to-use feature and people use it quite often. But there is a risk here. The autotrace option doesn’t always show you the correct execution plan. Lately I prepared […]
A new version 4.23 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version comes only with minor changes, see the change log below.
Here are the notes from the change log:
- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)
Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.
This was the statement:
from CLIENT k
where k.expires is null;
I've used fake names for table name and columns.
Database version: 126.96.36.199.0
Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)
It's probably not that well known that Oracle can actually rollback / re-start the execution of a DML statement should the cursor become invalidated. By rollback / re-start I mean that Oracle actually performs a statement level rollback (so any modification already performed by that statement until that point gets rolled back), performs another optimization phase
of the statement on re-start (due to the invalidation) and begins the execution of the statement from scratch. Note (more...)
This post provides a short summary and pointers to previous work on Extended Stack Profiling for troubleshooting and performance investigations.Understanding
the workload is an important part of troubleshooting activities. We seek answers to questions like: what is the system doing, where is the time spent
, which code
paths are most used, what are the wait events, etc. Sometimes the relevant diagnostic data is easy to find, other times we need to dig (more...)
A new version 4.22 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version primarily addresses an issue with 12c - if the HIST mode got used to pull ASH information from AWR in 12c it turned out that Oracle forgot to add the new "DELTA_READ_MEM_BYTES" columns to DBA_HIST_ACTIVE_SESS_HISTORY - although it got officially added to V$ACTIVE_SESSION_HISTORY in 12c. So now I had to implement (more...)
The v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database. v$log_history queries You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the … Continue reading Analyze database activity using v$log_history
The post Analyze database activity using v$log_history appeared first on Oracle (more...)
A rather not so great post about an ORA-00600 error i faced on a standby database. Environement was 188.8.131.52 on Sun Super Cluster machine. MRP process was hitting ORA-00600 while trying to apply a specific archive log.
The error message was something like this
MRP0: Background Media Recovery terminated with error 600
Errors in file /u01/app/oracle/product/184.108.40.206/diag/diag/rdbms/xxxprd/xxxprd1/trace/xxxprd1_pr00_6342.trc:
ORA-00600: internal error code, arguments: , , , , , , , (more...)
Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.
This was virtual machine running oldish 10.2.0.5.0 Oracle database.
I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)
First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]
Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP. It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window. Here’s an excerpt of a Data Pump Logfile: ORA-39083: Object type PROCOBJ failed to create with error: ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job […]
One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.
In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)
I wanted to install Oracle JDevelper 12.1.3 – a version that I had been eagerly awaiting. Since my primary machine is a MacBook, I wanted to install it on OS X 10.9.3.
I downloaded the generic installer and found that the install didn’t run. Since OS X had disappeared from the documentation, I assumed that I would have to fiddle around until I found a combination of JDK and OS (more...)