Search

Top 60 Oracle Blogs

Recent comments

October 2018

Column Groups

Sometimes a good thing becomes at bad thing when you hit some sort of special case – today’s post is an example of this that came up on the Oracle-L listserver a couple of years ago with a question about what the optimizer was doing. I’ll set the scene by creating some data to reproduce the problem:

Oracle wait event ‘TCP Socket (KGAS)’

I was asked some time ago what the Oracle database event ‘TCP socket (KGAS)’ means. This blogpost is a deep dive into what this event times in Oracle database 12.1.0.2.180717.

This event is not normally seen, only when TCP connections are initiated from the database using packages like UTL_TCP, UTL_SMTP and the one used in this article, UTL_HTTP.

A very basic explanation is this event times the time that a database foreground session spends on TCP connection management and communicating over TCP, excluding client and database link (sqlnet) networking. If you trace the system calls, you see that mostly that is working with a (network) socket. Part of the code in the oracle database that is managing that, sits in the kernel code layer kgas, kernel generic (of which I am quite sure, and then my guess:) asynchronous services, which explains the naming of the event.

18c XE–running locally

There’s already a few blog posts out there, showing people (easily) getting their 18c XE instance downloaded, installed and running, so I won’t rehash that here.

For the majority of people, I’d imagine they’ll have their XE instance running on a small box and connecting to it from “afar”, namely, their own PC or an application server or similar.

But for those of you, especially in these early stages of release, who like me, want to just jump right into our VM’s and the like, and connect to the XE instance directly, there’s perhaps one thing we omitted as part of the automated install that might catch you out.

The default tnsnames.ora will look something like:

18c XE is live!

Just a quick post because this is perhaps what I think is one of the biggest game changers for the Oracle Database.

18c Express Edition (18x XE) is now available for general use. For those people with experience with 11g XE, this might not seem to be a big deal, but there is one crucial difference.

Unlike 11g XE, the new version has virtually no restrictions on the functionality offered by the database. And yes, we are talking Enterprise Edition features and options here.

So if you want to explore:

  • In-memory
  • Multi-tenant
  • Partitioning
  • Text

etc etc etc, the list goes on, then these will all be there in 18c XE for you.

And the product installs with just a couple of commands.

add_colored_sql

The following request appeared recently on the Oracle-L mailing list:

I have one scenario related to capturing of sql statement in history table..  Like dba_hist_sqltext capture the queries that ran for 10 sec or more..  How do I get the sql stmt which took less time say in  millisecond..  Any idea pleae share.

OpenWorld Wednesday – step right up!

If you’ve read my two previous posts on the OpenWorld schedule, you’re probably expecting a huge long list of sessions to pique your interest.

But no! There’s only ONE you need on your list Smile

The Fast Lane to Database Success [TIP4094]
Connor McDonald, Developer Advocate for SQL, Oracle
Wednesday, Oct 24, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3009

Little things worth knowing: OSWatcher Analyser Dashboard

I have written a few articles about Tracefile Analyzer (TFA) in the recent past. As you may recall from these posts, a more comprehensive TFA version than the one provided with the installation media is available from My Oracle Support (MOS for short). As part of this version, you get a lot of useful, additional tools, including OSWatcher. I love OSWatcher, simply because it gives me insights that would be very hard to get with sar for example. SAR tends to be the least common denominator on most (proper) operating systems and it’s better than nothing, but please read on and let me explain why I like OSWatcher so much.

Feel free to head back to my earlier posts if you like to get some more details about “stock TFA” and “MOS TFA”. These are terms I coined by the way, you won’t find them in a MOS search.

Problem Solving

Here’s a little question that popped up on the Oracle-L list server a few days ago:

I am facing this issue running this command in 11.2.0.4.0 (also in 12c R2 I got the same error)

SQL> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL;
SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL
ORA-01873: a precisão precedente do intervalo é pequena demais

 

How do you go about finding out what’s going on ? In my case the first thing is to check the translation the error message (two options):

Compressed LOB–my table got bigger?

We had an interesting question on AskTOM the other day about LOB compression. Someone was very concerned that after applying compression to the LOBS in the column of their table, the table actually got larger! Cue much confusion and panic, but after a little exploration, the explanation was pretty simple.

By default, when you create a LOB column in a table, the default storage definition is ENABLE STORAGE IN ROW. As per the documentation:

If you enable storage in row, then the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information