Tag Archives: performance

Data Dictionary – user_constraints/user_cons_columns is slow in Oracle 12cR1

Recently one of our client after upgrade, complaint about sluggish performance for one of the module comprise of getting table information in Oracle 12c R1. Impacted SQL, comprise of Data Dictionary tables getting Table metadata. SELECT UTC.TABLE_NAME, UTC.COLUMN_NAME, UTC.COLUMN_ID AS … Continue reading

Rate this:

Posted in Oracle 12c New Features, Oracle Internal | Tagged , , , | Leave a comment

Alias on Rownum might degrade performance in oracle.

We use First’s row filtering(using Rownum) for lot of day to day functional implementation, Pagination being one of the best example. Rownum clause is best way to impose First – N Rows optimization and restrict subset of projected rows as … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , , | Leave a comment

Incorrect Refer to a column alias causing performance degradation

For easy of referencing and better code readability we add aliases to table reference in most of queries. For correlated queries we add conditions based on correlation columns between any two tables or entities. Below is one of query shared … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , | Leave a comment

NVL Function Internal Behaviour in Oracle for Scalar Calls.

During a performance analysis, identified a NVL Scalar Function calls was eating up lot of SQL time. Scalar function was like below, NVL ( A_Table_Column, (SELECT COUNT(*) FROM TABLE_B WHERE <>) ) If we check Scalar Columns, we are ideally … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , , | Leave a comment

Oracle 12c TOP N Pagination performance considerations.

In my post on TOP N and Pagination in Oracle 12c highlighted features introduce. In current post will try to compare performance and cardinality estimations for a scenario between INLINE ORDER BY + OUTER ROWNUM and TOP N IN 12C. … Continue reading

Rate this:

Posted in Oracle 12c New Features, Oracle Internal | Tagged , , , , , | 1 Comment