If this is the closing section of a trace file from a single end-user session that has a performance problem, what’s the most obvious deduction you can make about the cause of the problem, and what sort of action would you take next ?
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 296 0.06 0.10 0 651 0 0
It’s hard to understand all the ramifications of Oracle’s undo handling, and it’s not hard to find cases where the resulting effects are very confusing. In a recent post on the OTN database forum resulted in one response insisting that the OP was obviously updating a table with frequent commits from one session while querying it from another thereby generating a large number of undo reads in the querying session.
It’s a possible cause of (more...)
I decided this week to be a little brave and upgrade one of the Enkitec Exadata racks to 184.108.40.206. I installed the 220.127.116.11.0 Exadata image a few weeks ago, and have been waiting for a chance to upgrade clusterware to 12.2. Thankfully, Oracle provides a very good note for this, but I did hit one large snag that should be documented.
The process for upgrading GI to 12.2 (more...)
I’ve waxed lyrical in the past about creating suitable column group statistics whenever you drop an index because even when the optimizer doesn’t use an index in its execution path it might have used the number of distinct keys of the index (user_indexes.distinct_keys) in its estimates of cardinality.
I’ve also highlighted various warnings (here (with several follow-on URLs) and here) about when the optimizer declines to use column group statistics. (more...)
A question came up on the OTN database forum a little while ago about a very simple query that was taking different execution paths on two databases with the same table and index definitions and similar data. In one database the plan used the “index full scan (min/max)” operation while the other database used a brute force “index fast full scan” operation.
In most circumstances the starting point to address a question like this is (more...)
One of the difficulties with trouble-shooting is that’s it very easy to overlook, or forget to go hunting for, the little details that turn a puzzle into a simple problem. Here’s an example showing how you can read a bit of an AWR report and think you’ve found an unpleasant anomaly. I’ve created a little model and taken a couple of AWR snapshots a few seconds apart so the numbers involved are going to be (more...)
Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 18.104.22.168. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:
I was playing around with the Exadata X2-2 in the Enkitec lab this weekend, and hit an interesting issue when patching the storage servers. We were taking the system up to version 22.214.171.124.3 for testing purposes. I fired off the patchmgr script, and one of the storage servers failed when beginning the first phase of the patching cycle:
[root@enkdb03 patch_126.96.36.199.3.161208]# ./patchmgr -cells cell_group -patch -ignore_alerts
Few days ago we had concurrency problem with "cursor: pin S wait on X
" wait event. This wait event is mostly associated with parsing in some form.
After quick diagnosis I’ve found problematic query. It was fairly complex query which was executed very often with average 0.20 seconds of execution time. As this query was using bind variables, Oracle reused existing plan and problems with "cursor: pin S wait on X" wait (more...)
While dropping a tablespace, getting error ORA-38301: can not perform DDL/DML over objects in Recycle Bin .
SQL> drop tablespace DATA including contents and datafiles;
drop tablespace DATA including contents and datafiles
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
Before dropping the tablespace, disable recyclbin .
1. Disable recyclebin:
SQL> ALTER SYSTEM SET recyclebin (more...)
While running rman commands from the catalog database, you might get the error RMAN-20005: target database name is ambiguous. , if the database was restored from another database.
rman target sys/oracle#51234@RMANBKP catalog rman_b2ccrmpd/rman@catdb
Recovery Manager: Release 188.8.131.52.0 – Production on Fri Oct 14 10:34:05 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to (more...)
I got a call earlier today from the Tech Support Scammers. You’ve probably heard of this horribly unethical practice already, but the premise is that they cold-call seemingly randomly and try to convince you that there is a problem with your PC/router, and then attempt to get you to allow them remote access to your PC to ‘fix it’. Some then claim problems are due to expired warranties on the computer and demand payment, others (more...)
Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.
This was the statement:
from CLIENT k
where k.expires is null;
I've used fake names for table name and columns.
Database version: 184.108.40.206.0
Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (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...)
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 220.127.116.11 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/18.104.22.168/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...)
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...)