MongoDB :-- NoSQL DB

MongoDB University has started certification program . Its been exciting 1st week to complete it 

Oracle Performance Tuning

Below are areas to look for performance Tuning

1) Instance Performance Tuning
2) Oracle Services Performance Tuning
3) sqlnet.ora tuning
4) listemer.ora tuning --> IP Protocol
5) Archive log tuning
6) redo log tuning
7) standby redo log tuning
8) datafile tuning
9) controlfile tuning
10) backup tuning , cold , rman and expdp
11) ASM file location tuning
12) cluster performance tuning
13) table tuning
14) tablespace tuning
15) partition tuning
16) index tuning
17) sga tuning
18) pga tuning
19) flashback tuning
20) undo tuning
21) refering awr , asb, addm and statspace report
22) topas,smitty,nmon tuning
23) disk tuning 

How to read statspack report

1) Wait Events :-- Look for excessive waits and wait times and check for specific problems
2) SQL Ordered by Buffer Gets,Physical Reads and Rows Processed :-- Figure out which SQL statements to tune
3) Instance Activity statistics :-- Compare with baseline report,compute additional statistics
4) Tablespace and File I/O :-- Investigate I/O bottlenecks,identify files and tablespaces with heavy I/O
5) Buffer Pool :-- Identify buffer pool with high I/O
6) Buffer wait statistics :-- Identify buffer type with large waits
7) Enqueue Activity :- Identify the locks which are causing most waits
8) Rollback Segment statistics and Storage :-- Investigate waits for rollback segments
9) Latch Activity,Latch Sleep Breakdown ,Latch Miss Sources :-- Identify latch bottlenacks
10) Non-default init.ora -- Look for init.ora parameter definitions



Look for statspack report in this order

1) Top 5 wait events
2) Load profile
3) Instance Efficiency Hit Ratios
4) Wait Events / Wait Event Histograms
5) Latch Waits
6) Top SQL
7) Instance Activity/Time Model Stats/ O/S Stats
8) File I/O / File Read Histogram / Undo Stats
9) Memory Allocations
10) Undo


Defintion

Top 5 wait events

Load Profile :-- use the Load Profile to verify the validity of comparing a report generated during a problem period with a report generated during a baseline period

Example of Load Profile

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            351,530.67              7,007.37
              Logical reads:              5,449.81                108.64   --Key Metric
              Block changes:              1,042.08                 20.77
             Physical reads:                 37.71                  0.75   --Key Metric
            Physical writes:                134.68                  2.68   --Key Metric
                 User calls:              1,254.72                 25.01
                     Parses:                  4.92                  0.10
                Hard parses:                  0.02                  0.00
                      Sorts:                 15.73                  0.31
                     Logons:                 -0.01                  0.00
                   Executes:                473.73                  9.44   --Key Metric
               Transactions:                 50.17                         --Key Metric

  % Blocks changed per Read:   19.12    Recursive Call %:     4.71
 Rollback per transaction %:    2.24       Rows per Sort:    20.91

No SQL database :-- BaseX

Software Download Location :-- http://basex.org/products/download/all-downloads/

Starting Guide :-- http://www.swennenhuis.nl/basexfordummies/BaseX_for_dummies.pdf

Sep 23rd 

Oracle Wait Class Definition 

1) PX Deq Credit: send blkd

English Defiintion :--  PX  -- Parallel Execution
                                    Deq -- To remove the first available element from queue
                                    Credit  -- Acknowledgment of Some Work
                                    Send  :-- to permit
                                    blkd    :-- blocked

Technical Definition :-- Parallel Execution process wants to send message to slave and its not allowed

Note :-- Any parallel execution process has two sub process . One process say p0 queries the database and send the output to p1 another parallel sub process to send back the output of the query back to client or program

alter table emp parallel (degree 2); // this will enable two parallel process. The parallel process of any table in can be viewed in degree column of user_tables

PX Deq Credit: send blkd and PX Deq Credit: need buffer are same . The first one is seem more on localsystems and second one is seen on RAC systems 

There is no general recommendation to reduce this wait times

2) direct path read

English Definition :--  Direct :-- send toward a place or object
                               Path   :--- a course of action
                               Read :-- To anticipate

Technical Definition :-- The direct path read occurs when oracle access datafile into the PGA instead of SGA buffer memory .

This happens under these conditions 

1) Reading a Lob Segment
2) Doing a full-table scan
3) Reading a temp file 

This happen in case when table in query has more degree of parallelism . Because oracle thinks that doing a full table scan is more beneficial than doing index scan 

RDA :-- Remote Diagnostic Agent

Download RDA

Remote Diagnostic Agent (RDA) - RDA Documentation Index (Doc ID 414966.1)

Remote Diagnostic Agent (RDA) - Getting Started (Doc ID 314422.1)

I downloaded the Linux 64-bit version of RDA

Step 1 :-- Unzip for installation
Step 2 :-- Read README_Unix.txt
Step 3 :-- Verify Installation
                 rda.sh -cv
Step 4 :-- Run Setup
                 rda.sh -S
Step 5 :-- Run RDA
                 rda.sh -v 
Step 6 :-- FTP the report and view in web - browser

Installing Mongodb on 64bit windows 7 machine

http://docs.mongodb.org/manual/tutorial/install-mongodb-on-windows/

Active Data Guard and Snapshot Standby Database

Active Data Guard

All query needs to be done on physical standby

In this physical database is open in read only mode . Still archive log gets shipped and applied

Step 1) Check if media recovery is enabled or not

sql > select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

Check for MRP0 process in output

Step 2) Cancel the recovery

SQL> alter database recover managed standby database cancel;

Step 3) Shutdown the database

sql > shutdown immediate

Step 4) Mount the database

sql > startup mount

Step 5) Bring the database in read only mode

SQL> alter database open read only;

SQL> select name,open_mode,database_role from v$database;

Step 6) Resume the media recovery in read-only mode database

SQL> alter database recover managed standby database disconnect from session;

SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

To revert back the active data guard

Step 1) cancel the media recovery

Sql > alter database recover managed standby database cancel;

Step 2) shutdown database

Sql > shutdown immediate

Step 3) start the database in mount mode

Sql > startup mount

Step 4) Enable the recovery process

sql > alter database recover managed standby database disconnect from session ;

Step 5) Check the recovery mode

sql > select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

Step 6) Do alter system switch in primary and query about log applied on physical

SQL> select max(sequence#) from v$archived_log where applied='YES';

Step 7) Check the open_mode of the database

Sql > select name,open_mode,database_role from v$database

Snapshot database

Execute the below query in physical standby database

step 1) Cancel the managed recovery

Sql > alter database recover managed standby database cancel;

Step 2) shutdown the database

sql > shutdown immediate

Step 3) startup the database in mount mode

sql > startup mount

Step 4) Check flashback_on parameter from v$database

Sql > select flashback_on from v$database;

Value if no not an issue

Step 5) Execute the below query to convert physical standby to snapshot database

SQL> alter database convert to snapshot standby;

SQL> alter database open;

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

RESTORE POINT ONLY

1 row selected.

SQL>

SQL> select name,open_mode,database_role from v$database;

NAME                           OPEN_MODE            DATABASE_ROLE

------------------------------ -------------------- ----------------

PRD01                          READ WRITE           SNAPSHOT STANDBY

1 row selected.

Converting the snapshot standby database back to physical database

sql > shutdown immediate
sql > startup mount;
sql > alter database convert to \physical standby ;
sql > shutdown immediate;
sql > startup mount;
sql > alter database recover managed standby database disconnect from session;
sql > select flashback_on from v$database;
sql > > select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

How to change hostname in linux

vi /etc/sysconfig/network :-- Make the change in this file . Save it and reboot the server 

ORA-01031: insufficient privileges

[oracle@STDBY admin]$ sqlplus sys@DBSTP11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 1 01:14:18 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01031: insufficient privileges

After following all listed in the below URL

http://askdba.org/weblog/2008/05/ora-1031-as-sysdba/

My issue could not got resolved

Then i started looking into tnsnames.ora and listener.ora file

and i found that oracle_home value is incorrect in listener.ora file

After correcting it .It got resolved

List of Oracle Enqueue

http://docs.oracle.com/cd/B19306_01/server.102/b14237/enqueues.htm

The Oracle enqueues are:

• BL, Buffer Cache Management
• BR, Backup/Restore
• CF, Controlfile Transaction
• CI, Cross-instance Call Invocation
• CU, Bind Enqueue
• DF, Datafile
• DL, Direct Loader Index Creation
• DM, Database Mount
• DR, Distributed Recovery Process
• DX, Distributed Transaction
• FP, File Object
• FS, File Set
• HW, High-Water Lock
• IN, Instance Number
• IR, Instance Recovery
• IS, Instance State
• IV, Library Cache Invalidation
• JI, Enqueue used during AJV snapshot refresh
• JQ, Job Queue
• KK, Redo Log "Kick"
• KO, Multiple Object Checkpoint
• L[A-P], Library Cache Lock
• LS, Log Start or Switch
• MM, Mount Definition
• MR, Media Recovery
• N[A-Z], Library Cache Pin
• PE, ALTER SYSTEM SET PARAMETER = VALUE
• PF, Password File
• PI, Parallel Slaves
• PR, Process Startup
• PS, Parallel Slave Synchronization
• Q[A-Z], Row Cache
• RO, Object Reuse
• RT, Redo Thread
• RW, Row Wait
• SC, System Commit Number
• SM, SMON
• SN, Sequence Number
• SQ, Sequence Number Enqueue
• SR, Synchronized Replication
• SS, Sort Segment
• ST, Space Management Transaction
• SV, Sequence Number Value
• TA, Transaction Recovery
• TC, Thread Checkpoint
• TE, Extend Table
• TM, DML Enqueue
• TO, Temporary Table Object Enqueue
• TS, Temporary Segment (also TableSpace)
• TT, Temporary Table
• TX, Transaction
• UL, User-defined Locks
• UN, User Name
• US, Undo Segment, Serialization
• WL, Being Written Redo Log
• XA, Instance Attribute Lock
• XI, Instance Registration Lock