Parallel execution in Oracle seems to be a topic of myths, mysteries and misunderstandings. In this post I hope to clear up at least one of them by diving into a discussion about parallel DML. Actually, I want to show you two things that can go wrong with it.
My demo setup and case are pretty simple, I just create two copies of dba_objects:
SQL> create table a as select * from dba_objects; Table created. (more...)
This blog entry shows you how to reset the
system password for an Oracle Database. It uses a Linux image running Oracle Database 11g Express Edition. It assumes the
student user is the sudoer user.
After you sign on to the
student user account, you open a Terminal session and you should see the following:
oracle user account should be configured to prevent a login. So, you should use the
In the previous weeks, we started a series of blog posts that show you how we use “web scale” patterns to achieve scalability and flexibility in our back office software. The previous patterns discussed were Event Sourcing and CQRS. This week we will dive into mixed SQL – NoSQL. Showing you how this doesn’t just solve a technical problem, they help (more...)
The problem being this error:
ORA-01489: result of string concatenation is too long
It relates to when the result set is over 4000 characters, eg:
with data as (select rpad('x',400, 'x') str from dual connect by level <=10)Ten rows of 400 characters equals 4000, add the concatenation operator and you get (more...)
select listagg(str,', ') within group (order by null)
This blog post looks at how to format the output or the returned returns from an Oracle R Enterprise (ORE), user defined R function, that is run using a SELECT statement in SQL.
Sometimes this can be a bit of a challenge to work out, but it can be relatively easy once you have figured out how to do it. The following examples works through some scenarios of different results sets from a user defined (more...)
Working on a data warehouse system can be quite challenging, as I mentioned in the post from yesterday. One of the things we need to take care of is the amount of parallel processes that are in used at all times. Yesterday I wrote about how to locate downgraded sessions. Today we will look at another aspect – who “steals” parallel processes and what can we do to solve it.
One of the biggest thieves (more...)
I was working with the data warehouse team at a customer site and at some point we realized that some parallel executions are not getting enough resources (downgraded).
Not getting enough parallel processes in such a complex environment is really bad. That means that since everybody is hogging the CPU, some sessions will not be able to complete inside the night ETL time frame. If that happens – some ETLs will go on into the (more...)
In my previous blog post I introduced the new Explicit Semantic Analysis (ESA) algorithm and gave an example of how you can build an ESA model and use it. Check out this link for that blog post.
In this blog post I will show you how you can manually create an ESA model. The reason that I'm showing you this way is that the workflow (in ODMr and it's scheduler) may not be for everyone. (more...)
It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.
For this example, you create the following
table_struct object type and a
A new Oracle Data Mining algorithm in the Oracle 12.2c Database is called Explicit Semantic Analysis.
[The following examples are built using Oracle Data Miner 4.2 (SQL Developer 4.2) and the Oracle 12.2 Database cloud service (extreme edition) ]The Explicit Semantic Analysis algorithm is an unsupervised algorithm used for feature extraction. ESA does not discover latent features but instead uses explicit features based on an existing knowledge base. There (more...)
A Recipe for Summoning the RBO Monster (even in Oracle 12c): On Delete Cascade, Function-Based Index and Missing Table Statistics
The last version of Oracle in which CHOOSE was officially supported as an OPTIMIZER_MODE parameter value was 9.2.
This is what the documentation of Oracle 9.2 says about it:
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal (more...)
When inserting data into the database, it is occasionally forgotten (especially by English-speakers) that we need to take steps to ensure we are inserting data correctly and without unexpected character translation.
For example, in SQL*Plus we need to ensure we set the NLS_LANG environment variable to the correct setting for our database before we initiate SQL*Plus.
Here’s a quick example showing what can go wrong:
[oracle@ORA122 ~]$ echo $NLS_LANG [oracle@ORA122 ~]$ sqlplus neil/neil SQL*Plus: Release (more...)
In a previous blog post I talked about how you can rename and comment your Oracle Data Mining models. This is to allow you to easily to see and understand the intended use of the data mining model.
Another feature available to you is to audit the usage of the the data mining models. As your data mining environment grows to many 10s or more typically 100s of models, you will need to have some (more...)
Suppose that every time we add records into the T1 table we have to do some additional stuff.
One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be a negative impact on performance).
I prefer writing a procedure that inserts the records into T1 and performs this additional (more...)
As your company evolves with their data mining projects, the number of models produced and in use in production will increase dramatically.
Care needs to be taken when it comes to managing these. This includes using meaningful names, adding descriptions of what the model is about or for, and being able to track their usage, etc.
I will look at tracking the usage of the models in another blog post, but the following gives examples (more...)