If you have a sorted collection of elements, how would you find index of specific value?
"Binary search" is likely to be your answer.
Algorithms theory is teaching us what binary search is most optimal algorithm for this task with log(N) complexity.
Well, hash table can do better, if you need to find key by exact match. In many cases, though, you have reasons to have your collection sorted, not hashed.
On my job, I'm (more...)
February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected. The person who reported the issue to me provided a sample SQL statement that […]
“You can swim all day in the Sea of Knowledge and not get wet.”
― Norton Juster, The Phantom Tollbooth
In previous posts compression options have been discussed, and now it’s time to see how Oracle performs basic compression. It isn’t really compression, it’e de-duplication, but it does result in space savings for data that won’t be modified after it’s ‘compressed’. Let’s look at how Oracle saves space with your data.
Oracle de-duplicates the (more...)
Bind peeking is a nice feature in Oracle to have many optimized plans for an SQL for various bind values. DBAs believe that bind peeking happens during a soft parse which will identify an alternate plan. Why do I say that?
Hard Parse: Parsing first time, nothing exists to bind peek
Soft Parse : SQL cursor is existing and executing not the first time. Under the soft parse, bind peeking (more...)
This blog hasn’t seen any updates since over a year now and I do apologize for that. However, the reason for that is simple. Last year I’ve decided to take one a new project. But before I was going to tell everybody about it I wanted to make sure that I also really got the time to do so. In the meantime I got the confidence that the project will succeed and that (more...)
A new version 4.21 of the XPLAN_ASH utility is available for download. I publish this version because it will be used in the recent video tutorials explaining the Active Session History functionality of the script.As usual the latest version can be downloaded here.
This is mainly a maintenance release that fixes some incompatibilities of the 4.2 version with less recent versions (10.2 and 18.104.22.168).
As an extra however, (more...)
This is a write-up of an issue I recently posted to the OTN discussion forum (https://community.oracle.com/message/12798407). I thought the associated test case was useful in demonstrating the issue, so is captured here for future reference. There were some useful replies to the OTN post, confirming my suspicions.
The test was performed using Oracle Enterprise Edition 22.214.171.124.0 on Linux.
A parallel delete blocks insert into dependent (more...)
With Oracle “merging” Oracle GoldenGate into Oracle Streams (or vise-versa), capturing statitics on the intergrated extract (capture) or integrated replicat (happy) will be needed. In order to do this, the Streams Performance Advisor (UTL_SPADV) can be used. Before using the Stream Performance Advisor, it needs to be configured under the Streams Administrator, i.e. Oracle GoldenGate user. In my test lab, I use a user named GGATE for all my Oracle GoldenGate work.
Yesterday, I have seen huge waits “enq SQ – contention’” – in every snapshot there were thousands of waits. But the fix was so simple! Here is the root cause of the issue –
When you select from a sequence, the NEXTVAL generated from a the seq$ table if it is not cached. If it is cached, it will be available in a memory structure and no need to generate the value (more...)
I recently investigated a performance problem on an Oracle 11.2 OLTP trading system and although we still don’t fully understand the issue (and which versions of Oracle it effects), I thought I would share what we found (and how we found it). We had a hardware failure on the database server, within 30 seconds the database had automatically been restarted on an idle identical member of the cluster and the application continued on the (more...)
Happy New Year!
Oracle supplied various tools to trace the SQL and identify execution plans for the SQLs. 10046 Event, AUTOTRACE, DBMS.XPLAN etc are some of the most used tracing methods in DBAs daily life. Sometimes, we need to be very careful while using these tools specially using bind variables. Following are some test cases where wrong plans reported by the above tracing tools.
Most easiest method to get (more...)
A new version 4.2 of the XPLAN_ASH utility is available for download.As usual the latest version can be downloaded here.
There were no too significant changes in this release, mainly some new sections related to I/O figures were added.
One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings (more...)
This is the second part of a series of blogpost on Oracle database PGA usage. See the first part here. The first part described SGA and PGA usage, their distinction (SGA being static, PGA being variable), the problem (no limitation for PGA allocations outside of sort, hash and bitmap memory), a resolution for Oracle 12 (PGA_AGGREGATE_LIMIT), and some specifics about that (it doesn’t look like a very hard limit).
But this leaves out Oracle version (more...)
First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]
If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views
We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.
You can create a snapshot (materialized view) using something as simple as
create materialized view apx_application_pages
APPEND hint in oracle is very useful, there are many benefits
Fragmented data loading – data will clustered above the HWM so that similar data will be together. Otherwise Oracle will place the records in blocks using PCT_FREE rule
- No undo will be generated – in large direct path insert will free up large amount of data blocks
- Less CBC latches (because of no UNDO blocks), less CPU usage
- Readers need not undo the records, (more...)