What it used to be
After Oracle 9.2, there has been a significant changes in redo behavior. Before 10g, Oracle used to have a single log buffer for writing redo information which eventually gets written to online redo logs in round robin fashion. Of course if your database is (more...)
One of the parameter we configure in physical standby setup is about how much amount of time LGWR on primary should wait for physical standby to respond.
When changes happens on primary side, those redo changes are shipped on physical standby database. If physical standby database is down or if (more...)
Recently I ended up screwing up my Apex application. I was trying to enable SSL (making URL https) for security reason and somehow the configuration did not work on production.
At this stage I was trying to get back my APEX application. Unfortunately nothing seems to be working.
So I (more...)
I have seen many times DBAs are getting confused with Static registration and dynamic registration of services/instance with listener.
As far back I remember, dynamic registration of services was introduced in Oracle 9i.
In this article, I am going to cover everything about static and dynamic service/instance registration with listener (more...)
In my previous posts we have seen fixing plans by applying baselines and profiles.
For profiles we saw in details how to generate the same using SQL hints.
This article is about another feature of Oracle 11.2, called SQL Patch.
I am not sure if this is supported by Oracle, but in days to come they will make this official.
This is a kind of silver bullet for doing minor changes in the plan which is difficult to get it done using baselines and profiles.
What is SQL Patch:
A SQL patch is a SQL manageability object that can (more...)
One of the many challenges that we face in production environment is make changes to big tables.
If you consider a case of any OLTP systems, its easy to have tables whose size is beyond 10G.
This again depends on the nature of the database and kind of transactions happening on the table.
So lets consider a case where you have a big table which is also a very hot table having very high number of transactions / sec and something that continues 24X7.
Its difficult to take downtime on such tables and making DDL changes to such tables (more...)
We have seen Materialized view Concepts – Discussion Series 1 and Materialized view Concepts – Discussion Series 2.
This is the third article about Materialized views.
In this article we are going to discuss “How fast refresh works ?”
How fast refresh works ?
As we know in case of fast refresh only the changes that happened on master site (or master table) will be applied to MView on target site.
So the changes that happens on master table will be stored in MLOG table created on top of master table.
This is more efficient way than doing complete refresh.
We have seen Discussion Series 1 of materialized view concepts and we know how to create materialized view and also what each clause of Mview creation mean.
In this article we will see all backend tables that can be accessed to check the details of materialized view.
We will begin with identifying materialized view
DBA_SNAPSHOTS (On MView site)
Most important table for checking MView info is DBA_SNAPSHOTS.
We need to query this table on snapshot site (where MView is created)
SQL>select name, table_name, MASTER, MASTER_LINK, REFRESH_METHOD, UPDATABLE , LAST_REFRESH, STATUS, PREBUILT, REFRESH_MODE from dba_snapshots where name = 'T_REP';
Materialized view concept: Why do we need materialized view?
Materialized views are nothing but views created on the base table and having data which is extracted from the base table.
How is materialized view different from the normal view.
Difference # 1:
Normal view does not contain data. It is just a transparent layer on the top of base
Materialized view contains data and additional space is required to create materialized view.
Difference # 2:
To use normal view, a user needs to provide the view name in the query
To use materialized view user does not need (more...)
In part 1 of this series http://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/ I showed you how to fix a SQL plan by picking good plan hints from other database and applying those hints/plan to your existing bad query
In part 2 of this series http://avdeo.com/2012/07/04/fixing-sql-plans-the-hard-way-part-2/ I showed you how to fix a SQL plan even if you don’t have another database having better plan. I explained you about hint you can use in the query and execute the query to get better plan. Then we can use the new plan generated and apply to the original SQL.
In this article we can go (more...)