Don’t forget Oracle Midlands Event #17 next week!
I don’t know about you, but I would like to know what Oracle have been up to. There has been very little in the way of publicity from them recently…
If you live near, please show your support for the event and come along. It’s free thanks to the sponsorship by RedStackTech.
Oracle Midlands : Event #17 was first posted on September 22, 2016 at 7:41 am.
We had question in the OpenWorld panel about why queries on date columns are “always slow”. Well….they aren’t but here’s a common cause of that misconception.
Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is a date).
SQL> create table t as 2 select * 3 from dba_objects; Table created. SQL> SQL> create index t_ix on t ( created ); Index created.
The problems start when (more...)
Just a stupid error. Posting it so that someone else googling for the same thing can get a clue.
An ASM instance running with default parameters (no pfile, no spfile). Updated spfile for the instance with asmcmd spset command and bounced crs. After reboot also, it still wasn’t using spfile. Got puzzled and checked GPnP settings again. All looked good. Then in alert log came across this
ERROR: SPFile in diskgroup <> does not match (more...)
In response to a complaint by Lukas Eder about having to educate people in 2016 that there is no (performance) difference between count(*) and count(1), Nuno asked me to blog about my claim that this non-difference is a good educational example on at least three different counts.
One thing I won’t count in the list of three is an actual demonstration (more...)
Along with the deep learning I’ve been allowed to do about data virtualization, I’ve learned a great deal about Test Data Management. Since doing so, I’ve started to do some informal surveys of the DBAs I run into and ask them, “How do you get data to your testers so that they can perform tests?” “How do you deliver code to different environments?”
At the end of the last installment we had seen a test case that caused Oracle to add a couple of redundant new extents to a LOB segment after one process deleted 3,000 LOBs and another four concurrent processes inserted 750 LOBs each a few minutes later (after the undo retention period had elapsed). To add confusion the LOBINDEX seemed to show that all the “reusable” chunks had been removed from the index which suggests (more...)
I was googling for things related to NOLOGGING operations and found this useful post on the Ask Tom web site: url
There is a nice table in the post that shows when insert operations generate redo log activity. But it isn’t formatted very well so I thought I would format the table here so it lines up better.
Table Mode Insert Mode ArchiveLog mode result ----------- ------------- ----------------- ----------- LOGGING APPEND ARCHIVE LOG redo generated (more...)
I received this error message when I was playing around with Extended Policy Framework in Oracle Clusterware 12c.
Policy-managed environment in Oracle clusterware 12c offers the capability to create policies to configure pools in accordance with business needs or application demand, so that pools provide the right service at the right time. Typically, administrators create multiple policies to reflect differences in requirements for the cluster based on business needs or demand, or based on (more...)
I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s). Their database was 11g, but I thought it would worth showing off some 12c features that would let you do this process with minimal disruption.
First we’ll create our table, with a standard primary key index
I am working on a production issue. I do not think that we have a database issue but I am graphing some performance metrics to make sure. I made a new graph in my PythonDBAGraphs program.
It shows the average number of active sessions for a given minute. It prompts you for start and stop date and time. It works best with a relatively small interval or the graph gets too busy. Red is sessions active (more...)
In a previous post, I noted that the parameter db_securefile changes from PERMITTED in 11g, to PREFERRED in 12c. Jonathan Lewis raised the interesting question of what happens when you upgrade to 12c from 11g, where a partitioned table may already have some basicfile LOBs defined. This blog post explores that.
We’ll start with “PERMITTED” mode, and create a table with two partitions. We can see that at table level and partition (more...)
Last year on September 1, the AskTom site was resurrected under Apex 5 with myself and Chris Saxon manning the fort to deliver as much value to the Oracle community as the esteemed previous custodian of the site did in the many years preceding us.
In the last year, we have
- answered ~2,500 questions
- taken and followed up on ~3000 reviews
It’s great fun and very rewarding working on the AskTom site. We get (more...)
Prompted by a recent OTN posting I’ve dug out from my library the following demonstration of an anomalty with the parallel_index() hint. This note is a warning about how little we understand hints and what they’re supposed to mean, and how we can be caught out by an upgrade. We’ll start with a data set which, to match a comment made in the origina posting rather than being a necessity for the demonstration, has an (more...)
Don’t forget that there’s lot of great content still to come your way throughout the rest of the year.
And for a change of pace… I’ll be doing a keynote at UKOUG ! (I’m the person in the list below who isn’t a senior director or vice president )
Hope to see you at some or all of these events.
(Disclamer: any opinions expressed here are fully my own and not representative of my employer)
photo by alvaroprieto (cc 2.0)
Super excited to be working at Amazon on my passion which is performance data visualization and database monitoring. Suffice it to say this is the most excited I’ve been about work in my career and I’ve had ample opportunity to work on database performance in the past such as at (more...)
Just a short post to announce that I’ll be presenting an User Group Forum session. Thanks ODTUG!
“In this session learn about the new Java-based command line interface that takes advantage of Oracle SQL Developer’s scripting engine. It delivers a modern command line interface that is backward compatible with SQL*Plus but also introduces new commands and features that have been missing for a long (more...)
In Oracle 11G, DG Broker needed to be told whether the standby database was physical or logical. The command to add database in 11G was something like this:
ADD DATABASE <DB Unique Name> AS CONNECT IDENTIFIER is <TNS Descriptor>
MAINTAINED AS [PHYSICAL|LOGICAL];
Oracle 12c is definitely smarter and can figure it out on its own. The “ADD DATABASE” syntax is simpler:
DGMGRL for 64-bit Windows: Version 22.214.171.124.0 – 64bit Production
I wrote a post a while back showing how one of the options with an Oracle Text index is to keep that index perfectly updated with its underlying table. (Long termers with Oracle may know that this is a relatively recent addition to Text indexes, which historically were updated with calls to CTXSYS.CTX_DDL.SYNC_INDEX, either on demand or via the scheduler).
I also said that there is “no such thing as a free (more...)
Synthetic full backup is a method for combining incremental backup into a full backup. It is not a new thing, many backup utilities can do that with file system backups. It is also well described:
However, there is only one backup suite that can do that with Oracle RDBMS: Commvault 11.
How is it done? Well, Commvault is a very well documented tool, the documentation is here: