5 Replies Latest reply: Aug 26, 2009 3:11 PM by damorgan RSS

    How compressed are my securefiles?

      I have an 11g database set up with compression on a securefiles BLOB field.

      I can tell via several means roughly how much "compressing" is getting done to the whole blob segment or whatever, but I can't actually tell how much any individual file is being compressed when it's stored in the field.

      How can I tell how much one file/record is being compressed? Is that even possible? The internet seems to be pretty quiet about this subject, or maybe I'm just not looking in the right place. Hell, even Oracle support is taking almost a week at this point to get back to me about it.
        • 1. Re: How compressed are my securefiles?
          I would be really helpful to see what you have been doing but you have not posted your DML.

          You can determine compression with DBMS_LOB.GETLENGTH.

          If you are not seeing compression then it is likely Oracle has determined that it can not get past the 20% threshhold
          under which it will not compress because it believes the overhead is greater than the benefit.
          • 2. Re: How compressed are my securefiles?
            Hi there,

            to measure how large a LOB value is when it is uncompressed just copy it to another table actually another LOB segment without LOB compression
            and check out dbms_space.space_usage procedure.
            This will tell you the thinks you are interested in.

            HTH Mathias
            • 3. Re: How compressed are my securefiles?

              I've used the getlength method but that only tells me the uncompressed size of a record. I don't really need that since I have the original, uncompressed, file to compare things with.

              The compression threshold thing is a nice thought but I know for a fact it is doing SOME compression because I can check in a huge file and the size difference of the total LOB will be only marginal.

              Thanks for the help, but I ran out of time to determine an answer on this issue.
              • 4. Re: How compressed are my securefiles?
                Not really answered, but also don't have time to spend on it anymore.
                • 5. Re: How compressed are my securefiles?
                  DBMS_LOB will show you the file size compressed. If you are not seeing it then post an example showing what you are doing.

                  I have a working demo at www.morganslibrary.org/library.html under SecureFiles.