How to Create A Table With Specific Frequency Distribution

We often need to create tables for testing or testcases, the usual method is to use CTAS (Create Table As Select) on a select from the dual table and the “CONNECT BY ROWNUM < <number_of_rows_we_need>” clause. Looking at the example below we see that we are creating a table with a single column and one hundred tuples, it has the same uniform distribution as the values we will insert correspond to the ROWNUM. We generated one hundred tuples with the values 1,2,3, … 100, and if we took one random row do they all have the same probability to be elected.

 

 -- CREATE TABLE WITH LINEAL-DISTRIBUTION ---

CREATE TABLE T
AS SELECT ROWNUM            
FROM DUAL
CONNECT BY ROWNUM <= 100;

 
Creating tables with specific distributions is not very common, but they are a very powerful tool if we wish implement testing, especially to play with the histograms of the columns. An example of creating a table with a Gaussian distribution or Gaussian bell curve called.

 

 -- CREATE TABLE WITH GAUSS-DISTRIBUCION  ----

CREATE TABLE T
AS SELECT CASE WHEN (ROWNUM BETWEEN 1  AND 10) THEN 1
               WHEN (ROWNUM BETWEEN 11 AND 30) THEN 2
               WHEN (ROWNUM BETWEEN 31 AND 70) THEN 3
               WHEN (ROWNUM BETWEEN 71 AND 90) THEN 4
               WHEN (ROWNUM BETWEEN 91 AND 100) THEN 5
          END X
FROM DUAL
CONNECT BY ROWNUM <= 100;

 

In this case we have created a table with one hundred tuples, and has the values 1,2,3,4 and 5 however, if we took a random row we would have the following probabilities.

  • The probability of being a value of 1 is 10%
  • The probability of being a value of 2 is 20%
  • The probability of being a value of 3 is 40%
  • The probability of being a value of 4 is 20%
  • The probability of being a value of 5 is 10%

 
HTH – Antonio NAVARRO

 

Advertisements

Sql Developer 4.0.3 Released

September 15 is the last release version of SQL Developer. This powerful tool, which is the replacement for sqlplus, not stop of grow in functionality, presenting us some new and some improved such as below. I have to admit that I am still reluctant to use it on an ongoing basis. Although it has solved some problems. Is more continuous jamming, more than anything java, and resource intensive. Here everyone should try it and draw their own conclusions.

 

Remarks new features

  • TimesTen and In-Memory Database Cache (new in Oracle from 12.1.0.2).
  • Big Data Appliance.
  • Data Miner (required install Data Miner Repository in Database Server).

You can download from the following link;

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

 

HTH – Antonio NAVARRO

Dispatcher In Status Outbound

Today i have received an alarm indicating some abnormality in a dispatcher, examining in more detail I note that in the v$circuit there is a dispatcher, shared server with associated, but has no user and is in continuous state of outbound.

 

By documentation;

OUTBOUND Status: The circuit above dealer an outbound connection to another database.

 

It seems that the dispatcher are hang, and that never leaves this state, in this case the solution has been to eliminate the process from the perating system. Of course, to kill the dispatcher all connections to be removed by, so that we find a window where the impact on the system as small as possible, or we can try to guess, go by select on the v$circuit view for a moment where we see little or no activity to kill the process, but this is an danger option because there can be no activity at time t and at time t+1 may have the most critical process across the enterprise.

 

HTH – Antonio NAVARRO

 

ORA-01722 Error

Today I have received this error;

 

CLOUD*ANTO> sta G:\SQL_FRA\QUERY_0x0022
AND Z.CD_ELEMENT_PROVIDER IN
*
ERROR en línea 176:
ORA-01722: invalid number

 

This error is not new, at least for me, I have sometimes occurred, the problem is why we are making an implicit conversion data, the engine, in this case Oracle (also happens in SYBASE ASE / IQ, SQL Server, MySQL …) tries to convert values ​​for one condition at the same type.

In this particular case is a NUMBER and VARCHAR in an IN clause, the solution is to make an explicit conversion like the one shown below;

 

 WHERE  Z.CD_ELEMENT_PROVIDER  IN
(SELECT TO_CHAR (TAB_ELEMENTS.CD_ELEMENT_PROVIDER)
                  FROM TAB_SATELLITE_RECEIVER, ...

 

Summary

Must always make an explicit conversion so that these problems do not occur. Mainly development is people who should do it as a “best practices” and in general for all database engines.

 

HTH – Antonio NAVARRO

Cross-cabling As An Interconnect (RAC)

In RAC (Real Application Cluster) interconnect is the internal channel that union all nodes in the cluster. This network within the cluster must be specialized. This focuses on the management of the same and it is based for cache fusion. The idea was not new although Oracle recreated it since version 8, if memory serves me, Oracle had create before RAC what was called Oracle Parallel Server (versions 7 and earlier). On OpenVMS VAX and using the interconnect was delegated to hardware, I used to use a Digital product (actully HP) known as “Memory Channel”, which is actually what is now called a RDMA (Read Direct Memory Access), fused memories of the nodes the cluster.

 

The theme of today’s post is to indicate that many people, as a cheap solution, use a simple cable to implement the interconnect, this basically goes for a cluster of two nodes. However, this solution have a lot of problems, please noted;

 

  • Not supported by Oracle. If there is a problem with it can become a serious problem.
  • The cross-cabling is unstable.
  • Should not be installed in Production.
  • If you lost a node, the other will take longer to detect the problem, waiting for a timeout expires.
  • Can be mounted only for two nodes, but would involve installing a soft on nodes that act as a router, but it is complex.
  • Some NIC cards do not work properly with cross-cabling.

 

Summary;

You should use a switch.

 

HTH – Antonio NAVARRO

How To Reduce Consumption Of Physical Resources?

Today I have been requiered to lower the minimum resources for a database, which will be set in read mode. The issue is that share  machine with other databases, and  do not want to remove resources, mainly physical (memory, CPU, bandwidth). This is what I thought so far.

 

  • Download MEMORY_TARGET from actual to 0,5 Gb, according documentation can be lowered to 152 Mb (11g), but it depends on the machine as having rounded to the defined granule.
  • Lower the job_queue_processes to zero.
  • Put a BASIC statistics_level.
  • ALTER SYSTEM SET control_managemente_pack_access = NONE disables the license TUNING, DIAGNOSTIC and seems don’t run the AWR.
  • Lower the Processes and sessions parameters to 70-100 each.
  • Suspend backups (full + archives), do a final one (better off), retention must be changed to “keep” (forever or until time) to withstand a year or so deemed.
  • Open in read only (in this state consider removing archivelog).
  • Fix by clusterware set on a node, so you do not have to promote.
  • This set XDB, consider whether it’s worth stopping dispatcher and shared server.
  • The UNDO has a lot of gigas, reduce it.
  • Something generating audit and the audit_trail = “DB”, if set to READ ONLY should not be able to write, but would have to disable it.

 
Does anyone have any idea more? All are welcome.

 

HTH – Antonio NAVARRO

ORA-01480 Error

Today I received the error ora 1480, as described below, it occurs when we run a batch process type;

 

berlin:/sw/oracle12c=> oerr ora 1480
01480, 00000, “trailing null missing from STR bind value”
// *Cause: A bind variable of type 5 (null-terminated string) does
// not contain the terminating null in its buffer.
// *Action: Terminate the string with a null character

 

 
The process in question is a pro*c program. The process is new, or rather is a new version of one that already existed, actually it has included new features, but some of them have introduced a bug which is what makes raised this error.

Specifically in pro*c there is a known bug in which to assign values ​​to strings of type char must include the NULL , has nothing to do with NULL used in databases, represented by lowercase omega. Specifying the same with \n or , the only thing it does is say that the chain ends, the process gets so has the array of characters, if they are 10 by definition I would take those 10, although the data is HELLO, the remaining positions from 5 to 9 (in c start counting at zero) gets it has the following memory locations normally be trash or other data streams.

An example of how you can specify is as show below;

 

 #include <stdio.h>
#include <string.h>

int main()
{
   char my_bind_1 [40];
   char my_bind_2 [100];
  
   /*** Method I ***/
   /* Set my bind variables to null, in this case we use \0 */
   memset(my_bind_2, '\0', sizeof(my_bind_2));
   memset(my_bind_1, '\0', sizeof(my_bind_1));
   
   strcpy(my_bind_1, "hello");
   strcpy(my_bind_2, my_bind_1);


   /*** Method II ***/
   /* In this case we use \n */
   strcpy (my_bind_1, "hello\n");
   
   return(0);
}

 

HTH – Antonio NAVARRO