Tag: 11g

Database, Performance

Oracle 11g Semantic Hints

 , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

Here is a quick summary I did for a client regarding new Semantic Hints on 11g..

11g New Semantic Hints

In Database version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. They have different working ends but they all tries to avoid ORA-0001 in some specific situations. An important note is that, unlike almost all other hints, this hint has a semantic effect: it changes the actual behavior – not just the optimization – of the SQL. Oracle Docs says about this Hint:

“The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.”

Some specifics about each one:

  • IGNORE_ROW_ON_DUPKEY_INDEX: “When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row.”
  • CHANGE_DUPKEY_ERROR_INDEX: “The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.”
  • RETRY_ON_ROW_CHANGE: “When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.”

The use of those hints, however, is only recommended for pretty specific ends and should be avoided if possible. Not only for having some related bugs, but to add some complexity to known operations like bulk inserts and similar.

Other important hint is the Result Cache Hint, mentioned some months ago, under Result Cache Post.

Cheers!

Database

Oracle Read Only Tables: Do you use it?

 , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Read Only Tables

In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command. Examples as per below:

ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

In case of any other DML or DDL operation try to be ran, the following error will be prompted:

ORA-12081: update operation not allowed on table "OWNER"."TABLE_NAME"

This can be a very useful feature for specific situations where we need to guarantee that no changes be performed on table by applications, like a forced revoke on this specific table, even system grants be in place like “update any table”.

Cheers!

Database, SQL | PLSQL

Oracle Trigger Follows Clause: Simultaneous Ordered Triggers

 , , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So here is something you may not be aware and is really cool:

Follows Clause

Oracle allows more than one trigger to be created for the same timing point, but up to version 11g is not possible to establish the execution order of execution. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point.

The example below establish that second_trigger is executed after first_trigger before inserting each row in table_example.

CREATE OR REPLACE TRIGGER second_trigger
BEFORE INSERT ON table_example
FOR EACH ROW
FOLLOWS first_trigger
BEGIN NULL; END;
/

Nice, han?
Cheers!

Database, SQL | PLSQL

Regular Expressions with SUBEXPR

 , , ,

Hi All!

Did you know since 11g the REGEXP_INSTR and REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern?

Also, a new function, REGEXP_COUNT, returns the number of times the search pattern appears in source string.

Below a simple example of each one:

SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) FROM dual;

REGEXP_INSTR('1234567890','(123)(4(56)(78))',1,1,0,'I',3)
---------------------------------------------------------
                                                        5

SQL> SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) FROM dual;

RE
--
56

SQL> SELECT REGEXP_COUNT('123 123 123 123', '123', 1, 'i') FROM dual;

REGEXP_COUNT('123123123123','123',1,'I')
----------------------------------------
                                       4

Cheers!

Database, SQL | PLSQL

Oracle: Create Disabled Triggers

 , ,

Hey,
I believe you may know about this already, but in case its something new to you: Yes, this is possible since 11g!

Prior to 11g, it was only possible to create triggers in the enabled state, and after creating to disable them. Since 11g they can be explicitly enabled or disabled at creation time using clause ENABLE or DISABLE. As per example:

CREATE OR REPLACE TRIGGER disabled_trigger
BEFORE INSERT ON table_example
FOR EACH ROW
DISABLE
BEGIN NULL; END;
/

If didn’t know it yet. Np! You know it now! 😀

Cheers!

Database

Oracle Invisible indexes

 , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So here is an 11g feature that is actually when known and I really like to use  when considering to remove indexes on clients or even “testing” a creation in production. How does that work?

Invisible indexes

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command. Examples of those commands are listed below:

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

Cheers!

Database, Performance, SQL | PLSQL

Oracle SQL Join Factorization Transformation (JFT)

 , , , ,

Hi All!
Following the line of last week post, let’s talk today about the JFT…

Oracle SQL Join Factorization Transformation (JFT)

The UNION operator is great for merging the results of multiple queries that return similar rowsets, essentially executing each query separately and merging the results together into a single result set. Oracle 11g release 2 come with improvements for SQL optimizer on how Oracle handles UNION ALL performance with the new join factorization transformation (JFT). The Join Factorization Transformation applies only to UNION ALL queries.

The following example show how the optimizer improves the performance of UNION ALL by dynamically re-writing a UNION ALL query into a more efficient form using an in-line view:

Original Query: 

select t1.c1, t2.c2     
    from t1, t2, t3     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2    
union all     
    select t1.c1, t2.c2     
    from t1, t2, t4     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Re-Written Query:

select t1.c1, VW_JF_1.item_2
     from t1, (select t2.c1 item_1, t2.c2 item_2
                    from t2, t3
                    where t2.c2 = t3.c2 and t2.c2 = 2
               union all
                    select t2.c1 item_1, t2.c2 item_2
                    from t2, t4
                    where t2.c3 = t4.c3) VW_JF_1
     where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

Nice, right?
Cheers!

Database, Performance

11g SQL transformations (CBQT, CNT, JE, JPPD)

 , , , ,

Hi All!
Let’s save some time to review those?
They usually very important when analysing query performance, specially on 10046 traces. I separated those were included on 11g, to have it as subset. Here it goes:

New 11g SQL transformations (CBQT, CNT, JE, JPPD)

The Oracle cost-based optimizer CBO is constantly being improved in its ability to re-write and transform sub-optimal SQL queries. In general way, CBO work by replacing operations by equivalent optimal when possible, changing clauses or re-grouping to take advantage on in indexes, eliminating subqueries by replacing with joins and not null tests and similar. In 11g new SQL transformations were implemented looking to continue improving this process. They are:

  • Cost-Based Query Transformation (CBQT): This is one of the core mechanisms in CBO, that tries different transformed and untransformed variants of the query and runs them through the costing algorithm. Improvements were done in 11g, specially related to clauses NOT IN into NOT EXISTS.
  • Count Transformation (CNT): If all rows have a value on this column (not null), Oracle CBO can simply count the number of rows. There is no need to actually retrieve the column value. This transformation changes count(col) to count(*).
  • Join Elimination (JE): Join elimination is a query transformation of the Oracle optimizer to remove joins that are not required in a query. Released on 10g, Join Elimination got several improvements and new mechanisms in 11g and even more in 12c.
  • Join Predicate PushDown (JPPD): Allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. The following types of views supports predicate pushdown: UNION ALL/UNION view, Outer-joined view, Anti-joined view, Semi-joined view, DISTINCT view and GROUP-BY view.

Nice?

Hope you enjoy it!

Database, SQL | PLSQL

PL/Scope – Did you know that?

 , , ,

Hi All!
I decided to make a serie of posts with really quick notes about some really awesome features we simply don’t use. Today’s one is about PL/Scope.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

PL/Scope

PL/Scope is a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of “IDENTIFIERS:NONE”. To enable collection, switch value to “IDENTIFIERS:ALL”. The data is stored in the SYSAUX tablespace, this space needs to be well planned and managed..

The PL/Scope data is available from the %_IDENTIFIERS views. The following query displays data gathered during the compilation of a test procedure:

COLUMN name FORMAT A30
SELECT LPAD(' ', level*2, ' ') || name AS name, type, usage, usage_id, line, col
FROM   user_identifiers
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;

NAME                           TYPE               USAGE         USAGE_ID       LINE        COL
------------------------------ ------------------ ----------- ---------- ---------- ----------
  TEST_PLSCOPE                 PROCEDURE          DECLARATION          1          1         11
    TEST_PLSCOPE               PROCEDURE          DEFINITION           2          1         11
      P_IN                     FORMAL IN          DECLARATION          3          1         25
      L_VAR                    VARIABLE           DECLARATION          4          2          3
      L_VAR                    VARIABLE           ASSIGNMENT           5          4          3
        P_IN                   FORMAL IN          REFERENCE            6          4         12
      L_VAR                    VARIABLE           ASSIGNMENT           7          6          3
        L_VAR                  VARIABLE           REFERENCE            8          6         12

8 rows selected.

(this script was taken from here)

Some additional information about it: The documentation states that some identifiers will not listed unless the STANDARD package is recompiled after the PLSCOPE_SETTINGS parameter is set properly. However there is some opening community discussions about invalidation of basic procedures not being revalidated again, even on use of urlrp procedure. This way, we don’t recommend to use it directly on Production environment without validating before in an equivalent environment and taking all caution measures as possible.

Hope you enjoy it. Cheers!

Database, SQL | PLSQL, Tools

PLSQL: Which code is taking longer?

 , ,

So you have a slow process calling several PLSQL Codes, including Procedures, Functions and etc, but don’t know what is taking longer?

Your problems has ended… 

In Oracle 11gR1 was introduced the PL/SQL Hierarchical Profiler to help developers by providing hierarchical profiler data analysis for PL/SQL programs.

From Oracle base:
“The hierarchical profiler consists of the DBMS_HPROF package, which feels similar to the DBMS_PROFILER and DBMS_TRACE packages, and the plshprof command line utility to converts the profile information into HTML format.”

For example, we can set a profiler for procedure proc_example_1 and check in all calls and related statistics:

BEGIN
  DBMS_HPROF.start_profiling (
    location => 'ORACLE_DIR',
    filename => 'prof.txt');

  proc_example_1(p_number => 10);

  DBMS_HPROF.stop_profiling;
END;
/

And to see results, a simple output is:

COLUMN owner FORMAT A20
COLUMN module FORMAT A20
COLUMN type FORMAT A20
COLUMN function FORMAT A25
SELECT symbolid,
       owner,
       module,
       type,
       function
FROM   dbmshp_function_info
WHERE  runid = 1
ORDER BY symbolid;

  SYMBOLID OWNER                MODULE               TYPE                 FUNCTION
---------- -------------------- -------------------- -------------------- -------------------------
         1 TEST                 proc_example_1       PROCEDURE            proc_example_1
         2 TEST                 proc_example_2       PROCEDURE            proc_example_2
         3 TEST                 proc_example_3       PROCEDURE            proc_example_3
         4 SYS                  DBMS_HPROF           PACKAGE BODY         STOP_PROFILING
    5 TEST                 proc_example_3       PROCEDURE            __static_sql_exec_line5

5 rows selected.

Which can also be seen with this nice SQL from Oracle Base (Tim Hall):

SET LINESIZE 500 PAGESIZE 1000
COLUMN name FORMAT A100
SELECT RPAD(' ', (level-1)*2, ' ') || a.name AS name,
       a.subtree_elapsed_time,
       a.function_elapsed_time,
       a.calls
FROM   (SELECT fi.symbolid,
               pci.parentsymid,
               RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function,fi.module), '.') AS name,
               NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time,
               NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time,
               NVL(pci.calls, fi.calls) AS calls
        FROM   dbmshp_function_info fi
               LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
        WHERE  fi.runid = 1
        AND    fi.module != 'DBMS_HPROF') a
CONNECT BY a.parentsymid = PRIOR a.symbolid
START WITH a.parentsymid IS NULL;


NAME                                                SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME      CALLS
--------------------------------------------------- -------------------- --------------------- ----------
TEST.proc_example_1                                                31262                    31          1
  TEST.proc_example_2                                              31231                   133         10
    TEST.proc_example_3                                            31098                  3241        100
      TEST.proc_example_3.__static_sql_exec_line5                  27857                 27857       1000

Cheers!