SQL> create or replace 2 function p2p_distance( 3 p_latitude1 number, 4 p_longitude1 number, 5 p_latitude2 number, 6 p_longitude2 number) return number deterministic is 7 earth_radius number := 6371; 8 pi_approx number := 3.1415197/180; 9 lat_delta number := (p_latitude2-p_latitude1)*pi_approx; 10 lon_delta number := (p_longitude2-p_longitude1)*pi_approx; 11 arc number := sin(lat_delta/2) * sin(lat_delta/2) + 12 (more...)
Looks DO matter! Today’s users are fed up with the current look and feel of their Oracle Forms / EBS System, many of which were developed last century. But we know how hard it can be to take the first step towards modernization. As Forms administrators / DBA’s your management many not want to believe there is any life left in these applications and won’t move forward. He probably thinks its wiser to move on. But (more...)
If you are on any version of the database past 10.2.0.4, then savvy DBA’s may have noticed the following message popping up occasionally in their trace files
Warning: log write time 540ms, size 444KB
In itself, that is quite a nice little addition – an informational message letting you know that perhaps your log writer performance is worth closer investigation. MOS Note 601316.1 talks a little more about this message.
In response to a recent lamentation from Richard Foote about the degree of ignorance regarding the clustering_factor of indexes I commented on the similar level of understanding of a specific hint syntax, namely use_nl(a b) pointing out that this does not mean “do a nested loop from a to b”. My comment was underscored by a fairly prompt response asking what the hint did mean.
Surprisingly, although I’ve explained it many times over the last (more...)
Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer.
Create a tablespace to hold your history, and create a flashback archive using that space allocation.
SQL> create tablespace SPACE_FOR_ARCHIVE 2 datafile 'C:\ORACLE\DB11\ARCH_SPACE.DBF' 3 size 100M; Tablespace (more...)
From time to time someone comes up with the question about whether or not the order of tables in the from clause of a SQL statement should make a difference to execution plans and performance. Broadly speaking the answer is no, although there are a couple of boundary cases were a difference can appear unexpectedly.
When considering join permutations the optimizer has a few algorithms for picking an initial join order and then deciding how (more...)
I was reading an article today about how 10,000+ Mongo installations that are/were openly accessible on the internet have now been captured by ransomware, with nearly 100,000 other instances potentially vulnerable to the same issue.
Now, since I’m an Oracle blogger, you may be inclined to think the post is going to jump on the “bash MongoDB” bandwagon, but it’s not. I am going to bash something…but it’s not MongoDB
I’m going to (more...)
For over 16 years, AskTom has been one of the most valuable resources available to developers and database administrators working with the Oracle Database. With over 20,000 questions tackled and answered, along with over 120,000 follow up’s to additional queries, it remains an outstanding knowledgebase of Oracle assistance.
And today, AskTom just got a whole lot better!
We’re excited to announce a new member of AskTom team…database evangelist Maria Colgan. Many of you (more...)
A while ago I published an article about the ODA X6-2S and X6-2M, the new entry level machines for small business. And since then Oracle introduced the ODA X6-2L and the successor of de ODA X5-2: ODA X6-2 HA. Quite a line of ODA’s. But when to choose what ODA?
This post is a sequeal to the former article and intended to outline the two ‘new’ (quite a while already by now) machines and the (more...)
The next Arizona Oracle User Group meeting is January 26th in Scottsdale. Signup on this meetup link.
Copied from invitation:
When: January 26, 2017 (Thursday)
12:30 pm – 4:00 pm
Where: Republic Services
1st Floor Training Rooms
14400 N 87th St (AZ101 & Raintree)
12:30 – 1:00 Registration and Pizza
Room 1 Biju Thomas – OneNeck IT Solutions
(Oracle ACE Director)
“Oracle Database 12c New (more...)
So Jmeter seems super cool.
I’ve only used it a little bit but it does seem a bit touchy about somethings (like spaces in input fields) and the errors are often less than obvious and I’m not finding that much out there on google for the errors.
Today I ran into the error
Variable Name must not be null in JDBC Request
and Googling it didn’t turn up anything.
I’m pretty sure I ran (more...)
Before I left for vacation I noticed this blog post about setting up a network on VirtualBox: “CREATING A TEST LAB USING VIRTUALBOX / NAT NETWORKING”. Now that I am back in the office I thought I would try to apply some of the things that I learned from the blog post to my existing VirtualBox network. I’m pretty happy with the results so I thought I would document my experience here.
My situation differed slightly from (more...)
This blog post is just a start at documenting some of my experiences with jmeter. As far as load testing tools go, jmeter looks the most promising to me. It has an active community, supports many different databases and looks quite flexible as far as architecting different work loads goes.
The flexibility of jmeter also makes it hard to use. One can use jmeter for many other things besides databases so the initial set up (more...)
Well…when I say “liked”, what I mean is “the stuff you all clicked on a lot” last year. Whether you liked it or not will remain one of those great mysteries
The top 6 posts from 2016 were:
Nice to see a 12c post in there – 2017 is the year I think most people will (more...)
Dan Tow, in his book SQL Tuning, lays out a simple method of tuning SQL queries. The method is
- Draw a diagram of each table in the query with Children above Parents
- Draw join lines between each join (many-to-many, one-to-many)
- Mark each table with a predicate filter and calculate the amount of table filtered out
Then to find a great optimal optimization path candidate
- Start at the table with the strongest predicate filter (the (more...)
Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year .
Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table
SQL> create table t ( x date, y int ) 2 partition by range ( x ) 3 interval ( numtoyminterval(1,'MONTH')) (more...)
Just a little video montage of the fun and learning from UKOUG. A great conference every year.
I was fortunate enough to receive a Best Speaker award (from a 12c features talk given at the 2015 conference). I gave three talks, one of which was the Database Keynote for 2016 – I felt very privileged for the invitation to do so. The slides for the talks can be found on the AskTom site (more...)