Data Dictionary and Dynamic performance view

The Database Library is built on a Data Dictionary, which provides a complete description of record layouts and indexes of the database, for validation and efficient data access. You can use the data dictionary for automated database creation, including building tables, indexes, and referential constraints, and granting access rights to individual users and groups. The database dictionary supports the concept of Attached Objects, which allow database records to include compressed BLOBs (Binary Large Objects) containing images, text, sounds, video, documents, spreadsheets, or programmer-defined data types.

Tuning PGA_AGGREGATE_TARGET

The oracle 9i introduces a new parameter PGA_AGGREGATE_TARGET to fix the issue of multiple parameters in oracle 9i such as SOR_AREA_SIZE, HASH_AREA_SIZE of earlier version.
The PGA is private memory region that contains the data and control information for a server process. Oracle Database reads and writes information in the PGA on behalf of the server process. The RAM allocated to the PGA_AGGREGATE_TARGET is used by Oracle connections to maintain connection-specific information (e.g., cursor states) and to sort Oracle SQL result sets.

How to Execution and Explain Plan

Execution plan and explain plan

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement’s execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

· An ordering of the tables referenced by the statement

· An access method for each table mentioned in the statement

· A join method for tables affected by join operations in the statement

· Data operations like filter, sort, or aggregation

How to generate awr report in oracle

VAR dbid NUMBER

PROMPT Listing latest AWR snapshots …
SELECT snap_id, end_interval_time
FROM dba_hist_snapshot
–WHERE begin_interval_time > TO_DATE(‘2011-06-07 07:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)
WHERE end_interval_time > SYSDATE – 1
ORDER BY end_interval_time;

ACCEPT bid NUMBER PROMPT “Enter begin snapshot id: ”
ACCEPT eid NUMBER PROMPT “Enter end snapshot id: ”

StatsPack and AWR Reports

I am planning to put up a few posts on  StatsPack and AWR reports. 

Note : Some figures / details may be slightly changed / masked to hide the real source.

Row Migration & Row chaining

What is Row Chaining
-----------------------------

Row Chaining happens when a row is too large to fit into a single database block. For example, if you use a 8KB block size for your database and you need to insert a row of 16KB into it, Oracle will use 2/3 blocks and store the row in chain of data blocks for that segment. And Row Chaining happens only when the row is being inserted.

SQL Trace & TKPROF

1.Compare the number of parses to number of executions. 

A well-tuned system will have one parse per n executions of a statement and will eliminate the re-parsing of the same statement. 

2.Search for SQL statements that do not use bind variables (:variable). These statements should be modified to use bind variables.

Some performance related issues...

As there are over 800 wait events but but frequently you may come across very few. As working on performance tuning since more than 4 yrs there are very few wait events. In this post I try to cover most popular of them.

How to write / tune SQL Queries for better performance

Performance of the SQL queries of an application often play a big role in the overall performance of the underlying application. The response time may at times be really irritating for the end users if the application doesn’t have fine-tuned SQL queries. Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

Wait event Read by other session or Buffer busy

Oracle 9i we called buffer busy wait event and oracle 10g/later we called “read by other session”

About “Read by other session wait event”

When a user issue the query in a database, oracle server processes will read the database blocks from disk to database buffer cache. When two or more session issue the same query/related query (access the same database blocks), the first session will read the data from database buffer cache while other sessions are in wait.

Oracle Database 11g new feature – Automatic Memory Management

Automatic Memory Management was a new feature introduced in 10g. With 10g release oracle has come up with anew parameter called sga_target which was used to automatically manage the memeory inside SGA.

The components which were managed by sga_target are db_cache_size, shared_pool_size, large_pool_size, java_pool_size and streams_pool_size

When should I rebuild my indexes?

Need is necessary for any change. I hope all agree to this. So why many DBA’s (not all) rebuilds indexes on periodical basis without knowing the impact of it?

Let’s revisit the facts stated by many Oracle experts: