Oracle Partitioning

         Partitioning addresses key issues in supporting very large tables and indexes by splitting them into smaller and more manageable pieces called partitions.

 LOB  datatype can be partitioned
 LONG/LONG RAW datatype cannot be partitioned

When to Partition a table Table Size &  Historical data table 

Types of Partitioning

• Range Partitioning
• Hash Partitioning
• List Partitioning
• Interval Partitioning ( Only in 11G )
• REF Partitioning ( Only in 11G )
• Composite Partitioning

Range Partitioning

    Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key.

List Partitioning allows unordered and unrelated sets of data to be grouped and organized together

Interval partitions are automatically allocated based upon the value/interval that we set

   Restrictions on Interval Partitioning
•Cannot be used for index organized tables
•Must use only one partitioning key column and it must be a DATE or NUMBER
•Cannot create domain indexes on interval partitioned tables
•Are not supported at the sub-partition level

Benefits of Interval Partitioning
Range partitioned table can easily be converted to use interval partitioning by using the following command:

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Since Oracle will automatically create the new partition based on interval if a partition does not exist in range partition .So that the following error can be solved

ORA-14400: inserted partition key does not map to any partition


ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions.


Oracle uses some sort of fixed algorithm for defining partitions. Choose partition interval correctly.

REF partitioning child table inherits the partitioning strategy of parent table through PK-FK relationship


Different Types of REF  partitioning

---------

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE my_table AS SELECT * FROM my_other_table

• The best thing about CTAS is that it does not use rollback segments .
• The above query will copy all the data from the source table to destination table 
• CTAS is a particularly powerful tool for populating or rebuilding a single partition of a partitioned table 

     CREATE TABLE my_table AS SELECT * FROM my_other_table
             where 1= 2

•  The above query will not copy the data from the source table to destination table.It will only create the structure of source table. 
  

Points Explain Plans

• The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute.
• Read Right-Left and Top-Down on the same indentation level.
• Try to reduce the cardinality of the rowset as soon as possible.
• Using an index is not always the most beneficial approach. Where greater than 10% of rows in any table are joined, a HASH JOIN and Full Table Scan may be the best approach.
• Trust the Cost Based Optimiser but experiment by running the query. Cost and duration are not always the same.
• We use a SORT_AREA_SIZE set for Online Transaction Processing. Consider setting SORT_AREA_SIZE larger for long running batch jobs and other larger tables, but do this outside the normal working hours.
• This can be done using ALTER SESSION SET SORT_AREA_SIZE = 2M;

Oracle Trace - tkprof

Oracle Trace 

 Explain plan provides an approximation of the cost of an individual SQL statement . It doesn’t always correspond to what is happening when SQL is executed and it doesn’t tell you the elapsed time or other important statistics.

We can then analyse the trace file using tkprof to present the output in a more meaningful manner for subsequent analysis.

How to do trace

• Initiate a forms or PL/SQL session
• Identify the Sid and Serial# of the session you wish to trace via PL/SQL Developer (Tools-Sessions menu entry)
• Establish a point in the application where you want to initiate tracing e.g. on a button click
• Log in as dba or ask a colleague to do so if you don’t have access
• Run the following SQL as dba to initiate the trace
  begin dbms_monitor.session_trace_enable(session_id => Sid , serial_num => Serial#); end;
• Run the following SQL as awcdba to stop tracing
       begin dbms_monitor.session_trace_disable(session_id => Sid , serial_num => Serial#); end;
• Log in to the database server .
• The log file will be available in the USER_DUMP_DEST directory. In our environments this is set to /oralog//udump.

Running TKPROF

• Identifying the trace file
• ALTER SESSION SET TRACEFILE_IDENTIFIER = 'my_trace_id';
• Formatting the trace file using tkprof
• tkprof filename.trc output_filename.txt sys=no sort=(prsela,fchela,exeela)
• This sorts the statements from longest running to shortest running. This format allows you to concentrate on the top two or three statements for the most impact.
  Sort on elapsed parse time, fetch-time, elapsed executed time
• SYS=NO omits SQL executed by the SYS user or recursive SQL.
• Warning: You need to ensure that the trace file isn’t too large. Limit the size of the trace file using the following SQL.
• ALTER SESSION SET MAX_DUMP_FILE_SIZE = 10M;

Interpreting Trace File Output

The Parse Phase – Oracle finds the Query in the Shared Pool or creates a new plan for the query (Hard Parse).
Note: Hard parses aren’t good in OLTP systems. That’s why we should always use bind variables.

The Execute Phase – This is the work done by Oracle on OPEN or EXECUTE of the query. For a SELECT this will be empty whereas for an UPDATE, this will be where all the work is done.

The Fetch Phase – For a SELECT this will be where most of the work is done and visible, but an update will show no work.

Column Headings
COUNT      - How many times the event occurred
CPU           - In CPU seconds; how much time was spent
ELAPSED  - As measured by the wall clock
DISK           - How many Physical I/O’s to disk
QUERY      - Blocks read in Consistent mode from Undo segment
CURRENT – Blocks accessed as they are now for UPDATES or Data Dictionary lookups.


Observations on the query plans In trace file

1. CR  Consistent Read ie. Logical IO from the Buffer Cache in the SGA in query mode
2. PR  Physical Read from disk.
3. If the ratio of PR to CR is high then we have a problem!
4. We aren’t using logical IO and the buffer cache
5. The query is returning too much data and swamping the buffer cache.

Advantages of tracing

• Tracing is very useful for diagnosing poor performance.
• It provides far more detail than a simple explain plan.
• Using proper sorts quickly identifies the “worst offenders”
• Experiment with different access paths. Try to reduce the cost of the query.
• Hints don’t always work. The Cost Based Optimiser often knows best.
• Remove functions on join predicates where you can to enable index use.
• Pay attention to cardinality/selectivity of predicates to determine join order.
• Often application tuning gains the best results. e.g. Amalgamate SQL. Use Analytic SQL

USING clause in EXECUTE IMMEDIATE

BEGIN
   INSERT INTO MY_DATA  VALUES (1, 'STEVEN');
   INSERT INTO MY_DATA  VALUES (2, 'VEVA');
   INSERT INTO MY_DATA VALUES (3, 'ELI');
   COMMIT;
END;

(a)  
 DECLARE
   TYPE MY_DATA_T IS TABLE OF MY_DATA%ROWTYPE INDEX BY PLS_INTEGER;
   L_MY_DATA   MY_DATA_T;
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM my_data
         WHERE nm <> :nmvalue
           AND pky <> :keyvalue
           AND SUBSTR (nm, 1, 3) = :nmvalue'
     USING 'abc' ,5  ,'def';
END;
 
(b)
DECLARE
   TYPE MY_DATA_T IS TABLE OF MY_DATA%ROWTYPE INDEX BY PLS_INTEGER;
   L_MY_DATA   MY_DATA_T;
BEGIN
   EXECUTE IMMEDIATE
      'begin
           DELETE FROM my_data
            WHERE nm <> :nmvalue
              AND pky <> :keyvalue
              AND SUBSTR (nm, 1, 3) = :nmvalue;
        end;'
      USING 'abc', 5 ;    
END;


What is the difference between the PLSQL (a) & (b) 




When executing dynamic SQL, you must provide an expression or variable for each placeholder.


When executing dynamic PL/SQL, you must provide an expression or variable for each uniquely-named placeholder.