SOUG last week

duplicate post of http://laurentschneider.blogspot.com/2006/09/soug-last-week.html

SOUG last week

The documents from last SOUG special interest group with Tom Kyte are now available for downloadable on the soug.ch homepage (under history).

The day started with Sven Vetter, who talked about SLA management pack for Enterprise Manager Grid Control 10gR2. We saw also how to define a custom shell script and let OEM generate a graphic over time. He talked about "beacons", a kind of interface to define application interaction with the grid.

Than came an interesting presentation about Performance Tuning from Patrick Schwanke. He talked about views like DBA_HIST_SQLTEXT and DBA_HIST_SQLBIND. I also learnt the virtual index trick : There is a parameter called _use_nosegment_indexes with allow you to generate EXPLAIN PLAN for a segment without segment. There is a magic keyword called NOSEGMENT. This mechanism is used internally by Oracle Tuning Pack, and by other tools like the ones from Quest.


SQL> create table t as select * from all_objects;

Table created.

SQL> create index i on t(object_id) NOSEGMENT;

Index created.

SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

SQL> select * from t where object_id<10;

Execution Plan
----------------------------------------------------------
Plan hash value: 242607798

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 768 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 768 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | 307 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"<10)

Note
-----
- dynamic sampling used for this statement

SQL> alter session set "_use_nosegment_indexes"=false;

Session altered.

SQL> select * from t where object_id<10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 768 | 151 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 6 | 768 | 151 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"<10)

Note
-----
- dynamic sampling used for this statement

SQL> drop index i;

Index dropped.


Later, Tim Polland talked about Texas Memory and the RAMdisks. It is a piece of hardware which use memory instead of disk to store the datas. The hardware has a kind of RAID mechanism for consistency, and it is not supposed to lose data (!). It is quite different from a typical disk cache, because it does not "cache" the datas, it just saves them in memory. Well, you are not supposed to use this to stores a terabyte of datas, but you could store only your most time-critical datas there, for example move a table to a different tablespace, and store that table on the RAMDISK, also the redo logs, and eventually the undo/system tablespaces too. The RAMDisk hardware is not specific for Oracle. Some "entry-level" ramdisk can offer something like 400 Megabytes/seconds data with nanoseconds access time. Well, it is the speed of memory, so it is also the price of memory. So do not expect to have this at home to play your favorite games !

In the afternoon, we started with Oracle Benchmarks with Manfred Drozd. Very interesting presentation, which shows that there is no "quick" benchmark, but a benchmark should consider I/O, PL/SQL performance, amount of disks, volume manager/ASM, cpu types/count, OS, architecture, Oracle Version, etc. The performance for select, for insert, for update, all those may differ from one system to another.

Real world example just followed with the presentation of a study case in the swiss post. The comparison was mainly between Sun Solaris on Sparc and Linux on x86_64. Well, there was no Better/Worst answer, you know, like is real world...

Last but not least, Tom Kyte, the "Tom" behind asktom, did talk about Instrumentation and the advantage -the need- of using debugging info, with DBMS_APPLICATION_INFO, with your own debug procedure, with Log4Plsql/Log4J packages. The tools we show were DBMS_MONITOR (10g) and trcsess tool (located in $ORACLE_HOME/bin), DBMS_TRACE and PL/SQL conditional compilation. DBMS_TRACE is usefull for detecting catched exception :


SQL> @?/rdbms/admin/dbmspbt
SQL> @?/rdbms/admin/prvtpbt.plb
SQL> @?/rdbms/admin/tracetab
SQL> exec dbms_trace.set_plsql_trace(dbms_trace.trace_all_exceptions)

PL/SQL procedure successfully completed.

SQL> exec begin dbms_output.put_line(1/0); exception when others then null; end;

PL/SQL procedure successfully completed.


SQL> select EXCP,EVENT_UNIT_KIND,ERRORSTACK from plsql_trace_events where ERRORSTACK is not null;
EXCP EVENT_UNIT_KIND
---------- --------------------
ERRORSTACK
----------------------------------------
1476 ANONYMOUS BLOCK
ORA-01476: divisor is equal to zero


Xiang-Qi

Sunday, November 26th, 2006, I will defend my Title in the Chinese Chess Swiss Championship.

It is free and open to anyone !

10gR2 solaris x86 is out

Oracle Database 10g Release 2 (10.2.0.2)
Enterprise/Standard Edition for Solaris Operating System (x86)

Camel

Camel
Originally uploaded by laurentschneider.

Loïc is driving a camel

NLS_LANG=german_switzerland

There is a bug about NLS_LANG and SWITZERLAND in 10g.

This is because the tausend separator has changed from 9i (.) to 10g (').

Check Note 4598613.8

The workaround is to not use NLS_LANG=german_switzerland, but if you have different NLS parameters on the client and on the server, than you will have other problems, ex: questionable statistics by exp/imp.

OK, here is the bug :


$ export NLS_LANG=german_switzerland.we8iso8859p1
$ sqlplus -L scott/tiger

SQL*Plus: Release 10.2.0.2.0 - Production on Do Sep 21 13:50:05 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-02248: Ungültige Option für ALTER SESSION


SP2-0751: Anmeldung bei Oracle nicht möglich. SQL*Plus wird beendet


and my workaround


$ export NLS_LANG="" NLS_TERRITORY=switzerland NLS_LANGUAGE=german
$ sqlplus -L scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Sep 21 13:51:30 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> select * from NLS_SESSION_PARAMETERS ;
PARAMETER VALUE
---------------------- -----------
NLS_LANGUAGE GERMAN
NLS_TERRITORY SWITZERLAND
NLS_CURRENCY SFr.
NLS_ISO_CURRENCY SWITZERLAND
NLS_NUMERIC_CHARACTERS .'
NLS_CALENDAR GREGORIAN
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_DUAL_CURRENCY SF

17 Zeilen ausgewahlt.

SQL> select to_char(1000,'9G999') from dual;

TO_CHA
------
1'000

SQL> quit
Verbindung zu Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options beendet




So it is possible to use german_switzerland. Not sure if this workaround is bullet-proof. Metalink says it is fixed in 11g

OOW2006 Blogger Meetup : Tuesday 24th October, Thirsty Bear Brewing Company, 7pm

blogger meetup organized by Mark Rittman

KEEP DENSE_RANK versus ROW_NUMBER()

I often see questions like

How do you get the row of each department with the highest salary

In case you only want 1 row, you have two modern solutions :
Analytics, which is trend, and KEEP, which is not very known

The old fashion would be something like where s in (select max())

Ok, let's start with analytics

SQL> select ename,deptno,sal
  2  from (select ename,deptno,sal,
  3  row_number() over (partition by deptno
  4  order by sal desc,empno) r from emp)
  5  where r=1;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

and the KEEP method, which is a special aggregation

SQL> select max(ename) keep (dense_rank first
  2  order by sal desc,empno) ename,
  3  deptno,max(sal) sal
  4  from emp group by deptno;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

the second one should be more performant

SQL> select count(*) from emp2;
  COUNT(*)
----------
    917504

SQL> select max(ename) keep (dense_rank first
  2  order by sal desc,empno) ename,
  3  deptno,max(sal) sal
  4  from emp group by deptno;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

Elapsed: 00:00:01.00
SQL> select ename,deptno,sal
  2  from (select ename,deptno,sal,
  3  row_number() over (partition by deptno
  4  order by sal desc,empno) r from emp)
  5  where r=1;
ENAME          DEPTNO        SAL
---------- ---------- ----------
KING               10       5000
SCOTT              20       3000
BLAKE              30       2850

Elapsed: 00:00:01.43

Swiss Oracle User Group

On wednesday, there is a DBA day in the SOUG community.
DBA-SIG 06/4

The highlight of this session will be Tom Kyte, speaking about Oracle System Architecture.

Check the homepage of SOUG.CH for registration

_Workshop _be_informed_

Tomorrow LC Systems is organizing an event about Identity Management in Zurich.

The focus will be on Sun Java System Directory Server and Oracle Virtual Directory.

wget from otn

wget is a well-known command line utility for downloading files from the internet/intranet.

I like to use the limit-rate, so that my coworkers still can surf. Until today, I never succeeded to download from otn, because wgetting software required me to log in and accept the export restriction

How to automate login is actually extremly easy :

1) open your mozilla browser, go to the page, login, accept the export, download the file, click properties, copy paste the "Saving From", Cancel
2) find out where is located your cookies.txt file

$ find $HOME -name "cookies.txt"
/home/lsc/.mozilla/default/86iy2n5j.slt/cookies.txt

3) wget

$ wget --load-cookies /home/lsc/.mozilla/default/86iy2n5j.slt/cookies.txt --limit-rate 250k http://download-uk.oracle.com/otn/compaq/oracle10g/10202/10202_database_HP-Tru64.zip
100%[++++++++++++++++++++++++++++======>] 1,081,211,566 254.98K/s ETA 00:00

16:48:05 (249.99 KB/s) - `10202_database_HP-Tru64.zip.1' saved [1,081,211,566/1,081,211,566]



It works!

generate dml

I just discovered that cool new function in SQL/Developer

just right click on the table and chose export sql insert


-- INSERTING into EMP
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7369,'SMITH','CLERK',7902,to_date('1980-12-17','DD-MON-RR'),800,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7499,'ALLEN','SALESMAN',7698,to_date('1981-02-20','DD-MON-RR'),1600,300,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7521,'WARD','SALESMAN',7698,to_date('1981-02-22','DD-MON-RR'),1250,500,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7566,'JONES','MANAGER',7839,to_date('1981-04-02','DD-MON-RR'),2975,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7654,'MARTIN','SALESMAN',7698,to_date('1981-09-28','DD-MON-RR'),1250,1400,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7698,'BLAKE','MANAGER',7839,to_date('1981-05-01','DD-MON-RR'),2850,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7782,'CLARK','MANAGER',7839,to_date('1981-06-09','DD-MON-RR'),2450,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7788,'SCOTT','ANALYST',7566,to_date('1987-04-19','DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7839,'KING','PRESIDENT',null,to_date('1981-11-17','DD-MON-RR'),5000,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7844,'TURNER','SALESMAN',7698,to_date('1981-09-08','DD-MON-RR'),1500,0,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7876,'ADAMS','CLERK',7788,to_date('1987-05-23','DD-MON-RR'),1100,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7900,'JAMES','CLERK',7698,to_date('1981-12-03','DD-MON-RR'),950,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7902,'FORD','ANALYST',7566,to_date('1981-12-03','DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (7934,'MILLER','CLERK',7782,to_date('1982-01-23','DD-MON-RR'),1300,null,10);


It is quite handy to post insert statements on forums, or move a table from one database to another just with copy paste, or export only a subset of the columns, because you can export a view too

children picture privacy

Cannot you see the pictures anymore? Ask me to be your flickr friend !

Tom Kyte in France, Switzerland and Austria

Register in Paris (18-19 sep), Zurich (21-22 sep) or Wien (25-26 sep) at oracle university for the tom kyte workshop.

Lutz Hartmann wrote about this event in his blog