dbms_space – Standard Edition proof?

dbms_space -Can we use this package to the fullest in Standard Edition?

Updated: 27.5.2015 Confirmation received from an Oracle License Expert….scroll down to

the red part ,There is the confirmation.

27th May 2015/Ann

The Original post 12th April 2015 starts here

When I was googling for information about this DBMS_SPACE package, I found that some people say “No you need the diagnostic & tuning pack”, and other people say “go ahead and use it”.

So this requires a test case.

This situation of finding out “can we use something or not” is for us Oracle Standard Edition DBA’s basic “daily“- routines (at least for a customer oriented DBA).

Oracle has started to help out more and more, when comparing to old documentation vs. new documentation, but there are still stuff out there that need to be better documented, or even better; the software should tell us if something is not ok to use.

I feel that this DBMS_SPACE package is one of those things that need better documentation.

 Please NOTE:

Oracle licensing is a complicated business. The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third-party company, who are professionals on this business.

SUMMARY of Test Case on the DBMS_SPACE package

“Technical blog post sometimes become to lengthy with all the tech stuff“, so for you who are not keen on knowing the details, here is a brief summary of the test cases performed on DBMS_SPACE and the challenges I think it causes us Standard Edition DBA’s.

Some test results I have received are of a surprise to me. And I would love to hear from you gurus, is my test method to simple and need corrections, or why is the outcome as it is?

<27th May 2015 – Confirmation from an Oracle License Expert START

According to a letter from an Oracle expert on License  that I received, the DBMS_SPACE package is Standard Edition proof: 
“the DBMS_SPACE can be used in Standard Edition, since it is making use of DBA_HIST_SEG_STAT and DBA_HIST_SEG_STAT_OBJ views, and doesn’t require the Diagnostic Pack”
Let’s take a look at the Enterprise Manager License document:

“All data dictionary views beginning with the prefix DBA_HIST_* are part of this pack, along with their underlying tables.
The only exception is the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.”

“There are neither any explicit information found about this package from Oracle License Document nor the Enterprise Manager License document, saying that this DBMS_SPACE package is part of any management pack”.

As always when you get questions about Oracle License, follow the note above, and verify with an Oracle License Expert. Thanks Oracle License Expert for your assistant!
Note to myself: On the internet you can find an article on how to Measuring Database Space Growth if you would like another method on how to accomplish the growth measurement, instead of using this package.

27th May 2015 -Confirmation from an Oracle License Expert  END>

….and back to the post written on the 12th Apr 2015 …

From the Oracle Document on Diagnostic Pack API’s, I cannot find any information about DBMS_SPACE (if it’s ok to use in Standard Edition or not)

This post by Tim Hall on DBMS_SPACE (old yes, but still valid?) Crunchy “To Be Or Not To Be?”; Question still remains.

Test environment & methods information:

  • fresh newly installed 12.1.0.1 Standard Edition with one PDB
    • control_management_pack_access     string NONE
  • Oracle Linux 6.6
  • VirtualBox
  • These tests are done connected to the CDB. I will later write about the results from the PDB.
  • TC methods:
    • 1) Read Oracle License document
    • 2) Read PL/SQL reference document
    • 3) run the DBMS_SPACE.[function][procedure]
    • 4) run DBMS_FEATURE_USAGE_INTERNAL (update usages)
    • 5) check DBA_FEATURES_OPTIONS_STATISTICS
    • 6) Reporting the test results

Since Oracle provide with two scripts for checking the usage of a feature/option (see below), I just used them (see below for the Doc Id).

According to the documentation, Oracle automatically update the statistics every 7th day, and according to Dan Morgan (see below), this can be overrides by running the package DBMS_FEATURE_USAGE_INTERNAL, so of cause I used that instead of waiting 7 days after every test case :). The Description for this package: “Collects usage statistics for all features”.

RESULTS: My Oracle Standard Edition did not register any use of these (question: test methods to simple? ), and I would have expected that at least one function should have been registered.

Or is it just so that this package can be used to the fullest in a Standard Edition Database? What do YOU think?

As I already pointed out. I think there are something fishy in here, and I will continue to investigate to why my Oracle Standard Edition didn’t record the use of at least one function, that use the AWRF.

We already know the facts – we cannot buy the diagnostic & tuning pack for our Standard Edition database environment, so when the documentation states that something is using the AWRF, at least I would have expected my standard edition database to register the function that I ran.

If the reason is that my test case methods is to simple – than this is just awesome, since I just redefine my test methods.

BUT if the reason is something within Oracle, that for example the registration of using something is more complicated, than running this DBMS_FEATURE_USAGE_INTERNAL package as Dan Morgan told us to, than this is not good – how on earth can a Standard Edition DBA be sure of not violating the license agreement, if neither the software nor the documentation is guiding us?

I hope you enjoyed this post, and if you would like to know more details about the tests, just keep on reading.

And to you who are not so much into details I wish you a nice Sunday!

Take care, stay well, and see you soon again.

-Ann

Turku 12th April, 2015

DETAILS onTest Case on the DBMS_SPACE package

First I check the Oracle 12 License document to see what information can be found about the DBMS_SPACE package.

  • There are no specific information about DBMS_SPACE in the license document.

(Note: With Oracle, the right to use or not to use something depends on the version of the database, so remember to verify against the right database versions license document).

Next step is to check the DBMS_SPACE PL/SQL reference document to see if there might be any license related information in there:

dbms_space_summary

One function in particularly – the OBJECT_GROWTH_TREND, caught my attention:

dbms_space_object_growth

“…will be retrieved from Automatic Workload Repository Facilities (AWRF), or the current space usage will be computed and combined with space usage deltas retrieved from AWRF”

This is to my understanding pretty straight forward – This function uses AWRF, and that means the Diagnostic Pack, and that we CANNOT use in an Oracle Standard Edition.

How about the rest of the procedures/functions?

My third step; I googled a little bit more on the subject and from  this great blog (the example SQL was used in this tests) by Dan Morgan I saw this dependencies list:

dbms_space_dependencies

The Dependencies part of this post, mentiones tables starting with WRI$, WRH$, X$,  and at least the two first prefixed tables are AWR related, so for me this is clearly a signal: more investigations are required.

Fourth step: As many of you already know, Oracle Support provide two scripts on “Database Options/Management Packs usage” (Doc ID 1317265.1), to help us find out what is actually happening in our database.

My test is very simple. After running the specific function/procedure, I ran these scripts, to see what my Oracle Standard Edition database thinks about life.

Note: from DOC:
“Note that the feature usage data in the view is updated once a week, so it may take up to 7 days for the report to show recent usage of options and/or packs.”

This can be fixed according to Morgan’s Library that has some notes about how to force an update by using

EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Baseline before tests: 

banner

options

So after a clean installation (click-click method) there are 17 options in use by default out of 82 options (ver 12.1.0.1).

Test Case 1: DBMS_SPACE.ASA_RECOMMENDATIONS Function

Returns recommendations/findings of segment advisor run automatically by the system or manually invoked by the user

t1

Now the check. Has my database recorded this?

t1_r

Conclusion on  DBMS_SPACE.ASA_RECOMMENDATIONS Function : Nothing was recorded by Oracle Standard Edition.

Test Case 2: DBMS_SPACE.CREATE_INDEX_COST PROCEDURE 

Determines the cost of creating an index on an existing table

t2

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

tc2

Conclusion on  DBMS_SPACE.CREATE_INDEX_COST PROCEDURE : As in TC1, nothing was recorded by Oracle Standard Edition.

Test Case 3: DBMS_SPACE.CREATE_TABLE_COST Procedures

Determines the size of the table given various attributes

Overload 1:

t3

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t3

Conclusion on  DBMS_SPACE. CREATE_TABLE_COST Procedures: As in TC1, and TC2. Nothing was recorded by Oracle Standard Edition.

Test Case 4: DBMS_SPACE.FREE_BLOCKS Procedure

Information about free blocks in a table, index, or cluster. Only work on tablespaces w/o ASSM. With ASSM use dbms_space.space_usage

t4 (1)

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t4

Conclusion on  DBMS_SPACE. FREE_BLOCKS Procedure : As in the previous test cases, nothing was recorded by Oracle Standard Edition.

Test Case 5: DBMS_SPACE.ISDATAFILEDROPPABLE_NAME Procedure

Checks whether datafile is droppable

t5t

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

Conclusion on  DBMS_SPACE. ISDATAFILEDROPPABLE_NAME Procedure  : As in the others test cases, nothing was recorded by Oracle Standard Edition.

Test Case 6: DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS Function

Returns the list of segments that are associated with an object

t6t

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE. OBJECT_DEPENDENT_SEGMENTS Function  : Same result as in the previous TC’s. Nothing was recorded by Oracle Standard Edition.

Test Case 7: DBMS_SPACE.OBJECT_SPACE_USAGE Procedures

Returns the space used, space allocated, and percentage of chained rows in a table. 

t7t

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE.OBJECT_SPACE_USAGE Function  : Same result as in the previous TC’s. Nothing was recorded by Oracle Standard Edition.

Test Case 8:  DBMS_SPACE.SPACE_USAGE Procedures

Shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management.

Overload 1

t8t

EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t8

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE.SPACE_USAGE Procedures : As in previous tests. Nothing was recorded by Oracle Standard Edition.

Test Case 9: DBMS_SPACE.UNUSED_SPACE Procedure

Returns information about unused space in a table, index, or cluster 

DECLARE
   segown varchar2(30):='SYS';
   segname varchar2(30):='USERS_T';
   segtype varchar2(30):='TABLE';
   partname varchar2(30);
   totblock number;
   totbytes number;
   unusedbl number;
   unusedby number;
   lu_ef_id number;
   lu_eb_id number;
   lu_block number;
BEGIN
DBMS_SPACE.unused_space(segown, segname, segtype, totblock,
totbytes, unusedbl, unusedby, lu_ef_id, lu_eb_id,
lu_block, partname);
dbms_output.put_line('Total Blocks: ' || TO_CHAR(totblock));
dbms_output.put_line('Total Bytes: ' || TO_CHAR(totbytes));
dbms_output.put_line('Unused Blocks: ' || TO_CHAR(unusedbl));
dbms_output.put_line('Unused Bytess: ' || TO_CHAR(unusedby));
dbms_output.put_line('Last Used Extent File ID: ' ||    TO_CHAR(lu_ef_id));
dbms_output.put_line('Last Used Extent Block ID: ' || TO_CHAR(lu_eb_id));
dbms_output.put_line('Last Used Block: ' || TO_CHAR(lu_block));
END;
Total Blocks: 8
Total Bytes: 65536
Unused Blocks: 3
Unused Bytess: 24576
Last Used Extent File ID: 6
Last Used Extent Block ID: 176
Last Used Block: 5

EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t9

SQL> @/home/oracle/scripts/used_options_details

<No Rows Returned>

Conclusion on  DBMS_SPACE.UNUSED_SPACE Procedure  : As in all other test cases. Nothing was recorded by Oracle Standard Edition. What would you say? Is it fare to assume it is ok to use in a standard edition database?

Test Case 10: DBMS_SPACE.OBJECT_GROWTH_TREND Function

Pipelined table function where each row describes the space usage of the object at a specific point in time

This function on the other hand, should update the use of something that is not ok in our Standard Edition database, since it according to the documentation make use of the AWRF.

Since it’s all about history, let’s insert 150 rows to the USER_T

SQL> exec gen_data('USERS_T',150);
SQL> commit;

And now we run the dbms_space.object_growth_trend function

set linesize 121
col timepoint format a40

SELECT *
FROM TABLE(dbms_space.object_growth_trend('SYS', 'TAB$', 'TABLE'));
11-MAR-15 08.04.34.006835 PM 8996062 12582912 INTERPOLATED 
12-MAR-15 08.04.34.006835 PM 8996062 12582912 INTERPOLATED 
......(shortened the list)......
 09-APR-15 08.04.34.006835 PM 8996062 12582912 INTERPOLATED 
10-APR-15 08.04.34.006835 PM 8996062 12582912 GOOD 
11-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
12-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
13-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
14-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
15-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
36 rows selected.

EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t10

What! My Oracle Standard Edition database didn’t record this?

@/home/oracle/scripts/used_options_details

<no rows returned>

More investigations

 select NAME,
 detected_usages USED,
 to_char(first_usage_date,'dd.mm.yyyy hh24:mi:ss') FirstUsage,
 to_char(last_usage_date,'dd.mm.yyyy hh24:mi:ss') LastUsage,
 aux_count AuxCount
 from dba_feature_usage_statistics
 where detected_usages >0;

NAME                 USED FirstUsage
LastUsage           AuxCount
------------------------------------------
Automatic SGA Tuning 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Automatic Undo Management 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1 
Deferred Segment Creation 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1689 
Automatic Maintenance - Optimizer Statistics Gathering 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 
Automatic Maintenance - Space Advisor 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic Maintenance - SQL Tuning Advisor 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic Segment Space Management (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic SQL Execution Memory 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Character Set 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Locally Managed Tablespaces (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4 
Locally Managed Tablespaces (user) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Oracle Managed Files 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Oracle Multitenant 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1 
Partitioning (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4.2 
Recovery Area 5 10.04.2015 17:24:26 10.04.2015 20:07:49 5033164800 
Logfile Multiplexing 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Server Parameter File 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Virtual Private Database (VPD) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
LOB 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
SQL Plan Directive 5 10.04.2015 17:24:26 10.04.2015 20:07:49 135 
SecureFiles (user) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 325 
SecureFiles (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 374 
Heat Map 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Job Scheduler 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4 
Oracle Java Virtual Machine (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 32082 
Unified Audit 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
26 rows selected.

Conclusion on  DBMS_SPACE. OBJECT_GROWTH_TREND Function  :  Nothing was recorded by Oracle Standard Edition, which to my understanding is strange, since the PL/SQL reference guide  said:

“…will be retrieved from Automatic Workload Repository Facilities (AWRF), or the current space usage will be computed and combined with space usage deltas retrieved from AWRF”

What do YOU think about theses results?

27th May 2015: A confirmation received from an Oracle License Expert was added to this post. Scroll up to the red text, if interested.

Have a nice Sunday! Take care, stay well, and see you soon again!

-Ann

Turku 12th April, 2015

Published by

Ann Sjökvist

Standard Edition has the same core tech as Enterprise Ed. It just haven't all nice gadgets available so different solutions is required. Easy to violate license agreements, so be careful out there :)

2 thoughts on “dbms_space – Standard Edition proof?”

  1. Thank you very much Ann for the detailed test case on DBMS_SPACE and for sharing the response from Oracle on your licensing question. I have often wondered this myself. Best regards!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *