Oracle

Jonathan Lewis's picture

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.

Franck Pachot's picture

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:

Jonathan Lewis's picture

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:

Jonathan Lewis's picture

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

Jonathan Lewis's picture

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.

Franck Pachot's picture

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:

Jonathan Lewis's picture

Historic Stats

If you want to examine historic object stats Oracle gives you a few procedures in the dbms_stats package to compare sets of stats captured at two different time periods, but there’s no view that you can query to get an idea of how a table’s stats have changed over time. This is a problem that can be addressed when you discover two things:

  • There are views to report pending table, index, column and histogram stats.
  • Pending stats are stored stored as “historic” stats with a future date.

Once you’ve spotted the second detail, you can acquire the SQL to generate the pending stats views:

Franck Pachot's picture

Stays One Step Ahead With Certification

I started with Oracle certifications at the time of 8i. I had good experience on development side, but more limited on operation side. The certification, 4 exams at that time, was the way for me to prove my knowledge on *all* database domains, including operations. After that, I was able to find position on operation DBA side more easily without the ‘did you administrate big critical databases in production?’. It was clearly more liberty in job seeking.

Franck Pachot's picture

My tmux scripts to script demos

By Franck Pachot

.
When I did my first demo using tmux, I have written the following blog post to explain it: https://blog.dbi-services.com/using-tmux-for-semi-interactive-demos/. Since then I’ve done all my demos with this technique. My scripts and configuration files have evolved now I have uploaded the on GitHub to share them: https://github.com/FranckPachot/scripts/tree/master/tmux-demo

Franck Pachot's picture

Oracle archivelog deletion policy

Here are my posts on the dbi-services blog about archivelog deletion policy.

The deletion policy on a dataguard configuration should be:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

for the site where you don’t backup. It can be the standby or the primary.

and:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

for the site where you do the backups. It can be the primary or the standby.

Some related posts:

To prevent automated spam submissions leave this field empty.
Syndicate content