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,

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

Hi Reader,

had some problems with the installation i want to mention for myself to remember next time;

Strange security settings of Oracle Software Directories

Hi Reader,

after installing Oracle 10.2 64bit on Suse Linux 10.1 I started the dbconsole. A page i visited soon was Tablespace page. Usually on our Windows Server 2003 32Bit system, with the same Oracle version, the Tablespace query lasts for about 20 seconds! On the Linux System with the same number of Application tablespaces the Query response time was < 0.5 seconds. I thought this could not be the result being on just another operating system or driving the database with 64bit.

After reviewing the explain plan i detected the access to the Table RECYCLEBIN$; As by default Tables dropped are moved logically to the RECYCLEBIN$; The segments remain until the place in the tablespace exceeds; And now I recognized a big difference; On the slow system the RECYCLEBIN$ had about 2,600 rows on the fast system only 16 rows.

I started to purge all these tables of the different users and believe me or not the Query got faster and faster; Now with 12 rows I have the same response time for both Systems Linux 64 bit / Windows 2003 32 Bit;

So keep your Oracle 10.2 RECYCLEBIN$ as small as possible;

HTH Karl Reitschuster

The Query :

SELECT Dtp.Tablespace_Name "TABLESPACE_NAME",
       Dtp.Status "STATUS",
       Dtp.Contents "CONTENTS",
       Dtp.Extent_Management "EXTENT MANAGEMENT",
       Dtp.Allocation_Type "ALLOCATION TYPE",
       Dtp.Logging "LOGGING",
       Nvl(Ts.Bytes,
           0) "SIZE (M)",
       Dtp.Initial_Extent "INITIAL EXT SIZE (K)",
       Dtp.Next_Extent "Next Extent",
       Dtp.Pct_Increase "INCREMENT PCT",
       Dtp.Max_Extents "MAX EXTENTS",
       Nvl(Ts.Bytes - Nvl(f.Bytes,
                          0),
           0) "USED BYTES"
 FROM Sys.Dba_Tablespaces Dtp,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Data_Files
         GROUP BY Tablespace_Name) Ts,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Free_Space
         GROUP BY Tablespace_Name) f
 WHERE Dtp.Tablespace_Name = Ts.Tablespace_Name(+)
   AND Dtp.Tablespace_Name = f.Tablespace_Name(+)
   AND NOT
        (Dtp.Extent_Management LIKE ‘LOCAL’ AND Dtp.Contents LIKE ‘TEMPORARY’)
UNION ALL
SELECT Dtp.Tablespace_Name "TABLESPACE_NAME",
       Dtp.Status "STATUS",
       Dtp.Contents "CONTENTS",
       Dtp.Extent_Management "EXTENT MANAGEMENT",
       Dtp.Allocation_Type "ALLOCATION TYPE",
       Dtp.Logging "LOGGING",
       Nvl(a.Bytes,
           0) "SIZE (M)",
       Dtp.Initial_Extent "INITIAL EXT SIZE (K)",
       Dtp.Next_Extent "Next Extent",
       Dtp.Pct_Increase "INCREMENT PCT",
       Dtp.Max_Extents "MAX EXTENTS",
       Nvl(t.Bytes,
           0) "USED BYTES"
 FROM Sys.Dba_Tablespaces Dtp,
       (SELECT Tablespace_Name,
               SUM(Bytes) Bytes
          FROM Dba_Temp_Files
         GROUP BY Tablespace_Name) a,
       (SELECT Ss.Tablespace_Name,
               SUM((Ss.Used_Blocks * Ts.Blocksize)) Bytes
          FROM Gv$sort_Segment Ss,
               Sys.Ts$         Ts
         WHERE Ss.Tablespace_Name = Ts.NAME
         GROUP BY Ss.Tablespace_Name) t
 WHERE Dtp.Tablespace_Name = a.Tablespace_Name(+)
   AND Dtp.Tablespace_Name = t.Tablespace_Name(+)
   AND Dtp.Extent_Management LIKE ‘LOCAL’
   AND Dtp.Contents LIKE ‘TEMPORARY’

Hi Reader,

Inserting on a table with a lot of indexes could cause the wait event db file sequential read. For every row to be inserted the index must be scanned and updated when an indexed column was specified in the DML statement. With about 150 rows a second the insert operation was very slow. I enabled extended trace 10046 event and reviewed the generated raw trace file;

The traditionaly way - trace files

In the 10046 event trace file you will find the Cursor definiton section of the insert statement:

=====================

PARSING IN CURSOR #8 len=693 dep=1 uid=50 oct=2 lid=50 tim=226692485 hv=1579689243 ad=’396bb604‘

INSERT INTO COMPANY (…) VALUES (:B18 , NULL, 0, NULL, NULL, SYS_GUID(), :B17 , NULL, NULL,