Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

dbms_random

In a recent ODC thread someone had a piece of SQL that was calling dbms_random.string(‘U’,20) to generate random values for a table of 100,000,000 rows. The thread was about how to handle the ORA-30009 error (not enough memory for operation) that is almost inevitable when you use the “select from dual connect by level <= n” strategy for generating very large numbers of rows, but this example of calling dbms_random.string() so frequently prompted me to point out an important CPU saving , and then publicise through this blog a little known fact (or deduction) about the dbms_random.string() function.

If you generate a random string of length 6 using only upper-case letters there are 308,915,766 different combinations (266); so if you’re after “nearly unique” values for 100 million rows then a six character string is probably good enough – it might give you a small percentage of values which appear in a handful rows but most of the values are likely to be unique or have two rows. If you want to get closer to uniqueness then 7 characters will do it, and 8 will make it almost certain that you will get a unique value in every row.

So if you want “nearly unique” and “random 20 character strings” it’s probably sufficient to generate random strings of 6 to 8 characters and then rpad() them up to 20 characters with spaced – the saving in CPU will be significant; roughly a factor of 3 (which is going to matter when you’re trying to generate 100 million rows. As a little demo I supplied the OP with a script to create a table of just one million random strings – first of 20 random characters, then of 6 random characters with 14 spaces appended. The run time (mostly CPU) dropped from 1 minute 55 seconds to 41 seconds.

Why is there such a difference ? Because to generate a random string of 6 characters Oracle generates a random string of one character six times in a row and concatenates them. The difference between 6 calls and 20 calls per row gives you that factor of around 3. For a quick demo, try running the following anonymous PL/SQL block:

rem
rem     Script:         random_speed.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2010
rem

begin
        dbms_random.seed(0);
        dbms_output.put_line(dbms_random.string('U',6));
        dbms_output.new_line;

        dbms_random.seed(0);
        dbms_output.put_line(dbms_random.string('U',1));
        dbms_output.put_line(dbms_random.string('U',1));
        dbms_output.put_line(dbms_random.string('U',1));
        dbms_output.put_line(dbms_random.string('U',1));
        dbms_output.put_line(dbms_random.string('U',1));
        dbms_output.put_line(dbms_random.string('U',1));
end;
/

Jere are the results I got from instances of 12.1.0.2, 12.2.0.1, and 18.1.0.0 (from LiveSQL):


BVGFJB
B
V
G
F
J
B

I haven’t shown the tests for all the possible dbms_random.string() options but, unsurprisingly, changing the test to use the ‘L’ (lower case alpha) option produces the same effect (and the same 6 letters changed to lower case). The same effect, with different characters, also appeared using the ‘A’ (mixed case alpha), ‘X’ (uppercase alphanumeric) and ‘P’ (all printable characters) options.

I haven’t considered the effect of using a multi-byte character set – maybe Oracle calls its random number generator once per byte rather than once per character. The investigation is left as an exercise to the interested reader.

tl;dr

When generating a very large number of random strings – keep the “operational” part of the string as short as you can and leave the rest to be rpad()‘ed.

Need Help with Oracle Security GDPR Training and Services

I talked here a few days ago about GDPR in general and I also published my slides from my talk GDPR for the Oracle DBA . We have been helping clients secure data in their Oracle databases and training people....[Read More]

Posted by Pete On 09/06/18 At 04:33 PM

Creating complete synthetic test data sets from production data

How easy could it be to generate a complete set of test data
from existing tables? To make sure that data looks like production
but in no way is a copy or scramble of production data. Instead it
is synthetically created, to look like what your production data looks
like. Same types, same data domain, same coherence between values, same data distribution
and also maintaining the relationships (foreign keys) between all the tables
simulating.

Creating complete synthetic test data sets from production data

How easy could it be to generate a complete set of test data
from existing tables? To make sure that data looks like production
but in no way is a copy or scramble of production data. Instead it
is synthetically created, to look like what your production data looks
like. Same types, same data domain, same coherence between values, same data distribution
and also maintaining the relationships (foreign keys) between all the tables
simulating.

Massive Delete

The question of how to delete 25 million rows from a table of one billion came up on the ODC database forum recently. With changes in the numbers of rows involved it’s a question that keeps coming back and I wrote a short series for AllthingsOracle a couple of years ago that discusses the issue. This is note is just a catalogue of links to the articles:

There is an error in part 2 in the closing paragraphs – it says that the number of index entries deleted varies “from just one to 266″, it actually varies from 181 to 266.

 

The AskTOM data model

I popped out a tweet yesterday in Throwback Thursday style showing the date of the first question we took AskTOM – 18 years ago! Many entire computer systems don’t even last that long, and AskTOM hasn’t really needed to change that much in those 18 years. We’ve added a new skin, added the ability to have multiple AskTOM experts on the answer team, and of course, our new-for-2018 Office Hours program, which gives everyone free access to experts inside Oracle Corporation.

John replied to my tweet asking if we could show the ERD.

image

So here it is – I’ve taken out a few things here and there that don’t have any impact on the public facing part of AskTOM. And I’ve included some notes underneath about each table’s purpose in AskTOM.

Relational_1

ATE_ADMINS

The administrators on AskTOM. There are various privilege levels such as being able to see the admin pages, view unanswered questions and actually answer them.

ATE_HOME_MESSAGES

Any messages that we want to appear on the Questions home screen, such as upcoming conferences etc.

ATE_SUPPORTING_FILES

Files that support the answering of questions. We’ll sometimes link in scripts, images etc to better answer a question. They are all stored in here, in the database of course.

ATE_PRESENTATIONS

The presentations and other resources that you can download from the Resources page.

image

ATE_FEEDBACK

Our recently added facility to allow people to give us feedback on the AskTOM application.

image

ATE_SUBMITTED_QUESTIONS

The bread and butter of AskTOM. Your questions are stored here, along with our answer.

ATE_POSTING_STATUS

image

When you submit a question, we record it as “New, Never Read”. Once we start working on it, we will then mark it as “Read, Not Answered” so that another AskTOM administrator does not work on it. We might then have a little back-and-forth with our original submitter if we need more detail (which is a polite way of saying they didn’t read the Question Guidelines, and hence we didn’t get a test case or sample data or nicely formatted code etc etc) Smile. So that is “Need More Info” and “More Info Supplied” statuses.

Once we’ve answered the question, if we think the general community will benefit it will be “Answered Publish” which puts it up on the home page, otherwise its “Answered, Not Published”, which means the original submitter will see it, but no-one else. As you can see from the numbers in the picture (taken today), for the 18,500 questions you can see on AskTOM, there’s another 5,500 we’ve answered that don’t make the front page.

“No Further Action” is what happens when we ask for more information and the submitter decides to ghost us, or if a submitter decides the best way to communicate with us is via a torrent of insults. Either way, we ain’t bothering with that question again Smile

ATE_QUESTION_REVIEWS

The review comments from the community, and our follow-ups for each question that we answer.

image

ATE_REVIEW_STATUSES

We read every single review that comes into AskTOM. They come in with a status of “New”, we’ll “Assign” them to an administrator, and once we’re done with it, we mark it “Complete”. Yup, that’s over 125,000 reviews we’ve taken.

image

ATE_QUESTION_STATUS_HIST

We track our own performance on AskTOM so that we can better serve you all. So every time we change a question status, eg from “New” to “Need More Info” to “Answered” etc, we log the time at which the status change occurred so we can review it later to see how we’re tracking.

ATE_OBSOLETE_QUESTIONS

18 years of taking and answering questions means that some of the answers may no longer be correct or up to date. If a reviewer brings this to our attention, we’ll record it here as an action for an administrator to look at and rectify. If you’re wondering why it is not just a flag on the question, that’s because a single question may become obsolete more than once in as time marches on.

image

ATE_QUESTION_VIEWS

Every time you click on a question in AskTOM, we record that fact. Why? Because that drives the metrics on knowing which questions are most sought after by the community. We get hundreds of thousands of question views every week in AskTOM.

ATE_QUESTIONS_TOP20

This drives our “Popular Questions” page on AskTOM.

image

ATE_QUESTIONS_SOCIAL_MEDIA

Whenever we tweet out about a particular AskTOM question, or pop it on Facebook, we log it here – because we don’t want to be spamming you. Smile

And that’s all there is to it. All driven by SQL, PL/SQL and of course the latest and greatest version of Application Express.

Grants WITH GRANT

The ability to make grants on objects in the database such as tables, views, procedures or others such as SELECT, DELETE, EXECUTE and more is the cornerstone of giving other users or schemas granular access to objects. I say granular....[Read More]

Posted by Pete On 07/06/18 At 06:58 PM

“Call me!” Many many times!

Some readers might recall that classic Blondie track “Call me”.  Of course, some readers might be wishing that I wouldn’t harp on about great songs from the 80’s. But bear with me, there is a (very tenuous) link to this post. If you haven’t heard the song, you can jump to the chorus right here.  Go on, I’ll wait until you get back. Smile

This golden oldie is relevant when it comes to dealing with object types in PL/SQL, and in particular, when you are using them in a SQL-related context.  To set the scene, I’ll start with a trivial example – creating a simple object, utilizing that object in a function, and then using a simple SELECT-FROM-DUAL on that function to check that it works.


SQL> create or replace
  2  type three_values as object
  3   ( val_1 int,
  4     val_2 int,
  5     val_3 int
  6   );
  7  /

Type created.

SQL>
SQL> create or replace
  2  function f return three_values is
  3  begin
  4     return three_values(1,2,3);
  5  end;
  6  /

Function created.

SQL>
SQL> select f from dual;

F(VAL_1, VAL_2, VAL_3)
-----------------------------------------
THREE_VALUES(1, 2, 3)

So far so good. But as is often the case, the requirement is not to get the object as an entity in it’s own right, but to get at the scalar values within the object. Hence the wrapping of that query as an inline view to an outer one to get the individual columns:


SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

But let’s explore that a little more carefully. Observation of the SQL text might lead us to think that

  • we ran the inner query,
  • saved the result we’ve already seen into a temporary result of type THREE_VALUES,
  • then extracted the elements via the outer SELECT.

But now I’ll make a small amendment to the function as follows. I’ll introduce a package variable so we can do some tracking of executions


SQL> create or replace package pkg as
  2    x int := 0;
  3  end;
  4  /

Package created.

SQL>
SQL> create or replace
  2  function f return three_values is
  3  begin
  4     pkg.x:= pkg.x+1;
  5     return three_values(1,2,3);
  6  end;
  7  /

Function created.

As you would expect, a simple SELECT-FROM-DUAL results in the function being executed once.


SQL> select f from dual;

F(VAL_1, VAL_2, VAL_3)
-----------------------------------------------------
THREE_VALUES(1, 2, 3)

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

As before, I’ll now wrap that query in another SELECT to extract the column elements. I’ll reset my package variable to start the count again from zero.


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=3

PL/SQL procedure successfully completed.

Notice the difference. The function was called three times. If that function was doing some “heavy lifting” then those additional executions might be a cause for concern. The number of calls to a function referenced from SQL has always been indeterminate, so this is nothing new, but it still might catch you by surprise. References throughout the SQL to those function scalar values can bump the count up even more:


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x
  4  where x.c.val_1 = 1 and x.c.val_2 = 2;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=5

but conversely, you cannot just simply assume that every reference will result in an additional execution. For example, adding an ORDER BY clause containing references does not increment the count.


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x
  4  where x.c.val_1 = 1 and x.c.val_1+10 = 11
  5  order by x.c.val_1, x.c.val_2, x.c.val_3;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=5

PL/SQL procedure successfully completed.

A 10053 trace on any of these queries reveals the reason why we can expect to see multiple executions. When I parse my initial query


select x.c.val_1, x.c.val_2, x.c.val_3
from
(select f c from dual ) x

then scrolling through the 10053 trace, I’ll end up with this:


Final query after transformations:******* UNPARSED QUERY IS *******
SELECT SYS_OP_ATG("SCOTT"."F"(),1,2,2) "C.VAL_1",SYS_OP_ATG("SCOTT"."F"(),2,3,2) "C.VAL_2",SYS_OP_ATG("SCOTT"."F"(),3,4,2) "C.VAL_3" 
FROM "SYS"."DUAL" "DUAL"

You can see that the query has been transformed to have explicit calls of the function for each column we requested.

Can the multiple calls be avoided? Yes. There’s a couple of simple options to explore here. First of these, is that you can use a scalar subquery to take advantage of some query level caching that the database will do to avoid repeated executions of query sub-components.  Here is the previous examples repeated but with a scalar subselect to access the function.


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select ( select f from dual ) c
  4   from dual ) x;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL>
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select ( select f from dual ) c
  4   from dual ) x
  5  where x.c.val_1 = 1 and x.c.val_2 = 2;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL>
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

Alternately, since we are returning an object as a table, then we can reflect this by adding an additional nested table object type to handle this within the function:


SQL> create or replace
  2  type three_val_list as
  3   table of three_values
  4  /

Type created.

SQL>
SQL> create or replace
  2  function f return three_val_list is
  3  begin
  4     pkg.x:= pkg.x+1;
  5     return three_val_list(three_values(1,2,3));
  6  end;
  7  /

Function created.

SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select * from f();

     VAL_1      VAL_2      VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

Before you get carried away and start screaming “Performance Bug!” from the rooftops, the moral of the story here is probably to first ask yourself: Is there a valid reason for referencing my OBJECT data types via SQL? Very often I see the instantiation of any variable (complex or scalar) being done with a SELECT-FROM-DUAL. Don’t do it – it is almost never needed.

But, if you have truly embraced the Object-Relational features and are genuinely gathering object types from SQL statements, then consider some of the workarounds above to avoid excessive function calls.

Quick install of prometheus, node_exporter and grafana

This blogpost is a follow up of this blogpost, with the exception that the install method in this blogpost is way easier, it uses an Ansible playbook to do most of the installation.

1. Install git and ansible via EPEL:

# yum -y localinstall https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# yum -y install ansible git

2. Clone my ‘prometheus_node_exp_grafana_install’ repository:

# git clone https://gitlab.com/FritsHoogland/prometheus_node_exp_grafana_install.git

3. Run the prometheus.yml playbook to install prometheus, node_exporter and grafana:

# cd prometheus_node_exp_grafana_install
# ansible-playbook prometheus.yml

Now all you need to do is setup grafana (at port 3000) to talk to prometheus to fetch data.
If you are interested, the memory usage dashboard can be imported, dashboard id 2747.

In fact, it seems node_exporter has renamed the operating system statistics, so the old Linux memory dashboard didn’t work anymore. I updated it, and uploaded a new version of the memory usage dashboard.

Time is Gold

So many have asked me when I’m starting at Microsoft and the official date is Monday, June 11th now.  Many also wonder what my upper limits are on how much I can handle, well folks, it looks like we’ve reached them!

I was supposed to start this last Monday, but as my last day approached, it became all the more clear that there was a need to change my ending and start date.  Individual deadlines had pushed out, requirements changed and I was left feeling a bit overwhelmed because as most know, I don’t do anything the easy way-  major change?  I’ll make three or four at once!  I’m fearless…and sometimes a bit crazy in my endeavors.

As anyone knows, “The best laid plans of mice and [wo]men often go astray” and as such, this is for me as well.  I needed time to get one kid into her apartment, another moved to his Father’s before college and then we need to sell/donate 95% of our belongings.  Why?  Because as many of you know, Tim and I are selling our house and moving into a 42Ft. 5th wheel.  Oh, that brand new 5th wheel?  Yes, I wanted to remodel it beforehand to make sure it would support our lifestyle, including remote working.  This included chained generators, proper work areas and multiple WiFi options.  These don’t come easy and along with that, I decided to change jobs.

Limit reached! </p />
</p></div>

    	  	<div class=