Coskan’s Approach to Oracle

April 2, 2007

Using Virtual Indexes

Filed under: Basics, Tips — coskan @ 2:28 pm

While looking at Mr Julian Dyke’s presentation about index internals I saw a new (at least new for me) feature Virtual Indexes. It is there since the 8.1.5 release but i was unaware :( .

These indexes are not physically located on a segment but they have a data dictionary definition (you can’t add a real index with same name of virtual index). Virtual indexes allow user to test a potential advantages or disadvantages of a new index prior to actually building the new index in the database . For example you want an index on a table with 10 million rows and you do not sure about using an index then you can use virtual indexes for having idea about new execution plans.

CBO do not use virtual indexes by default without setting the undocumented parameter “_use_nosegment_indexes” to true and this is something you must consider before deciding to use virtual indexes. Since it is undocumented then it is not supported, unless the Oracle say that you can set. In my tests with virtual index i got similar cost results and same access paths from CBO comparing to real index usage.

=>Index Creation Syntax (you add nosegment clause to instead of storage clauses)

CREATE INDEX INDEX_NAME
ON TABLE_NAME (INDEX_COLUMN)
NOSEGMENT;

You have to generate statistics for the virtual index to get realistic costs. Creation of the index is not enough, it is called virtual but it acts like if it is real so if you want a good index simulation don’t forget the statistics. You can use the syntax below to generate statistics;

SQL>EXECUTE DBMS_STATS.GENERATE_STATS (user,’index_name’)

Don’t forget to set the session wide “_use_nosegment_indexes” parameter .

SQL> ALTER SESSION SET “_use_nosegment_indexes” = TRUE;

=>Sample Demonstration (Tests are done on 10.2.0.1 XE);

code_listing_8

References Used:

Index Internals Presentation of Julian Dyke (www.juliandyke.com)

http://www.idevelopment.info/data/Oracle/DBA_tips/Tuning/TUNING_15.shtml

April 1, 2007

Viewing Error Definitions from SQL*plus or Command Line

Filed under: Tips — coskan @ 1:05 pm

Suppose that you faced with an error when you execute a command. What you do to first is usually to look at the error codes documentation of Oracle and read the action you can take against the error. There is an another way to do it in Unix environments using OERR utility. This useful utility could lower the steps of your error code solution research. It is not an executable, it is a shell script and it is flexible for customizing. OERR is located under $ORACLE_HOME/bin directory with a name oerr.ksh. It reads the file $ORACLE_HOME/lib/facility.lis to show the messages. This file contains the paths to messages for the given facalities. (for example ORA errors are related with rdbms folder and the facility.lis files directs the oerr command to the rdbms/mesg/* folder to get the definition of the error code). You can add your own message files to the facility.lis for customized errors or change the definitions of the predefined error codes.

The syntax to call the oerr is below;

#oerr <facility> <error number>

    

     
Usage Samples; (! is used to call the command from the OS command line while you are in sql*plus)
SQL> !oerr ora 07400
07400, 00000, “slemtr: translated name for the message file is too long.”
// *Cause: The name for the message file overflows internal buffer.
// *Action: Try making the complete path-name of the message file shorter
// by reorganizing the directory hierarchy.

=>default ora-01652

SQL> !oerr ora 01652
01652, 00000, “unable to extend temp segment by %s in tablespace %s”
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

=>custimized ora 01652 (I have edited the $ORACLE_HOME/rdbms/mesg/oraus.msg file )

SQL> !oerr ora 01652

// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.Please be carefull when you are creating TEMPFILES!!!!!

Note: OERR comes with oracle installation on Unix environments and is not implemented for windows by default because of its nature which depends on the awk command however you can install freeware programs for windows too (look at the references)

References Used:

http://www.oracleutilities.com/OSUtil/oerr.html

« Newer Posts

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers