ORA-12154 TNS:could not resolve service name

You might have come accross this error when connecting to an Oracle server from an Oracle client. Oracle client will throw this error if it is unable to connect to the server by using the service name specified. This error actually means that Sql * Net is  unable to recognize the host/service name specified in the connection Parameters.

Explanation about how sql * net client connects to the server:

The parameters that are required to connect to an Oracle server.

1. Username
2. Password
3. Service name/connection String

What is a service name ?

Service name specifies an entry in the tnsname.ora file. Each service name consists of  hostname/ip address of the Oracle database server you want to connect, Port at which Oracle listener is working, the database instance you want to connect to, and the protocol you are using for the connection.

  Continue Reading

Differences between automatic undo management and Manual undo management

Traditionally until Oracle 8i you have been using Manaul undo management which deals with roll back segments. Typically undo information is stored in rollback segments until a rollback or commit statement is issued.But now with Oracle 9i all your troubles are gone with Manual undo management. Oracle has introduced auto undo management.

Terminology differences :

In automatic undo management we call undo segment rather than rollback segment.

Automatic undo Management can be abbrevated as AUM oR sYSTEM MANAGED UNDO (SMU).

Manual undo management is usually abbrevated as MUM OR ROLLBACK UNOD (RBU).

COMPATIBILITY differences :

Automatic undo management is possible and compatible in Oracle versions 9.0.0 and higher.

Automatic Undo Management is enable by default if the database is created using database configuration assistant.

Manual undo management is not obseleted. it is even supported in the latest versions of Oracle. If you are flexible with Manual undo management ,you can even use it in Oracle version 9.x or 8.x.

You can even upgrade from Manaul undo management to Automatic undo Management.

Continue Reading

Ora-01555 : Snapshot too old error

By this time you might have come accross this ora-01555 error lot of times. Before learning about the causes of the Ora-01555 error, you need to understand about Read Consistency and dirty reads in Oracle.

Read Consistency : Let us take an example of Reading a 1 million records from a table emp through a cursor.
While fetching the data from the cursor in session A, if another session B updates the data simultaneously in the table emp by which 10000 records has been affected in the set of 1 million records. if these 10000 rows are not yet read by the cursor in session A, then

 Whether the Oracle would read the updated records to Session A ? NO.

  Continue Reading

Oracle Application Server provides a powerful utility to help you generate XML straight from data in your Oracle database — the XSQL processor. XML style sheets allows you to manipulate XML into various usable forms.

 What is xml ?

xml stands for eXtensible Markup language.

“eXtensible” - Information in a neutral way,stored in a neutral form, independent of systems, devices and applications

“Markup” - Adding information to a document relating to its structure and/or format

“Language” - A standard methodology with formal syntax.

Reasons behind xml Popularity

Platform independent

Ease of data exchange

License-Free and well supported

Big players (Oracle and Microsoft) are backing the W3C XML standard

  Continue Reading

An index hint instructs the optimizer to consider the use of an indexed table scan for a specified table. You can optionally specify one or more indexes in the hint. If no indexes names are specified in the hint, Oracle optimizer evaluates the cost of all indexes for a table and uses the most efficient.

 
Instructing the optmizer to use an  index is just a hint, it’s Oracle optimizer decision to make use of an index or not. If the Oracle optimizer recognizes that the cost of full table scan is cheaper than using an index, your index hint will be ignored. And there are more factors that optimizer  may favor the use of full table scan rather than using an index.one among them is db_file_multiblock_read_count  initiliazation parameter.

db_file_multiblock_read_count is a parameter which specifies the number of blocks than can be read by Oracle simultaneously.

If you increase the value in the db_file_multiblock_read_count rather than the default value, then Oracle favours more of full table scan’s rather than index usage. If you decrease the value in the db_file_multiblock_read_count  , then Oracle favours more of index usage rather than full table scans.

Continue Reading

All_rows hint is used in an opposite view to first_rows hint. This hint is mainly used in datawarehouse environments. if You want to instruct the optimizer to choose the best plan in getting all the rows of the query by the minimum resources of the machine.

This hint is designed mainly  to consume less machine resources like CPU, MEMORY as well as DISK I/O.

All_rows mainly targets on througput of the query rather than response time. So this hint is mainly used in dataware house environments rather then transactional environments.

The result of using an All_rows hint on optimizer Plans:

All_rows hint favours more of full table scans rather than index usage.

All_rows hint favour more of Sort_merge or Hash Joins rather than nested loops.

Synatx of ALL_ROWS HINT:

SELECT /*+ ALL_ROWS */ ….. FROM ……

Why your hint is Ignored ? Know the correct Usage of Hints

Usually a hint is specified as  a comment to the sql statement. But the job of Oracle optimizer is to differentiate between a hint and a comment. If the Oracle hint is not specified correctly in  an sql statement, there is 100% chance of your hint being ignored by the Oracle optimizer. If an incorrect hint is specified or a hint is mispelled, Optimizer ignores the hint instead of specifying an error. As the name specifies it is a “hint” to OPtimizer to choose the best path, not to report an error because of wrong hint.

I will list some of the cases where people go wrong in specifying a hint

Table aliases :

Let us have a look at the below query

SELECT /*+ INDEX(emp emp_PK) */ emp_name
FROM emp a
WHERE a.emp_no = 32322

In the above query you are instructing the oracle optimizer to use index hint. But Oracle definitely ignores your specification, it may or may not use the index based on the statistics available on the table EMP.This means that if the table is referenced via an alias within the SQL statement, the hint must also reference it via the alias .

Correct Placement of Hints:

Always Place an hint immediately after the first sql verb. if you place the oracle hint anywhere else your hint will be ignored.

Let us see the following query

SELECT emp_name
FROM /*+ INDEX(a emp_pk) */ emp a

Oracle does not report an error, but will definitely ignore your specification of hint.

The correct way of using a hint is

SELECT /*+ INDEX(a emp_pk) */ emp_name
FROM EMP a

INSERT /*+ APPEND */

First three consecutive letters of  your hint should be “/” ,”*”,”+” and the last two letters should be “*”, “/”.

Oracle Performance Tuning Exam

This exam focuses on the skills that you use to tune all components of an Oracle database application system. Expect to answer ~65 multiple-choice questions in 90 minutes or less. To pass the exam, you need to answer at least 46 questions correctly (70%). In general, the exam tests you about the following categories of information:

 the fundamental goals and strategies for Oracle database system and application tuning
 the components that you can tune to affect overall performance
 the roles that application designers, developers, database administrators, system administrators, and application end users play in the tuning process
 the steps in SQL statement processing (e.g., open a cursor, parse)
 registering application information in the data dictionary with the DBMS_APPLICATION_INFO package
 techniques for using shared SQL and maximizing application performance
 gathering optimizer statistics to ensure optimal execution plans
 revealing SQL statement execution plans with the SQL command EXPLAIN PLAN and the SQL*Plus    AUTOTRACE feature
 controlling optimizer behavior with initialization parameters, hints, and stored outlines
 monitoring SQL statement performance statistics in the dynamic performance views
 monitoring SQL statements using SQL tracing and TKPROF
 designing transactions
 controlling and monitoring data locks (e.g., table locks, row locks)
 avoiding deadlocks
 setting isolation levels and extending read consistency
 minimizing disk I/O with indexes (normal, bitmap, and function-based), index-organized tables, and clusters (indexed and hash)
 tuning host operating system memory and disk I/O
 tuning a database instance’s buffer cache
 configuring multiple buffer pools (default, keep, and recycle pools) in the buffer cache
 measuring dictionary cache and library cache hit ratios
 Oracle tuning a database instance’s shared pool for general use
 Oracle tuning a database instance’s shared pool for shared server sessions
 preventing fragmentation of shared pool memory
 monitoring database I/O using dynamic performance views
 using tablespaces and data files to reduce contention for database data
 tuning the database writer background process(es)
 tuning database checkpoints
 tuning temporary (sort) space allocations
 tuning redo log I/O with online log members and the redo log buffer
 using the UTLBSTAT and UTLESTAT utilities to gather tuning statistics
 using Oracle Expert to tune an Oracle database system

Specific tips for the exam 1Z0-0032:-

Although you are not expected to understand how to configure and make use of advanced Oracle Net features, you should be able to explain their function in an Oracle Net design. Such topics include the Oracle Internet Directory (OID, Oracle’s LDAP-compliant directory service), Oracle Names, Oracle Connection Manager, Oracle Advanced Security, the Oracle Net Application Proxy Kit, and Oracle Heterogeneous Services.

 
Do you understand the OSI model and how various Oracle Net software layers with different types of connections fit into this model? Expect several questions about this subject.

You must understand how to configure the two basic naming methods (name resolution methods) for Oracle Net: host naming and local naming.

Continue Reading

Shared Pool tuning is the most important aspect of Oracle shared global area optimization. And by far it is the most neglected area.

Shared Pool is a memory area in Oracle which is in turn a part of Oracle SGA(System Global Area). Shared Pool comprises of  different meory Strctures. If shared pool size is not configured Properly , the performance of the database suffers drastically.

Shared Pool consists of

1. Library Cache

2. Dictionary Cache

3. Control Structures.

Library cache inturn comprises of Shared Sql Area, Pl/sql Procedures, Functions, Packages (loaded) and control Structures (Latches and locks).

Continue Reading