Common ORA errors

Below are some ORA-ERROR which every DBA face:

1. ORA-00600/ORA-07445
Clause: Most likely it is related to bug
Solution: go to metalink and apply patch set.

2. ORA-03113
Clause: It is generic error means lots of reason behind this.
Solution: Depends on condition but best way to search on google/forms.

3. ORA-04031
Clause: Mis-configuration of SGA, insufficient memory
Solution: Sametime need to add memory on system, or sametime need to reduce SGA memory

4. ORA-12154
Clause: Wrong TNS_ENTRY is used.
Solution: Using NETCA tools configure tns entry or check tnsnames.ora file for correct entry.

5. ORA-12368
Clause: It is generic error lots of reason
Solution: Edit SQLNET.ORA and change NONE from NTS "SQLNET.AUTHENTICATION_SERVICES=NONE"

6. ORA-12500
Clause: It is generic error lots of reason
Solution: increase "processes" parameter, increase physical memory

7. ORA-12528
Clause: It is BUG in 10g (if you face this error in 10g)
Solution: Don't use TNS_ENTRY with connect string or edit listener configuration from STATIC to DYNAMIC.(search on my blog for more)

Below are some question which everyone is confused

1. select count(*) or count(columname) which is faster ?
Answer: Both are same in performance in speed.

2. How frequently index should be rebuild.
Answer: NO, We never need to rebuild index becuase it is not increase database performance

3. For Oracle Database Windows is good OS or Linux is good.
Answer: Linux is much better than Windows for Oracle.

4. How to increase Database Performance?
Answer: Database performance use below methology.

1. Define what database performance is acceptable.
2. When you face performance related problem

Define where is come from means
1. it is related to Instance (SGA Configuration)
2. it is related to Client machine ( SYSTEM configuration)
3. it is related to Network delay

3. check database performance through
1. statspack report, awr report,tk*prof tools,sql trace etc.

http://www.oracledbain.blogspot.com/

Comments

2. How frequently index should be rebuild.
Answer: NO, We never need to rebuild index because it is not increase database performance

Where to start? There is so much inaccuracy with your answers my mind boggles.

YES - Index rebuilds are needed occasionally!

Please do research before putting up things like this.

Index rebuild should be done periodically. The period should depend on the data movement.

mazqurayahoocom, you say

Quote:
1. select count(*) or count(columname) which is faster ?
Answer: Both are same in performance in speed.

This is not necessarily correct.
First, count(*) will count the rows but count(columnname) will count the number of rows where the column is not null. So the queries are not equivalent. For example:

orcl> select count(*) from emp;

  COUNT(*)
----------
        14

orcl> select count(comm) from emp;

COUNT(COMM)
-----------
          4

orcl>

Second, performance may be very different because count(*) may be able to use an index but count(columnname) might not be able to:

orcl> select count(*) from emp;

  COUNT(*)
----------
        14


Execution Plan
----------------------------------------------------------
Plan hash value: 2937609675

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------

orcl> select count(ename)from emp;

COUNT(ENAME)
------------
          14


Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |     6 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

orcl>

Rebuilding indexes on a schedule is a DBA best practice!!!!!!!!!

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

Rebuilding indexes is indeed a common error. The Burleson website is not a very good reference: some of the information there is out of date or controversial or (as in this case) both. wrt out-of-date, Burleson is talking about indexes in freelist managed tablespaces; do you think he would apply the same reasoning to ASSM tablespaces? wrt to being controversial, he speaks almost exclusively about index rebuilds being beneficial for SELECT. He ignores the effect on INSERT. After you rebuild an index, the keys are compressed into the minimum possible number of blocks. This means that for days afterwards, most of your inserts will require block splits. Because of this performance will be degraded until the index has enough free space in its leaf nodes.