I was trying to use DBMS_COMPRESSION on an 11gR2 (11.2.0.4 on RHEL 6.3) database the other day and was helped by this article from Neil Johnson. I was having some trouble with permissions until I read that article which nicely summarises everything you need to know – thanks Neil!
One thing I did notice is that Neil stated that you can’t parallelise the calls to the advisor since it uses the same named objects each time and this would then cause conflicts (and problems). Neil illustrated the example calls that the advisor is making based on him tracing the sessions…
create table "ACME".DBMS_TABCOMP_TEMP_UNCMP tablespace "SCRATCH" nologging
as select /*+ DYNAMIC_SAMPLING(0) FULL("ACME"."ACCS") */ *
from "ACME"."ACCS" sample block( 99) mytab
create table "ACME".DBMS_TABCOMP_TEMP_CMP organization heap
tablespace "SCRATCH" compress for all operations nologging
as select /*+ DYNAMIC_SAMPLING(0) */ *
from "ACME".DBMS_TABCOMP_TEMP_UNCMP mytab
Because I kept having permissions issues I was repeatedly running the advisor and I ended up with a situation where one of the transient objects (above, or so I thought) had been left in place and when I tried the next rerun it complained that the object existed. I can’t reproduce this as I can’t remember all the steps that I took and I wasn’t recording my session at the time – it’s not really the point of this blog in any case, rather the knowledge it led to. Because the error was that the object existed, I figured I just needed to find the object and drop it and I’d be good to carry on – obviously I looked at the above code fragments and started to search for the two objects in question (DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP) but found nothing. I started looking for DBMS_TABCOMP% and again found nothing.
Somewhat confused, I then looked for the latest object created and found that the objects were actually called something completely different and of the form CMPx$yyyyyyyy. I think this must have changed since Neil wrote his article (it is from 2013 after all).
I can’t work out what “x” is – at first I thought it was the RAC instance but that was just a coincidence that I saw a 3 and I was on instance 3 of a RAC cluster. In fact on a single instance database (test below) I saw numbers higher than 1 so it’s not the RAC instance number and I can’t work out what it is. “yyyyyyyy” is definitely the OBJECT_ID of the data object, confirmed by cross referencing the data dictionary.
Given this naming standard is therefore object specific, it suggests that you could execute these advisor calls in parallel.
Just to be clear, I’m not sure what version of 11g Neil was using but I am using 11.2.0.4:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
A little test using Neil’s code (and a bit more as I don’t have acme user on my database):
First create a script called dbms_comp.sql with the following content:
set serveroutput on
set feedback on
set verify off
declare
blkcnt_cmp BINARY_integer;
blkcnt_uncmp BINARY_integer;
row_cmp BINARY_integer;
row_uncmp BINARY_integer;
cmp_ratio number;
comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('&3.')
, ownname => upper('&1.')
, tabname => upper('&2.')
, partname => null
, comptype => dbms_compression.comp_for_oltp
, blkcnt_cmp => blkcnt_cmp
, blkcnt_uncmp => blkcnt_uncmp
, row_cmp => row_cmp
, row_uncmp => row_uncmp
, cmp_ratio => cmp_ratio
, comptype_str => comptype_str
, subset_numrows => &4.
);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
--DBMS_OUTPUT.PUT_LINE('Compression type = ' ||comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||round(blkcnt_uncmp/blkcnt_cmp,1)||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression % benefit = '||round((blkcnt_uncmp-blkcnt_cmp)/blkcnt_uncmp*100,1));
--DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
end;
/
set verify on
Then create another script called setup.sql with the following content – I’m using auditing (thanks Tim!) to see the statements rather than tracing in this instance:
conn sys as sysdba
drop user acme cascade;
drop user nj cascade;
drop tablespace acme including contents and datafiles;
drop tablespace scratch including contents and datafiles;
drop role nj_dba;
create user acme identified by acme;
grant create session,create table to acme;
create tablespace acme datafile '/u01/app/oracle/oradata/db11g/acme01.dbf' size 2G;
alter user acme quota unlimited on acme;
create tablespace scratch datafile '/u01/app/oracle/oradata/db11g/scratch01.dbf' size 2G;
create role nj_dba;
create user nj identified by nj;
REM Use auditing instead of tracing to identify the statements run:
audit all by nj by access;
audit create table by nj by access;
grant create session, create any table, drop any table, select any table to nj_dba;
grant execute on sys.dbms_monitor to nj_dba;
grant nj_dba to nj;
alter user acme quota unlimited on scratch;
alter user nj quota unlimited on scratch;
grant ANALYZE ANY to NJ_DBA;
Now login to sqlplus /nolog and run setup.sql which should show this:
SQL> @setup
Enter password:
Connected.
User dropped.
User dropped.
Tablespace dropped.
Tablespace dropped.
Role dropped.
User created.
Grant succeeded.
Tablespace created.
User altered.
Tablespace created.
Role created.
While, it generic viagra is a fact that sexual contact between two married people is not only the vital part in life of individuals. For wholesale sildenafil http://appalachianmagazine.com/author/appalachianmagazine/page/60/ you must consult your doctor first. A heavy meal before consuming sildenafil drug may delay the results cheap cialis for sale of the medicine. Here are a few causes that the cheapest viagra make young men facing this hard time of the life- Smoking- It has entered as a modern habit in young males to puffing several cigarettes a day.
User created.
Audit succeeded.
Audit succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
User altered.
User altered.
Grant succeeded.
Now login to acme and create the subject table for the compression advisor:
conn acme/acme
create table test tablespace acme as select lpad(TO_CHAR(ROWNUM),2000,'x') char_col from dual connect by level < 300000;
Table created.
Now check the compression using the advisor (ignore the actual compression results as we’re not interested in those at this time):
conn nj/nj
@dbms_comp acme test scratch 200000
Block count compressed = 2048
Block count uncompressed = 2048
Row count per block compressed = 3
Row count per block uncompressed = 3
Compression ratio = 1 to 1
Compression % benefit = 0
PL/SQL procedure successfully completed.
Now check the audit trail to find the statements run:
conn sys as sysdba
column username format a8
column obj_name format a30
column timestamp format a30
column action_name format a20
set linesize 200
set pagesize 1000
SELECT USERNAME, action_name,obj_name, to_char(extended_TIMESTAMP,'DD-MON-YYYY HH24:MI:SSxFF') timestamp FROM DBA_AUDIT_TRAIL WHERE USERNAME='NJ' and action_name='CREATE TABLE';
USERNAME ACTION_NAME OBJ_NAME TIMESTAMP
-------- -------------------- ------------------------------ --------------------
NJ CREATE TABLE CMP3$87401 10-DEC-2017 18:01:15.119890
NJ CREATE TABLE CMP4$87401 10-DEC-2017 18:01:15.177518
(Abridged to remove non relevant tests)
Now check the dictionary to see the OBJECT_ID:
select object_name from dba_objects where object_id=87401;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
TEST
1 row selected.
OK, how about the parallelism? Let’s create a second table called TEST2 in ACME:
conn acme/acme
create table test2 tablespace acme as select lpad(TO_CHAR(ROWNUM),2000,'x') char_col from dual connect by level < 300000;
Table created.
Now run two parallel sessions – I did it by firing off the calls manually in separate SQL*Plus sessions rather than being clever:
In session 1:
conn nj/nj
@dbms_comp acme test scratch 200000
In session 2:
conn nj/nj
@dbms_comp acme test2 scratch 200000
First one gives:
Block count compressed = 1920
Block count uncompressed = 1920
Row count per block compressed = 3
Row count per block uncompressed = 3
Compression ratio = 1 to 1
Compression % benefit = 0
PL/SQL procedure successfully completed.
Second one gives:
Block count compressed = 2432
Block count uncompressed = 2432
Row count per block compressed = 3
Row count per block uncompressed = 3
Compression ratio = 1 to 1
Compression % benefit = 0
PL/SQL procedure successfully completed.
Both ran at the same time and didn’t fail
Now check the audit trail:
conn sys as sysdba
column username format a8
column obj_name format a30
column timestamp format a30
column action_name format a20
set linesize 200
set pagesize 1000
SELECT USERNAME, action_name,obj_name, to_char(extended_TIMESTAMP,'DD-MON-YYYY HH24:MI:SSxFF') timestamp FROM DBA_AUDIT_TRAIL WHERE USERNAME='NJ' and action_name='CREATE TABLE';
USERNAME ACTION_NAME OBJ_NAME TIMESTAMP
-------- -------------------- ------------------------------ --------------------
NJ CREATE TABLE CMP3$87401 10-DEC-2017 18:01:15.119890
NJ CREATE TABLE CMP4$87401 10-DEC-2017 18:01:15.177518
NJ CREATE TABLE CMP3$87408 10-DEC-2017 18:12:18.114321
NJ CREATE TABLE CMP3$87409 10-DEC-2017 18:12:18.114353
NJ CREATE TABLE CMP4$87408 10-DEC-2017 18:12:22.730715
NJ CREATE TABLE CMP4$87409 10-DEC-2017 18:12:22.735908
(Abridged to remove non relevant tests)
And from the dictionary:
select object_name from dba_objects where object_id IN(87408,87409);
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
TEST
TEST2
2 rows selected.
So, it appears to allow parallel running without issue.
If anyone works out what the “x” part of the object names is (3 and 4 in the above example), please shout out in the comments…
