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

Finding the SQL that is chewing up all the Temp Tablespace

Here is a script that will do just that.  Enjoy.
Thx

***************
REM: tempfindsql.sql


create or replace view tempspfindsql_view
as
SELECT   S.sid,S.serial#,S.username,S.osuser,P.spid,S.module,S.program,
                 SUM (U.blocks) * T.block_size / 1024 / 1024 mb_used, U.tablespace,
                 COUNT(*) sort_ops
  FROM      sys.v$sort_usage U, sys.v$session S, sys.dba_tablespaces T, sys.v$process P
  WHERE    U.session_addr = S.saddr
  AND      S.paddr = P.addr
  AND      U.tablespace = T.tablespace_name
  GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
           S.program, T.block_size, U.tablespace
/

create or replace view tsql1_view
as
select a.username,a.osuser,a.sid,a.SERIAL#,a.sql_id,substr(sql_text,1,74) as sql_text
      ,decode(command ,1,  'CrTab'
                      ,2,  'Ins'
                      ,3,  'Sel'
                      ,4,  'CrClus'
                      ,5,  'AltClus'
                      ,6,  'Up'
                      ,7,  'Del'
                      ,8,  'Drop'
                      ,9,  'CrInd'
                      ,10, 'DrInd'

                      ,11, 'AltInd'

                      ,12, 'DrTab'

                      ,13, '---'

                      ,14, '---'

                      ,15, 'AltTab'

                      ,16, '---'

                      ,17, 'Grant'

                      ,18, 'Revoke'

                      ,19, 'CrSyn'

                      ,20, 'DrSyn'

                      ,21, 'CrVi'

                      ,22, 'DrVi'

                      ,23, '---'

                      ,24, '---'

                      ,25, '---'

                      ,26, 'LkTab'

                      ,27, 'NoOper'

                      ,28, 'Rena'

                      ,29, 'Com'

                      ,30, 'Aud'

                      ,31, 'Noaud'

                      ,32, 'CrExtDb'

                      ,33, 'DrExtDb'

                      ,34, 'CrDb'

                      ,35, 'AltDb'

                      ,36, 'CrRolbSeg'

                      ,37, 'AltRolbSeg'

                      ,38, 'DrRolbSeg'

                      ,39, 'CrTsp'

                      ,40, 'AltTsp'

                      ,41, 'DrTsp'

                      ,42, 'AltSes'

                      ,43, 'AltUsr'

                      ,44, 'Commit'

                      ,45, 'Rolbk'

                      ,46, 'Savpo'

                      ,62, 'AnaTab'

                      ,    'Unknown') as command

from sys.v$session a,sys.v$sqlarea b

where b.address = a.sql_address

/

create or replace view tempfind_view

as

select a.sql_id,sum(b.mb_used) as Tot_MBs_Used

from   tsql1_view a, tempspfindsql_view b

where a.sid = b.sid

and   a.serial#=b.serial#

group by a.sql_id

order by a.sql_id;

set linesize 132

column Tot_MBs_Used format 999,999,999,999

column sql_text format a82

spool Temp_Space_SQL.out

select distinct a.sql_id,a.Tot_MBs_Used,b.sql_text

from tempfind_view a, tsql1_view b

where  a.sql_id = b.sql_id

order by Tot_MBs_Used;

spool off

*******************

Here is a sample of the out put:

SQL_ID            TOT_MBS_USED SQL_TEXT

------------- ---------------- ----------------------------------------------------------------------------------

2b064ybzkwf1y                1 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

Parallel Information

• The degree of parallelism for a query can be specified
• By specifying the parallel clause during table/index creation (Or later using alter)
• Using a parallel hint in the sql statement
• Using ‘alter session force parallel query parallel integer’ statement
• If One of the options in the above statement has not been specified (To enable parallelism) then, irrespective of whether the table or index is partitioned or not, oracle does not use parallelism in the query
• When using The dedicated server connection model (As opposed to the shared server model), the sessions shadow process acts as the query co-ordinator.
• The query uses as many “parallel execution servers” as determined by the query optimizer, to execute the query (This is based on a varitey of factors)
• When there is No parallel degree specified at the table level, in the query hint or at the session level, but parallelism is enabled at the session level (using alter session  force parallel query) then the optimizer uses the default degree of parallelism.
• The Default Degree of Parellism (DOP) is determined by cpu_count x parallel_threads_per_cpu
• If you specified parallelism by issueing ‘alter session force parallel query parallel integer’ statement, then the value used for integer is used for the degree of parallelism (If a parallel hint is not specified in the query).
• All of the above statements assume that there are enough query servers available in the pool and parallel_adaptive_multi_user does not reduce the number of parallel execution servers.
• If interoperation parallelism can be used then you could end up using double the number of parallel execution servers as the degree of parallelism
• If for some reason your query cannot be allocated enough parallel execution servers (typically when there are multiple sessions and all the parallel execution servers (gated by parallel_max_servers) are currently in use) and parallel_min_percent > 0 (0 is the default so your query will still get run without parallelism), your query will get an error and you can try again later.

 For a good primer on parallel query in oracle please read the white paper, Oracle Sql Parallel Execution

What Is Being Audited

Here are some little scripts to find that out:



select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;

select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;

Also a few audit commands I find handy:

noaudit all;
-(Turns off all statement audit options).

noaudit all privileges;
-(Turns off all privilege audit options).

noaudit all on default;
-(Turns off all default object audit options).

audit drop any table:
-(Adds auditing for any table that is dropped.

A Faster Insert

Question: I need to speed up some slow running big inserts, what can I do?  Would if help if the table was partitioned?

Well first off, partitioning a table does not make inserts run any faster.   Now if you were trying to improve the performance of deletes, then partitioning would buy you some speed there. So if partitioning isn't the answer what else can you do to help out your slow processing inserts?   

Are you doing any joins withing the insert code to generate the data you are using for the inserts?  If you are, then that would be a good place to take a long hard look.  As often that can very will be a large part of the issue.  It is important to know what all tables are being joined together.  You might find that you are using a view or two or three within you join conditions; and views are often built on multi-table joins.  You could find yourself joining a larger number of tables together and not even realize it. 

Speaking of joins; You can play with your explain plan (I know you executed one before just throwing some new code out there on the server and letting it run wild consuming resources-) by trying things like a sort merge or maybe a nested loops join instead of hash join (which don't seem to be the best choice when doing inserts as it tends to execute much slower).

Some more basic first-line type of things you can do to help with insert speeds are:

-Ensure an appropriate parallel degree setting for the table.

1.     -Alter session enable parallel dml.

2.      -Add the /*+ append */ hint.  Note that this hint will increase your insert speed, but it will also increase your table fragmentation over time. Therefore, you will need to keep an eye on your table and rebuild it as needed.

3.     

I Want A Bigger SGA But My Server Won't Let Me

Well let's say you want to increase the size of the SGA on one of your instances residing on a Linux box.  So you get an updated pfile (create pfile form spfile;), edit it, increasing the sga_max_size and sga_target parameters.  Then you update the spifle (create spfile from pfile='$ORACLE_HOME/dbs/init.ora;) and restart the instance. But the new size throws an error each time you try a startup.

If you find yourself here, a quick place to look is the kernel parameter shmall.  Many times this tends to be the limiting factor.  Find out what your shmall setting is: (cat /proc/sys/kernel/shmall).  Now sum all the values of the sga_target or sga_max_size (whichever is greater) parameter for all the instance(s) you have on this server.  Don't worry about the PGA, as it does not factor into the shmall value.  Let's say your shmall value is 4194304 and the sum of all your instances is 16 gbs.  And you attempted to increase one of your instances SGA by adding 22 more gbs (this would have given you an new overall total SGA size of 38 gbs).  This of course would account for the failure during the startup attempt as your shmall setting is too small.

Now let's see what you need to do to figure out the correct shmall setting to accommodate your desires:

First you will need to know your page size (getconf PAGE_SIZE); you issue this command and find out your pagesize is 4096.

Here is the simple math to get you there:
                 
                    ( X 1024 X 1024) / 4
                                              (16 X 1024 X 1024) / 4 = 9961472

Therefore you new shmall setting will need to be 9961472 to accommodate 38 gbs of SGA.

Easy As Pie: Changing REDO Logfile Sizes

Below is a quick and to the point example of how to quickly change you current logfile sizes.  

Log in as sys

Select * from v$log; 
- (The Group# field will tell you what you current highest logfile group number being used is).


Select * from v$controlfile_record_section where type ='REDO LOG';
- (Comparing the Records_Used against the Records_Total will tell you how man more groups can be added).

Alter database add logfile group 5 ''+DGRP_RAID5_01_0000' size 1G reuse;
Alter database add logfile group 6 ''+DGRP_RAID5_01_0000' size 1G reuse;
Alter database add logfile group 7 ''+DGRP_RAID5_01_0000' size 1G reuse;
Alter database add logfile group 8 ''+DGRP_RAID5_01_0000' size 1G reuse;

Or


Alter database add logfile group 5 ''+DGRP_RAID5_01_0000' size 1G reuse
                                                ,''+DGRP_RAID5_01_0000' size 1G reuse;
Alter database add logfile group 6 ''+DGRP_RAID5_01_0000' size 1G reuse
                                                ,''+DGRP_RAID5_01_0000' size 1G reuse;
Alter database add logfile group 7 ''+DGRP_RAID5_01_0000' size 1G reuse
                                                ,''+DGRP_RAID5_01_0000' size 1G reuse;
Alter database add logfile group 8 ''+DGRP_RAID5_01_0000' size 1G reuse
                                               , ''+DGRP_RAID5_01_0000' size 1G reuse;



-(Add your new logfile groups).


Select * from v$log;
-(Use the Status filed to ensure that none of you old logfile groups are "CURRENT").
-(If any of them are, then use the following as many times as necessary to move the "CURRENT" to a new log).


Alter system switch logfile;

Alter system checkpoint;
-(This will force a checkpoint and write all the current contents of the logs to the appropriate data file(s)).



Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;
Alter database drop logfile group 3;
-(This will drop the old log file groups).


Select * form v$log;
-(To see your handy work).