What are Materialized View & Materialized View Logs?

A materialized view is a database object that contains the results of a query same like a table. They are local copies of data located on remote site. Also, used to create summary tables based on aggregations of a table's data, which store data based on remote tables are also, referred as Snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local server as read-only data.If we want to update the local copies of data, we have to use the Advanced Replication features in oracle. We can select data from a materialized view same as local table or view.

Materialized Views refer local data from Materialized View Logs (Snapshot Logs), which need to create on Local tables.

CREATE MATERIALIZED VIEW LOG ON emp;

Primary Key Materialized Views which are created with "WITH PRIMARY KEY" clause.

CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE 
NEXT  SYSDATE + 1/48
WITH PRIMARY KEY 
AS SELECT * FROM emp@remote;

Rowid Materialized Views which are created with "ROWID" clause.

CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID 
AS SELECT * FROM emp@remote;

Subquery Materialized Views which are created by using subquery.

CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote e
WHERE EXISTS
     (SELECT * FROM dept@remote d
     WHERE e.deptid = d.deptid)

For data warehousing purposes, materialized views are the aggregate views, single-table aggregate views and join views.

CREATE MATERIALIZED VIEW  mv_empdept_dwh
AS SELECT * FROM emp@remote a, dept@remote b
WHERE a.deptid = b.deptid
and a.deptid < 1000;

COMPLETE refreshes by recalculating the defining query of the materialized view.

FAST refreshes by incrementally applying changes to the materialized view as per materialized view logs. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient.

FORCE Attempts a FAST refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST and COMPLETE.

NULLS LAST & NULLS FIRST in Select Statement.

In modern DBMS like Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB , H2, etc. you can specify NULLS LAST or NULLS FIRST in a select statement.

Used when you required sorted data in output having required column contains NULL values in it. It shows NULL values per below clauses.

NULLS LAST -  To sort data in which NULLS are showing in the last records.
NULLS FIRST - To sort data to the beginning - Default clause of a sql select statement.

select * from emp order by emp_name DESC NULLS LAST; 

select * from emp order by emp_name DESC NULLS FIRST; 

What are Global Temporary Tables in Oracle?

Global Temporary Tables, introduced by Oracle since 8i and mainly they are the private tables which stored data inserted by a session only and only accessed by that session itself.
Widely used in large applications make extensive use of temporary data storage which preserves data for the whole session or just for current transaction.

CREATE GLOBAL TEMPORARY TABLE GTT_TEMP  
(
AAAA  Number,
BBBB  Varchar2(10),
CCCC  Number
)
ON COMMIT [DELETE | PRESERVE] ROWS;

ON COMMIT DELETE ROWS : It sets the life of the data contained by the table to a single TRANSACTION.
The data is automatically flushed away after each COMMIT/ROLLBACK is executed - such tables are Transaction-specific Temporary tables.

ON COMMIT PRESERVE ROWS: It restricts the life of the data to a single SESSION.
Data is preserved in the table for a session only - such tables are Session-specific Temporary tables.

Below are the major features due to that it is used in large applications:

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Triggers can alo be associated with such tables.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• Table Statistics of temporary tables are common to all sessions.
• Foreign key constraints are not applicable in case of Temporary tables.
• Global Temporary tables cannot be partitioned.
• Data in Global Temporary Tables is stored in temp segments in the temp tablespace only.
• In Oracle 8i,9i & 10g, TABLESPACE cannot be defined for GTT i.e. GTT segments were created in user’s default tablespace. 
• After Oracle 11g, GTT segments can be created on other Temporary Tablespaces too, it must be create on a TEMP Tablespace not any other.
• Use of LOB_STORAGE_CLAUSE, LOGGING/ NOLOGGING, MONITORING/ NOMONITORING, LOB_INDEX_CLAUSE is restricted in GTT definition. 

PIVOT & UNPIVOT functions in Oracle 11g

PIVOT Function is used in oracle 11g to produce query output in matrix style same as what we are doing in xls or xlsx.

in oracle 10g,

select deptid,jobid,sum(sal) from emp
group by deptid,jobid
order by deptno,jobid;  

for to get ouput of a query like in Excel Pivot funcationality.

select deptid, 
sum(decode(jobid, 101, sal)) "Analyst",
sum(decode(jobid, 102, sal)) "Programmer",
sum(decode(jobid, 103, sal)) "DBA",
sum(decode(jobid, 104, sal)) "Developer"
from emp
group by deptid
order by deptid;

Using the same output in oracle 11g with below query.

select * from(select deptid,jobid,sal from emp)
PIVOT (sum(sal) FOR jobid 
in (
101 "Analyst",
102, "Programmer",
103, "DBA",
104, "Developer"
))order by deptid;

same way we can achieve the first query output using UNPIVOT function.

select * from(select deptid,jobid,sal from emp)
UNPIVOT (sum(sal) FOR jobid 
in (
101, "Analyst",
102, "Programmer",
103, "DBA",
104, "Developer",
))order by deptid;

How to increase performance of PL/SQL code using IN/OUT Parameters and NOCOPY directive?

While wring PL/SQL code, keep in mind that 

• if I need any calculations, create a function. 
• if any DB operations which does not return any values from it, create a procedure.

Following ways we can use the parameters passed and returned from functions and procedures:

• All IN parameters of procedure are passed by pointer. Variable is not copied when program block is called.However PL/SQL block is not allowed to modify the variable value.
• All OUT and IN/OUT parameters are passed by value. Variable is copied when program block is called, so the pl/sql code can modify a copy of the variable.When pl/sql block completes without error, the original variable is overwritten with the modified copy of variable. If the program block raises exception, the original value remains unchanged.
• The OUT and IN/OUT parameters can be passed by reference if we use the NOCOPY directive for them.However its not available for function return value.

A function that returns variable as pass by value.

CREATE OR REPLACE FUNCTION func_VarIN(p1 IN VARCHAR2) RETURN VARCHAR2 IS

BEGIN

  RETURN p1;

END;

/

An empty procedure using the IN/OUT parameter.

CREATE OR REPLACE PROCEDURE prc_VarINOUT(p2 IN OUT VARCHAR2) IS

BEGIN

  NULL;

END;

/

An empty procedure using IN/OUT parameter with NOCOPY directive.

CREATE OR REPLACE PROCEDURE prc_VarINOUT_NOCOPY(p3 IN OUT NOCOPY VARCHAR2) IS
BEGIN
  NULL;
END;
/

PL/SQL Code Observations: 

• Function calls perform similar to procedure calls with IN/OUT parameters.
• NOCOPY directive reduces the copy overhead and causes the call to be twice as fast then the function or regular procedure.
• using NOCOPY, bigger value of variable gives bigger boost on performance.

Using of procedures with NOCOPY rather than functions, it pefroms really good - upto 20 -25 times faster and gives us good practices for PL/SQL code clarity.

Rsync Command to Sync files in Unix/Linux.

Rsync (Remote Sync) command is used for copying and synchronizing files and directories remotely as well as locally in Linux/Unix systems. With the help of rsync command you can copy and synchronize data remotely and locally across directories, across disks and networks, perform data backups and mirroring between two Linux/Unix machines.

• It supports copying links, devices, owners, groups and permissions.
• It’s faster than scp (Secure Copy) because rsync uses remote-update protocol which allows us to transfer just the differences between two sets of files. 
• First time, it copies the whole content of a file or a directory from source to destination. From next time. it copies only the changed blocks and bytes to the destination.
• It consumes less bandwidth as it uses compression and decompression method while sending and receiving data both ends.
• It copy files from locally, to/from another host over any remote shell, or to/from a remote rsync daemon.
• Its widely used for backups and mirroring and as an improved copy command for everyday use.
• It can use exclude and exclude-from options similar to GNU tar.
• It does not require root privileges.
• It supports anonymous or authenticated rsync servers which is ideal for mirroring.

Syntax

Local use:
rsync [OPTION...] SRC... [DEST]

Access via remote shell (PULL):
rsync [OPTION...] [USER@]HOST:SRC... [DEST]

Access via remote shell (PUSH):
rsync [OPTION...] SRC... [USER@]HOST:DEST

Access via rsync daemon (PULL):
rsync [OPTION...] [USER@]HOST::SRC... [DEST]
rsync [OPTION...] rsync://[USER@]HOST[:PORT]/SRC... [DEST]

Access via rsync daemon (PUSH):
rsync [OPTION...] SRC... [USER@]HOST::DEST
rsync [OPTION...] SRC... rsync://[USER@]HOST[:PORT]/DEST

Common Examples

Copy/Sync Files and Directory Locally

rsync -zvh aaa.tar /tmp/aaa/ destination_folder

destination_folder is current folder by default.

Copy a Directory from Local Server to a Remote Server

rsync -avz anand_files/ anand@192.168.0.1:/home/anand_files/
rsync -avzh anand@192.168.0.1:/home/anand_file /tmp/anand_files

Copy a File from a Remote Server to a Local Server with SSH

rsync -avzhe ssh anand@192.168.0.1:/anand/A.log /tmp/anand/
rsync -avzhe ssh /tmp/a.log anand@192.168.0.1:/backup/anand/

Show Progress While Transferring Data with rsync

rsync -avzhe ssh --progress /home/soft anand@192.168.0.1:/anand/soft

Use of –include and –exclude Options

It excludes/includes particular char/string file names while copying.

rsync -avze ssh --include 'RAJ*' --exclude '*' anand@192.168.0.1:/anand/unix/ /root/anand/unix/

Set the Max/Min Size of Files to be Transferred

rsync -avzhe ssh --min-size='5k' --max-size='2048k' /anand/unix/ anand@192.168.0.1:/anand/unix

Automatically Delete source Files after successful Transfer

rsync --remove-source-files -zvh aaa.tar /tmp/anand/

Set Bandwidth Limit (KBPS) and Transfer File

rsync --bwlimit=1024 -avzhe ssh  /anand/unix/  anand@192.168.0.100:/anand/unix/temp/

What is difference between Row Chaning and Row Migration and how to resolve it?

Row migration & Row chaining mainly depends on Orale DB Block Size will be 8K, will ranging from 2K to 32K, which will depend on OS. It occurs at a time when table row data is INSERTED or UPDATED. 

Row migration is typically caused by an UPDATE & Row chaining is typically caused by an INSERT.

Row Migration:

When we performing any Update statement on a table row, if the content of new data is not able to fit in one database block. It means in update of a larger content, overall row length increases and free space of database block is already full.

It replaces content of original block to a pointer address - rowid and entire row is moved to the rowid defined in original block.

Row Migration is caused due to lesser value of PCTFREE for the table.There is not enough place in the BLOCK for UPDATES.

To avoid row migration, table should have PCTFREE value to be set in such a way that there is some enough space within the BLOCKS for UPDATES.

select table_name,chain_cnt,pct_free,pct_used from dba_tables where table_name = table_name ;

alter table table_name pctfree 30;

Row Chaining:

When a row data is too large to fit into a single database block. For example, if your database is of 8K db block size, and we need to insert a row of 20K into it. At this time, Oracle will use 3 db blocks and store the row in pieces.

Below are the reasons at a time when row chaining is occurs:

• Tables having rowsize exceeds the db blocksize.

• Tables with LONG and LONG RAW or any other LOB columns.

• Tables with more then 255 columns.

Oracle internally stores the data in multiple blocks, one block stores all forwarding address - rowids and the data on another two or more blocks.

Row Chaining is UNAVOIDABLE but Row Migration can be MANAGED and resolved through reorganization.Whenever a row migration or row chaining will occur, I/O performance will be decrease as Oracle has to scan more than one data block to retrieve data for the row.

We can identify Row Changing and Row Migration by querying table CHAINED_ROWS, USER_TABLES and V$SYSSTAT or in an ADDM/Statspack report and using ANALYZE command.

Run below command on user to create a system table named - CHAINED_ROWS.

@$ORCL_HOME\ADMIN\utlchain.sql

select owner_name,table_name,count(head_rowid) row_count from CHAINED_ROWS where table_name = table_name GROUP BY owner_name,table_name;

SELECT owner, table_name, chain_cnt , initial_extent, num_rows, blocks, empty_blocks, avg_space, avg_row_len 
FROM user_tables WHERE chain_cnt > 0 and table_name = table_name;

SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';

analyze table table_name list chained rows;

select count(*) from chained_rows where table_name = table_name ;

To eliminate row migration / row chaining .

alter table table_name MOVE;

This statement does is to move the table to a new segment. Oracle reads the data from current table and rebuilds the table completely. During this reorganization all migrated rows get fixed.

This causes indexes on the table to be marked UNUSABLE, because it changes the rowids of rows in the table. It generates ORA-01502 error, before REBUILDING of a table .

Do remember that you have to REBUILD all your indexes and repopulate table/index statistics in DB before using the table.

We can perform Import/Export activity as an approach to eliminate the migrated rows from a table.

Please explain how WITH Clause working in a sql query?

An inline view or a query uses subquery in it.
Subquery execution done more efficiently rather by using the query repetadly.
On case by case the performance varies with WITH clause in a sql query.

Below example shows how to reduce repetition of query and simplify complex SQL statements.

SELECT e.ename AS employee_name,
       dc.d_count AS emp_dept_count
FROM   emp e,
       (SELECT deptid, COUNT(*) AS d_count
        FROM   emp
        GROUP BY deptid) dc
WHERE  e.deptid = dc.deptid;

Query will be like as below by using a WITH clause.

WITH d_count AS (
  SELECT deptid, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptid)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       d_count dc
WHERE  e.deptid = dc.deptid;

This way we can make multiple references to the subquery more efficient.

Sometime,WITH clause can be used for a subquery to use as a global temporary table or the query to be use as inline query.

What are Private procedure / functions in a Package ?

A function or procedure in a package which is not declared in package specification, its called as private function / procedure.

Scope of private function /procedure is very limited - on the package itself only.

While in declaring the private procedure/function in package, need to take care that it must be declare first in package declaration part of the package.

create or replace package pack_p1 is
    procedure proc_1;
end pack_p1;

create or replace package body pack_p1 is

    procedure test_proc_1 is
    begin
        dbms_output.put_line('package pack_p1 private procedure');
    end test_proc_1;

    function func_1 is
    begin
        dbms_output.put_line('package pack_p1 private function');
    end func_1;

    procedure proc_1 is
    begin
        dbms_output.put_line('package pack_p1 - proc 1');
        test_proc_1;
    end proc_1;

end pack_p1;

What to do I have to do when query is too slow, which was earlier running fine?

You need to know why its became slow suddenly. Need to check through following ways, rewrite the query or applying oracle hints on query or using parallellization. So the first step of yours should be to investigate the root cause.

Widely used tools are:
- dbms_profiler
- Explain plan
- SQL*Trace / TKPROF
- Statspack

dbms_profiler:

Use dbms_profiler, if you want to know where time is being spent in PL/SQL code.
Statspack is also a good tool if you are a DBA and you want to check query performance for entire database.

For a single query , explain plan & SQL*Trace & TKPROF are useful tools for query execution.

Explain plan:

in SQL*Plus you have to type:

explain plan for ;
select * from table(dbms_xplan.display);

if you are using older version of plan_table, you have to run the script $ORACLE_HOME\RDBMS\ADMIN\utlxplan.sql.

To get plan table output on serial or parallel execution using below scripts:
$ORACLE_HOME\RDBMS\ADMIN\utlxpls.sql - plan table output with serial execution.
$ORACLE_HOME\RDBMS\ADMIN\utlxplp.sql - plan table output with parallel execution.

The output is shows you what the query optimzer expects.It gives us an idea on choosing correct access path.

SQL*Trace/TKPROF:

Open a new SQL*Plus:
- open a new db session
- alter session set sql_trace true;
- run your query
- disconnect session
- identify your trace file in the server directory as specified in the parameter - user_dump_dest
select value from v$parameter where name = 'user_dump_dest'; 
OR 
Show parameter user_dump_dest;
- on your operating system: tkprof query_op.txt
query_op.txt will now give you valuable information on what has actually happened.

By comparing the output from explain plan with the output from TKPROF, We can able to identify problematic areas of the query.

What actions-factors you need to take care while creating index?

Creation of index on table depends on size of table, volume of data.
If table size is large and we need only few data while selecting data then create index. How frequently column is used in where condition, its also important.

if table has already a primary key, then create a unique key for the required column. Creating a primary key or unique key automatically create unique index on the column.

It is also not advised to create so many indexes as it impacts on query performance of DML statments as every DML statment need to go through with various index and table segments simultaneously.

We can create index or rebulid online also. It enables us to update base tables on the sametime.

creating any index parallelly is not supported while creating and rebuilding index online.

CREATE INDEX idx_emp_mgr ON employee (empid,mgrid) ONLINE;

/*+ APPEND */ hint in serial & parallel mode in oracle.

/*+ APPEND */ hint enables the optimizer to use direct-path insert particularly in insert statements in oracle

In serial mode, direct path can be used only if you include the APPEND hint.

Conventional insert is the default in serial mode. 
It means that NO UNDO will be written. 

UNDO is backed by REDO, so the REDO volume will be accordingly reduced.

Direct path insert is the default in parallel mode. 
In parallel mode, conventional insert can be used only if you specify the /*+ NOAPPEND */ hint.
data is appended to the end of the table,rather than using existing space currently allocated to the table, means after HWM of the current block. 

Direct-path inserts are considerably faster than conventional inserts.