Multimedia Databases, Multi-Terabyte Performance Oracle10 g inter Media

2. Session ID: 40121 Multimedia Databases, Multi-Terabyte Performance Oracle10g interMedia Jeremy FormanComputer System Analyst New Mexico Department of Transportation Jim SteinerSenior Director Server Technologies Oracle Corporation

3. Agenda • Oracle’s Multimedia Capabilities • Oracle10g New Features • How Oracle Compares • How users benefit • Multi-Terabyte, Multimedia databases • Jeremy Forman -- New Mexico DOT

4. Objective • Extend Oracle’s leadership as a platform capable of managing multimedia content as naturally as it does all other business information. • Lower the cost and complexity of developing, deploying and managing business applications which make extensive use of multimedia data.

5. The Media-enabled Oracle Platform • Oracle Database 10g • Storage, management, & retrieval of image, audio, video data • Native format understanding, metadata extraction, methods for image processing • Support for leading streaming media servers • Oracle Application Server 10g • JSP, servlet and PL/SQL application development support • Media Adaptation Services for Wireless • JDeveloper (BC4J/UIX) and Portal integration • Oracle Collaboration Suite • Metadata extraction for OCS Files

6. Oracle10gNew Multimedia Features

7. Multimedia and the Grid • Applications that make extensive use of multimedia face the same challenge as most business applications • Performance • Scalability • High level of service • At lowest possible cost • Multimedia applications often have greater storage, distribution, security, and “demand peaks” requirements • Enterprise Grid Computing benefits multimedia applications through dynamic provisioning of resources

8. New Oracle10gMultimedia Features • Standards Support – SQL/MM Still Image • New version of Java Advanced Imaging and additional image processing operators • Support for additional media formats • Microsoft ASF, MPEG2 & MPEG4 • Microsoft Windows Media Server Plugin • Real Server Plugin for Helix Server • XML DB integration

9. How Oracle’s Multimedia capabilites are better than other DBMSes Only Oracle10g: • Supports media content natively • No manual initiation of separate processes to enable database tablespace to accept media data. • No need for DBAs to initiate these processes for each table where they wish to store media data • Stores all media and its metadata in the same table as the associated relational data • No triggers on each and every media object created to update the separate “administration” tables that contain media objects and metadata. • No added processing and I/O overhead for access and retrieval • Provides Java class libraries and JSP Tag libraries for application development and media access.

10. How do users benefit?

11. Oracle Performs • Fast retrieval • 1TB image repository renders images in Web browser in less than 0.4 second • Load at device speeds

12. Oracle Scales • Multi-terabyte multimedia databases • 140 million images • 5 TB database • Scalable bulk load and process • Parallel processes load 300,000 images/hour • Bulk process – tiff to gif conversion, scale

13. Oracle is Easier to Manage • Multimedia VLDB is easier to manage using Oracle • RMAN for very large backup (3TB database) • Single DBA for 5TB database

14. Oracle is More Secure • Banks use it • Multimedia data inherit all of the built in security features of the Oracle Database • authentication, auditing, encryption, access control . . . • Image and media features enhance security applications

15. With JSP Tag Library: (14 point font) <ord:embedImage connCache = <% java.util.Vector otherValuesVector = new java.util.Vector(); otherValuesVector.add(fd.getParameter("desc")); otherValuesVector.add(fd.getParameter("loc")); %> “ mediaParameters = "photo" otherColumns = "description, location" otherValues = "<%=otherValuesVector%>" /> Oracle Simplifies CodeImage Insert using Multimedia JSP Tag Library– An Example

16. Without: (in 10 point font) <FORM ACTION="PhotoAlbumInsert.jsp" METHOD="POST" ENCTYPE="MULTIPART/FORM-DATA"> Description: <INPUT TYPE="text" NAME="desc"><BR> Location: <INPUT TYPE="text" NAME="loc"><BR> Photo: <INPUT TYPE ="file" NAME="photo"><BR> <INPUT TYPE ="submit" VALUE="submit"></FORM> try { // Parse multipart/form-data formData.setServletRequest( request ); formData.parseFormData(); // Insert new row into database stmt = (OraclePreparedStatement)conn.prepareStatement( "insert into spec_photos ( description, location, photo ) " + " values ( ?, ?, ORDSYS.ORDImage.init() )" ); stmt.setString( 1, formData.getParameter( "description" ) ); stmt.setString( 2, formData.getParameter( "location" ) ); stmt.executeUpdate(); stmt.close(); // Fetch OrdImage object from database stmt = (OraclePreparedStatement)conn.prepareStatement( "select photo from spec_photos where description = ? for update" ); stmt.setString( 1, formData.getParameter( "description" ) ); rset = (OracleResultSet)stmt.executeQuery(); rset.next(); OrdImage photo = (OrdImage)rset.getCustomDatum( 1, OrdImage.getFactory()); rset.close(); stmt.close(); // Load the photo into the database and set the properties. formData.getFileParameter( "photo" ).loadImage( photo ); // Update object in database stmt = (OraclePreparedStatement)conn.prepareStatement( "update spec_photos set photo = ? where description = ?" ); stmt.setCustomDatum( 1, photo ); stmt.setString( 2, formData.getParameter( "description" ) ); stmt.execute(); stmt.close(); // Commit changes conn.commit(); } finally { // Ensure JDBC connection is released and any temp files are deleted. album.release(); formData.release(); }%>

17. Saves Money “Central Bank” in nearby west coast city: • Reduces the administrative cost and the cost of float for member banks. • Enables on-line processing and rapid resolution of 26,000 bad checks each day. • Electronic transmission of check images and management in Oracle9i Database

18. Saves Time Caixa Economica Federal -Largest Brazilian Government bank: • interMedia automatically transforms original files .tiff to .gif during the load process – buying time and saving money. • Direct access by users to statement information with in secure fashion Palazzo Braschi Museum - Rome: • Reduced the time to process images by 90% using interMedia methods to bulk load and process image content compared to using client side tools. • Processing includes format conversion, thumbnail generation, metadata extraction, index & load

19. Saves Labor New Mexico Department of Transportation: • A single DBA designed, created, deployed, and maintains a 5 TB image management system

20. Multi-Terabyte, Multimedia databases

21. Best Practices for Multi-Terabyte, Multimedia databases • Storage planning: Media data may grow at faster rates from other data and will require different management strategies. • Store media data in separate tablespaces that can be tuned to meet these needs. • Consider the use of partitioning to spread media data over more I/O storage devices. • LOB tuning • Use a large CHUNK value (32K maximum) to specify LOB storage. This increases I/O efficiency. Only time you would not want to specify max value is when most media is smaller than 32K.

22. Best Practices for Multi-Terabyte, Multimedia databases • Loading media: • Initialize interMedia objects with empty LOB locators (Use the init() constructors). This allocates the required space in row on the database block when the row is inserted. • Consider disabling LOB LOGGING for media segments. This saves the cost of writing the media to the Redo log as well as to the tablespace. • If LOGGING is enabled, increase the LOB_BUFFER parameter to allocate more space for the extra media data that is logged. Consider using larger size redo log files to decrease the number of log switches. • Parallelize your loading so that media is written to multiple I/O storage devices. Avoid bottlenecking your load on a single storage device.

23. Best Practices for Multi-Terabyte, Multimedia databases • Retrieving media: • Use the CACHE option on LOBs if the same LOB data is to be accessed repeatedly. • Increase the DB_CACHE_SIZE parameter to account for increased cache requirements of media data. • Processing image data: • Many image processing operations will fully decompress a compressed image in order to perform the operation. Decompressed images can be ten times the size of a compressed image. • Increase the JAVA_POOL_SIZE parameter to allocate enough memory to process these decompressed images.

24. Multimedia DatabasesMultiterabyte Customer Examples • Financial • Caixa Economica, Brazil: 4TB bank statement image database • US ‘Central’ Bank: 1TB check image database • UBS Paine Webber: 1TB check image database • Healthcare • Michigan – Medical records repository – long term care assessment • Education • University of Oslo, Norway– 1 TB National Museum digital repository • Online Computer Library Center, Inc (OCLC) – 5TB+ Digital Library • Government • US Navy – Award winning LIFELines Portal also w/Oracle Portal • State of New Mexico D.O.T. – 5 TB image database w/Oracle Portal

25. Summary • Oracle treats multimedia like any other data • Users Save Money, Labor, and Time • But enough talk – lets see a real application deployed by the State of New Mexico . . .

26. Jeremy FormanComputer System Analyst New Mexico Department of Transportation

27. Some Useful New Oracle 10g Features • Support for Partitioning of tables with object columns in tablespaces with Automatic Space Management • Data Pump Import and Export • 4 GB RAM Tuning (4GT) on Windows (beta not available for 64 bit)

28. 4GT On Windows -- Performance • Allow Memory-intensive applications running on Oracle10gEnterprise Edition to access up to 3 GB of memory • 50 percent more memory is available for database use, increasing SGA sizes or connection counts

29. The Road Features Inventory • Multiterabyte database (4TB) • Approximately 5,000,000 images • 1,000,000 Assets • Web based Application

30. D E M O N S T R A T I O N The Road Features Inventory

31. Oracle interMedia • Media and application metadata management services • Storage and retrieval services • Support for popular formats • Access through traditional and Web interfaces and a search capability using associated relational data or using specialized indexing

32. Oracle interMedia supports multimedia storage, retrieval, and management of: • Binary large objects (BLOBs) stored locally in Oracle10g and containing audio, image, or video data • File-based large objects, or BFILEs, stored locally, containing audio, image, or video data, or other heterogeneous media data • URLs containing audio, image, or video data or other heterogeneous media data, stored on any HTTP server such as Oracle Internet Application Server • Streaming audio or video data stored on specialized media

33. interMedia Object Types • ORDAudio • ORDDoc • ORDImage • ORDVideo • ORDImageSignature

34. ORDImage Attributes • source: the source of the stored image data. • height: the height of the image in pixels. • width: the width of the image in pixels. • contentLength: the size of the on-disk image file in bytes. • fileFormat: the file type or format in which the image data is stored (TIFF, JIFF, and so forth.). • contentFormat: the type of image (monochrome and so forth). • compressionFormat: the compression algorithm used on the image data. • mimeType: the MIME type information.

35. ORDImage Methods • Init() • ProcessCopy() • Set/GetUpdateTime() • Set/GetMimeType() • GetCompressionFormat() • ReadFromSource() • WriteToSource()

36. interMedia Image Loading • PL/SQL • SQLLDR • Java

37. SQLLDR Example LOAD DATA INFILE * INTO TABLE SOUNDS APPEND FIELDS TERMINATED BY ','              (Item_ID  integer external,               sound    column  object                  (                                           source  column object                          (                              localdata_fname  FILLER CHAR(128),                              localdata LOBFILE (sound.source.localdata_fname),                          )                   )                            ) BEGINDATA 55,the_grid.au, 33,engine.wav, 44,spacemusic.au

38. INSERT INTO stockphotos VALUES ( 1, 'John Doe', 'red plaid',ORDSYS.ORDImage.init(), ORDSYS.ORDImageSignature.init()); Inserting an Image

39. interMedia: Loading Images SELECT RIMAGES_VIRTDRIVE_SEQ.NEXTVAL INTO v_NextSeqVal FROM DUAL; INSERT INTO RFI_VIRTUAL_DRIVE_IMAGES(ID,RROUTES_ID,ROUTE_PREFIX,ROUTE_ID, DIRECTION,SMPOINT,FILENAME,IMAGE,THUMBNAIL) VALUES (v_NextSeqVal, p_rroutes_id, p_RoutePrefix, v_RouteID, v_Direction, v_Smpoint, p_ImageName, ORDSYS.ORDImage.init(), ORDSYS.ORDImage.init()), ORDSYS.ORDImageSignature.init()); -- Select the newly inserted row for update SELECT IMAGE INTO v_Image FROM RFI_VIRTUAL_DRIVE_IMAGES WHERE ID = v_NextSeqVal FOR UPDATE; -- This procedure imports the image file from the RFI_IMAGES directory on a the local file system -- (srcType=FILE) and automatically sets the properties. v_Image.setSource('file','FINAL_JPEGS', p_ImageDir||'\'||p_ImageName); v_Image.import(ctx); UPDATE RFI_VIRTUAL_DRIVE_IMAGES SET IMAGE = v_Image WHERE ID = v_NextSeqVal; --Call the Copy to Thumbnail Procedure Rfi_Load_Images_Final.Copy_To_Thumbnail(v_NextSeqVal);

40. interMedia: Copying an Image BEGIN SELECT IMAGE INTO v_Image_1 FROM RFI_VIRTUAL_DRIVE_IMAGES WHERE ID = p_ImageID; SELECT THUMBNAIL INTO v_Image_2 FROM RFI_VIRTUAL_DRIVE_IMAGES WHERE ID = p_ImageID FOR UPDATE; -- Convert the image to a TIFF thumbnail image and store the -- result in Image_2 v_Image_1.processcopy('maxScale=250,250', v_Image_2); -- Continue processing UPDATE RFI_VIRTUAL_DRIVE_IMAGES SET THUMBNAIL = v_Image_2 WHERE ID = p_ImageID; COMMIT;

41. Dynamically Changing Image Format -- Some image formats are supported by interMedia but may not be able -- to be displayed in-line by a browser. The BMP format is one example. -- Convert the image to a GIF or JPEG based on number of colors in the -- image. IF new_image.contentFormat IS NOT NULL AND ( new_image.mimeType = 'image/bmp' OR new_image.mimeType = 'image/x-bmp' ) THEN BEGIN new_image.process( 'fileFormat=' || get_preferred_format( new_image.contentFormat ) ); EXCEPTION WHEN OTHERS THEN NULL; END; END IF;

42. FUNCTION get_preferred_format( format IN VARCHAR2 ) RETURN VARCHAR2 IS num_digits INTEGER; ch CHAR(1); BEGIN -- Image content format strings have the following format: <#bits><format> MONOCHROME -- Figure out the number of digits that represent the number of colors. num_digits := 0; LOOP ch := SUBSTR( format, num_digits + 1, 1 ); IF ch >= '0' AND ch <= '9‘ THEN num_digits := num_digits + 1; ELSE EXIT; END IF; END LOOP; Dynamically Changing Image Format

43. Dynamically Changing Image Format -- Images with more than 8 bits of color can be converted to the JPEG-- format without significant discernible loss of quality.IF num_digits > 0 THEN IF TO_NUMBER( SUBSTR( format, 1, num_digits ) ) > 8 THEN RETURN 'JFIF'; END IF; END IF;-- Images with 8 bits of color or less are best converted to the GIFformat to retain the quality. RETURN 'GIFF';END get_preferred_format;

44. Content Based Retrieval with ORDImage The primary benefit of using content-based retrieval is reduced time and effort required to obtain image-based information A content-based retrieval system processes the information contained in image data and creates an abstraction of its content in terms of visual attributes

45. Our Multiterabyte Environment

46. The Server • Windows 2000 Advanced Server • Compaq Proliant w/ 8GB RAM • 4 - 700Mhz Processors • ¾ TB Local Storage • 4 TB IBM Shark Storage • 24 different physical drives • 200GB per drive

47. The Database • 120 Tablespaces • Average Datafile: 16GB • Materialized Views • Partitioning for most tables, materialized views, and Indexes

48. Partitioning and a Multiterabyte Database Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

49. Partitioning • List Partitions • Range Partitions • Hash Partitions • Composite Range-Hash Partitioning • Composite Range-List Partitioning • Sub Partitions

50. List Partitioning Example CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2(20), quarterly_sales number(10, 2), state varchar2(2)) PARTITION BY LIST(state) (PARTITIONq1_northwestVALUES('OR', 'WA'), PARTITIONq1_southwestVALUES('AZ', 'UT', 'NM'),PARTITIONq1_northeastVALUES('NY', 'VM', 'NJ'), PARTITIONq1_southeastVALUES('FL', 'GA'), PARTITIONq1_northcentralVALUES('SD', 'WI'), PARTITIONq1_southcentralVALUES('OK', 'TX'));