Index

http://www.codeproject.com/Articles/9990/SQL-Tuning-Tutorial-Understanding-a-Database-Execu

• An index is a collection of pairs of key and location. The key is the word by which we are looking. In the case of a book, the location is the page number. In the case of a database, it is the physical row identifier. Looking for a record in a table by physical row identifier has constant complexity, that is, it does not depend on the number of rows in the table.
• Keys are sorted, so we don't have to read all keys to find the right one. Indeed, searching in an index has logarithmic complexity. If looking for a record in an index of 1000 records takes 100 ms, it may take 200 ms in an index of million of rows and 300 ms in an index of billion of rows. (Here I'm talking about B-Tree indexes. There are other types of indexes, but they are less relevant for application development). 

If we are looking for customers by name, we can perform the following physical operations:

• Seek the first entry in IX_CUSTOMERS_LAST_NAME where LAST_NAME=@LastName. This operation is named an index seek.
• Read the index from this entry to the last where the LAST_NAME=@LastName is still true. This will cost to read PAGES(IX_CUSTOMERS_LAST_NAME)*Prob[LAST_NAME=@LastName] pages from disk. This operation (always coupled with an index seek) is called an index range scan.
• Each index entry found by the previous steps gives us the physical location of the a record in the CUSTOMERS table. We still have to fetch this record from the table. This will imply RECORDS(CUSTOMERS)*Prob[LAST_NAME=@LastName] page fetches. This operation is called a table seek.

Execution plan

http://www.codeproject.com/Articles/9990/SQL-Tuning-Tutorial-Understanding-a-Database-Execu

You may for instance ask the server to retrieve all customers living in the region of Prague. The server chooses whether it is better to read completely the table of customers, or whether using an index would be faster. It compares the cost of all possible approaches. The way that a statement can be physically executed is called an execution plan or a query plan.

The component of the database server that is responsible for computing the optimal execution plan is called the optimizer. The optimizer bases its decision on its knowledge of the database content.


What are we optimizing

Application developers usually have to minimize processor use and sometimes memory use. However, when developing database applications, the bottleneck is elsewhere. The main concern is to minimize disk access.

The main disk allocation unit of database engines is called a page. The size of a page is typically some kilobytes. A page usually contains between dozens and hundreds of records. This is important to remember: sometimes you may think a query is optimal from the point of view of the record accesses, while it is not if you look at page accesses.

What is the difference between nomount,mount and open database in Oracle?

http://www.saptechies.com/difference-between-oracle-mount-no-mount-and-open-stage/

The differences in the 3 startup stages for oracle db are as below.
NoMount Stage:
When DB is in the nomount stage, oracle first opens and reads the initialization parameter file "init.ora" for the DB configuration which includes various parameters required for initialization. Based on the parameters, the memory areas in database instance are allocated and db background processes are started. The instance then starts.
Mount Stage:
When DB is in this stage, it opens and reads the control file which contains the physical structure of db  like the database datafiles etc.. It determines the location of the datafiles and the database is ready to be opened.
Open Stage
When DB is in this stage, it opens the database, it tries to access all of the datafiles associated with the database. Once it has accessed the database datafiles, it makes sure that all of the database datafiles are consistent.

After that DB is into normal operations state.


http://technado.wordpress.com/2012/10/10/what-is-the-difference-between-nomountmount-and-open-database-in-oracle/

Nomount – The database instance has been started (processes and memory structures have been allocated, but control file is not yet accessed).
Mount – Instance has accessed the control file, but has not yet validated its entry or accessed the datafiles.
Open – Instance has validated the entries in the control files and is accessing the datafiles – it is now open for business.
The startup process is Nomount -> Mount -> Open.

Automatic Workload Repository (AWR)

Automatic Workload Repository (AWR)

 http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php

http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm



AWR is a separately licensed feature of the Oracle 11gR2 Diagnostic Pack

While Statspack reports will certainly provide sufficient details for I/O performance tuning, AWR reports offer significantly more detail, are easier to navigate, and are more efficient to execute because they access performance metric data that’s already been captured within AWR snapshots … and those snapshots were gathered in real time using direct memory access.)


http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#i27008

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.
The statistics collected and processed by AWR include:

• Object statistics that determine both access and usage statistics of database segments
  
• Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
  
• Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
  
• SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time
  
• ASH statistics, representing the history of recent sessions activity
  

Automatic Performance Statistics

http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm

• When analyzing a performance problem in any of these scopes, you typically look at the change in statistics (delta value) over the period you are interested in. Specifically, you look at the difference between the cumulative value of a statistic at the start of the period and the cumulative value at the end.
• Cumulative values for statistics are generally available through dynamic performance views, such as the V$SESSTAT and V$SYSSTAT views
• Note that the cumulative values in dynamic views are reset when the database instance is shutdown
• The Automatic Workload Repository (AWR) automatically persists the cumulative and delta values for most of the statistics at all levels except the session level. 
• This process is repeated on a regular time period and the result is called an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period

NOT FOUND

Best practices in maintaining production support system including things not to do on a production system

pre-release and post release activities

Day to day customer support, query execution best practices, customer sensitive data

Oracle explain plan

http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm

http://stackoverflow.com/questions/10572619/difference-between-explain-plan-and-execution-plan

http://www.dba-oracle.com/t_explain_plan.htm