We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value. At first glance, TRUNC seems fine.
SQL> select trunc(localtimestamp,'MM') to_the_month from dual;
1 row selected.
SQL> select trunc(localtimestamp,'YYYY') to_the_year from dual;
1 row selected.
SQL> select trunc(localtimestamp,'DD') to_the_day from dual;
1 row selected.
But the moment you try apply a TRUNC down to (more...)
When you need to monitor web services, you can use EM beacons. A beacon is a target that allows the EM Agent to remotely monitor services. You can add beacons to any EM agent using “Add Non-Host Targets by Specifying Target Monitoring Properties” option in the Add Targets Manually page. After adding a beacon, you can configure it to fetch a web page and test the status of the service according to the result of (more...)
We bumped a Linux 220.127.116.11 database up to a 12 gigabyte SGA and the login time went up to about 2.5 seconds. Then a Linux admin configured 12 gigabytes of HugePages to fit the SGA and login time went down to .13 seconds. Here is how I tested the login time. E.sql just has the exit command in it so this logs in as SYSDBA and immediately exits:
$ time sqlplus / (more...)
As a “born again UX/UI” enthusiast I was quite interested when some of my Google services started telling me about the new UI they were introducing. As part of the “do you want to see more” links they pushed me across to Google’s Material design pages.
I had a bit of mixed reaction to a lot of it. Some of the stuff was straight from the ministry of the bleedin’ obvious, while other stuff was more (more...)
If you are using Data Guard and standby databases, you probably need to do stuff based on the database role. For example, have some services start on the primary database, whichever that is. You might want to update some tables upon failover/switchover and more. There are two mechanisms for that in Oracle databases: For services, … Continue reading Did you know #15 – Role Based Stuff
I recently tweeted about a comment I’d picked up at the Trivadis performance days regarding tablescans and performance.
“If you can write your SQL in conjunctive normal form it can help the optimizer to offload more predicates”
Inevitably someone asked me if I had an example to demonstrate this – I didn’t, and still don’t really, but here’s an interesting demo based on an example from the Oracle In-Memory blog showing how the optimizer will (more...)
A long time ago (in a galaxy far away) I wrote a series of blog posts about directly extracting useful sets of data from the AWR snapshots in the
tables for subsequent analysis and graphing using things like Excel. This post is to summarise where I got to on this, and provide links back to the main posts for future reference.
A guiding principle of my approach was that I wanted to avoid the (more...)
There was Twitter discussion about using context variables, accessed via SYS_CONTEXT within queries. It’s an easy means of passing parameters into a view. The question that got asked was – does a sys_context variable act exactly like a bind variable. Let’s take a look at a simple demo to explore that.
We’ll create a table with 1 row with a value of ‘a’, and 100,000 rows with a value of ‘b’. We’ll index that column, (more...)
When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology. But there are not many “perfect worlds” out there in the IT landscape So sometimes we have to “make do” with the time and budget constraints that are placed upon us. To (more...)
I created a new graph in my PythonDBAGraphs to show how a plan change affected execution time. The legend in the upper left is plan hash value numbers. Normally I run the equivalent as a sqlplus script and just look for plans with higher execution times. I used it today for the SQL statement with SQL_ID c6m8w0rxsa92v. It has been running slow since 10/11/2016.
Since I just split up my Python graphs into multiple smaller scripts (more...)
I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256
This was strange because ipcs -a showed all shared memory segments (more...)
VirtualBox 5.1.8 has burst through the chest of it’s host organism.
Downloads and changelog are in the usual places. It’s a maintenance release, so there are a bunch of bug fixes in there.
I’ve installed it on macOS (Sierra), Windows 7 and Oracle Linux 6 with no problems.SO far no dramas.
was first posted on October 19, 2016 at 2:19 pm.
©2012 "The (more...)
I want you to imagine that you are about to run a race. You have your trainers on, your pre-race warm up is complete and you are at the start line. You look to your right… and see the guy next to you, the one with the bright orange trainers, is hopping up and down on one leg. He does have two legs – the other one is held up in the air – he’s just choosing to (more...)
The Critical Patch Update for October 2016 was released on October 18th, 2016. Oracle strongly recommends applying the patches as soon as possible.
Visit the URL below for more updates
Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem. That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling back the transaction. The metaphor commonly used was the “someone locks a row in a table and then goes out for lunch”, with them being totally oblivious to (more...)