2008/06/20

"ORA-12547: TNS:lost contact" when creating database in Oracle 10g

Problem: I didn't optionally to create a database at install time, and when trying to do so later using the dbca tool, I keep getting the error "ORA-12547: TNS:lost contact" popping up.

Action: Install libaio & libaio-devel packages which are asynchronous library for Linux.

2008/06/16

Manually Drop an Oracle Database

1)Set the Oracle SID of the database which you interested to drop.

$ export ORACLE_SID=XXXX
2)Connect to SQL*Plus as SYSDBA privilege.

3)Mount the database.

SQL> startup force mount
4)Enable restricted session.

SQL> alter system enable restricted session;
5)Drop the database.

SQL> drop database

EXP-00091 Exporting questionable statistics tips

The "EXP-00091 Exporting questionable statistics" error happens when you export table data with its related optimizer statistics, and Oracle cannot verify the currency of these statistics. When CBO statistics are created/updated with dbms_stats, the time is noted, but it’s always a good idea not to export statistics since they should be recalculated after import.

You can remove the EXP-00091 error by exporting with "statistics="none" or by setting the client character set (e.g. export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1).

The Oracle docs note:

EXP-00091 Exporting questionable statistics

Cause: Export was able to export statistics, but the statistics may not be useable. The statistics are questionable because one or more of the following happened during export:

- A row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export,


- Only certain partitions or subpartitions were exported, or a fatal error occurred while processing a table.

Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, or export complete tables. If desired, import parameters can be supplied so that only
non-questionable statistics will be imported, and all questionable statistics will be recalculated.

2008/06/12

RULE Based Optimizer vs. COST Based Optimizer

RULE Based Optimizer

As its name implies the rule based optimizer uses a set of 15 rules to determine how to best process a query. The rule chosen is based strictly on query structure and pays no attention to the number of rows, partitions or other query specific features and statistics. The query optimization paths used by the cost based optimizer are:

Rank Path
1 Single row by ROWID
2 Single row by Cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite Index
9 Single Column index
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort-merge join
13 MAX or MIN on indexed column
14 ORDER BY indexed columns
15 Full table scan

The major benefit of the rule based optimizer was that for a specific query the execution plan was stable regardless of how big the table or its associated indexes grew as long as the index structure didn't change. The major disadvantages of the rule based optimizer are that the execution plan didn't change no matter how large the table or its indexes grew, and, it can't take advantage of new features as easily as a cost based optimizer.

COST Based Optimizer

The COST based optimizer was introduced in later versions of Oracle 7. The cost based optimizer uses a proprietary algorithm to assign a cost to each step of an execution plan for a query, and then, by looking at equivalent plans the optimizer can choose the one with the lowest cost for a specific query. The cost algorithm is dependent up on accurate table statistics in order to come up with accurate cost information. The table statistics are created and maintained by use of the ANALYZE command.

The major benefit of the cost based optimizer is that for changes in table and index sizes the optimizer can re-evaluate the execution plan and adjust to obtain an optimal performance profile. However, the cost based optimizer is only as good as the statistics fed to it, and, it tests a fully qualified DBA using the rule based optimizer can get better performance. However, unless you like to spend your free time using the tools tkprof, explain plan or third query party analyzers, I suggest jsut using the cost based optimizer and an "intelligent" table analyzer procedure.

2008/06/02

Modify Parameter in RAC Environment

You can modify the value of your initialization parameters by using the ALTER SYSTEM SET command. This is the same as with a single-instance database except that you have the possibility to specify the SID clause in addition to SCOPE clause.

SQL>alter system set <dpname> scope=<svalue> sid='<sid|*>';

The default value for the sid clause is sid='*'.
Specify sid='*' or use default value, if you want to change the value of the parameter for all instances.
Specify sid='sid' if you want to change the value of the parameter only for the instance sid.

The <sid>.<pname> will override the value of <sid>.<pname>.

The sid='sid' of the ALTER SYSTEM RESET command allows you to override the precedence of a currently used <sid>.<pname> entry. The <sid>.<pname> entry will be deleted. Oracle use the value of *.<pname> for instance 'sid'.

SQL>alter system reset <dpname> sid='<sid>';

The following command will reset the value for instance * from instance sid.

SQL>alter system reset <dpname> scope='spfile' sid='*';

You must specify scope as 'spfile', because you can reset memory value for instance * from instance sid.