Jerry Held

1
(No Transcript)
2
Multimedia Databases, Multi-Terabyte Performance
Oracle10g interMedia
Session ID 40121

• Jim SteinerSenior Director
• Server Technologies Oracle Corporation

Jeremy FormanComputer System Analyst New Mexico
Department of Transportation
3
Agenda

• Oracles Multimedia Capabilities
• Oracle10g New Features
• How Oracle Compares
• How users benefit
• Multi-Terabyte, Multimedia databases
• Jeremy Forman -- New Mexico DOT

4
Objective

• Extend Oracles 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
Oracle10g New 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 Oracle10g Multimedia 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
Standards Support

• Oracle10g supports the first edition of the
  ISO/IEC 13249-52001 SQL/MM Part 5 Still Image
  Standard.
• The standard defines object relational types for
  images and image characteristics. Each object
  type includes attributes, methods, and associated
  SQL functions and procedures.

10
Java Advanced Imaging

• Support for JAI 1.1.1_01, the newest version of
  the SUN Open Standard for Image Processing
• Additional image processing operators
• Arbitrary Image Rotate
• Flip Mirror
• Page extract from a multi page TIFF file
• Contrast Enhancement
• Quantize algorithm
• Gamma Correction

11
Microsoft ASF Windows Media Server

• Advanced System Format has become a popular
  streaming media format on the web
• Oracle10g Database can parse ASF file format
  metadata
• Windows Media Server
• An Oracle developed plugin for the Microsoft
  Windows Media that enables it to stream ASF
  audio/video files stored in Oracle10g Database
• Analogous to the existing Oracle9i Database
  support for Real Networks streaming server
• Available through OTN

12
XML database integration

• XML schema definitions provided for multimedia
  metadata
• Customers can use the features of XML DB with
  multimedia objects
• XML repository
• stores multimedia as resources
• parses and indexes metadata
• creates image thumbnail
• XML access to multimedia objects query, update
  delete
• Code samples, white papers
• Download on OTN for Oracle Database 10g

13
How Oracles 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.

14
How do users benefit?
15
Oracle Performs

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

16
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

17
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

18
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

19
Oracle Simplifies CodeImage Insert using
Multimedia JSP Tag Library An Example

• With JSP Tag Library (14 point font)
• ltordembedImage connCache
• lt
• java.util.Vector otherValuesVector new
  java.util.Vector() otherValuesVector.add(fd.getPa
  rameter("desc")) otherValuesVector.add(fd.getPara
  meter("loc"))
• gt
• mediaParameters "photo"
• otherColumns "description, location"
• otherValues "ltotherValuesVectorgt"
• /gt

20
Without (in 10 point font)
ltFORM ACTION"PhotoAlbumInsert.jsp"
METHOD"POST" ENCTYPE"MULTIPART/FORM-DATA"gt
Description ltINPUT TYPE"text" NAME"desc"gtltBRgt
Location ltINPUT TYPE"text" NAME"loc"gtltBRgt
Photo ltINPUT TYPE "file" NAME"photo"gtltBRgt
ltINPUT TYPE "submit" VALUE"submit"gtlt/FORMgt
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.getParamete
r( "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)stm
t.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.prepareStatem
ent( "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() gt
21
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

22
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

23
Saves Labor

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

24
Multi-Terabyte, Multimedia databases
25
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.

26
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.

27
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.

28
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

29
Multimedia Databases - Applications

• Hosted Web Applications
• Spa Microsystems, UK Hosted multi-office real
  estate application
• Asset Management
• Austrian Petroleum Company image archive for
  physical assets
• Indelible Software, US digital image repository
• Cre8tiv, UK digital image repository
• Oracle.com profile - http//www.oracle.com/custome
  rs/profiles/PROFILE8205.HTML
• Media Capital Group, Portugal media portal,
  streaming media
• MediaSet, Italy image storage and processing
• Web Publishing
• BioMed Central, UK On line research publishing.
  Members include Dana-Farber, Harvard, NIH, World
  Health Organization.
• Oracle.com profile - http//www.oracle.com/custome
  rs/profiles/PROFILE9085.HTML
• Distance Learning
• University of Michigan with streaming media

30
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 . . .

31

• Jeremy FormanComputer System Analyst
• New Mexico Department of Transportation

32
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)

33
4GT On Windows -- Performance

• Allow Memory-intensive applications running on
  Oracle10g Enterprise Edition to access up to 3 GB
  of memory
• 50 percent more memory is available for database
  use, increasing SGA sizes or connection counts

34
The Road Features Inventory

• Multiterabyte database (4TB)
• Approximately 5,000,000 images
• 1,000,000 Assets
• Web based Application

35
D E M O N S T R A T I O N
The Road Features Inventory
36
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

37
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

38
interMedia Object Types

• ORDAudio
• ORDDoc
• ORDImage
• ORDVideo
• ORDImageSignature

39
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.

40
ORDImage Methods

• Init()
• ProcessCopy()
• Set/GetUpdateTime()
• Set/GetMimeType()
• GetCompressionFormat()
• ReadFromSource()
• WriteToSource()

41
interMedia Image Loading

• PL/SQL
• SQLLDR
• Java

42
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

43
INSERT INTO stockphotos VALUES ( 1, 'John Doe',
'red plaid', ORDSYS.ORDImage.init(),
ORDSYS.ORDImageSignature.init())
Inserting an Image
44
interMedia Loading Images
SELECT RIMAGES_VIRTDRIVE_SEQ.NEXTVAL INTO
v_NextSeqVal FROM DUAL INSERT INTO
RFI_VIRTUAL_DRIVE_IMAGES(ID,RROUTES_ID,ROUTE_PREFI
X,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
-- (srcTypeFILE) and automatically sets the
properties. v_Image.setSource('file','FINAL_JPE
GS', p_ImageDir'\'p_ImageName)
v_Image.import(ctx) UPDATE RFI_VIRTUAL_DRIVE_I
MAGES SET IMAGE v_Image WHERE ID
v_NextSeqVal --Call the Copy to Thumbnail
Procedure Rfi_Load_Images_Final.Copy_To_Thumbn
ail(v_NextSeqVal)
45
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('maxScale250,250
', v_Image_2) -- Continue processing
UPDATE RFI_VIRTUAL_DRIVE_IMAGES SET THUMBNAIL
v_Image_2 WHERE ID p_ImageID COMMIT
46
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

47
Dynamically Changing Image Format

• 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 ltbitsgtltformatgt 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 gt '0' AND ch lt '9 THEN
• num_digits num_digits 1
• ELSE
• EXIT
• END IF
• END LOOP

48
-- Images with more than 8 bits of color can be
converted to the JPEG-- format without
significant discernible loss of quality.
IF num_digits gt 0 THEN IF TO_NUMBER(
SUBSTR( format, 1, num_digits ) ) gt 8
THEN RETURN 'JFIF' END IF
END IF -- Images with 8 bits of color or
less are best converted to the GIF format to
retain the quality. RETURN 'GIFF'END
get_preferred_format
Dynamically Changing Image Format
49
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

50
Our Multiterabyte Environment
51
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

52
The Database

• 120 Tablespaces
• Average Datafile 16GB
• Materialized Views
• Partitioning for most tables, materialized views,
  and Indexes

53
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.

54
Partitioning

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

55
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)
• (PARTITION q1_northwest VALUES ('OR', 'WA'),
• PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
  PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),
  
• PARTITION q1_southeast VALUES ('FL', 'GA'),
• PARTITION q1_northcentral VALUES ('SD', 'WI'),
• PARTITION q1_southcentral VALUES ('OK', 'TX'))

56
Querying Directly from a Partition

• SELECT DEPTNO, DNAME
• FROM Q1_SALES_BY_REGION PARTITION(Q1_SOUTHWEST)
• WHERE STATE NM

57
CREATE TABLESPACE ltinterMedia Tablespacegt
DATAFILE ltdatafile_namegtBLOCKSIZE 16384EXTENT
MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT
MANUALOracle 10g supports partitioning objects
in Tablespaces with Automatic Segment Space
Management
Partitioning and interMedia
58
Partitioning an interMedia Table

• CREATE TABLE RFI_VIRTUAL_DRIVE_IMAGES (
• ID NUMBER (9) NOT NULL,
• RROUTES_ID NUMBER (5),
• FILENAME VARCHAR2 (100) NOT NULL,
• SMPOINT NUMBER (11,3) NOT NULL,
• DIRECTION NUMBER (1),
• IMAGE ORDIMAGE,
• THUMBNAIL ORDIMAGE,
• SIGNATURE ORDIMAGE,
• ROUTE_PREFIX VARCHAR2 (2),
• ROUTE_ID NUMBER (6),
• CREATEDBY VARCHAR2 (30),
• CREATEDDATE DATE,
• MODBY VARCHAR2 (30),
• MODDATE DATE)

59
Partitioning an interMedia Table

• PARTITION BY RANGE(ID)
• SUBPARTITION BY HASH(ROUTE_ID)
• SUBPARTITIONS 12
• (PARTITION IMAGES_PART1 VALUES LESS THAN(500000)
  TABLESPACE RFI_PART1_DIST1_TBS
• LOB (IMAGE.SOURCE.LOCALDATA) STORE AS(TABLESPACE
  RFI_IMAGES_PART1_TBS NOCACHE CHUNK 16346),
• LOB (THUMBNAIL.SOURCE.LOCALDATA) STORE
  AS(TABLESPACE RFI_THUMBNAILS_PART1_TBS NOCACHE
  CHUNK 8132)
• LOB (SIGNATURE.SOURCE.LOCALDATA) STORE AS
• (TABLESPACE RFI_SIGNATURES_PART1_TBS NOCACHE
  CHUNK 8132)

60
Partitioning an interMedia Table

• PARTITION IMAGES_PART2 VALUES LESS THAN
  (MAXVALUE) TABLESPACE RFI_PART2_DIST2_TBS
• LOB (IMAGE.SOURCE.LOCALDATA) STORE AS(TABLESPACE
  RFI_IMAGES_PART2_TBS NOCACHE CHUNK 16346)
• LOB (THUMBNAIL.SOURCE.LOCALDATA) STORE
  AS(TABLESPACE RFI_THUMBNAILS_PART2_TBS NOCACHE
  CHUNK 8132)
• LOB (THUMBNAIL.SOURCE.LOCALDATA) STORE
  AS(TABLESPACE RFI_THUMBNAILS_PART2_TBS NOCACHE
  CHUNK 8132)

61
interMedia Code Wizard

• Oracle8i download the Code Wizard for the PL/SQL
  Gateway
• http//otn.oracle.com/software/products/intermedia
  /content.html
• Oracle9i ltoracle_homegt/ord/http/demo/plsgwycw
• Oracle10g ltoracle_homegt/ord/http/demo/plsgwycw
• Execute ordplsui.sql as ORDSYS
• Create Code Wizard administration DAD
• Grant Access to other DAD

62
Next Steps.

• Recommended sessions
• BioMed Central
• JDeveloper Sessions
• Recommended demos and/or hands-on labs
• JDeveloper Campground
• Server Technologies Campground interMedia Demo
• Portal Demo
• See Your Business in Our Software
• Visit the DEMOgrounds for a customized
  architectural review, see a customized demo with
  Solutions Factory, or receive a personalized
  proposal. Visit the DEMOgrounds for more
  information.
• Relevant web sites to visit for more information
• http//otn.oracle.com/products/intermedia/
• interMedia Users Guide
• interMedia Reference

63
A