Here’s an example of where the optimizer really should ignore a hint.
I’m testing an implementation of deliberately unusable index partitions – i.e. some empty index partitions, some usable.
This is 18.104.22.168 so whilst there are enhancements in 12c, they’re no use here.
SQL> create table t1
2 (col1 number)
3 partition by list(col1)
4 (partition p0 values(0),
5 partition pdef values(default));
SQL> create index i1 on t1 (col1) (more...)
I recently had to change the db_unique_name of a database to make it jive with our typical database/DataGuard naming policy of appending the datacenter location. For the sake of this post let's say it was changed from ORCL to ORCL_NYC, since this database is in our fictional New York City datacenter.
I did a quick set of tests and thought I'd share the findings to save anyone any unpleasant surprises. Here are the things to (more...)
Readers of an earlier post on this blog
will know about my latest forays into the world of Direct NFS. Part of that means stumbling over configuration hiccups or slamming into brick walls when you find new bugs.
To quickly re-set the table, my organization purchased the Oracle ZFS Storage Appliance (ZFSSA) 7420
. Oracle sold us on the Infiniband connectivity as a way to make a possible future transition to Exadata easier. However the pre-sales (more...)
When I sat down at my desk yesterday morning I was greeted with some disturbing email alerts notifying me that one of the NFS mounts on my standby database host was full. This was the NFS mount that held an image copy of my database that is updated daily from an incremental backup. The concept and an example can be found in the documentation
. With a 25Tb database, waiting to restore from backups is not (more...)
My friend asked me to check about error in alert log file - "dispatcher 'D000' encountered error getting listening address
". After checked, I found.
Wed Feb 12 09:46:27 2014
dispatcher 'D000' encountered error getting listening address
Wed Feb 12 09:46:27 2014
found dead dispatcher 'D000', pid = (17, 154)
I checked trace file about d000 processed.
Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_d000_31988.trc
Oracle Database 11g Release 22.214.171.124.0 - 64bit Production
Today, while tuning a fairly complex query experiencing wrong cardinality estimates, I noticed something I was not aware of. Hence, I thought to write this short post to illustrate how to reproduce the problem I experienced…
- Create the test table (notice the correlation between the data of the two columns):
SQL> CREATE TABLE t
3 SELECT mod(rownum,50) AS n1, mod(rownum,50) AS n2
4 FROM dual
5 CONNECT BY level < = 1000;
Sometimes a simple question turns out to be harder than expected.
“Can we see if a particular SQL execution in AWR used a baselined plan?”
Initial thoughts might be:
Q: Does DBMS_XPLAN.DISPLAY_AWR tell us this?
A: Apparently not. See below. This question could also be rephrased as two other possible questions:
Q:Isn’t there a column on DBA_HIST_SQLSTAT which tell us this?
A: No. You’d think there should be. There is a SQL_PROFILE column. (more...)
On Oracle Database, DBAs can check broken job for Oracle Job (dbms_job) at *_JOBS.BROKEN column. Anyway, DBAs have changed from DBMS_JOB to DBMS_SCHEDULER. So, I was curious How to check broken job for Oracle Scheduler (DBMS_SCHEDULER)?
After found out... DBAs can check on *_SCHEDULER_JOBS.STATE
|Current state of the job:|
When does Oracle Scheduler change STATE to be BROKEN?
This post is more of a note for myself and might be helpful to few other. Assuming db is 11gR2 and baselines/spm is used. When a new query is introduced in db, it might be that it runs with the good plan, but sometimes it picks up wrong plan. It could be that Index Range [&hellip
At March I will be presenting my very successful seminar “Mastering Backup and Recovery” in some countries of Latin America for the very first time. Thank you Panama, Chile and Brazil OUGs for inviting me to your amazing countries! Please, use the following links for registration and also to find more information about the seminar: […]
Hi All, On February 4, 2014 at 9:30 am PT I will be talking on the next OTN Virtual Developer Day about Oracle VM and Oracle Database. Come and discover the answers for the following questions: Does an Oracle Database perform well on a virtualized environment? What virtualization technology is more stable and […]
You may have previously seen a short post I did on a SQL statement to identify which statements are using dynamic sampling.
If not, quick recap:
SELECT p.sql_id, t.val
FROM v$sql_plan p
, xmltable('for $i in /other_xml/info
where $i/@type eq "dynamic_sampling"
columns attr (more...)
A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post
includes links to video tutorials explaining what the tool is about.As usual the latest version can be downloaded here.
The new version comes with numerous improvements and (more...)
When the optimizer has to estimate the data volume (the BYTES
column in the plan output), it usually bases this information on the column statistics
, if applicable and available (think of complex expressions).However, whenever there is a VIEW operator
in an execution plan, that represents an unmerged view, the optimizer obviously "loses" this information and starts applying defaults that are based on the column definition
.Depending on the actual content of the columns (more...)
Yesterday I was creating a new Oracle 126.96.36.199 database from a copy of datafiles and archivelogs taken from our standby. I was sure to include archivelogs from just prior to well after the span of the datafile backup time. I had created a new controlfile, gotten everything (more...)
The aim of this post isn’t to explain what the “exadata mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only think I would like to add is that the “exadata mode” is available (more...)
If you tired from the android emulator speed you may have read Chris Muir’s article ADF Mobile: Avoiding the Android Emulator with AndroVM.
In the meantime the successor of AndroVM is GENYMOTION.
If you have configured the port forwarding section from the AndroVM or use GENYMOTION, then you have to (more...)
The values provided by the “parse count (total)” and “session cursor cache hits” statistics are subject to several bugs. And, what’s worse, for years Oracle didn’t care to fix it. This is my impression, at least.
Then, when few weeks ago I read in the Oracle Support note 13837105.8 (more...)