Why I Choose Oracle as my Career ?

Dear Friends,

There are some question which asked frequently on forms from every oracle newbie.

1. Which Oracle Certification I choose ?
Here I will explain in details.

There types of Oracle Certification we can do.
1. Oracle DBA
2. Oracle Developer
3. Oracle Application DBA

1. Oracle DBA
The person which maintain Oracle Database called Oracle Database Administrator.
There are some frequent work for Oracle DBA
1. Database Backup Responsibity.
2. Database Performance Tunning
3. Database Basic Administration Like " user creation, database security"
Above work is complete dedicated to Oracle DBA and every company which use Oracle Database they always need Oracle DBA.

Certification: Oracle 9i
http://dbataj.blogspot.com/2007/08/oracle-9i-certified-professional.html

Certification: Oracle 10g
http://dbataj.blogspot.com/2007/03/oracle-certified-professional.html2. Oracle Developer
The person which create coding and design forms and report for front-end application
There are some frequent work for Oracle Developer
1. Coding
2. Create and Design Forms and Reports
3. SQL Tunning
Above work is complete dedicated to Oracle Developer

Certification: Oracle9i,10g
http://dbataj.blogspot.com/2007/08/oracle-forms-developer-certified.html


3. Oracle Application DBA

The Person which work on ERP (enterprise resource planning)
There are some frequent work for Oracle Application DBA
1. Implement Oracle ERP according company requirement.
2. Patching and Clonning
3. Maintain Application Module like "financial,hr etc"

Certification: Oracle10g
http://dbataj.blogspot.com/2007/08/oracle-11i-apps-dba.html
________________________________________________________________

2. From where I get Real Time Exprience ?

This is very most important and difficult question :)

because without exprience we can't get job or without job we can't get real time exprience :)

So First understand what is real time exprience ?
Read Time exprience means where so many users working in your Oracle DB and DBA have to maintain that database, if any error comes DBA have to fix as soon as possible.

This exprience we can't get without Job ?
Ans: Yes,

Now if we remove REAL TIME word in exprience then we can get only exprience without Job ?
Ans: Yes,

How ? i will show you.

First understand what is exprience ?
Exprience means we have to good concept with some practical knowledge in Oracle DB.

Now Question is how can get good concept knowledge with some pratical knowledge ?

1. Buy On PC with minimum 512 RAM with 80 harddisk.
2. Install Oracle 10g Database or Oracle 11g
It is free you can download here
http://www.oracle.com/technology/software/products/database/index.html

Note: Oracle 11g is also released on Windows or Linux Platforms

3. Download below books from Oracle Documentation
1. Oracle Concept
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

2. Oracle Administrator Guide
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/toc.htm

3. Oracle Sql Reference Guide
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

4. Oracle Performance Tunning Guide
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm

4. Now start reading with step by step first read 1 pointed books then 2 and so on.

5. If you face in problem means any error message or anything which you don't understand then please refer oracle forms.
1. Oracle OTN forms
http://forums.oracle.com/forums/category.jspa?categoryID=18
2. OraFAQ forms
http://www.orafaq.com/forum/
3. Asktom
http://asktom.oracle.com/pls/asktom/f?p=100:1:4506654324364228

4. Some Oracle guru's Blog
http://jonathanlewis.wordpress.com/
http://tkyte.blogspot.com/
http://tonguc.wordpress.com/
http://jaffardba.blogspot.com

I am not Oracle Guru's but visit my blog also :)
http://dbataj.blogspot.com
http://babudba.blogspot.com
http://sabarsyed.blogspot.com
5. Some good Site
http://www.akadia.com/
http://www.adp-gmbh.ch/
http://www.psoug.org/

After Follow above instruction you will be very good Oracle Knowledgable person.
________________________________________________________________

3. How can I get Oracle Job ?

What you think after complete Second point (above) you will not get job ?
Ans: NO, we will get job...hurrrey :)

But also keep in mind below things when you going for interview
1. Make sure you have very good RESUME/CV created.
If anybody want to know about how to create CV for oracle please email me at asktaj i will check and correct your CV.

2. First decide In which subject you are very powerful ?
1. Administration
2. Backup and Recovery
3. Performance Tunning

3. Good Dress Code

________________________________________________________________

Hope this article give you good understand about Oracle Certification , Exprience and Job.

Please give me your comments.
Thank You

Oracle Latch

My Signature Article

Tips:
Oracle Certification - Tips
DBA Preparation - Tips
Practical DBA - Tips

What is Latch ?

A mechanism to protect shared data structures in the System Global Area.
For Example: latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.

A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.


During DB performance we will see LATCH event ...so what is latch event and how many types of latch events ?

A latch is a low-level internal lock used by Oracle to protect memory structures.

The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.

Most Popular latch wait event are ...

1. Latch: library cache or Latch: shared pool

Below is Possible causes for above both latch events.

1. Lack of statement reuse
2. Statements not using bind variables
3. Insufficient size of application cursor cache
4. Cursors closed explicitly after each execution
5. Frequent logon/logoffs
6. Underlying object structure being modified (for example truncate)
7. Shared pool too small

Below is Possible suggestion for aviod above both latch events.

1. Increase SHARED_POOL_SIZE parameter value.
2. Modify Frontend application to use BIND VARIABLE
3. Use CURSOR_SHARING='force' (for temporary basis)


2. Latch: cache buffers lru chain

Possible Causes

1. Inefficient SQL that accesses incorrect indexes iteratively (large index range scans) or many full table scans.
2. DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer
3. Cache may be too small

Possible Suggestion

1. Look for: Statements with very high logical I/O or physical I/O, using unselective indexes
2. Increase DB_CACHE_SIZE parameter value.
3. The cache buffers lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained.

For symmetric multiprocessor (SMP) systems, Oracle automatically sets the number of LRU latches to a value equal to one half the number of CPUs on the system. For non-SMP systems, one LRU latch is sufficient.

Contention for the LRU latch can impede performance on SMP machines with a large number of CPUs. LRU latch contention is detected by querying V$LATCH, V$SESSION_EVENT, and V$SYSTEM_EVENT. To avoid contention, consider tuning the application, bypassing the buffer cache for DSS jobs, or redesigning the application.


Latch: cache buffers chains

Possible Causes

1. Repeated access to a block (or small number of blocks), known as a hot block
2. From AskTom:

Contention for these latches can be caused by:

- Very long buffer chains.
- very very heavy access to the same blocks.

Possible Suggestion

1. From AskTom:
When I see this, I try to see what SQL the waiters are trying to execute. Many times,
what I find, is they are all running the same query for the same data (hot blocks). If
you find such a query -- typically it indicates a query that might need to be tuned (to
access less blocks hence avoiding the collisions).

If it is long buffer chains, you can use multiple buffer pools to spread things out. You
can use DB_BLOCK_LRU_LATCHES to increase the number of latches. You can use both
together.

The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers:

SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH
FROM X$BH
WHERE HLADDR = 'address of latch'
ORDER BY TCH;

X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.

Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment.

After you have identified the hot block, you can identify the segment it belongs to with the following query:

SELECT OBJECT_NAME, SUBOBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = &obj;

In the query, &obj is the value of the OBJ column in the previous query on X$BH.


5. Latch: row cache objects

The row cache objects latches protect the data dictionary.
Suggestion: Increase SHARED_POOL_SIZE parameter to avoid this latch.

System Statistics

What is system statistics ?

System statistics describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer.

Why gather SYSTEM STATISTICS ?

When choosing an execution plan, then opitmizer estimate the I/O and CPU resources required for each query.
System statistics enable the query optimizer to more accurately estimate I/O and CPU costs, enabling the query optimizer to choose a better execution plan.

It is important to gather system statistics ?

System statistics give accurate cost for sql query so optimizer take good decision.
If you have good statistics then query take good decision and database performance is increase.

Many Oracle Guru's and Oracle Corp. Also recommended to gather system statistics.

How Gather System Statistics ?

Through DBMS_STATS package we can gather system statistics

Step1
SQL> exec dbms_stats.gather_system_stats('Start');

Step2
SQL>--Wait for some time ...it will 1 hr minimum or 2 hr or whole day according database load.

Step3
SQL exec dbms_stats.gather_system_stats('Stop');


Keep in mind.
1. User must granted DBA privilege role for gather system statistics.
2. After gather system statistics... Unlike table, index, or column statistics, Oracle does not invalidate already parsed SQL statements when system statistics get updated. All new SQL statements are parsed using new statistics.
3. Always Gather System statistics During Heavy Peak Load.


Where check gather system statistics data information ?

After Gather system statictics query sys.aux_stats$ view.

SQL> select pname, pval1 from sys.aux_stats$;

PNAME PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS 1
CPUSPEEDNW 904.86697
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM .995
MREADTIM 1.701
CPUSPEED 1268
MBRC 16
MAXTHR
SLAVETHR

13 rows selected.

Note:
CPUSPEEDNW,IOSEEKTIM,IOTFRSPEED is New column comes with Oracle 10g. and these columns already populated before gather system statistics.


SREADTIM

Single block read time is the average time to read a single block randomly.

MREADTIM

Multiblock read is the average time to read a multiblock sequentially.

MBRC

Multiblock count is the average multiblock read count sequentially.

Active Session History (ASH)

Tips:
Oracle Certification - Tips

DBA Preparation - Tips
Practical DBA - Tips

Active Session History (ASH)

Oracle 10gr2 Introduce new option for capture performance problem.
ASH >>> Active session History <<<<
What is ASH ?

Whenever you want to know information about blocker and waiter identifiers and their associated transaction IDs and SQL.

About V$ACTIVE_SESSION_HISTORY


1. The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance.

2. Active sessions are sampled every second and are stored in a circular buffer in SGA.

3. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.

4. This includes any session that was on the CPU at the time of sampling.

5. Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.

6. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view

7. Historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view,
SQL identifier of SQL statement


What information provide ASH view ?


1. Object number, file number, and block number

2. Wait event identifier and parameters

3. Session identifier and session serial number

4. Module and action name

5. Client identifier of the session

6. Service hash identifier


How to generate ASH report ?

It is New feature of 10GR2(10.2.0.1.0)
For report creation we have to use ASHRPT.SQL script.
Located: In $ORACLE_HOME/rdbms/admin folder


How to run ASHRPT.SQL script


To generate a text report of ASH information, run the ashrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the time frame to collect ASH information by first specifying the begin time in minutes prior to the system date.

Enter value for begin_time: -10
Note: here you have to just put number in minutes eg: 10 for 10 minutes

Next, enter the duration in minutes that the report for which you want to capture ASH information from the begin time. The default duration of system date minus begin time is accepted in the following example:

Enter value for duration:
Note: left blank for default value. Default value is SYSDATE

The report in this example will gather ASH information beginning from 10 minutes before the current time and ending at the current time. Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:
Using the report name ashrpt_1_0310_0131.txt
Note: Left it blank for default value.

The session history report is generated.

For Futhere Detail please check metalink note: 243132.1

ORA-02449

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace users including contents and datafiles ;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


Whenever get ORA-02449 error during drop tablespace then just use CASCADE CONSTRAINTS cluase with DROP TABLESPACE statement.

SQL> drop tablespace users including contents and datafiles cascade constraints;


Tablespace dropped.

AWR Report/Snapshot

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips



Prior Oracle 10g we use STATSPACK report to analyze instance health for performance. but now in 10g we use AWR(automatic workload repository) report to analyze Oracle Database health.


How to generate AWR report ?

It is very easy to generate AWR report in Oracle 10g.

We have to run just one sql script to generate AWR report.


There is TWO sql script to create AWR report.
1. awrrpt.sql
If we have only One Oracle Database then run awrrpt.sql sql script.

2. awrrpti.sql
If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

Location of AWR report sql script

$ORACLE_HOME/rdbms/admin

Example Of AWR report

AWR report Example

What is default interval period between two awr report ?

AWR report generate automatically for database every 1 hr interval period.
But Recommanded is 15 Minutes is enough in two snapshot for better performance bottleneck.

How to Manage & change interval period for awr report ?

There is two to modify or changes in AWR report.
1. Manually ---> through DBMS_WORKLOAD_REPOSITORY plsql package

2. GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSITORY

How to Interpreting with AWR report ?

Below is Main Section in AWR report for concern.

Load Profile

In this section we know about Logical Read, Physical Read, Soft Parse, Hard Parse

Instance Efficiency Percentages

Many Oracle Guru's is not recommended to trust on Instance percentage ratio. becuase suppose if your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.

Top 5 Timed Events

It is very important section in AWR report.
through this we can know most five wait event is effecting database performance.

Oracle Wait Event


SQL Statistics

It this section we will know about Execution time, cpu used, logical & physical read for top most sql statement.


Advisory Statistics

In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.


Visit following link for reference interpreting awr report

Reference I

EXECUTION PLAN

What is execution plan ?

To run a DML statement, Oracle might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to run a statement is called an execution plan. An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order). The steps of the execution plan are not performed in the order in which they are numbered.

How to generate execution plan ?

There are three ways to view execution plan

1. Through AUTOTRACE

SQL> set autotrace trace exp
SQL> select * from emp;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=51
8)

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
=518)

2. Through DBMS_XPLAN package

SQL> explain plan for select * from emp;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

3. Through trace file (trace event 10046,sql_trace=true) and format trace file through TKPROF utility.

10046 trace event

What is 10046 trace events

whenever we want to tune any sql statement and want to know about waits and bind variable ...then we can use 10046 trace events.

How to use 10046 trace events

First define trace identifier for generated trace files so we can easily identify our trace files in UDUMP folder.

SQL> alter session set tracefile_identifier='MYSESSION';

Enable 10046 tracing.

SQL> alter session set events '10046 trace name context forever, level 8';

Now execute SQL Statements...

select e.empno, e.ename, d.dname, d.deptno
from emp e , dept d
where e.deptno = d.deptno;

Disable 10046 tracing.

SQL> alter session set events '10046 trace name context off';

Check UDUMP directory for generated trace file.
file name like "XXXX_MYSESSION.TRC"

C:\Oracle\admin\ora9i\udump>dir *MYSESSION.trc

Now generated trace file is RAW trace file and very hard to read and understand ...so through TKPROF utility create readable output file for generated trace file.

C:\Oracle\admin\ora9i\udump>tkprof ORA01904_MYSESSION.TRC c:\output.log

TKPROF: Release 10.1.0.5.0 - Production on Wed Oct 17 19:01:44 2007

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

Open c:\output.log file and analyze it. below is content of output.log file

select e.empno, e.ename, d.dname, d.deptno
from emp e , dept d
where e.deptno = d.deptno

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.29 0.31 2 0 2 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 3 30 2 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.29 0.31 5 30 4 14

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 66

---Below is execution plan

Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS
14 TABLE ACCESS FULL EMP
14 TABLE ACCESS BY INDEX ROWID DEPT
14 INDEX UNIQUE SCAN (object id 32119)

---Waits time information.

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 3 0.00 0.00
SQL*Net message from client 2 56.49 56.51

10046 Trace Level


Level 1
Basic trace level. Like the standard SQL_TRACE trace file. Provides statistics for parse, execute, fetch, commit and rollback database calls.

Level 4
Displays bind variables

Level 8
Displays wait statistics

Level 12
Displays wait statistics and bind variables

Autotrace in SQLPLUS

What is AUTOTRACE

In SQL*Plus you can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after a successful SQL DML statement, such as SELECT, DELETE, UPDATE or INSERT. It is useful for monitoring and tuning the performance of these DML statements.

How to configure AUTOTRACE in SQLPLUS for database user

1. Change directory path and connect with SYSTEM user to database
C:\>cd c:\oracle\product\10.1.0\db_1\rdbms\admin

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:08:20 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn system/manager
Connected.

2. run UTLXPLAN.SQL script for plan table.
SQL> @utlxplan.sql

3. Create public synonym for plan table or grant all privilege.
SQL> create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public;

Grant succeeded.

4. Exit and again change directory path and connect with SYS user to database
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>cd\

C:\>cd c:\oracle\product\10.1.0\db_1\sqlplus\admin

C:\Oracle\product\10.1.0\Db_1\sqlplus\admin>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:12:07 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.

5. run plustrce script and script must be run with SYS user.
SQL> @plustrce
SQL>
SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> set echo off

6. Grant plustrace role to public.
SQL> grant plustrace to public;

Grant succeeded.

Configuring the Autotrace Report

SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the
default.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer
execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
statement execution statistics.
SET AUTOTRACE ON - The AUTOTRACE report includes both the
optimizer execution path and the SQL
statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the
printing of the user's query output, if any.

Database Statistics for SQL Statements

recursive calls
Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.

db block gets
Number of times a CURRENT block was requested.

consistent gets
Number of times a consistent read was requested for a block.

physical reads
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

redo size
Total amount of redo generated in bytes.

bytes sent via SQL*Net to client
Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client
Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client
Total number of Oracle Net messages sent to and received from the client.

sorts (memory)
Number of sort operations that were performed completely in memory and did not require any disk writes.

sorts (disk)
Number of sort operations that required at least one disk write.

rows processed
Number of rows processed during the operation.

EXAMPLE

SQL> set autotrace on
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'




Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> set autotrace traceonly
SQL> select * from dept;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'




Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> set autotrace trace explain
SQL> select * from dept;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'



SQL> set autotrace trace statistics
SQL> select * from dept;


Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

IMP-00032;IMP-00008

During Import process if you will get below error message

IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:


Solutions

use BUFFER parameter with import command.
Note: use MAXIMUM value for BUFFER parameters eg: BUFFER=1000000

Tunning PL/SQL through DBMS_PROFILER

What is DBMS_PROFILER

dbms_profiler is oracle supplied package which use for tunning plsql application.
through this package we can tune our plsql procedure, trigger,funtions. and findout where plsql spent more time to execute.

How to install dbms_profiler package

In Basic Oracle Installation "dbms_profiler" package is not created. We have to manually create this packages running below scripts.

conn with SYS user and run "PROFLOAD.SQL"
Located: $ORACLE_HOME/rdbms/admin folder


SQL> conn sys@hgc as sysdba
Enter password:
Connected.
SQL> @d:\oracle\product\10.1.0\db_1\rdbms\admin\profload.sql

Package created.


Grant succeeded.


Synonym created.


Library created.


Package body created.

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

Conn to application user and run "PROFTAB.SQL", this script create three tables.

1. PLSQL_PROFILER_RUNS
2. PLSQL_PROFILER_UNITS
3. PLSQL_PROFILER_DATA

SQL> conn scott/tiger@hgc
Connected.
SQL> @d:\oracle\product\10.1.0\db_1\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist


drop table plsql_profiler_units cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist


drop table plsql_profiler_runs cascade constraints
*
ERROR at line 1:
ORA-00942: table or view does not exist


drop sequence plsql_profiler_runnumber
*
ERROR at line 1:
ORA-02289: sequence does not exist



Table created.


Comment created.


Table created.


Comment created.


Table created.


Comment created.


Sequence created.

How can use dbms_profiler ?


1. Connect with application user which you want to optimize.
Start dbms_profiler

SQL> exec dbms_profiler.start_profiler('Test procedure by Scott');

Information is store in memory so flash profiler to update their repository.

SQL> exec dbms_profiler.flush_data();

Stop dbms_profiler

SQL> exec dbms_profiler.stop_profiler();


View Information Generated by dbms_profiler

Query in below views
1. plsql_profiler_runs
2. plsql_profiler_units
3. plsql_profiler_data

For more Reference
OracleUtilities
Oracle Documentation

CURSOR_SHARING parameter

CURSOR_SHARING

What is cursor_sharing parameters ?

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

What is possible values for this parameter ?

1. EXACT (default)
Only allows statements with identical text to share the same cursor.

2. FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

3. SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

When we have to / should use this parameter ?

Whenever you have lots of some sql statement to execute but differ in liternal and your application is not design to use BIND VARIABLE that time you can use CURSOR_SHARING=FORCE to share cursor for every sql statement which differ only in literal.

Consider setting CURSOR_SHARING to SIMILAR or FORCE if both of the following questions are true:

Are there statements in the shared pool that differ only in the values of literals?
Is the response time low due to a very high number of library cache misses?


Tom Kyte said "cursor_sharing=force" is not permanent solution for performance it is use for temporary basis during developer fix there bug in application... instead of this parameter use BIND VARIABLE.



Performance improvement when we set cursor_sharing=force ?

When your application use lots of similar sql statement but differ in literal then yes performance will improve when you set cursor_sharing=force.

Side Effects on database when set cursor_sharing=FORCE/SIMILAR

Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.

Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE. For more information, see the "OPTIMIZER_FEATURES_ENABLE Parameter".

BUG with cursor_sharing=FORCE/SIMILAR

In Oracle Version 8i there is bug when set cursor_sharing=force/similar.

We need to down our database to set this parameter

No, we can set this parameter when our database is open.

alter system set CURSOR_SHARING=force SCOPE=both;

"SMON: Parallel transaction recovery tried"

Wed Sep 26 11:34:23 2007
SMON: Parallel transaction recovery tried

We found above message in alert_sid.log file.

No need to worry about it. it is information message ...SMON start recovery in parrallel but failed and done in serial mode.

"kccrsz: expanded controlfile section"

kccrsz: expanded controlfile section 11 from 224 to 252 records
requested to grow by 2 record(s); added 1 block(s) of records
Controlfile has resized from 190 to 192 blocks.

If we found above message in alert_sid.log file then no need to warry about it.
it is just information message for controlfile is increase in size.

we can check "v$controlfile_record_section" view or "controlfile_record_keep_time" parameters

Eg:
kccrsz: expanded controlfile section 11 from 224 to 252 records
Note: Section 11 is belong to log_history.

When controlfile possibly extended

1. Any upgrade or migration on database
2. Any Phyiscal changes in database

PMON "failed to acquire latch" during shutdown

PMON "failed to acquire latch" during shutdown

Above message in found in alert_sid.log file during database shutdown.
It is reported as bug.

Database 10gr1(10.1.0.5.0)
OS Windows 32bit

Check Metalink Note: 4632780.8, BUG# 4632780

Fixed in 10.2.0.2 or 11g.