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.

Oracle global temp tabs or nologging/append – saved redo in numbers

In an attempt to reduce redo entries (and archive space) generated for only temporarily computed contents of a couple of simple tables, I regarded employing Global Temporary Tables as well as Nologging Inserts as redesign options. This article, however, is not meant to dive into the details of these redesign options at all. There’s plenty of examples and discussions about the pros and cons of each option around the net. Most notably to mention, and a stimulus to post just my redo reduction test results, is Tim Hall’s compact articles on the subject like https://oracle-base.com/articles/misc/temporary-tables and https://oracle-base.com/articles/misc/append-hint that do include an assessment of the redo matter.

So, what’s the lineup for short? On startup, some data will be sourced throughout the database, losely computed and brutely written into a couple of simple tables. Then some third party will read this data for own purposes and will report completion such that the data can eventually be discarded completely. In making shure that an iteration step always starts unsoiled, any data left over from a crashed precursor will be rubbed out as well. Nobody cares about backups of this data, iteration management runs on other tables. You see, this is not really something that takes a database to complete successfully but…

Identifying excessive load causes with oracle enterprise manager 11g

There are times when people ask me to identify the root causes of excessive Oracle Database (and host) loads in the past, still being astonished how i do manage to get this information out of nothing. Well, really, there is no out of nothing, never. There may be a complex scenario and a couple of tools at hand which span another level of complexity, unfortunately. That’s all of it. You see, complexity is a bad thing, something you will want to avoid or eliminate at all times. Some people furthermore aim to prove their skills in talking about what degree of complexity they are able to handle… hhm, poor ones, I don’t envy them, always struggling to tighten a plethora of strings, over and over, I’m more a convention-over-configuration guy, but that’s another discussion.

Back to to the point, Oracle Database is a vast stack of technology, indeed inducing a fair level of complexity in analysis and so is Oracle Enterprise Manager, the number one monitoring tool for the database and further (so called) targets like hosts and jvms, in operation. In fact, Oracle Enterprise Manager, being based on data collection in the database (AWR – Automatic Workload Repository), data evaluation in the database (ADDM – Automatic Database Diagnostic Monitor) and data transfer to some production instance (Enterprise Manager Agent) constitutes another mountain top in the Oracle landscape not everyone is able or willing to climb. So iff you misplaced your hiking boots for today, the following is for you.

Ok then, log yourself in, using your EM credentials.

Bottom-up hierarchical root path query recipe in oracle

Oracle supports hierarchical querying since long and has continuously extended the set of available features, i.e. pseudo-columns, path generation, leaf sorting by name, etc. With 11gR2, Oracle even complements with the ANSI SQL standard approach: the CONNECT BY syntax for hierarchical querying that can now be replaced by the ANSI SQL standard Recursive Subquery Factoring clause, see Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it for more information.
However, talking about hierarchical querying with Oracle always implies a top-down traversion (see another post on that subject from a couple of years ago that did not provided a PL/SQL ready solution as Implementing bottom-up path traversal for hierarchical tables). You start (START WITH) at some root node(s) of your choice and dive into the branches and leaves (CONNECT BY) by connecting child-nodes to parent-nodes until the final leaf has been consumed (check CONNECT_BY_ISLEAF) or a certain LEVEL has been reached by filter. There is tons of examples out there showing this in action. Here is just some simple code example to start off with, employing SYS_CONNECT_BY_PATH, introducing root- to leaf-node path generation over the child-to-parent relationship between columns T.P_ID and T.ID.

(raw) Oracle Linux 6 memory foorprint with/out X11

Running quite a couple of (Linux) guests on a virtual host sooner or later raises the question of the (guests / host resources) ratio. That is, commonly for cpu, ram and i/o, how many guests will fit on that specific host for an average load.

Having this question nagging in my head, I was particularly curious to find out how much memory an OL6 will consume for a pure operating system installation with and without the convenience of running X11 (which is animated by Gnome 3.x in OL6, having most of the autostart apps removed – xfce will be much leaner but that’s another story).

The top Mem: used snapshots were taken immediately after a bounce of the guest to have as less application code inference as possible since Linux never frees memory iff not necessary. Here we go.