Disk Consumption with Average Incremental Value

SELECT b.tsname tablespace_name ,
  MAX(b.used_size_mb) cur_used_size_mb ,
  ROUND(AVG(inc_used_size_mb),2)avg_increas_mb
FROM
  (SELECT a.days,
    a.tsname ,
    used_size_mb ,
    used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM
    (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,
      ts.tsname ,
      MAX(ROUND((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
    FROM DBA_HIST_TBSPC_SPACE_USAGE tsu ,
      DBA_HIST_TABLESPACE_STAT ts ,
      DBA_HIST_SNAPSHOT sp,
      DBA_TABLESPACES dt
    WHERE tsu.tablespace_id    = ts.ts#
    AND tsu.snap_id            = sp.snap_id
    AND ts.tsname              = dt.tablespace_name
    AND sp.begin_interval_time > sysdate-7
    GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
      ts.tsname
    ORDER BY ts.tsname,
      days
    ) a
  ) b
GROUP BY b.tsname
ORDER BY b.tsname;

SQL Plan Management (SPM) in Oracle

Many times we experience performance regressions because of many things i.e a execution plan has been changed.For this type of problem there is an elegant solution  called SQL Plan Management (SPM). 

Execution plan changes occur due to various system changes. For example, you might have (manually or automatically) updated statistics for some objects, or changed a few optimizer-related parameters. A more dramatic change is a database upgrade (say from 10gR2 to 11g). All of these changes have the potential to cause new execution plans to be generated for many of your SQL statements. Most new plans are obviously improvements because they are tailored to the new system environment, but some might be worse leading to performance regressions. 

DBAs have several options for addressing these regressions. However, what most DBAs want is simple: plans should only change when they will result in performance gains. In other words, the optimizer should not pick bad plans, period. 

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

How SPM Works:

When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline. If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Oracle call this a "conservative plan selection strategy", as the optimizer preferentially uses a tried an tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.

SQL PLAN MANAGER (SPM) has three major components

SQL Plan Baseline Capture - Create SQL Plan Baseline for trusted (accepted) SQL plans.

SQL Plan Baseline Selection - Conform that only accepted SQl plans are used when an SQL statement is executed.

SQL Plan Baseline Evolution - Evaluated all SQL Plans (old & new) for each SQL statement. The new/old plan will only be excepted if it gives better or equal performance compared with existing trusted plan otherwise rejected. (If no trusted plan exist then current plan will be accepted)

To enable this cool feature we just do the followings:

ALTER SYSTEM set optimizer_capture_sql_plan_baselines= TRUE scope=both; [Default value is false]

ALTER SYSTEM set optimizer_user_sql_plan_baselines= TRUE scope=both; [Default value is true, so just check the parameter value]

Global Resource Background Process

Global Cache Service (GCS) and Global Enqueue Service (GES) are the two important services in RAC Database. These services are basically collection of background processes.  These two processes together cover and manage the total Cache Fusion process, resource transfers, and resource escalations among the instances.

These play the key role in implementing Cache Fusion. GCS ensures a single system image of the data even though the data is accessed by multiple instances. The GCS and GES are integrated components of Real Application Clusters that coordinate simultaneous access to the shared database and to shared resources within the database and database cache. GES and GCS together maintain a Global Resource Directory (GRD) to record information about resources and enqueues. GRD remains in memory and is stored on all the instances. Each instance manages a portion of the directory. This distributed nature is a key point for fault tolerance of the RAC. The following resources information is available in GRD.

• Data Block Identifiers (DBA)
• Location of most current version 
• Modes of the data blocks: (N)Null, (S)Shared, (X)Exclusive
• The Roles of the data blocks (local or global) held by each instance
• Buffer caches on multiple nodes in the cluster

The coordination of concurrent tasks within a shared cache server is called synchronization. Synchronization uses the private interconnect and heavy message transfers. The following types of resources require synchronization: data blocks and enqueues. GCS maintains the modes for blocks in the global role and is responsible for block transfers between instances. LMS processes handle the GCS messages and do the bulk of the GCS processing.

An enqueue is a shared memory structure that serializes access to database resources. It can be local or global. Oracle uses enqueues in three modes: null (N) mode, share (S) mode, and exclusive (X) mode. Blocks are the primary structures for reading and writing into and out of buffers. An enqueue is often the most requested resource.

GES maintains or handles the synchronization of the dictionary cache, library cache, transaction locks, and DDL locks. In other words, GES manages enqueues other than data blocks. To synchronize access to the data dictionary cache, latches are used in exclusive (X) mode and in single-node cluster databases. Global enqueues are used in cluster database mode.

Global Resource Directory (GRD) is the internal database that records and stores the current status of the data blocks. Whenever a block is transferred out of a local cache to another instance's cache the GRD is updated. The following resources information is available in GRD.

Atomic Control File to Memory Service (ACMS):
In a RAC environment, the ACMS per-instance process is an agent that contributes to ensuring that a distributed SGA memory update is either globally committed on success or globally aborted if a failure occurs.

Global Enqueue Service Monitor (LMON):
The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations.

Global Enqueue Service Daemon (LMD): 
The LMD process manages incoming remote resource requests within each instance.

Global Cache Service Process (LMS): 
The LMS process maintains records of the data file statuses and each cached block by recording information in the GRD. The LMS process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances. This processing is part of the cache fusion feature.

Instance Enqueue Process (LCK0): 
The LCK0 process manages noncache fusion resource requests such as library and row cache requests.

Global Cache/Enqueue Service Heartbeat Monitor (LMHB):
LMHB monitors LMON, LMD, and LMSn processes to ensure that they are running normally without blocking or spinning.

Result Cache Background Process (RCBG): 
This process is used for handling invalidation and other messages generated by server processes attached to other instances in Oracle RAC.

How to show all privileges from a user in oracle?

You can try these below views.

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

DBAs and other power users can find the privileges granted to other users with the DBA_ versions of these same views. They are covered in the documentation .

Those views only show the privileges granted directly to the user. Finding all the privileges, including those granted indirectly through roles, requires more complicated recursive SQL statements:

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;

select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;

select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;

Java Conceptual Diagram