Performance Tuning

Unified Auditing – Performance Improvements in Oracle 12.1.0.2

Unified Auditing got introduced in Oracle Database 12.1.

Unified Auditing - Performance Improvements in Oracle 12.1The downsides of the “old” auditing facilities became obvious when too many users had activities or transactions at the same time leading to audit records being written into AUD$. Contention was a typical issue. The same thing happened when too many users tried to login at the same time. Furthermore protecting the auditing information required Database Vault as there was no default protection available.

This – and some other things – should be remedied by Unified Auditing which is available since Oracle Database 12c. It gets enabled in sort of a “mixed … [ Read more ]

What happened to the blog post about “12c parameters”?

Best Practice Hint

Two weeks ago I published a blog post about Parameter Recommendations for Oracle Database 12.1.0.2. And I took it down a day later. Why that?

I’ve got a lot of input from external sources for the “Parameter” blog post. And I’d like to thank everybody who contributed to it, especially Oracle ACE Ludovico Caldara.

Generally there was a bit of a misunderstanding internally about whether we should advertise” underscore parameters to cure some misbehavior of the database. In 99% of all cases I’d agree that underscores are not a good solution – especially when it comes … [ Read more ]

Query on ALL_SYNONYMS is slow in Oracle Database 12c

A customer (Thanks Stefano!) alerted me on this issue during a workshop and I did some further investigation. Basic headline is:

Query on ALL_SYNONYMS is very slow in Oracle Database 12.1.0.2 compared to 11g.

The workaround for this  21324443: SLOW QUERY IN 12C ON ALL_SYNONYMS. is:

dbms_stats.gather_table_stats('SYS','OBJ$',estimate_percent
=>100, method_opt => 'for columns flags size 1, spare3 size 254, type# size 254');

and I see that at least 8 other customers opened SRs hitting the same issue.
Does the workaround suit you in any way?

The bug had no progress since it was opened. If you are seeking progress … [ Read more ]

Different Metrics for SPA (SQL Performance Analyzer)

I’m more the command line type of person. Once I’ve understand what’s going on behind the curtains I certainly switch to the GUI-click-click tools. But in the case of Real Application Testing – even though the support via the OEM GUI is excellent – sometimes I prefer to run my procedures from the command line and check my reports in the browser.

Recently Thomas, a colleague from Oracle ACS Support, and I were asking ourselves about the different comparison metrics for the SQL Performance Analyzer reporting We did scan the documentation but we found only examples but no complete … [ Read more ]

SQL Monitoring – Limitation at 300 lines per statement

One of the best parts of my job at Oracle:
I still learn something new every day.

Yesterday I’ve learned from my colleague from Oracle Switzerland, Thomas Teske, that SQL Real Time Monitoring has an embedded default limitation on the number of lines in the statement. If the limit (default: 300 lines) is exceeded the statement won’t be monitored. We both work with a leading Swiss company and we wanted to monitor a complex plan.

Now you may think: Who the heck has statements longer than 300 lines?
Well … sometimes that is beyond your influence as in this particular … [ Read more ]

How to select statements from AWR?

Simple question – and more a reminder to myself as I’d assume that many people have their examples somewhere already. But in upgrade and migration projects it’s sometime useful to simply select SQL statements directly from between two AWR snapshots into a SQL Tuning Set (STS). This will give you the possibility to later put a failing plan directly into a SQL Plan Baseline, part of the free Enterprise Edition’s SQL Plan Management feature.

Please find a scripted example for download here:

You’ll need to create a SQL Tuning Set and simply fill it with staments from an AWR … [ Read more ]