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
“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”.
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?
- Test case 1: DBMS_SPACE.ASA_RECOMMENDATIONS Function
- Test case 2: DBMS_SPACE.CREATE_INDEX_COST PROCEDURE
- Test case 3: DBMS_SPACE.CREATE_TABLE_COST Procedures
- Test Case 4: DBMS_SPACE.FREE_BLOCKS Procedure
- Test Case 5: DBMS_SPACE.ISDATAFILEDROPPABLE_NAME Procedure
- Test Case 6: DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS Function
- Test Case 7: DBMS_SPACE.OBJECT_SPACE_USAGE Procedures
- Test Case 8: DBMS_SPACE.SPACE_USAGE Procedures
- Test Case 9: DBMS_SPACE.UNUSED_SPACE Procedure
- Test Case 10: DBMS_SPACE.OBJECT_GROWTH_TREND Function – This one uses the AWRF, so this usage should have been registered in the DBA_FEATURE_OPTION_STATISTICS – or that’s what I expected. But no information?
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:
One function in particularly – the OBJECT_GROWTH_TREND, caught my attention:
“…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:
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:
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
Now the check. Has my database recorded this?
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
Now the check. Has my database recorded this?
SQL> @/home/oracle/scripts/option_usage
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:
Now the check. Has my database recorded this?
SQL> @/home/oracle/scripts/option_usage
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
Now the check. Has my database recorded this?
SQL> @/home/oracle/scripts/option_usage
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
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
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.
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
EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);
Now the check. Has my database recorded this?
SQL> @/home/oracle/scripts/option_usage
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
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
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
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!
Thanks Tom, great that you enjoyed it! The very best regards to you too!