Aug 16, 2011

Oracle Exdata

Exdata is a hardware machine which runs oracle database; supports OLTP, Database
Exologic is a hardware machine for application and primarily for cloud computing
Exadata X2-2 Hardware Architecture (FULL Rack)
DBA's has to think about storage server, DB server, storage connecting devices, fiber channel connectivity, Network components, OS, storage drives this all includes in Exdata

In Storage Grid of Exdata it contains 2 Xenon CPU with computing power which take care of processing inside the storages 
Flash is the key performance of Exdata machine. Its a kind of h/w in a pci slot where all your data is stored intermediately between db server and storage server. 
InfiniBand Networks (fiber channel has only 10gb/s) which is also a key performance of Exdata ;which used to transfer data between db server to storage which has high bandwidth

In Exdata is a preconfigured plug and play software .

Key Performance of Exdata
1)      Intelligent Storage Grid

2)      Hybrid Columnar Compression will compress 10 times the data

3)      Smart Flash Cache (patented product of Oracle) different from SSD; it’s a second SGA at storage level which also contains algorithm which drives the flash cache.

In Traditional San if we increase the storage it will not increase the db performance because the spindles of hardware will perform as usual to read from storage
In Exdata there storage is intelligent.  ( to get which is the fastest machine)
ROW level locking
it means any moment of time any person can do DML at a moment of time so entire table is locked ,so oracle introduced rowlevel locking mechanism which makes oracle faster
Rowlevel is managed at block level ultimate storage element of ur row.
RMAN is the backup in exdata


Once the database time zone is set (with ALTER DATABASE SET TIME_ZONE command) and you have defined single table with a TSWLTZ column in the database, you're locked to that time zone - Oracle won't let you change the database time zone because this will implicitly alter all TSWLTZ columns data - remember that they only hold the offset from the database time zone? If you will change the time zone, TSWLTZ will yeld different timestamps because it will add or substract the offset from new database time zone, not the one that was in effect when you created the row, and this will result in wrong data being returned. So if you attempt to alter the database time zone with at least one column of TSWLTZ type defined in the database, Oracle will throw this rather misguiding error.
Worse yet, TSWLTZ data type is not suited well for distributed systems where several databases around the world serve the application: each database will probably have its own time zone and TSWLTZ data in these databases will not be interchangeable between them. TSWTZ is free from this effect, because original time zone is always known. But then again, you certainly would want to present that data to users residing in different time zones and you would want to show what time it was in THEIR time zone, won't you? Oracle knew about that, and they extended the Datetime Expression syntax with AT clause to allow you to convert TSWTZ to different time zones easily:

 select * from V$TIMEZONE_NAMES;

SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM') "US/Eastern",
TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR') AT TIME ZONE 'US/Pacific','YYYY-MM-DD HH24:MI TZH:TZM') "US/Pacific" FROM DUAL;
SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 Europe/Moscow','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM')"Daylight" FROM SYS.DUAL;

select SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') kl,SYS_EXTRACT_UTC(SYSTIMESTAMP) sy , CURRENT_TIMESTAMP,  DBTIMEZONE , localtimestamp ,extract(hour from systimestamp),systimestamp,to_timestamp_tz('10/07/2004', 'DD/MM/YYYY') now,systimestamp(2) ,SESSIONTIMEZONE from dual;


Aug 3, 2011

Spatial Error Related with SDO_NN

SELECT sdo_nn_distance (1) dist, a.cid,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b
WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'
AND b.gid = 1341

When executing this sql  following error occurs .

ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9

We can resolve this error by removing  SDO_NN_DISTANCE ancillary operator from the query but what to do when we require distance what I have noted   is that it is due to non-spatial column which is in where clause is not indexed .When you create a index for that non-spatial column the query will execute

SQL > create index idx on golf_boundaries(gid) ;

SELECT sdo_nn_distance (1) dist, a.cid,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,
sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b
WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'
AND b.gid = 1341 ;