How to determine if it is necessary to increase the sga and the pga in Oracle database

I'll outline SGA and PGA, and then will try to give you a couple of appropriate examples.

SGA consists of many structures, shared by many processes (both background and foreground). Just a few of them:

• shared pool
• buffer cache
• log buffer

If you undersize, for example, buffer cache, the queries will perform slower. Buffer cache is the area of memory used to hold the blocks of data read from and written to data files. Whenever you query a table, data is read from the data files in the units called blocks. The requested data is not returned straight to the user process, instead it is copied to the buffer cache, and then is read from the buffers in the buffer cache if other queries request the same data. Undersized buffer cache leads to lots of I/O and thus adversely impacts performance.

PGA, on the other hand, is the area of memory private to one process. It cannot be shared shared by processes. It is used to maintain the process state. One of the parts of PGA is sort area. It is used to sort a set of rows. In the best case the sort is performed in memory, but if the PGA (sort area in particular) is undersized, the sorting involves the temporary tablespace segments residing on disk. This makes the queries perform slower because disk is one of the bottlenecks in the computer systems.

For more info read "Overview of the Program Global Area" and "Overview of the System Global Area" in Concepts.

The magic key word is NLS (National Language Support). It will define the dateformat, calender format etc.

You can query your current NLS-settings with this command:

SELECT * FROM nls_database_parameters;

If you want to change it in a session or an instance you can query the current settings using this views:

SELECT * FROM nls_session_parameters;
-- or
SELECT * FROM nls_instance_parameters;

You need to set this NLS settings, maybe the proper ISO as character set too.

NLS_LANG=ARABIC_SAUDI ARABIA.UTF8
NLS_LANGUAGE=PERSIAN
NLS_CALENDAR=PERSIAN

You have some possible solutions to setting the NLS. If you want to use it globally I would suggest to set it in the initialization parameters.

You can read more about setting the NLS in the docs.

Oracle 12c should support persian, as stated in this whitepaper in the section "New Locale Coverage".

By the way, if you need a larger multi language support, you may want to use AL32UTF8 instead of normal UTF8 language sets. You can read something about it here and here.