Search

Top 60 Oracle Blogs

Recent comments

Oracle

That demned elusive archive log!

Introduction

With apologies to Emma Orczy again for stealing a line from “The Scarlet Pimpernel” … </p />
</p></div>

    	  	<div class=

That demned elusive archive log!

Introduction

With apologies to Emma Orczy again for stealing a line from “The Scarlet Pimpernel” … </p />
</p></div>

    	  	<div class=

Direct IOT

A recent (automatic ?) tweet from Connor McDonald highlighted an article he’d written a couple of years ago about an enhancement introduced in 12c that allowed for direct path inserts to index organized tables (IOTs). The article included a demonstration seemed to suggest that direct path loads to IOTs were of no benefit, and ended with the comment (which could be applied to any Oracle feature): “Direct mode insert is a very cool facility, but it doesn’t mean that it’s going to be the best option in every situation.”

strace the current Oracle session process

Here is my way to trace system calls from the current session process.
This must not be done in production.
An strace.log file will be generated with system calls

connect / as sysdba
column spid new_value pid
select spid from v$process join v$session on v$session.paddr=v$process.addr where sid=sys_context('userenv','sid');
column spid clear
define bg=&:
host strace -fy -p &pid -o strace.log &bg
select * from v$osstat;
disconnect

Originally posted on Twitter, but improved here

https://twitter.com/FranckPachot/status/969898128030695424

Quiz Night

Here’s a question prompted by a recent thread on the ODevCom database forum – how many rows will Oracle sort (assuming you have enough rows to start with in all_objects) for the final query, and how many sort operations will that take ?


drop table t1 purge;

create table t1 nologging as select * from all_objects where rownum < 50000;

select owner, count(distinct object_type), count(distinct object_name) from t1 group by owner;

Try to resist the temptation of doing a cut-n-paste and running the code until after you’ve thought about the answer.

Oracle: connection rate to the listener

A single line command to read the listener log, filter the message from a date (you can change it), and group by hour to count the connections:

adrci exec='set home diag/tnslsnr ; show alert -term -p \"' "MESSAGE_TEXT like \'%establish%\' and ORIGINATING_TIMESTAMP > \'2018-06-07 00:00:00\'" '\"' | awk '/establish/{sub(/:.*/,"",$2);print "connection/hour at "$1,$2":00-"$2":59"}' | uniq -c

Originally published on Twitter:

pushing predicates

I came across this odd limitation (maybe defect) with pushing predicates (join predicate push down) a few years ago that made a dramatic difference to a client query when fixed but managed to hide itself rather cunningly until you looked closely at what was going on. Searching my library for something completely different I’ve just rediscovered the model I built to demonstrate the issue so I’ve tested it against a couple of newer versions  of Oracle (including 18.1) and found that the anomaly still exists. It’s an interesting little detail about checking execution plans properly so I’ve written up the details. The critical feature of the problem is a union all view:

Cardinality Puzzle

One of the difficulties of being a DBA and being required to solve performance problems is that you probably never have enough time to think about how you got to a solution and why the solution works; and if you don’t learn about the process itself , you just don’t get better at it. That’s why I try (at least some of the time) to write articles and books (as I did with CBO Fundamentals) that

Validate FK

A comment arrived yesterday on an earlier posting about an enhancement to the truncate command in 12c that raised the topic of what Oracle might do to validate a foreign key constraint. Despite being sure I had the answer written down somewhere (maybe on a client site or in a report to a client) I couldn’t find anything I’d published about it, so I ran up a quick demo script to show that all Oracle does is construct a simple SQL statement that will do check the data – and then do whatever the optimizer does to produce the fastest possible plan.

Here’s the script – with a few variations to show what happens if you start tweaking features to change the plan.

Data Guard: always set db_create_file_dest on the standby

By Franck Pachot

.
The file name convert parameters are not dynamic and require a restart of the instance. An enhancement request was filled in 2011. I mentioned recently on Twitter that it can be annoying with Active Data Guard when a file on the primary server is created on a path that has no file name conversion. However, Ian Baugaard mentioned that there is a workaround for this specific case because db_create_file_dest is dynamic: