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...)
We bumped a Linux 22.214.171.124 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...)
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...)
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...)
We have several posts on AskTom where it is described how to “carve up” a table into equi-sized chunks in order to (say) perform a task in parallel on that table. Here is an example of one.
Much of this has nowadays been obsoleted by the DBMS_PARALLEL_EXECUTE package, but in either instance, one key point remains: When you are generating rowids, there is no guarantee that the rowid you generate is either valid or (more...)
Although I normally use the job or scheduler facility to run database-centric style processes, most commonly PL/SQL programs, there is nothing to stop you from using the scheduler to gain control over tasks that might normally need to be done outside of the database. And of course, in the world of virtualization, cloud and other such innovations, the ability to initiate and perform tasks from within the database becomes increasingly useful, since often access (more...)
I’ve used Python to make graphs of Oracle database performance information. I put the scripts out on GitHub at https://github.com/bobbydurrett/PythonDBAGraphs. As a result I’m keeping my Python skills a little fresher and learning about git for version control and GitHub as a forum for sharing Open Source. Really, these Python scripts were an experiment. I don’t claim that I have done any great programming or that I will.
But, as I review what I have done (more...)
This year’s Oracle Open World was an exciting experience for AuraPlayer, with many flocking to our booth for live demos and attending Mia’s sessions with clients like Rogers Group Inc. We were sad that many were unable to attend the conference, so we are bringing Oracle Open World to you in our October webinar series!
In the first installment of this series, “Digital Transformation: From Legacy to Enterprise Mobility,” we will demonstrate step-by-step how best (more...)
In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions. As we saw, the partition numbers are preordained based on the low boundary and the interval size.
That also creates an interesting scenario that can catch people out – you might exceed the allowable number of partitions, with an empty table !
This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I thought I would bring it to your attention. The issue occurs when optimizing a query that accesses a single partition via pending statistics.
Things get a little interesting in the data dictionary when it comes to interval partitions. Consider the following example:
SQL> create table t
2 partition by range(x) interval(1)
3 ( partition p0 values less than (1) )
5 select rownum x from dual connect by level <= 5;
SQL> select count(*)
2 from user_tab_partitions
3 where table_name = 'T';
So our table has 6 partitions. Let’s now (more...)
I had worked on creating a Delphix virtual copy of our production PeopleTools 8.53 database and wanted to use ENCRYPT_PASSWORD in Datamover to change a user’s password. But I got this ugly error:
Error: Process aborted. Possibly due to JVM is not available or missing java class or empty password.
What the heck! I have used Datamover to change passwords this way for 20 years and never seen this error. Evidently in PeopleTools 8.53 (more...)
To answer Tim Hall’s call to appreciate OTN, I could have written about my go-to feature, the MATCH_RECOGNIZE clause, or my go-to development tool, Oracle SQL Developer. Instead, I’d like to salute my go-to “Oracle Technology” guy for over 10 years, Tom Kyte. It was 2005. After almost 25 years in IT, I knew something about […]
As part of the #ThanksOTN idea on Twitter, my favourite Oracle feature is the ability to easily view and analyse execution plans for queries. Time and time again I’ve needed to see how a query is running, and Oracle databases make it easy to view the execution plan. You can view it in either a text format […]
I learnt a lot from the entire Oracle Technology Network and I still do. One of the best features of OTN these days, IMHO, is the ability to download prebuilt Virtual Machines. Since I am a developer and not an administrator, I don’t like to be bothered with stuff like how much disk space do I need, how many cores should I use etc. I can just download a Virtual Box image, import it and (more...)
This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out.
Lets set the scene with a simple procedure that commences a transaction, but then always fails
SQL> drop table test$tab purge;
SQL> create table test$tab (val varchar2(1));
SQL> create or replace procedure foo_proc is
3 dbms_output.put_line('in foo_proc');
4 insert into test$tab values ('t');
A project team asked me to look at the performance of an Oracle database application that does a bunch of inserts into a table. But, when I started looking at the AWR data for the insert the data confused me.
The SQL by elapsed time section looked like this:
So, 1514 executions of an insert with 1 second of elapsed time each, almost all of which was CPU. But then I looked at the SQL text: