ReIM Consolidated documents cost resolution – performance improvement
After external contractor of the company where I’m working and Oracle Support were not able to find solution that would improve performance of the ReIM consolidated documents functionality, it’s time for me to take that challenge. Very quickly I’ve found the problematic SQL. Here I’ll provide only the important part, as the query is over […]
Example of how 10x technically faster code is 1000x slower in production
Recently I’ve solved this interesting issue on Oracle Retail production (code is from RPM – Retail Price Management). After upgrading Oracle Retail I found problematic part of code that is running fine in the old version of the RPM. In new version, developer has correctly changed the following part of code with much faster version […]
High Performance Computing in Retail Industry
I’d like to invite you at HIPEAC (European Network on High Performance and Embedded Architecture and Compilation) conference in Zagreb, April 27-28, 2017. There I’ll have a presentation about how to build IT system and how HPC technologies like parallelism and clustering can help. This is excerpt from the announcement. Josip Pojatina, Solution Architect, mStart, […]
RMAN performance tuning in combination with Tivoli server
Last month I’ve been working on rman backup optimization. One of the largest databases that I manage starts to break time frame given for the full backup. Full backup starts every Saturday at midnight and should finish till Sunday 10AM. Suddenly, for no apparent reason, full backup finish around 2:30 PM. As the database is […]
optimizer_features_enable hint and when to use it
Cost based optimizer is the most important piece of code in Oracle database as CBO is responsible for optimal execution of SQL queries. CBO has been constantly developed, where each release introduces many new features/tricks and improvements. As with any programming code, CBO is not immune on bugs in the code. Good news is that […]
Compound triggers performance and use cases
Let’s assume you have to track changes in stage table (in this case tstage table) and insert all inserted rows in trigger_test table. Once of your logical choices is to create a trigger (there are a couple of other options related to AQ/streams). Before 11g version, you can create before/after row trigger (where later is […]
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 […]