Oracle 12c Some Differences To Consider

Some things to be aware of (no particular order):

1. SID will no longer be usable for DB connections; SERVICE NAME must be used, including all tnanames entries.
2. Database links are claimed to be faster, however testing has shown this to not be the case.
3. Cost Based Optimizer (CBO) is paramount in 12c and must have good statistics.
   1. Statistics has improved in 12c; the CBO is getting smarter.
4. Should use “auto_sample_size” & should never use “estimate 100%” when gathering statistics to prevent skewing by outliers.
5. Don’t use “1=2” when using CTAS as it will leave you ‘0’ for your stats, which is not the same thing for the CBO as null.
   1. Note that for bulk load/direct-path inserts and CTAS’s &  stats are gathered automatically (similar to an index create in 11g) during the data load, thereby avoiding additional scan operations.
6. “Hash Joins” are parallel; they have a slow start and then are very fast. (Good for large row sets.) “Nested Loops” are serial; (Good for small row sets).
7. New join (cross_outer_apply_clause); Allowing for a variation of the ANSI cross join or left outer join.
8. New join syntax for Lateral clause for inline views which allows for columns within the inline view to be accessed.
9. 12c adds an “Identity Column”, (declare a column as IDENTITY).
10. 12c adds extended data types: Strings of up to 32k (varchar2, nvarchar2, raw). Meaning the same size limits on those datatypes for both SQL & PLSQL.
11. Invisible (hidden) columns, (alter table …modify). [“set column invisible on” to display them in a “DESC”].
12. New “Pagination” row limiting  for top N numbers or percentage of rows to be returned with 12c. Implementation is down via an analytic function and now is ANSI compliant syntax.
13. Sequences can now be created at a session level. Also KEEP and NOKEEP feature added for NEXTVAL. Can also be used as table column default value.
14.  Auditing without the pervious performance impact.
15. Bloom filters perform better in 12c.
16. New in-memory aggregation hint: “vector_transform”. Conceptually similar to star transformations.
17. In-memory index pruning hints: “inmemory” and “inmemory_pruning”.
18. RMAN single table recovery automated from backups. (Must have the necessary space to accomplish. Consider smaller tablespaces to accommodate.)
19. DataPump new feature allows you to disable REDO generation (Force_Logging will take precedence in used.)
20. SQLLoader supports identity columns, Posix wildcards for data file names and new line characters in CSV files.
21. New SQL syntax for pattern matching across multiply rows.
22. Some new hints:
    1. GATHER_OPTIMIZER_STATISTICS/NO_GATHER_OPTIMIZER_STATISTICS (to enable or disable statistics gathering during bulk loads).
    2. PQ_CONCURRENT_UNIONALL/NO_PQ_CONCURRENT_UNIONALL (to enable or disable concurrent procession of union or union all).
    3. PQ_FILTER (tells the optimizer how to process rows when filtering correlated subqueries).
    4. PQ_SKEW/NO_PQ_SKEW (tells the optimizer if the distribution of values for the join keys for a parallel join are skewed or not).
    5. USE_CUBE/NO_USE_CUBE (specifies whether to use or exclude cub joins).
23. PL/SQL Functions  are now allowed in WITH clause.
24. STREAMS has been depreciated and will no longer be available in future releases.
25. Oracle Label Security has been depreciated.
26. IGNORECASE & SEC_CASE_SENSITIVE_LOGIN has been depreciated; now passwords are always case sensitive.
27. REMOTE_OS_AUTHENT depreciated.  No OS authenticated logins allowed.

A Faster Tablespace Sizing Script

     I have some databases with many thousands of tables and many of those tables are quite large containing billions of rows.  Several of them have over a million extents.  As I am sure you can image, this takes a terrible performance toll on the traditional tablespace sizing scripts which utilized sources such as dba_data_files and dba_free_space.
     Then along comes a new view in 11g that has changed my worst performer from around 30 minutes to sub-second response time. Got to love that. It is the dba_tablespace_usage_metrics view.
     This is the same view used by Oracle Enterprise Manager (OEM) to generate tablespace consumptions alerts.  Here is the code for a much faster, and simpler tablespace sizing script without all the union all's and such.
     Enjoy:




set linesize 188 trimspool on pagesize 66


column tablespace_name format a24
column TOTAL_MB format 999,999,999
column MB_USED format 999,999,999
column PERCENT_USED format 999.9


select tablespace_name
      ,round((tablespace_size*32768)/1048576,0) as TOTAL_MB
      ,round((used_space*32768)/1048576,0) as MB_USED
      ,round(USED_PERCENT,1) as PERCENT_USED
from dba_tablespace_usage_metrics
order by PERCENT_USED desc;

Oracle Hints SAS

If any of you are using SAS and query data from Oracle database with HINTS, you might want to take note that Oracle Hints will be considered as comments in SAS and will be ignored and will not be passed to the Oracle database.  You won't even be able to see them on the DB side in your SQL, unless (what an interesting word) you have the "PRESERVE_COMMENTS" keyword set in your SAS connect.

EXAMPLE:

proc sql;   

connect to oracle as mycon(user=testuser

        password=testpass preserve_comments);   

select *      

   from connection to mycon

     (select /* +indx(empid) all_rows */

          count(*) from employees);

quit;

Your can refer to the following note for more detail:

Usage Note 7290: Oracle hints are not passed when coded in a SAS macro :    http://support.sas.com/kb/7/290.html