This is just a little example of thinking about hinting for short-term hacking requirements. It’s the answer to a question that came up on the Oracle-L listserver a couple of months ago (Oct 2015) and is a convenient demonstration of a principle that can often (not ALWAYS) be applied as a response to the problem: “I can make this query work quickly once, how do I make it work quickly when I make it part (more...)
If you don’t want to read the story, the summary for this article is:
If you create bitmap join indexes on a partitioned table and you use partition exchanges to load data into the table then make sure you create the bitmap join indexes on the loading tables in exactly the same order as you created them on the partitioned table or the exchange will fail with the (truthful not quite complete) error: ORA-14098: index (more...)
It’s amazing how easy it is to interpret a number incorrectly until the point comes where you have to look at it closely – and then you realise that there was a lot more to the number than your initial casual assumption, and you would have realised it all along if you’d ever needed to think about it before.
Here’s a little case in point. I have a simple (i.e. non-partitioned) heap table t1 (more...)
I thought I had written this note a few years ago, on OTN or Oracle-L if not on my blog, but I can’t find any sign of it so I’ve decided it’s time to write it (again) – starting as a question about the following code:
create table t1
with generator as (
select rownum id
rownum <= 1000
Here’s a convenient enhancement for tracing that came up on Twitter a few days ago – first in a tweet that I retweeted, then in a question from Christian Antognini based on this bit of the 12c Oracle documentation (opens in separate tab). The question was – does it work for you ?
The new description for max_dump_file_size says that for large enough values Oracle will split the file into multiple chunks of a few megabytes, (more...)
It's probably not that well known that Oracle can actually rollback / re-start the execution of a DML statement should the cursor become invalidated. By rollback / re-start I mean that Oracle actually performs a statement level rollback (so any modification already performed by that statement until that point gets rolled back), performs another optimization phase
of the statement on re-start (due to the invalidation) and begins the execution of the statement from scratch. Note (more...)
This post provides a short summary and pointers to previous work on Extended Stack Profiling for troubleshooting and performance investigations.Understanding
the workload is an important part of troubleshooting activities. We seek answers to questions like: what is the system doing, where is the time spent
, which code
paths are most used, what are the wait events, etc. Sometimes the relevant diagnostic data is easy to find, other times we need to dig (more...)
A new version 4.22 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
This version primarily addresses an issue with 12c - if the HIST mode got used to pull ASH information from AWR in 12c it turned out that Oracle forgot to add the new "DELTA_READ_MEM_BYTES" columns to DBA_HIST_ACTIVE_SESS_HISTORY - although it got officially added to V$ACTIVE_SESSION_HISTORY in 12c. So now I had to implement (more...)
The v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database. v$log_history queries You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the … Continue reading Analyze database activity using v$log_history
The post Analyze database activity using v$log_history appeared first on Oracle (more...)
A rather not so great post about an ORA-00600 error i faced on a standby database. Environement was 22.214.171.124 on Sun Super Cluster machine. MRP process was hitting ORA-00600 while trying to apply a specific archive log.
The error message was something like this
MRP0: Background Media Recovery terminated with error 600
Errors in file /u01/app/oracle/product/126.96.36.199/diag/diag/rdbms/xxxprd/xxxprd1/trace/xxxprd1_pr00_6342.trc:
ORA-00600: internal error code, arguments: , , , , , , , (more...)
Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.
This was virtual machine running oldish 10.2.0.5.0 Oracle database.
I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)
First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]
Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP. It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window. Here’s an excerpt of a Data Pump Logfile: ORA-39083: Object type PROCOBJ failed to create with error: ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job […]
One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.
In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)
I wanted to install Oracle JDevelper 12.1.3 – a version that I had been eagerly awaiting. Since my primary machine is a MacBook, I wanted to install it on OS X 10.9.3.
I downloaded the generic installer and found that the install didn’t run. Since OS X had disappeared from the documentation, I assumed that I would have to fiddle around until I found a combination of JDK and OS (more...)
I noticed something for the first time tonight when I was playing around in the Enkitec lab – something that I have been doing wrong for a while. When working in the lab, I often rely on the crsctl command to shut down the entire cluster stack for me. It’s really easy to use “crsctl stop cluster -all” followed by “dcli -l root -g ~/dbs_group /u01/app/188.8.131.52/grid/bin/crsctl stop crs” to get everything down (more...)
We had a client that was running into a strange issue on their Exadata where new connections coming in through the SCAN were failing. After doing some troubleshooting, it was discovered that it was related to one of the SCAN listeners not properly accepting requests from new sessions. The VIP and listener were running, and everything looked normal.
We had the following SCAN setup:
Last week I’ve mentioned on Twitter
that we ran into wrong result bug
. We found workaround quickly but I’ve decided to spend some time to reproduce error and write blog post to warn you about this optimizer behavior.
Special thanks to my colleague who spotted odd results which led us to this finding.
My test (virtual) environment is:
OS: Oracle Enterprise Linux 5.8
DB: Oracle EE 184.108.40.206.12
In test I (more...)