Deutschsprachiger Datenbank & Cloud Technologie Blog

My favourite feature inside Oracle Database: session parameters #JoelKallmanDay

October 11, 2021
Text Size 100%:

Today is a special day - the Joel Kallman Day (see #JoelKallmanDay) former OTN Appreciation Day. I'd like to reach as many people in the Oracle community as possible. Therefore this blog post is in english language - it's an exception of course.

If you don't know what is meant by Joel Kallman Day, please read Tim's blog post about this initiative. You can find the infromation on this here

So what is my favorite feature in the Oracle database? Since I already experienced many new releases and features in the database, the decision was not easy for me.
What should I write about? After thinking about it for a while, I decided to choose a general and very useful capability of the Oracle Database which is constantly being extended: 
the capability to use a feature without changing existing code and without impacting other users 
- in short Oracle session parameter

Here is my list of useful session parameters with a short description. Of course it is not complete .. and feel free to extend this list.

RESULT_CACHE_MODE (MANUAL/FORCE): when the ResultCache operator is added.
RECYCLEBIN (ON/OFF): if dropped tables go into the recycle bin and can be recovered.
OPTIMIZER_USE_INVISIBLE_INDEXES (TRUE/FALSE): enables or disables the use of invisible indexes
INMEMORY_QUERY (ENABLE/DISABLE) is used to enable or disable in-memory queries.
OPTIMIZER_USE_SQL_PLAN_BASELINES (TRUE/FALSE): enables or disables the use of SQL plan baselines.  
OPTIMIZER_IGNORE_HINTS (TRUE/FALSE): enables embedded hints to be ignored.
OPTIMIZER_IGNORE_PARALLEL_HINTS (TRUE/FALSE): enables embedded parallel hints to be ignored.
PARALLEL_DEGREE_POLICY (MANUAL/LIMITED/AUTO/ADAPTIVE): specifies whether automatic degree of parallelism, statement queuing, and in-memory parallel execution will be enabled.
OPTIMIZER_USE_PENDING_STATISTICS (TRUE/FALSE): specifies whether the optimizer uses pending statistics.
OPTIMIZER_FEATURES_ENABLE (e.g. : for enabling a series of optimizer features based on an Oracle release number
OPTIMIZER_MODE (FIRST_ROWS/ALL_ROWS/FIRST_ROWS_[1 | 10 | 100 | 1000] : establishes the default behavior for choosing an optimization approach for the instance
APPROX_FOR_COUNT_DISTINCT (TRUE/FALSE): automatically replaces COUNT (DISTINCT expr) queries with APPROX_COUNT_DISTINCT queries
PLSQL_CODE_TYPE (INTERPRETED/NATIVE): specifies the compilation mode for PL/SQL library units.
Here is one example to show the usage:

-- make the index invisible
alter index ...invisible;

-- execute and check the execution plan
select * from ...;
select * from table(dbms_xplan.display_cursor);

-- enable the usage of invisible indexes
alter session set optimizer_use_invisible_indexes=true;

-- execute and check teh execution plan and notify the difference
select * ... from ...;
select * from table(dbms_xplan.display_cursor);

And do not forget the useful parameters with prefix NLS such  NLS_DATE_FORMAT, NLS_SORT, NLS_TERRITORY, NLS_LANGUAGE etc.
Here is an example: 
NLS_DATE_FORMAT (e.g. "MM/DD/YYYY"): specifies the default date format to use with the TO_CHAR and TO_DATE functions. 

Try it yourself and have fun with it!

Ulrike Schwinn

Studierte Mathematik und ist bei der Firma Oracle als Systemberaterin tätig. In ihrer Funktion berät und schult sie Kunden in Fragen der neuesten Datenbank und Cloud Technologien. Sie wirkte an mehreren Oracle-Buchprojekten mit und ist als Referentin auf IT Kongressen vertreten.

Previous Post

Oracle Datenbanken - Monthly News: September Ausgabe

Ulrike Schwinn | 1 min read