Basic compression, advanced compression, HCC - an overview

Basic Compression:

Basic compression was released in Oracle 9iR2.It is part of the Enterprise Edition and comes at no extra licencing cost.

If you specify only the keyword COMPRESS, it is equivalent to specifying ROW STORE COMPRESS BASIC and enables basic table compression.

Basic Compression
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS COMPRESS;

The keyword COMPRESS is kept for backward compability, but the current preferred syntax is

ROW STORE COMPRESS

or

ROW STORE COMPRESS BASIC

Basic compression does not apply compression to DML operations after the initial load.
With basic compression, Oracle will attempt to compress data during direct-path INSERT operations, but not during conventional-path INSERT operations. Make sure you fullfil the requirements for direct path inserts.

Advanced Compression:

Avanced compression was released in Oracle 11gR1 in 2007, and was called "OLTP Table Compression" at the time. You need to purchace the Advanced Compression Option from Oracle to use it.

When you enable table compression by specifying ROW STORE COMPRESS ADVANCED, you enable Advanced Row Compression. Oracle Database compresses data during all DML operations on the table. This form of compression is recommended for OLTP environments.

Advanced Compression
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS ROW STORE COMPRESS ADVANCED;

In earlier releases, Advanced Row Compression was called OLTP table compression and was enabled using COMPRESS FOR OLTP. This syntax is still supported for backward compatibility.

Hybrid Columnar Compression

For certain storage systems, Oracle has introduced Hybrid Columnar Compression, or HCC.


COLUMN STORE COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH:

HCC Compression - non-dormant data
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS COLUMN STORE COMPRESS FOR QUERY HIGH | LOW;

COLUMN STORE COMPRESS FOR ARCHIVE uses higher compression ratios than COLUMN STORE COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW:

HCC Compression - dormant data
CREATE TABLE SCOTT.test ( id NUMBER, fname VARCHAR2(32), ename VARCHAR2(32), added_date DATE ) TABLESPACE USERS COLUMN STORE COMPRESS FOR ARCHIVE HIGH | LOW;

If you attempt to create a table With HCC on a non-supported Storage system, you will receive the following error Message:

ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

Sources: Create Table

Oracle Advanced Compression With Oracle 12cR2 - excellent document

How to find the number of full table scans

From the Oracle Documentation:

The view DBA_HIST_SEG_STAT view displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

 SELECT O.OBJECT_NAME,O.OWNER, SUM(S.TABLE_SCANS_TOTAL) "# of FTS",(SELECT NUM_ROWS FROM DBA_TABLES T WHERE T.TABLE_NAME = O.OBJECT_NAME) "num rows total"
 FROM DBA_HIST_SEG_STAT S JOIN DBA_OBJECTS O ON (S.OBJ# = O.DATA_OBJECT_ID)
 WHERE O.OWNER NOT IN ( 'SYS','DBSNMP','XDB')
 AND O.OBJECT_TYPE = 'TABLE'
 GROUP BY S.OBJ#,O.OBJECT_NAME,O.OWNER
 ORDER BY 4 DESC;

OBJECT_NAME	OWNER	# of FTS	num rows total
TABLE1	USER1	5702	1583895743
TABLE2	USER1	11	588921781
TABLE3	USER1	6195	241550246

As can be seen from above, the extreme number of rows in TABLE1, coupled with 5702 full table scans, should bring it to your attention as a candidate for partitioning!

How to purge objects from the recycle bin

sqlplus / as sysdba
purge dba_recyclebin; --> purges all recyclebin objects, globally
purge tablespace user_data user scott; --> purge only scott's Objects from the user_data tablespace
purge tablespace user_data; --> purge all recyclebin Objects from the user_data tablespace

conn scott/tiger
purge recyclebin; --> purges scott's recycle bin only

The next statement purges the specified object from the recycle bin. The first example refers directly to the system-generated name of the object, the second is refering to the original name of the object.

purge table "BIN$W/n28BqBgRbgU64JAQoz3A==$0";
purge table emp; 

What is the Adaptive SQL Plan Management introduced in Oracle 12c?

By "Adaptive SQL Plan Management", Oracle is referring to a new autotask that is executed every night during the maintenance window, as a part of the "sql tuning advisor".

It will automatically evolve all non-accepted plans in the SQL Plan Management Base.
If the New plans improves the performance of the Query, they will automatically be promoted to an accepted plan.

In previous versions of Oracle, plans needed to be evolved manually by the DBA by running dbms_spm.evolve_sql_plan_baselines.

Is the sql tuning task enabled?

SELECT CLIENT_NAME, STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'sql tuning advisor';

View details about a specific run:

SELECT OWNER,TASK_ID, TASK_NAME,DESCRIPTION,ADVISOR_NAME,EXECUTION_TYPE,STATUS,RECOMMENDATION_COUNT "num rec",EXECUTION_START,EXECUTION_END,LAST_EXECUTION,HOW_CREATED
FROM  DBA_ADVISOR_TASKS 
WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';

OWNER	TASK_ID	TASK_NAME	DESCRIPTION	ADVISOR_NAME	EXECUTION_TYPE	STATUS	num rec	EXECUTION_START	EXECUTION_END	LAST_EXECUTION	HOW_CREATED
SYS	2	SYS_AUTO_SPM_EVOLVE_TASK	Automatic SPM Evolve Task	SPM Evolve Advisor	SPM EVOLVE	COMPLETED	0	13.11.2017 23:00:06	13.11.2017 23:00:07	EXEC_20606	AUTO

If the parameter accepted_plans is set to TRUE, it means plans will be automatically evolved:

SELECT PARAMETER_NAME,PARAMETER_VALUE
FROM DBA_ADVISOR_PARAMETERS 
WHERE TASK_NAME LIKE 'SYS_AUTO_SPM_EVOLVE_TASK'
AND PARAMETER_NAME='ACCEPT_PLANS';

PARAMETER_NAME	PARAMETER_VALUE
ACCEPT_PLANS	TRUE

If you want to view the results of such an automatic job, use the following code:

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', execution_name => 'EXEC_20606') AS output
FROM dual;

How to find the time for the last system reboot on your linux server

# who -b
         system boot  2017-11-10 01:23

How to use ROWTYPE to fetch entire rows for insert into an interim table

Here is how I used a PL/SQL record to insert rows from a table into an interim table, using parallel slaves and direct path:

alter session force parallel dml;
alter session force parallel query;

DECLARE
  --declare a table-based record for the table "document"
  v_document_rec document%ROWTYPE;

  CURSOR document_cur IS
    SELECT *
    FROM document
    WHERE trunc(created_date) between TO_DATE('01.01.2017','dd.mm.yyyy') AND to_date(to_char(SYSDATE,'dd.mm.yyyy'))
    FETCH FIRST 100000 ROWS ONLY;

   -- declare a cursor-based record
   v_document_rec document_cur%ROWTYPE;

BEGIN
   OPEN document_cur;
   LOOP
   -- fetch the content of the cursor into the record
   FETCH document_cur into v_document_rec;
   EXIT WHEN document_cur%NOTFOUND;
   INSERT /*+ APPEND PARALLEL 8 */ INTO document_interim VALUES( v_document_rec.ID,
                                                 v_document_rec.document_id,
                                                 v_document_rec.document_name,
                                                 v_document_rec.created_date,
                                                 v_document_rec.security_level,
                                                 v_document_rec.content );
   END LOOP;
   COMMIT;
   CLOSE document_cur;
END;
/

What is the difference between a bind-sensitive and bind-aware cursor?

The terms "bind-sensitive" and "bind-aware" are used when explaining Oracles Adaptive Cursor Sharing (ACS) feature.

Adaptive cursors sharing makes it possible for a single statement to use different execution plans, depending on the values of its bind variables.

Oracle explains the difference between bind-sensitive and bind-aware in their documentation like this:


"A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable."

and

"A bind-aware cursor is a bind-sensitive cursor that is eligible to use different plans for different bind values."


In other words, when a bind-sensitive cursor have been executed, the database saves the execution statistics for the new values, and compares them with the values used previously.

If (and only if) there is a significant change, Oracle marks the cursor as bind-aware.
During future executions of the cursor, the CBO will generate a plan based on the bind values and their cardinality estimates.