Forum Stats

  • 3,853,068 Users
  • 2,264,172 Discussions


LOB monitoring

Marcelle Member Posts: 18 Bronze Badge
edited Jan 11, 2016 5:59PM in Database Ideas - Ideas

The ability to better track, control and manage any lob in the database.

In particular:
- track a lob being moved (loaded, inserted) and get a status as to its insertion into the database. Similar to v$session_longops.

So if I am inserting a 50Gb video blob I can track how far it has been inserted (or copied)

- ability to move a lob between tablespaces without it incurring redo. Again, move a 50Gb lob between tablespaces (from a hard drive to SSD - hierarchical storage) and back again will incur a lot of redo (and backup) when in fact nothing has changed. This involves changing the mentality of LOBs being based on Oracle blocks to the blob itself being the unit.

- ability to extract a lob from an rman backup (for long term archival)

- ability to backup a lob only once. Mark the lob as read only and never back it up again. Think of loading in 100 x 10Gb videos. Backup once. The idea of incremental forever backup is not a solution. Which leads to:

- ability to use a hadoop like tablespace structure but just for lobs. As in you specify three servers for lob location and the database distributes (sharding) the blob between, across them (setting dependent). This is a specialised tablespace structure for lobs only as it really only makes sense for lobs. This is not the same as ASM which has a relational focus. The idea here is to get away from doing backups of the lobs. Never backup. Which makes sense when you start to get to tens of terabytes of blob data. This is not the same as using partitions on tablespaces which is a different solution (valid, but different).

- ability to tune (via the optimizer) and load balance the performance distribution of lobs as they are moved in and out of the database. So if 500 hundred 50Mb images of different sizes are being accessed by users, being able to throttle and limit the speed based on resource manager concepts or something similar would be useful. Or even pause delivery, change the throttle speed. ftp servers allow this capability and its very useful.

- ability to allocate an area of SGA for blob caching and then being able to identify and cache an individual blob into it. You might have a million images (each of 100Mb) in your database and have a show on, and you know which images are going to be the most popular, so you can identify them and cache them. Similar concept exist, but not give you ability to specify an individual blob.

- low level amazon (or equivalent) integration, not mid tier integration. Ability to specify that a lob is to be stored on S3 and have the database auto replicate or move it there (as well as to multiple sites around the world). So when you retrieve it, your current location is taken into account and the closest location retrieves it. It makes more sense to have it at the database lowest layer than at a middle tier as this is really the job of the database.

- ability to use an MPEG style algorithm for storing multiple versions of a lob. A JPEG doesn't compress any more so database compression is not relevant. Being able to store changes to a digital image (versioning) means re-saving the whole thing which consumes a lot of storage. If you translate the concept of JPEG to video compression and use it in reverse, then the idea is a new version of a JPEG is loaded and the differences between it and the older one are only stored. Going back to an older version is a rarer occurrence so the cost in reassembling can be handled as there is savings in storage.

In summary, the Oracle database has the pieces in place for supporting properly multimedia but it still has a relational mentality and this is shown in the tuning and management of a lot of Oracle products. The ideas raised here are just some of many which are needed to enable true LOB support (and multimedia) support that will allow the database to truly scale.

Crispedregal-OracleEduardo SmaniottoGeeky NerdmanUser259623 -OraclemschmidtGugs-Oracleborneselvinaykumar2Gerald Venzl-OracleManish ChaturvediArpit Jain -OraclectriebPravin TakpireJGNbhagatsinghLothar Flatz1483608
19 votes

Active · Last Updated


  • Tmicheli-Oracle
    Tmicheli-Oracle Member Posts: 24 Red Ribbon

    We, Oracle are working on our internal process as to how to evaluate and prioritize the IDEAS submitted.  But the more votes obviously the more priority we will put on the request.  However votes/popularity alone will not determine the priority.

    As we move through the process the IDEA will change stages: (not in flow order)

    - Active

    - Already Offered

    - Archived

    - Coming Soon

    - For Future Consideration

    - in Progress

    - Partially Implemented

    - Under Review

  • Gugs-Oracle
    Gugs-Oracle Member Posts: 74

    The existing $ORACLE_HOME/rdbms/admin/spawrio.sql provides overall monitoring of the DB objects in text format.

    However, LOB specific monitoring script can be harvested from spawrio.sql to address the SIZE specific concerns  in idea above.

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    Sorry, that is just more than one idea. I agree with some and disagree with others. Would you splitt them so I could vote?