1. What is National Language Support?

National Language Support (NLS) is necessary for the following tasks:

• Converting character sets between client and server
• Supporting different languages
• Region-dependent formatting of date and currency data
• Sort sequence

2. What is Oracle Globalization Support?

Oracle Globalization Support is the new name for the National Language Support as of Oracle 9i.

3. What is a character set?

Internally, all characters on the computer (letters, figures, special characters) are represented by figures. Code pages on the operating system determine which character is represented by which figure.

Oracle also uses this kind of internal representation which is defined by character sets. You can therefore compare a character set to a code page which defines the Oracle-internal representation of characters.

4. Why do I need different character sets?

The number of characters that can be represented by a character set is limited. For example, you can only represent 2^8, that is, 256, different characters with an 8-bit character set. This number is not sufficient to represent all characters of all languages.

5. How many different characters can be represented by the character sets?

There are four groups of character sets:

• 7-bit character sets (for example, US7ASCII): 2^7, that is, 128, characters can be represented.
• 8-bit character sets (for example, WE8DEC): 2^8, that is, 256, characters can be represented.
• Multibyte character sets (for example, KO16KSC5601): more than 256 characters can be represented
• UNICODE character sets (for example, UTF8): In theory, over a million characters can be represented. -

6. What, from an Oracle point of view, is a proper setup of the character sets?

From an Oracle point of view, the following requirements should be met:

• You should set up the Oracle database with a character set that supports all characters used by the application.
• The Oracle clients should use a character set that matches the code page used in the operating system.

For example:

• A Windows client with code page WIN1252 uses the WE8MSWIN1252 character set.
• A UNIX client with code page ROMAN8 uses the WE8ROMAN8 character set.
• The relevant database server is defined with the UTF8 character set that contains the characters of both client code pages.

7. How can I determine the character set used by a database?

On an open database, you can use the following SELECT to determine which character set the database is using:

SELECT VALUE FROM V$NLS_PARAMETERS
   WHERE PARAMETER = ‘NLS_CHARACTERSET’;

8. Where are the character set definitions stored?

 On the server, the character set definitions are written to

<oracle_home>/ocommon/nls/admin/data or for Oracle 10: <oracle_home>/nls/data

On the client, the character set definitions are stored as part of the client software installation. On Windows, the directory corresponds to the directory of the server installation. On UNIX, the files are unpacked in directories such as the following, depending on the release:

$ORACLE_HOME/ocommon/NLS_805/admin/data
/oracle/805_32/ocommon/nls/admin/data
/oracle/client/92x_64/ocommon/nls/admin/data

9. Which environment settings do I need for NLS?

The following environment variables play a role in conjunction with NLS:

• NLS_LANG: Definition of the language, region and character set of the client (for example, AMERICAN_AMERICA.WE8DEC, see the information above);
• ORA_NLS: Directory of the NLS files for Oracle 7.2;
• ORA_NLS32: Directory of the NLS files for Oracle 7.3;
• ORA_NLS33: Directory of the NLS files for Oracle 8.0 and higher;
• ORA_NLS10: Directory of the NLS files for Oracle 10.

On WINDOWS, the ORA_NLS* variables must be set in the Registry and not in the environment.

10. Which character sets are known to the Oracle database?

You can use the following command to check which character sets are known to the Oracle database:

SELECT VALUE FROM V$NLS_VALID_VALUES
WHERE PARAMETER = ‘CHARACTERSET’;

Gosh…no way…after recompiling a package, it turned out to be invalidated other package. When I recompiling again the other package, it also invalidated another package..oh no!!! I would say this as "lingkaran setan" *jejeje

Well, there is a simple way to avoid this (acted like a pro, wacks!! huhu) .Rather than recompiling the entire package, just try to recompile only the package bodies, that is if the package spec is not invalid. Compiling the entire package can invalidate other objects; compiling just the body avoids this.

Yeah, another stupidity of me, arrgghh…But, at least I manage to get out from the "lingkaran setan" ^^

First, I dont know what autonomous means..hehehe, lack of vocabulary :p

I am quotting from online dictionary, it is said that Autonomous means not controlled by others or by outside forces; independent. Combine it with transaction, it becomes an independent transaction, not controlled by other transactions.

Now, the meaning of autonomous transaction in PL/SQL pretty clear:

"Pragma Autonomous Transaction is which is executed in the execution of another transaction known as parent transaction which is independent of it or the whole transaction splits in to two parts known as parent& child will be independent of both but have the same relationship."

When you are writing a PL/SQL block (procedure or trigger) with insert or update statement followed by commit, try to include PRAGMA AUTONOMOUS TRANSACTION in the declare part. It will perform that commit as an independent transaction despite of other transaction is being rolled back or commited.

In the above example, we created a procedure INSERT_INTO_T. In this procedure we used the new pragma AUTONOMOUS_TRANSACTION. That directive tells the database that this procedure, when executed, is to be executed as a new subtransaction – independent from its parent transaction. This procedure simply inserts a record with the value of ‘1’ into the table T and commits. We then proceed to create an anonymous PL/SQL block in which we insert the value of ‘-1’ into T, invoke the stored procedure INSERT_INTO_T and rollback. Prior to autonomous transactions, the commit in the subroutine INSERT_INTO_T would have committed not only the work it performed (the insert of ‘1’) but any outstanding work the session had performed and not yet committed (the insert of ‘-1’ in the anonymous block). The rollback would have rolled back no work since the commit in the procedure would have committed both inserts. We see this is not the case with autonomous transactions. The work performed in the procedure marked with AUTONOMOUS_TRANSACTION was committed however the work performed outside the autonomous transaction was rolled back.

What is it used for? It is for audit trail purpose. And what is audit trail used for??? Hm, still a mystery for me ^^ next time laaahhh

UNION and UNION ALL operations are used to combine two or more queries in a table.

UNION is best used for retrieving data in a sort-unique way. Oracle will process each data retrieved from the table and compare it if there is double values. UNION operation eliminates the duplicated data from the result set. It is usefull if you want distinct value to be retrieved from both collections. This extra process could takes time.

UNION ALL is best used when you want to join two or more tables without care about data duplication. UNION ALL operation is much faster than UNION because it does not require extra process for removing duplication.

So which one provides better performance? It depends on your need. But when you look at the auto trace result for both of them. On the execution plans, you can notice that UNION operation uses UNION ALL operation in execution. Shortly, it can be said that UNION = UNION ALL + sort operations.

So the best practice for this would be to use UNION ALL instead of UNION (whenever possible).

Hm…sometimes tuning the query just did not work. It did not give a big impact on how long the result will be fetched by the database (Oracle). I noticed, finally, there is something wrong with tables that I use for my query..which eventually leads to the poor performance of my query…hohoho…

First is to make sure that the problem does not come from your query. To make it sure, check wether all join condition have been made using indexed fields. Also, avoid string comparison and ‘IN’ clause. It is faster for database to process numeric value rather than text/string.

The idea of index is that it creates a glossary for each table. It main purpose is to avoid Oracle from doing the full scan..So by using indexes, rather than scan the whole tables in every of its fields (which are very time consuming and block consuming of buffer cache) , it will scan only the index. Finally after it find the desired result, it will go to the table to fetch the query result. Basically, index is like a pointer or just like a glossary in books. Surely, it helps a lot to make the query much much much faster ^^

Secondly, check when is the last time this table has been analyzed..including all of the indexes. Reanalyzing both table and its indexes will enhance the query performance. How big is the impact relies on how fast the data growing in those tables. For tables that growth rapidly and used actively by the application, it is always a good practice to analyze that tables in defined time manner. Honestly, I dont know what analyze table it is..*hahaha (gotcha)..But, I think it is a way for Oracle to generate a statistic towards data that is mostly accessed by the program/query. So, by creating this statistic, it will help the database to find the desired data quickly… (just a tought)

I tried this one and I can save around 20 seconds for my queries…First time I ran it, it took around 18 secs, after analyzing the tables and its indexes..it took only 3-5 secs..yippie ^^

O..and if you already try to tune the query, indexing table, analyzing table, increase cache size, increase ora parameters..and still..it did not work..try to recreate the table..SOMETIMES it works..hahaha, what an extreme solution..oh, not good not good… @@’)

*I AM A NEWBIE, so dont trust every single word that I wrote here..kekeke