Oracle 10g SQL Statement Tuning Course

A good, informative and broadening course authored by Hasan Tonguç Yılmaz. It lasted for 4 days hands on course. As a brief it consists of following topics, and exercises:

• Oracle SQL hints


• Oracle performance related views, and PLSQL packages


• BULK collect


• SQL bind variables


• Heap, Index Organized, Partitioned, Clustered tables


• Oracle sources regarding SQL statements (SQL Reference: http://tahiti.oracle.com, Package examples: http://psoug.org/library.html, For problems , articles, and solutions: http://metalink.oracle.com/, http://otn.oracle.com, http://asktom.oracle.com/tkyte/)


• Indexes, FTS


• Join methods, join orders

I will add above list later!

Hidden Parameters

To list hidden parameters and their meanings, you can use following sql:

SELECT   x.ksppinm NAME,
DECODE (BITAND (ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') sesmod,
DECODE (BITAND (ksppiflg / 65536, 3),
1, 'IMMEDIATE',
2, 'DEFERRED',
3, 'IMMEDIATE',
'FALSE'
) sysmod,
ksppdesc description
FROM SYS.x_$ksppi x
WHERE x.inst_id = USERENV ('INSTANCE')
AND TRANSLATE (ksppinm, '_', '#') LIKE '#%'
ORDER BY 1;

To list current values of hidden parameters:

SELECT   a.ksppinm "Parameter", b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND SUBSTR (ksppinm, 1, 1) = '_'
ORDER BY a.ksppinm;

Single Quote '

To insert or select single quote you can use chr(39). For example;

SQL> select chr(39) from dual;
'

SQL> CREATE TABLE MYTABLE (F1 VARCHAR2(100));

Table created.

SQL> insert into MYTABLE values('Orange Cote D'||chr(39)||' Ivoire S.A.');

1 row created.

SQL> select * from MYTABLE;
Orange Cote D' Ivoire S.A.

SQL>

hello world from wordpress!

Streams makes lake, many smalls make big! Then share, share, share... Many people even experts still continue to learn from others, learners live! I started this blog because I want to log what I learnt, and share with the Oracle community.

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!