Loading...

Follow Let's Develop in Oracle on Feedspot

Continue with Google
Continue with Facebook
or

Valid
In continuation of basics yet important topics of Oracle Database, with this post, I am trying to explain the difference between two similar looking function MOD and REMAINDER. MOD and REMAINDER both are used to calculate the remainder of a numeric value getting divided by another value. Both functions require two parameters: the value to be divided and the divisor. The main difference between these is that the MOD function uses the FLOOR function in its computation logic, and the REMAINDER function uses ROUND. For this reason, the values returned from the two functions can differ.

ngarg> select 25 value, level divisor, 
2 MOD(25, level) MODULUS, REMAINDER(25, level) REMAINDER from dual
3 connect by level <= 25;

VALUE DIVISOR MODULUS REMAINDER
---------- ---------- ---------- ----------
25 1 0 0
25 2 1 1
25 3 1 1
25 4 1 1
25 5 0 0
25 6 1 1
25 7 4 -3
25 8 1 1
25 9 7 -2
25 10 5 5
25 11 3 3
25 12 1 1
25 13 12 -1
25 14 11 -3
25 15 10 -5
25 16 9 -7
25 17 8 8
25 18 7 7
25 19 6 6
25 20 5 5
25 21 4 4
25 22 3 3
25 23 2 2
25 24 1 1
25 25 0 0

25 rows selected.


Related Links
- ROUND, TRUNC, CEIL and FLOOR in Oracle Database
- Order By and Null values in Oracle Database
- SQL Interview Question Answers
- Order By and Null values in Oracle Database
- Playing With Truncate and Date
- Oracle: Some Important Date Queries
- Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Here is my very first blog post of the year 2019 about a real issue faced by our development team. To make the blog post short and crisp, let me simply dive into the issue.

Problem Statement: I am using APPEND hint with "INSERT INTO SELECT" statement, but when I am trying to SELECT the data after "INSERT INTO SELECT", it is throwing me "ORA-12838: cannot read/modify an object after modifying it in parallel"

First, check what is ORA-12838-
ORA-12838: Cannot read/modify an object after modifying it in parallel
Cause: Within the same transaction, an attempt was made to add read or modification statements on a table after it had been modified in parallel or with direct load. This is not permitted.
Action: Rewrite the transaction, or break it up into two transactions one containing the initial modification and the second containing the parallel modification operation.

Here is the code snippet to reproduce ORA-12838.
nimish@garg> desc emp
Name Null? Type
------------------------ ------- -------- -----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

nimish@garg> create table emp_new as select * from emp where 1=2;
Table created.

nimish@garg> insert /*+ APPEND */ into emp_new select * from emp;
14 rows created.

nimish@garg> select * from emp_new where empno = 7839;
select * from emp_new where empno = 7839
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

As per ORA-12838, Oracle cannot read/modify an object after modifying it in parallel, but we have not used the PARALLEL hint. So we should read about APPEND hint too.

The APPEND hint instructs the optimizer to use direct-path INSERT with the subquery syntax of the INSERT statement.
- Direct-path INSERT is the default in parallel mode.
- In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
- If the table has referential integrity or a trigger, append hint is ignored and Oracle uses the conventional path loading

Now we completely understand "Why INSERT INTO SELECT with APPEND hint is causing ORA-12838". APPEND hint instructs the optimizer to use direct-path INSERT, which is PARALLEL by default and Oracle cannot read/modify an object after modifying it in parallel. The developer was trying to do the same, so before firing the SELECT statement on the TABLE after INSERT INTO SELECT with APPEND hint, he needs to issue a COMMIT which breaks these 2 statements into two transactions.

So now let's try the previous code again, but we will issue the COMMIT before SELECT
nimish@garg> drop table emp_new;
Table dropped.

nimish@garg> create table emp_new as select * from emp where 1=2;
Table created.

nimish@garg> insert /*+ APPEND */ into emp_new select * from emp;
14 rows created.

nimish@garg> commit;
Commit complete.

nimish@garg> select * from emp_new where empno = 7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000.15 10

Great! it worked. I hope you have Enjoyed reading this post.

Related Links:
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- How to Get Execution Plan and Statistics of SQL Query
- DBMS_PROFILER: How to analyze pl/sql performance
- Top 18 features of Oracle 18c
- Oracle 12c Partitioning New Features - Top 10
- SQL Loader Express Mode - Loading data in Oracle database can't be more easy
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
I am writing this post based on a very rare but simple data issue faced by our QA team. Our QA team was trying to compare data of two tables using the MINUS query for some regression testing. Both tables have the same exact structure, and columns in concern were of NUMBER type.

nimish@garg> select PIPELINED_AMT from SALES_FY2018
2 minus
3 select PIPELINED_AMT from REGRESSION_FY2018;
PIPELINED_AMT ORG_TERR_ID
------------ -----------
1408.92113 111587
.000000408 118104
6.5786E-07 118993
7.2110E-07 176297
7.5482E-07 145634
.0000018 120008
1.8962E-06 120536
.00000228 169570

When QA team manually validated each PIPELINED_AMT value for every ORG_TERR_ID, the data same -
nimish@garg> select PIPELINED_AMT from SALES_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT
------------
1408.92113

nimish@garg> select PIPELINED_AMT from REGRESSION_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT
------------
1408.92113

The issue came to me, citing that both tables have exactly same data still MINUS query is giving the results. It was a quite strange issue. As a first debugging step I checked what actually was stored in the tables using DUMP function.

DUMP function shows the datatype code, length and the internal representation of the expression. Two columns having the same datatype and value always return the exact same dump.

nimish@garg> select PIPELINED_AMT, dump(PIPELINED_AMT) from SALES_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT DUMP(PIPELINED_AMT)
------------ -----------------------------------------------------------------
1408.92113 Typ=2 Len=21: 194,15,9,93,12,29,12,4,63,25,34,98,68,65,42,93,97,72,20,29,45

nimish@garg> select PIPELINED_AMT, dump(PIPELINED_AMT) from REGRESSION_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT DUMP(PIPELINED_AMT)
------------ -----------------------------------------------------------------
1408.92113 Typ=2 Len=21: 194,15,9,93,12,29,12,4,63,25,34,98,68,65,42,93,97,72,20,29,47

Ahaan, Dataype and Length are same for both columns but last value of DUMP is different. SALES_FY2018 has 45 and REGRESSION_FY2018 has 47.

I started doubting that client, which was SQLPLUS, was not displaying the complete value and was rounding/truncating the actual values for formatting. So I used TO_CHAR to show the actual complete value stored in PIPELINED_AMT as string.

nimish@garg> select PIPELINED_AMT, to_char(PIPELINED_AMT) from SALES_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT TO_CHAR(PIPELINED_AMT)
------------ ----------------------------------------
1408.92113 1408.92112811036224339767644192967119284

nimish@garg> select PIPELINED_AMT, to_char(PIPELINED_AMT) from REGRESSION_FY2018 where org_terr_id = 111587 ;
PIPELINED_AMT TO_CHAR(PIPELINED_AMT)
------------ ----------------------------------------
1408.92113 1408.92112811036224339767644192967119285

Now, we can see there is a difference at last digit of values after decimal point (highlighted). It was the 35th digit after decimal, which was causing the issue and it actually has no significance. So I suggested QA and Dev Team to round off the values upto 5 decimal points, and complete the regression.

As a final note, we need to understand that client tools, SQL Plus, SQL Developer or Toad formats the data for better display, and sometimes are not the true representation of values stored in the database.


Related Post:
- SQL Interview Question Answers
- ISNUMERIC Function Check Value is Number or Not
- ROUND, TRUNC, CEIL and FLOOR in Oracle Database
- Oracle: Some Important Date Queries
- Playing With Truncate and Date
- Append String to CLOB in Optimized way
- ORA-01722: invalid number
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Proud Moment: My Article on debugging "My Query is taking long time to execute." is published at Oracle Website. https://developer.oracle.com/databases/my_sql_is_stuck

This article contains the step by step process to debug the performance problems of SQLs having a particular issue. I have also tried to include the information about some of the data dictionary tables and their columns. I hope this article will be useful for our database developer community.

A little snippet:

"My SQL is stuck." 
"My Query is taking long time to execute.".

These problem statements are very common, but for the most part the problems as easily solved – if you know how to debug the issue. In this article I will attempt to explain how I debugged and solved a real performance issue. (Note that the objects have been renamed.)
I received a request to look into a performance issue: “I am unable to refresh GLUSR_OPPORTUNITY_MV materialized view, and the dbms_mview.refresh is stuck for last the 45 minutes.” The person was trying to re-create GLUSR_OPPORTUNITY_MV materialized view after adding one new varchar2 column from the existing base table. There was no other change in the query of the materialized view, no table was added, and no condition was altered. Prior to the problem this materialized view refreshed in less than 5 minutes.

To Read the Steps to find the root cause of the issue and little details about data-dictionary tables and their column, please visit
https://developer.oracle.com/databases/my_sql_is_stuck


I would really appreciate your views, or suggestions or feedback on the article!
Happy Oracle Database Performance Tuning!


Read More on Tuning SQL
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Why Primary Key Foreign Key Relationship and Join Elimination
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- Why my distinct query is not using index?
- Avoiding unnecessary function calls to optimize SQL statements
- Why do I prefer COALESCE over NVL
- Query optimization tips for Oracle
- Tune Complete Refresh of Materialized View by atomic_refresh
- How to Fast Refresh Materialized View with Joins
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Oracle Database 18c XE (Express Edition) is now available for download. Oracle Database 18c Express Edition is free to use but has CPU, Memory and Storage limits to 2 CPUs and 2GB of RAM. Oracle Database 18c XE supports up-to 12GB of user data. Oracle Database 18c Express Edition gives you the ability to use database completely free for development and embed & redistribute, but remember it is a community supported edition and Oracle Support is not available for it also no patches will be provided for Oracle Database Express Edition.

Oracle Database 18c XE (Express Edition) supports relational database and also supports JSON, XML, Graph, Spatial data. It also includes key database features such as pluggable databases (up-to 3 pdb), in-memory column store, compression, spatial & graph support, encryption & redaction, partitioning, analytic views, and many more.

Apart from these features Oracle Database 18c XE also supports Oracle Application Express (APEX) which is low-code app development platform. We can utilize ORDS and XE for RESTful application development also.

Following is the list of features, available with Oracle Database 18c Express Edition
- Oracle Partitioning
- Query Results Cache
- PL/SQL Function Result Cache
- Materialized View Query Rewrite
- Adaptive Execution Plans
- In-Memory Column Store and Aggregation
- Oracle Advanced Analytics
- Oracle Spatial and Graph
- Online table redefinition and index rebuild
- Flashback Database and Table
- Oracle Advanced Compression
- Oracle Multitenant (Maximum of 3 PDBs)
- Oracle Database Vault
- Fine-grained auditing
- Virtual Private Database
- Cross-platform Backup and Recovery

Following is the list of features, missing in Oracle Database 18c Express Edition
- Parallel Query/DML
- Oracle Tuning and Diagnostics Pack
- SQL Plan Management and Tuning Sets
- Online Datafile Move
- Recovering tables and table partitions from RMAN backups
- Oracle Data Guard
- Oracle On-Line Analytical Processing (OLAP)
- Oracle Real Application Clusters (Oracle RAC)
- Oracle Sharding
- Oracle Streams
- Oracle NoSQL Database Basic Edition


The complete list of feature available with Oracle 18c Express Edition can be checked here
- https://docs.oracle.com/en/database/oracle/oracle-database/18/xelic/licensing-information.html


Oracle Database 18c Express Edition is freely available with following License Agreement
- you may develop, prototyping and running your applications for your own internal data processing operations
- you may also distribute the programs with your applications
- you may use the programs to provide third party demonstrations and training
- you may copy and distribute the programs to your licensees provided that each such licensee agrees to the terms of this Agreement.


For more information check following link
- https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html



Related Articles:
- Oracle Cloud Performance Benchmark by Accenture
- Oracle 12c Partitioning New Features - Top 10
- Oracle Database 12c R2 New Features for developers
- Oracle Database 12c R1 New Features for Developers
- JSON in Oracle Database 12c with Examples
- SQL Interview Question Answers
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
A lot of time we want results of our SQL statements in CSV, JSON, XML formats or wish to generate insert statement for our data. For these requirements, we can use SET SQLFormat in our favorite database tools - SQL Developer and SQLcl.

Oracle has provided a lot of SQLFormat options, the most commonly used are -

SET SQLFORMAT CSV

SET SQLFORMAT JSON

SET SQLFORMAT INSERT
SQL Developer also provides us option to directly put the SQLFORMAT in the comment in SQL itself as

Apart from these wonderful SQLFORMAT, we can use XML, HTML and LOADER with SQLDeveloper and SQLcl. There are others too but are not commonly used, and if you want to learn, explore the Oracle Documentation.

Related Posts:
- Oracle SQL Developer - Autotrace - Insufficient Privileges
- SQL Loader Express Mode - Loading data in Oracle database can't be more easy
- JSON in Oracle Database with Examples
- Top 18 features of Oracle 18c
- Oracle 12c Partitioning New Features - Top 10
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
I do not prefer to use "Not In" with my SQL statements. I usually re-write "NOT IN" with "OUTER JOIN" or "NOT EXISTS". Many people have asked me the reason for not using "Not In". With this post, I am trying to explain when NOT IN can be little tricky

There are mainly following two concerns of not using "Not In"
1. NOT IN can be very expensive in terms of performance.
2. NOT IN can give us unexpected results with NULLs, if the developer is not careful.

So without further delays, let's dive into the example with my favorite tables EMP and DEPT.

Here is the dataset, as you can see here EMP table does not contain any record for (DEPTNO 40, DNAME OPERATIONS).
nimish@garg> select d.deptno, d.dname, count(*)
2 from emp e, dept d
3 where e.deptno = d.deptno
4 group by d.deptno, d.dname;

DEPTNO DNAME COUNT(*)
---------- -------------- ----------
10 ACCOUNTING 2
20 RESEARCH 5
30 SALES 6

nimish@garg> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Now let us run a NOT IN SQL to get the DEPT which is NOT IN EMP table.
nimish@garg> select * from dept
2 where deptno not in (select deptno from emp);

no rows selected

Something is wrong here. It should have returned (DEPTNO 40, DNAME OPERATIONS) record.

Let me rewrite the SQL with OUTER JOIN.
nimish@garg> select d.* from dept d, emp e
2 where d.deptno = e.deptno (+)
3 and e.deptno is null;

DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON

Outer Join seems to be working fine. Let us run another SQL to perform the same operation with NOT EXISTS

nimish@garg> select * from dept d where not exists
2 (select 1 from emp e where e.deptno = d.deptno);

DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON


NOT EXISTS also worked.
The issue is NOT IN gets little tricky when there is NULL in data. NULL is not "equal" or not "not equal" to any value. NULL is simply NULL and signifies empty or no presence of any value in the column. This can be simply delat with NVL.

nimish@garg> select * from dept
2 where deptno not in (select nvl(deptno,-999) from emp);

DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON

Please do post your comments, if you find this post of any value. Enjoy writing SQLs.

Related Posts:
- Query Optimization - Not In
- Oracle: using OUTER JOIN in place of NOT IN
- Query Optimization - Handling NULL Values
- Why prefer COALESCE over NVL
- Oracle: Outer Joins - Old and New Syntax
- Query optimization tips for Oracle
- Order By and Null values in Oracle Database
- Why my distinct query is not using index?
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This SQL Puzzle is inspired from the problem which I solved recently, and I think the problem is quite interesting to be shared as a SQL Puzzle. It includes shifting the values among columns and also transpose of rows. I hope you will enjoy solving the problem.

We have an existing table and data with following rules -
1) We have a table of hierarchy going upto 4 levels for different PRODUCTS and Hierarchy TYPE.
2) We have only 3 PRODUCTS, MOBILE, LAPTOP, SPEAKER
3) Each hierarchy can be of PRODUCTION / SALE / SUPPORT type
4) Each Hierarchy may have GM -> RM -> MGR -> EXEC roles
5) In some cases any of the role from Hierarchy can be missing, like it can be GM -> MRG -> EXEC or GM -> MGR.

Following is the script for sample table and data:

create table product_staff_hierarchy
(
product varchar2(100),
level1_ecode number(10),
level1_ename varchar2(100),
level1_erole varchar2(100),
level2_ecode number(10),
level2_ename varchar2(100),
level2_erole varchar2(100),
level3_ecode number(10),
level3_ename varchar2(100),
level3_erole varchar2(100),
level4_ecode number(10),
level4_ename varchar2(100),
level4_erole varchar2(100),
hierarchy_type varchar2(100)
);

insert into product_staff_hierarchy
select 'MOBILE', 1, 'M1', 'GM', 2, 'M2', 'RM' , 3, 'M3', 'MGR', 4, 'M4', 'EXEC', 'PRODUCTION' FROM DUAL UNION ALL
select 'MOBILE', 5, 'M5', 'GM', 6, 'M6', 'MGR', 7, 'M7', 'EXEC', NULL, NULL, NULL, 'SALE' FROM DUAL UNION ALL
select 'MOBILE', 8, 'M8', 'GM', 9, 'M9', 'RM', 10, 'M10', 'EXEC', NULL, NULL, NULL, 'SUPPORT' FROM DUAL UNION ALL
select 'LAPTOP', 11, 'L11', 'RM', 12, 'L12', 'EXEC', NULL, NULL, NULL, NULL, NULL, NULL, 'PRODUCTION' FROM DUAL UNION ALL
select 'LAPTOP', 13, 'L13', 'GM', 14, 'L14', 'MGR', 15, 'L15', 'EXEC', NULL, NULL, NULL, 'SALE' FROM DUAL UNION ALL
select 'LAPTOP', 16, 'L16', 'GM', 17, 'L17', 'MGR', NULL, NULL, NULL, NULL, NULL, NULL, 'SUPPORT' FROM DUAL UNION ALL
select 'SPEAKER', 18, 'S18', 'GM', 19, 'S19', 'RM', 20, 'S20', 'MGR', 21, 'L21', 'EXEC', 'SALE' FROM DUAL UNION ALL
select 'SPEAKER', 22, 'S22', 'MGR', 23, 'S23', 'EXEC', NULL, NULL, NULL, NULL, NULL, NULL, 'PRODUCTION' FROM DUAL UNION ALL
select 'SPEAKER', 24, 'S24', 'GM', 25, 'S25', 'EXEC', NULL, NULL, NULL, NULL, NULL, NULL, 'SUPPORT' FROM DUAL UNION ALL
select 'MOBILE', 26, 'M26', 'GM', 27, 'M27', 'MGR', 28, 'M28', 'EXEC', NULL, NULL, NULL, 'PRODUCTION' FROM DUAL UNION ALL
select 'MOBILE', 29, 'M29', 'RM', 30, 'M30', 'MGR', 31, 'M31', 'EXEC', NULL, NULL, NULL, 'SALE' FROM DUAL UNION ALL
select 'MOBILE', 32, 'M32', 'GM', 33, 'M33', 'RM', 34, 'M34', 'EXEC', NULL, NULL, NULL, 'SUPPORT' FROM DUAL UNION ALL
select 'LAPTOP', 35, 'L35', 'GM', 36, 'L36', 'RM', 37, 'L27', 'MGR', NULL, NULL, NULL, 'PRODUCTION' FROM DUAL UNION ALL
select 'LAPTOP', 38, 'L38', 'MGR', 39, 'L29', 'EXEC', NULL, NULL, NULL, NULL, NULL, NULL, 'SUPPORT' FROM DUAL UNION ALL
select 'SPEAKER', 40, 'S40', 'RM', 41, 'S41', 'EXEC', NULL, NULL, NULL, NULL, NULL, NULL, 'PRODUCTION' FROM DUAL UNION ALL
select 'SPEAKER', 42, 'S42', 'GM', 43, 'S43', 'RM', 44, 'S44', 'MGR', NULL, NULL, NULL, 'SALE' FROM DUAL;

Now here is the Problem Statement:
We want to transpose above data in the following format
1) Per Product, we would have only one row.
2) We will have ROLE_TYPE column for each combination like SALE_EXEC or SUPPORT_RM
3) Values related to product and role combination should go in their designated columns
4) Multiple hierarchies of each type should be transposed into one row per product
5) The structure of final output should be as per the following table structure

create table product_staff_new
(
product varchar2(100),
production_gm_ecode number(10),
production_gm_ename varchar2(100),
production_rm_ecode number(10),
production_rm_ename varchar2(100),
production_mgr_ecode number(10),
production_mgr_ename varchar2(100),
production_exec_ecode number(10),
production_exec_ename varchar2(100),
sale_gm_ecode number(10),
sale_gm_ename varchar2(100),
sale_rm_ecode number(10),
sale_rm_ename varchar2(100),
sale_mgr_ecode number(10),
sale_mgr_ename varchar2(100),
sale_exec_ecode number(10),
sale_exec_ename varchar2(100),
support_gm_ecode number(10),
support_gm_ename varchar2(100),
support_rm_ecode number(10),
support_rm_ename varchar2(100),
support_mgr_ecode number(10),
support_mgr_ename varchar2(100),
support_exec_ecode number(10),
support_exec_ename varchar2(100)
);


The original problem was having 8 Products, 10 Levels, and 8 columns per level, and was originally solved with PL/SQL - loops and dynamic SQL. When this requirement came to me, I was able to solve it with only one SQL and gained a-lot of performance.

Do share your approach in the comment box. Have fun writing the SQL or PL/SQL, Enjoy :)

More SQL Puzzles:
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- Sorting Versions stored in Varchar2 Column
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
We encountered infamous ORA-00600: internal error code, arguments: [kglpin-bad-lock], [0x2DB07B478] in our development environment (Oracle 12c R1) while executing "UPDATE_AGGREGATES" procedure. The more painful thing was this ORA-00600 with "kglpin-bad-lock" was intermittent, sometimes it was coming and sometimes not. I executed my procedure 10 times to check and ORA-00600 with "kglpin-bad-lock" occurred only trice.

nimish@garg> exec UPDATE_AGGREGATES
BEGIN UPDATE_AGGREGATES; END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kglpin-bad-lock], [0x2DB07B478],
[], [], [], [], [], [], [], [], [], []
ORA-06512: at "APPDEVUSR.UPDATE_AGGREGATES", line 156
ORA-06512: at line 1

As we can see in above execution, the first parameter of ORA-00600 was "kglpin-bad-lock". KGL Pin activity is related to Library Cache, and can be viewed in X$KGLPN. There are 2 other tables X$KGLOB for objects and X$KGLLK for all locking structures on an object in Library Cache (Parse Locks).

Library Cache contains the Parsed SQL and Execution Plans for a given SQL statement, which is shared among multiple users/sessions to avoid re-parsing for SQL statements that are absolutely identical. Library cache locks or Parse Locks deals with dependency mechanism between Database Objects and their dependent objects in Library Cache like Parsed SQL. When a Database Object is modified all SQLs in Library Cache dependent upon the object must be invalidated (breaking the parse locks). Before breaking the Parse Lock (Library cache locks), Library cache pins must be acquired in an Exclusive mode on Library Cache Objects (Parsed SQL), to confirm they are not being used by any of the session and can be dropped. Library Pins will not be available until a session is executing an SQL.

Now coming back to our problem of ORA-00600 with kglpin-bad-lock. The workaround provided was to disable the QUERY_REWRITE_ENABLED parameter for the session to avoid ORA-00600 with "kglpin-bad-lock". The important thing here to note was in "UPDATE_AGGREGATES" procedure, there was no MATERIALIZED VIEW used and also there was no DDL which might cause to invalidate Library Cache objects, but the solution worked.

nimish@garg> ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;
Session altered.

nimish@garg> exec UPDATE_AGGREGATES
PL/SQL procedure successfully completed.

Most of the developers/dba assume that QUERY_REWRITE_ENABLED parameter is limited to MATERIALIZED VIEWS only and it allows Oracle to rewrite of queries using materialized views if enabled, but actually it is a very vast feature of Oracle Cost based optimizer. QUERY_REWRITE_ENABLED parameter when set to TRUE, allows Oracle checks whether it is advantageous to rewrite the original SQL statement into a semantically equivalent SQL statement with a lower cost, which includes "Join Eliminations", "Pushed Predicates", "Subquery Unnesting", "Query Rewrite with Materialized Views" and others.

I hope you enjoyed reading this article, and there was something to learn/review. :)
Please do write comments and suggestions.


Related Posts:
- ORA-00600 internal error code
- ORA-04031: unable to allocate n bytes of shared memory
- ORA-01555: snapshot too old
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Why Primary Key Foreign Key Relationship and Join Elimination
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- Foreign Key in Oracle Data Warehouse - Best Practice
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
With this post, I am sharing, Why I was able to refresh an Invalid MATERIALIZED VIEW in Oracle Database and SELECT data from it after one of the base tables were altered, which made the MATERIALIZED VIEW invalid (as expected).

To demonstrate this, I am using my favorite table "EMP", Which has the following structure...
ngarg> desc emp
Name Null? Type
------------------------------------ -------- --------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

and has the following data...
ngarg> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000.15 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850.3 30
7782 CLARK MANAGER 7839 09-JUN-81 2450.45 10
7566 JONES MANAGER 7839 02-APR-81 2975.5 20
7788 SCOTT ANALYST 7566 19-APR-87 3000.55 20
7902 FORD ANALYST 7566 03-DEC-81 3000.6 20
7369 SMITH CLERK 7902 17-DEC-80 800.75 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600.9 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250.05 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500.95 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100.01 20
7900 JAMES CLERK 7698 03-DEC-81 950.99 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

So let us create a MATERIALIZED VIEW on top of EMP table...
ngarg> create materialized view emp_mv
2 build deferred
3 refresh force on demand
4 with primary key
5 as
6 select * from emp;

Materialized view created.

EMP_MV has the same structure as expected...
ngarg> desc emp_mv
Name Null? Type
------------------------------------ -------- --------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

and refresh the materialized view...
ngarg> exec dbms_mview.refresh('emp_mv');

PL/SQL procedure successfully completed.

after which EMP_MV has the same data as of EMP table
ngarg> select * from emp_mv;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000.15 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850.3 30
7782 CLARK MANAGER 7839 09-JUN-81 2450.45 10
7566 JONES MANAGER 7839 02-APR-81 2975.5 20
7788 SCOTT ANALYST 7566 19-APR-87 3000.55 20
7902 FORD ANALYST 7566 03-DEC-81 3000.6 20
7369 SMITH CLERK 7902 17-DEC-80 800.75 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600.9 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250.05 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500.95 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100.01 20
7900 JAMES CLERK 7698 03-DEC-81 950.99 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Now, I am altering the EMP table, so that ENAME column can contains 10 characters no matter how many bytes each character has. The size of the ENAME was VARCHAR2(10 bytes). By default, VARCHAR2 uses NLS_LENGTH_SEMANTICS to determine the length type (byte or char).
ngarg> alter table emp modify ename varchar2(10 char);
Table altered.

ngarg> desc emp
Name Null? Type
------------------------------------ -------- --------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10 CHAR)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

After this ALTER in EMP table, Oracle Database marked our MATERIALIZED VIEW as invalid.
ngarg> select object_name, object_type, status from user_objects where object_name in ('EMP','EMP_MV');

OBJECT_NAME OBJECT_TYPE STATUS
---------------------- ----------------------- -------
EMP_MV MATERIALIZED VIEW INVALID
EMP_MV TABLE VALID
EMP TABLE VALID

You can notice here with EMP_MV name there are two objects, one is MATERIALIZED VIEW, second is a TABLE which is an actual segment and holds data for this MATERIALIZED VIEW.

Our MATERIALIZED VIEW is invalid but We are able to refresh INVALID MATERIALIZED VIEW, and SELECT data from it. To demonstrate this let us update one record in EMP table
ngarg> update emp set ename = 'NIMISH' where empno = 7839;
1 row updated.

ngarg> commit;
Commit complete.

and refresh the MATERIALIZED VIEW
ngarg> exec dbms_mview.refresh('emp_mv');

PL/SQL procedure successfully completed.

Hmmm, it got refreshed, does it has the latest data -
ngarg> select * from emp_mv;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 NIMISH PRESIDENT 17-NOV-81 5000.15 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850.3 30
7782 CLARK MANAGER 7839 09-JUN-81 2450.45 10
7566 JONES MANAGER 7839 02-APR-81 2975.5 20
7788 SCOTT ANALYST 7566 19-APR-87 3000.55 20
7902 FORD ANALYST 7566 03-DEC-81 3000.6 20
7369 SMITH CLERK 7902 17-DEC-80 800.75 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600.9 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250.05 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500.95 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100.01 20
7900 JAMES CLERK 7698 03-DEC-81 950.99 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Wow, Oracle database is smart enough and allows us to refresh even an INVALID MATERIALIZED VIEW, as long as SQL of MATERIALIZED VIEW is valid and data is valid for the structure created by MATERIALIZED VIEW (EMP_MV table).

Related Posts:
- Tune Complete Refresh of Materialized View by ATOMIC_REFRESH
- Oracle: Fast Refresh Materialized View with Joins
- ORA-23404 refresh group does not exist
- Top 18 features of Oracle 18c Database
- Top 15 features of Oracle 12.2 Database for developers
- Oracle Database 12c New Features for Developers
Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview