Karl about the Oracle Database

Hi reader,

in future t itry to concentrate more about Architecture and Design topics then on posting SQL stuff like many many other good Oracle Bloggers do.

Visit my new Blog at http://orcasoracle.squarespace.com/

I am not sure what’s the future of this blog

my be near end of the year it will be closed

Karl

Hi reader,

if you have to deal with datawarehouses you need to implement dimension tables. A dimension could be the time dimension - a table of month Id’s for example to aggregate data per month. How to generate  such month id’s (YYYYMM)?

Simply use the model clause - some Oracle 10g spreadsheet functionality and generate a bunch of cells with specified rules.

To get a stream of years execute following SQL :

SELECT Integer_Value AS YEAR

 FROM Dual

 WHERE 1=2

 MODEL DIMENSION BY(0 AS Key)

       MEASURES(0 AS Integer_Value)

       RULES UPSERT(Integer_Value [ FOR KEY FROM 1900 TO 2100 INCREMENT 1 ] = Cv(KEY))

SQL> SELECT Integer_Value AS YEAR
  2    FROM Dual
  3   WHERE 1 = 2
  4   MODEL DIMENSION BY(0 AS Key)
  5         MEASURES(0 AS Integer_Value)
  6         RULES UPSERT(Integer_Value [ FOR KEY FROM 1900 TO 2100 INCREMENT 1 ] = Cv(KEY))
  7  ;
 
      YEAR
———-
      1900
      1901
      1902
      1903
      1904
      1905
      1906
      1907

To get a stream of months execute following statement

SELECT Integer_Value AS MONTH

  FROM Dual

 WHERE 1 = 2

 MODEL DIMENSION BY(0 AS Key)

       MEASURES(0 AS Integer_Value)

       RULES Upsert(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key))

SQL> SELECT Integer_Value AS MONTH
  2    FROM Dual
  3   WHERE 1 = 2
  4   MODEL DIMENSION BY(0 AS Key)
  5         MEASURES(0 AS Integer_Value)
  6         RULES Upsert(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key))
  7  ;
 
     MONTH
———-
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
 
12 rows selected
 

To get the combination of both use cartesian product of month and year stream

SELECT YEAR , MONTH, (YEAR * 100) + MONTH AS MONTHID FROM (

  SELECT Integer_Value AS YEAR

    FROM Dual

   WHERE 1 = 2

   MODEL DIMENSION BY(0 AS Key)

         MEASURES(0 AS Integer_Value)

         RULES UPSERT(Integer_Value [ FOR Key FROM 1900 TO 2100 Increment 1 ] = Cv(Key))),

(

  SELECT Integer_Value AS MONTH

    FROM Dual

   WHERE 1 = 2

   MODEL DIMENSION BY(0 AS Key)

         MEASURES(0 AS Integer_Value)

         RULES UPSERT(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key))) 

SQL> SELECT YEAR , MONTH, (YEAR * 100) + MONTH AS  MONTHID FROM (
  2    SELECT Integer_Value AS YEAR
  3      FROM Dual
  4     WHERE 1 = 2
  5     MODEL DIMENSION BY(0 AS Key)
  6           MEASURES(0 AS Integer_Value)
  7           RULES UPSERT(Integer_Value [ FOR Key FROM 1900 TO 2100 Increment 1 ] = Cv(Key))),
  8  (
  9    SELECT Integer_Value AS MONTH
 10      FROM Dual
 11     WHERE 1 = 2
 12     MODEL DIMENSION BY(0 AS Key)
 13           MEASURES(0 AS Integer_Value)
 14           RULES UPSERT(Integer_Value [ FOR KEY FROM 1 TO 12 INCREMENT 1 ] = Cv(Key)))
 15  ;
 
      YEAR      MONTH    MONTHID
———- ———- ———-
      1900          1     190001
      1900          2     190002
      1900          3     190003
      1900          4     190004
      1900          5     190005
      1900          6     190006
      1900          7     190007
      1900          8     190008
      1900          9     190009
      1900         10     190010
      1900         11     190011
      1900         12     190012
      1901          1     190101

Now you get your monthid without writing one line of PL/SQL or the need of base data.

HTH Karl Reitschuster

Hi reader,

today i imported a database dump (Wow!!).  When all tables were imported i waited for a while. Usually after Import of the tables all procedural objects and views are created. Now after some minutes i got impatient and i looked into the current session SQL of imp and recognized following CREATE INDEX Statement :

CREATE INDEX "CUSTOMER_FX04" ON "CUSTOMER" (UPPER(TRIM("SHORTNAME")) , "CUSTOMERTYPE" ) 
       PCTFREE 5 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 52428800 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
       TABLESPACE "APPTSIXL" LOGGING

What??? An Index Create still after all Tables are already imported? The thing behind is that the index is a function based index. And a function indeed could be not only a built in but also a stored function. So Index creation of function based indexes starts after the procedureal objects were created. That’s the reason too for some import delays.

Karl Reitschuster

Hi Reader,

In a posting in  the Oracle Xing Forum Sven Vetter showed a very simple way to write for example a GUID generator Statement with CONNECT BY;

just

SELECT Sys_Guid()

 FROM  Dual

CONNECT BY LEVEL <= 10

;

SQL> SELECT Sys_Guid()
  2    FROM Dual
  3  CONNECT BY LEVEL <= 10;
 
SYS_GUID()
——————————–
2EDF60AEC8A41F7AE040A98C881B4573
2EDF60AEC8A51F7AE040A98C881B4573
2EDF60AEC8A61F7AE040A98C881B4573
2EDF60AEC8A71F7AE040A98C881B4573
2EDF60AEC8A81F7AE040A98C881B4573
2EDF60AEC8A91F7AE040A98C881B4573
2EDF60AEC8AA1F7AE040A98C881B4573
2EDF60AEC8AB1F7AE040A98C881B4573
2EDF60AEC8AC1F7AE040A98C881B4573
2EDF60AEC8AD1F7AE040A98C881B4573
 
10 rows selected
 
SQL>

Impressed?
Karl Reitschuster

NOTE:

As Laurent Schneider mentioned - this is not normal CONNECT BY behavior and should not work at all - it’s a bug. You must use the PRIOR Operator to describe the parent relationship.

Hi reader,

some aspects i need to remember in future installation i want to record here. SuSE Linux 10.1 is different from the SLES/OpenSuSE  Installations. Libraries,  Development Tools, … are missing.

In order to run Oracle 10G installation on SuSE 10.1 you must

• use DVD Media - only on DVD the 64Bit packages are available. Otherwise you could do a network install.
  
• create Oracle user with dba as primary group
  
• Install the C/C++ Compiler packages - without them you cannot relink the oracle binaries during installation

  gcc-c++ - The GNU C++ Compiler
  

• Install 32bit shared and development libraries

  glibc-32bit - Standard Shared Libraries (from the GNU C Library)
  glibc-devel-32bit - Include Files and Libraries Mandatory for Development
  

• Install the orarun package
• Support for Asyncronus I/O

  libaio - Linux-Native Asynchronous I/O Access Library
  

• Do not set LD_LIBRARY_PATH - sounds interesting ;-)

  unset LD_LIBRARY_PATH
  

• starting installer with disabling the check of the Operatingsystem.

runInstaller -ignoreSysPrereqs

With these settings i could install Oracle 10.2 on SuSE Linux 10.1 on AMD 64bit.

Some additional configuration is needed if you want to setup a database

• ulimit - added by orarun tool in  /etc/security/limits.conf
  

## added by orarun ##
oracle  soft    nproc   2047
oracle  hard    nproc   16384
oracle  soft    nofile  1024
oracle  hard    nofile  65536
# End of file

• shared memory parameters - once you installed the orarun package :

/usr/sbin/rcoracle start  # sets SHM paramter
/sbin/chkconfig oracle on # sets SHM parameter during startup

• Uncomment 127.0.0.2 ip address for Hostname and enter the real address for proper access via dbconsole

#127.0.0.2       sv019291.dev.global.taxi.com sv019291
150.179.37.146  sv019291.dev.global.taxi.com sv019291

• ORA-27102: out of memory/Linux-x86_64 Error: 28: No space left on device
  you have to size shared memory properly. Shared memory must be large enough for all started instances.
  But not all physical memory must be allocated; The Value should by <= 75% of total physical memory.

(TotalAllocatedSGA(all instances) < (shmall * PageSize) )
&&
( (shmall * PageSize) <= (TotalPhysicalMemory / 100 ) * 75 )

retrieving Memory pagesize :  getconf PAGE_SIZE

retrieving shmall setting : cat /proc/sys/kernel/shmall

• The network card settings should be checked. Sometimes due to a mismatch of duplex settings for example network speed could drop down < 10%
  ;

  view the settings of your servers network card interface eth0 :
  ethtool eth0

  set network card properties (interface eth0 *only* for example!) : 
  ethtool -s eth0 speed 100 duplex full autoneg off

Will complete the list in future

Karl Reitschuster

Links …

The Installation Guide for SLES 10 : Oracle 10g R2 (10.2.0.1) on SUSE Linux Enterprise Server 10

The Installation Guide for OpenSuSE : Oracle Database10g R2 (10.2.0.1) on openSUSE 10.2

Setting Shared Memory (Puschitz) : Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases

Blog of proper setting of SHMALL : It’s an SGA Explosion

Hi reader,

you find it here

HTH

Karl

hi,

everybody tells you to use bind variables (reduced parses) and that’s ok. But in some circumstances you get a lot of problems with it. Using bind variables with the LIKE operator could cause horrible performance even you index that column and you do not use a ‘%’ at the start of the searched string.

Case 1 comparing with = operator :

WHERE Company.Customer = :P02

                  TABLE ACCESS BY LOCAL INDEX ROWID Depth=18 Object owner=ISIS Object name=COMPANY Cost=4 CPU cost=28254 IO cost=4 Cardinality=1 Bytes=289   Options=BY LOCAL INDEX ROWID  Operation=TABLE ACCESS Optimizer=ANALYZED Partition id=18 Partition start=2 Partition stop=2
                   INDEX RANGE SCAN Depth=19 Object owner=ISIS Object name=COMPANY_IX01 Cost=3 CPU cost=21764 IO cost=3 Cardinality=1  Access predicates="COMPANY"."CUSTOMER"=:P02  Options=RANGE SCAN  Operation=INDEX Optimizer=ANALYZED Partition id=19 Partition start=2 Partition stop=2

Yeah!! the optimizer chooses the index - very fast response - in this case.

Case 2 comparing with LIKE operator :

WHERE Company.Customer LIKE :P02

                  TABLE ACCESS FULL Depth=18 Object owner=ISIS Object name=COMPANY Cost=45241 CPU cost=8354827075 IO cost=44959 Cardinality=346919 Bytes=100259591  Filter predicates="COMPANY"."CUSTOMER" LIKE :P02 Options=FULL  Operation=TABLE ACCESS Optimizer=ANALYZED Partition id=27 Partition start=2 Partition stop=2

Oh my god!!! what is Oracle doing here???? Full Tablescan over 6.6m rows. The reason for is that dummy 5% assumption. Oracle assumes to scan 5% rows (look at the expected cardinality it’s about 5% of 6.8m) of the table using LIKE with bind var. Unfortunately this is the limit for a full table scan.

We fixed that with a higher affinity to index blocks. Also there is an internal parameter known to cahnge globally the way oracle calculate the expected cardinality of the LIKE operator. But a better way in the future release of the software will be: If the queried field is full qualified (means full length without ‘%’ or ‘_’) then the comparison operator is used instead of like.

cheers

Karl

Hi reader,

after a benchmarking test of Components of a CRM System with Oracle 10.2 and Sun Solaris with either AMD Opteron or Sun SPARC IV+ CPU’s i started to think about the definition of Scalability. Her it is:

Scalability is about how strong the increase 
of the Throughput/decrease of Response Time
of an Application or Software Component is
when additional resources of CPU, Memory and I/O are added;

A second one follows :

Scalability is about how strong the decrease
of the Throughput/increase of Response Time
of an Application or Software Component is
when the workload ( Amount of work, Amount of Concurrency ) increases;

Karl Reitschuster

Hi reader,

looked during a data load via SQL*LOADER on the top Activity page of the Oracle Db Console and was fascinated by these tree very nice painted bands of CPU,COMMIT and User I/O . Some kind of beauty ;-)

HTH :-)
Karl Reitschuster

Hi reader,

Once an Oracle Block is read from the disk it gets a place in the buffer cache. How long the block remains cached depends on following parameters.

• if it’s an index block - they are stronger bound to the DEFAULT cache then table blocks resulting in a longer period of cache life cycle.
• if it’s a table block - it will be cached but earlier freed from cache then an index block
• if it’s a frequently accessed (LRU mechanism) a block frequently access will remain in the buffer cache
• if the table block is originated by a long full table scan - a block read by a scattered read on a table which does not fit in a scan window of the buffer cache - it will be displaced immediately from the next blocks.

To guarantee a longer life cycle for a table/index block another cache type i think was introduced with Oracle 8i. The KEEP pool. It tends to keep (to pin) the blocks even they are not frequently accessed and enables caching of all blocks of a segment read with scattered read and not only a subset of it. Thus it is guaranteed that access to a segment cached in KEEP pool will not have any physical I/O. Usually i learnt to keep frequently small objects for example lookup tables into the KEEP pool.

We faced a problem on our company table querying specific company types a full table scan could not prevented. If many columns are used in some reports/lists to filter the result you cannot put an index on every column of the table. The company table is about 1.7G large with about 6.5M of rows. On our test database server two concurrent queries on the company table resulted in  > 60sec instead of 25sec. And even 25 sec are too long.

Having a 64 bit system we decided to upgrade memory and to pin the Company table into the KEEP pool.

Following steps to load the company table int o the KEEP pool:

• set SGA_TARGET to 2.8G - Database is in automatic SGA sizing mode - a restart of the database will be needed
  ALTER SYSTEM SET sga_target=2800M SCOPE=SPFILE
  ;
  
• and then set DBA_KEEP_POOL_SIZE to 1.8G - This reduces available memory for other SGA (Shared pool + DEFAULT buffer cache) components to 1G 
  ALTER SYSTEM SET db_keep_cache_size=1800M SCOPE=BOTH
  ;
  
• Altered the tables buffer cache
  ALTER TABLE COMPANY STORAGE (BUFFER_POOL KEEP)
  ;
  
• Pin the Company Table with a FULL table scan into the keep pool. ( You must use a FULL Hint to prevent an index full scan)
  SQL> SELECT /*+FULL (COMPANY)*/ COUNT(*) FROM COMPANY;
    COUNT(*)
  ———-
     6764758
  SQL>

once loaded the table into the KEEP buffer cache the result of the query response time was < 5sec even with concurrent sessions;

Monitor Buffer Pool Usage (the physical reads on the KEEP pool were caused by the inital load (pin) of the COMPANY Table):

SQL> SELECT Vps.NAME,
  2         Vps.Consistent_Gets,
  3         Vps.Physical_Reads
  4    FROM V$BUFFER_POOL_STATISTICS Vps;

NAME                 CONSISTENT_GETS PHYSICAL_READS
——————– ————— ————–
KEEP                        35762385         194722
DEFAULT                    306683092        3568628

SQL>

To go this way successfully you need following resources

• fast CPU; for high LIO-Rate - on our Dual Xeon’s we had a rate of 1.4G/sec
• higher number of CPU >= 4
• Server optimized Mainboard/Memory access
• fast Memory

Conclusion

If your site drives a 64bit System. You could benefit from loading very large objects into the KEEP pool. Sure - this is not an all round optimisation for your weak SQL and your weak physical and logical design of your database/schema
;-)

HTH
Karl Reitschuster