Migrating ASM diskgroups to another storage without downtime
Task to migrate ASM diskgroups to another storage is not too complicate if you can afford some downtime period. If you can, then the basic steps are: 1. Shut down all Oracle Db instances that are using ASM instance which you are going to migrate into the new storage. 2. Shut down Grid infrastructure/Clusterware (that […]
Performance of various partition types and how to choose optimal partitioning strategy
One of the largest table in Oracle Retail is the TRAN_DATA_HISTORY table which holds stock ledger financial transaction data history. Due to business reporting reason, customer decided to disable salprg Pro*C batch which deletes TRAN_DATA_HISTORY records that are older than the user defined number of retention days defined in SYSTEM_OPTIONS table in the column tran_data_retained_days_no. […]
Curse of PARALLEL hint
Last week I’ve been faced with issue that is slowing down production system for more than 30 minutes. During that time, users were unable to work, Cluster services were failing and system was unresponsive. After completing some initial investigation, I’ve found the problematic SQL: SELECT /*+ PARALLEL*/ TRAN_NO, REF_NO1, REF_NO25 FROM SA_TRAN_HEAD WHERE STATUS <> […]
How to stabilize complex execution plan which cannot be stabilized by SQL Plan Baseline or SQL Profile
Recently I’ve been called to help with production issue that stopped all POS (Point of Sales) for 2 hours, producing lost for the affected company. I’ve found relatively quickly problematic query, which looks at first very simple. This is the problematic SQL code: SELECT * FROM (SELECT DOCUMENT_DATE, DOCUMENT_TYPE, AMT_TOTAL, DOCUMENT_NUM, FISCALISED, REF_DOCUMENT_NUM, ORDER_TSF_NO, STATUS_LOOKUP, […]
RPM – unable to open promotion component performance issue
Recently I’ve got a new interesting issue, which clearly shows all the traps and different kind of knowledge required to solve it. The tile of the issue is “Unable to open promotion component”. In description of the issue I’ve found the following: Users are unable to enter promotion component when it has more than a […]
How to index only rows of interest
This is a classical example of what you can achieve by combining several existing features and implemented in a new way, not described within Oracle docs. Here I’m using the fact that Oracle won’t add entry into the index structure if indexed column value is null, in combination with the function-based indexes functionality. I’ve created […]
PL/SQL vs Java for Data intensive tasks
This is the second part of my previous article, where I’ve compared PL/SQL with Java on CPU intensive tasks. I’ll use simple task of inserting values into the table with LOB columns, calculating the length of LOB, trim the LOB and re-calculate the length again. Although not realistic, test case is good enough to show […]
PL/SQL vs Java vs C vs Python for CPU intensive tasks – architectural decision
Main goal of this article is to show performance comparison mainly between PL/SQL and Java (running inside and outside of the Oracle Db), with included C and Python code just to be able to compare results. This may hopefully help you to decide where you are going to put Java CPU intensive code when you’ll […]
Case versus good old Join
SQL language gain a lot with introduction of the “Case” statement. Many SQL programmers are using it, as it is more logical way to accomplish some task without even thinking that SQL language is much older then Case statement, and there is always another (more efficient) way to get the same result. In this post […]
Tips when performing Oracle Retail upgrade (as well as all kind of upgrades based on Oracle technology)
Having a good backup strategy before performing all kind of upgrades are essential when things are going wrong and you have to revert to a previous stage. This is not only important when upgrading infrastructure software like Oracle Database, but for all upgrades, especially with application software upgrades. I’ve been involved in several Oracle Retail […]