Anyone who has used Oracle for a while will be familiar with the Parent/Child locking "issue" when it comes to tables and indexes on foreign keys. For many years you’d hear people crying "bug" etc but thankfully most now know the reason, and accept it as sensible behaviour.
But lets take a look at a slight variation on that theme.
Lets start with a table called "LOC" which will be our parent table in (more...)
I noticed from Debra Lilley's blog post
that there are some UKOUG elections
at the moment, with voting closing on 1st September 2014.
Although not an active member or supporter of UKOUG any more (at least partly because I'm based in Singapore!), I've had a pretty long association with the user group and a lot of my friends have been involved over the years, so I still take an interest in what's going on (more...)
I’m sharing this in the hope of saving someone from an unwelcome surprise.
I recent upgraded an Exadata system from 126.96.36.199.1 to 188.8.131.52.1. Apart from what turns out to be a known bug that resulted in the patching of the InfiniBand switches “failing”, it all seemed to go without a snag. That’s until I decided to do some node failure testing…
Having forced a node (more...)
by Vlado Barun
This solution has buffers=1 for the first execution, and buffers=0 for the second execution (using results cache as Timo Raitalaakso demonstrated previously).
The solution uses the query rewrite feature to retrieve the rows from an index on a materialized view. Since the index has blevel=0 and leaf_blocks=1, the execution plans shows buffers=1.
1. I do NOT consider this solution appropriate for PRODUCTION environments. It is too narrow and complex (more...)
by Kim Berg Hansen
Anything goes that is not explicitly disallowed? Okay, then let’s get down to three buffer gets ;-)
As a DBA I grant rights to HR:
grant on commit refresh to hr
And then in HR I create some materialized views and logs:
create materialized view log on employees with rowid, commit scn
(employee_id, job_id, first_name, last_name, department_id)
including new values;
create materialized view log on departments with rowid, commit scn
I’ve been struggling to find time to have any interaction with the Oracle community for the last couple of months – partly due to workload, partly due to family matters and (okay, I’ll admit it) I really did have a few days’ holiday this month. So making my comeback with a bang – here’s a quick comment about the 12cR2 in-memory feature, and how it didn’t quite live up to my expectation; but it’s also a comment (more...)
As I mentioned in my last post, I've been working with an exciting technology called Delphix
for database and ERP applications recently.
you can reduce the pain points involved with performing a traditional recovery operation for Oracle and other database platforms. Now when it comes to large ERP systems such as SAP and Oracle E-Business Suite, the challenge of backup and recovery and well as system and application refresh operations and (more...)
Just a quick post on a new Exadata feature called Zone Maps. They’re similar to storage indexes on Exadata, but with more control (you can define the columns and how the data is refreshed for example). People have complained for years that storage indexes provided no control mechanisms, but now we have a way to exert our God given rights as DBA’s to control yet another aspect of the database. Here’s a link to the (more...)
by Timo Raitalaakso
Here is my approach to the problem. I will be using the “Anything not explicitly disallowed by the problem statement is also permissible.” approach.
One key to query performance is the correct organization of the rows according to the access path. One way to get performance is to use indexing.
create index ej_idx on hr.employees(job_id,department_id) compress 2;
create index e_idx on hr.employees(first_name,last_name,department_id);
create unique index ejh_idx on hr.employees(employee_id,department_id);
by Craig Martin
Blasted.. after looking at the solution from Jimmy Brock, I see that adding one more hint to use index fast full scan to get to the Jobs row gets me down to 10 buffers. Great addition! So my newest solution is:
select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */ distinct l.location_id, l.city
left outer join jobs j
on j.job_title = 'President'
left outer (more...)
Normally, I don’t mind errors when I’m working with Oracle GoldenGate (OGG); I actually like getting errors, keeps me on my toes and gives me something to solve. Clients on the other hand do not like errors…LOL. Solving errors in OGG is normally pretty straight forward with the help of the documentation. Although today I can almost disagree with the docs.
Today, as I’ve been working on implementing a solution with OGG 11.1.x (more...)
Recently I have been working with an exciting technology called Delphix.
What is Delphix you might ask? Well Delphix is a data virtualization appliance that provides you with the ability to quickly clone, refresh and provision virtualized copies of database and application environments in record time compared to traditional copy, clone and refresh operations for Oracle and SQL Server as well as for SAP and Oracle EBS application ERP systems.
In addition we (more...)
by Jimmy Brock
Modified data set “curr_job” to return anyone that currently has the job_title of ‘President’ – even if his or her name is not Steven King.
Modify data set “job_hist” to return any employee that has ever had the job_id ‘AD_PRES’.
Buffer gets are now 9.
jbrock@ORA1> set serveroutput off
jbrock@ORA1> alter session set statistics_level=all ;
jbrock@ORA1> col version for a20
jbrock@ORA1> select distinct version from product_component_version;
by Craig Martin
My original entry is here. In the comments, Kim Berg Hansen made a great point that there was a flaw in my logic where if there were no President, the correct results wouldn’t be returned. This is an excellent catch, and a perfect example of why all code should be peer reviewed! While looking back through the code to fix this, I found a way to get the buffer count down to (more...)
by Kim Berg Hansen
I just keep playing with this inspired by the other great entries :-)
Emmanuel Humblot posted an entry that used as few as 12 buffer gets – very good. His entry also reminded me to remember to look at the constraints of the tables in order to see if some assumptions can safely be made that enables more efficient code – like for example that it can safely be assumed that (more...)
What is SQL Tuning Health Check? The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs. It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed. The script generates an [...]
The post SQL Tuning Health Check (SQLHC) appeared first on Oracle DBA Scripts and Articles (Montreal).
People ask me to justify use of Delphix. I can understand. Delphix is pretty new and often, most of my friends who are DBAs respond with “I can copy a database, so what, I can do it a little faster with Delphix.” Well that’s missing the whole boat. The question won’t be why you should use Delphix but “can you justify working without Delphix?”
I see Delphix as amazingly positioned at nexus of data (more...)
Heads up to all the folks running 184.108.40.206 and above if you're using function-based indexes! There's a new Oracle bug 19384287
. I'll fill you in with a complete post over at Toad World
Oracle introduced this view in version 10. It looks like this in version 11:
SQL> desc dba_feature_usage_statistics
Name Null? Type
-------------------------- -------- ------------------
DBID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
VERSION NOT NULL VARCHAR2(17)
DETECTED_USAGES NOT NULL NUMBER
TOTAL_SAMPLES NOT NULL NUMBER
As its name suggests, it allows you to see (more...)