administration

Grepping the actual bind values of sql through v$sql_bind_capture


Programmatically or intercepted by forced cursor sharing, sql where clause actual values shall always be bound to variable placeholders and never / no longer be literally coded. You know Tom Kyte talking that story on each and every occasion, pointing out that so many sql areas got stuck in size and performance because of literal values and the resulting multitude of variants of sql statements. On the other hand, people complain about the sporadic issue that the optimizer takes wrong decisions due to one-time probing of actual values in a given session, a nightmare in 10g, much improved by adaptive cursor sharing throughout 12c meanwhile. Well, I do not want to dive into this again, what follows is just a commented recipe sql statement to inspect what actual bind values have had been in action for a (potentially) sub-optimal sql execution. Please note, that the STATISTICS_LEVEL initialization parameter takes to be greater than BASIC to have the statement deliver any data.

The statement is neither complex nor long-running, just using two perfomance views v$sql_bind_capture and v$sqlarea, resp. While v$sql_bind_capture provides for the bind names, values, capture state and time, v$sqlarea offers different ways to approach the subject, by schema or sql-id or module etc, and not at least, the raw text of the statement in question.

(more…)

Advertisements

Oracle linux 7.x reboot systemd swap target timeout workaround


There is an issue around currently for Red Hat based systems, Oracle Linux here, in version 7.3. running systemd version 219-30.0.1, having a shutdown or reboot seemingly hang on a failed swap unit (deallocation). A lot of posts on the net discuss the issue, After switching to Ubuntu 15.04 laptop won’t shutdown suggested the w/a, doing a swapoff/swapon bounce in advance, that worked for me, referencing reboot hangs at ‘Reached target Shutdown’. Systemd: Hangs indefinitely on >90% of reboot attempts comprises in in-depth analysis.

I’m not shure, though, whether tmp.mount.hm4: After swap.target is releated, really, because the implemented change is already present on my systems (systemd‘s). I’m much more tending to suspect widespread storage fragmentation on the swap area to cause the relatively long term swap off run-times. In addition, I almost only experienced the issue for systems that have been up for a longer number of days, say from 90 days onwards.

(more…)

Deprecation announcement of oracle restart along 12c withdrawn


As being spotted on Bjoern Rost’s blog, commented by Trap, today, Oracle obviously has been backing down on the deprecation announcement of oracle restart along 12c. On metalink, see:

Withdrawn: Deprecation Announcement of Oracle Restart with Oracle Database 12c (Doc ID 1584742.1)

This is good news, accepted with delight, since we do no longer need to turn back the hands of time into the nifty-oracle-bounce-handycraft-scripts-era. In fact, I wonder how many dba’s are already comfortable with systemd service registration. I suppose, a lot of dba’s would have been resorting back to the dusty sysv configurations, using the systemd-sysv-compatibility engine, which is sort of retrofitting a car-key-starter with a car that already comes with wireless keying and just some starter button. Look around, the major share of on-premise oracle database installations is still single instance compared to rac and even 12c-containers.

Have fun, Peter

Undo retention, flashback query and ora-01555 snapshot too old


Setting up a fault aware database environment is in charge of regarding possible physical and logical error scenarios. On the physical side of the medal, you got real-application-cluster or dataguard at your disposal. The logical one usually comprises backup and replication. Recently, oracle and others introduced features that enables a database to recover from logical errors without or with less remote systems and data. Namely flashback is a powerful technology to look back into (query), back out (transaction) or fully restore (database) the history of data. Flashback of course takes accompanying (history) (meta)data to do its job, namely again, undo-before-images, archived-redo-logs and flashback-logs. So, essentially, the convenience of your course into history depends on the amount of history metadata that is available to the database at the time of a logical error, say an inadvertant delete from.

You may already have learned or heard about or even hardly experienced that flashback-logs may be purged from the recovery-area in favour of archived-redo-logs, rendering your configured flashback-retention-time (DB_FLASHBACK_RETENTION_TARGET) a value of theory. So far, sizing the file-system recovery-area is a task to be performed in a clear-sighted manner. The same is true then for undo-before-images (compare to logs) und the undo-tablespace (compare to area) to accomodate enough data to meet your configured undo-retention-time (UNDO_RETENTION) in UNDO_MANAGEMENT=AUTO mode, while in addition still support long running workload queries (not to overwrite old undo). This article discusses the usability of the (enterprise-manager) undo-advisor in sizing your undo-tablespace to always foster successful flashback queries up to the configured undo-retention-time (as opposed to the nasty surprise of getting an ora-01555, see below, which is also misleading here, imho, in suggesting that the undo-tablespace is to small now, as we know it from workload queries – nope, it has been to small before now, in the past, as regarded to flashback queries – also see : ora-01555 snapshot too old when running flashback query).

(more…)

Some irritation due to extended output of systemd / systemctl status for sysv services


Bad is an unpleasant word, right? “On” or “off” implies some final statement, or “failed” may signal something wrong but at least terminated. But “bad”… uuuhh, blameworthy, guilty, unaccountable, still being around. Ok, before diving into linguistic depression, the change eventually turned out simple and was actually in good faith but, however, produced remarkable irritation. You know, systemctl status {service} will show an overview of some systemd unit definition with load state, current activity and so on. The load state, in particular, details, in parantheses, into the path of the unit file, the enablement and the vendor enablement preset, respectively. Original systemd units may give a load state as follows:

Loaded: loaded (/usr/lib/systemd/system/atop.service; enabled; vendor preset: disabled)

However, systemd units, that have just been derived from systemv init scripts, only printed the init script path since lately:

Loaded: loaded (/etc/rc.d/init.d/sysv-thing)

The new irritating factor now is an extension for those derived systemv init scripts, to also state the enablement, but show up as “bad” for the running enablement for whatever weird reason:

Loaded: loaded (/etc/rc.d/init.d/sysv-thing; bad; vendor preset: disabled)

(more…)