How To Force Oracle To Use Full Table Scan

Usually we need to execute a statement  as fast as possible and with minimal resource cost. Our first thought as a solution is the use of indexes, but not always is the solution, there are times when we want to access by FTS, of course you can force the full by using hints.

Testcase

Create a table and an index

FTS_1

 

See the default execution plan

FTS_2

 

Now we force the full, we must say that in this case the object_id is number.

FTS_3

 

Add zero to object_id cancel the use of index. It should be noted also that this is a very common mistake when writing sql sentences, since it avoids the use of indexes when we want to use them.

 

HTH – Antonio NAVARRO

 

 

Advertisements

Enable SQL Trace on Logon

This is an easy way to trace all sql executing by a session from started a connection to the database. You can put more logical and filters such as by user, by time window and so on. It is especially useful when you have to  trace connections that begin to execute code immediately after opening the connection.

CREATE OR REPLACE TRIGGER SYSTEM.trace_login_trigger
AFTER LOGON
ON DATABASE
BEGIN
  DBMS_SESSION.session_trace_enable (waits  => TRUE,binds  => FALSE);
END;
Please, remeber it is a trigger created in system schema, be careful in production enviroments.
HTH – Antonio NAVARRO

Maximizing Data Loading Speeds

You are loading a large amount of data into a table and want to insert rows in a faster way, you can get this in a easy way using features of Oracle, of course, you issue more or less commit statements too.

  • Using table’s logging attribute, set to nolloging, it reduce the generation redo for direct path (this feature no apply to regular DML). Please be carefull about backup implications.
  • Using a direct path feature, such as the following;
  1. hint /*+ append */ on queries that use a subquery like source data. In 11.1 there is a BUG (8595132) that makes it works with the VALUES clause but isn’t correct.
  2. hint /*+ append_values */ on queries that use the VALUES clause.
  3. Using CTAS, CREATE TABLE <TAB1> AS SELECT.

 

HTH – Antonio NAVARRO

 

 

Getting Process Information on Unix

Sometimes it is interesting what a process doing at operating system level. This allows us to investigate whether there some kind of wait or latency, such as lack of cpu. On Unix you can see in several ways, which I propose here is through system calls and using C. This code applies to any type of process, regardless belonging to Oracle, Sybase, SAP, Siebel, etc.

This testcase is made on Solaris 10. I have to say that this information can be obtained in the case of Oracle using the view V$SYSSTAT (I think it is available from the 8i onwards).

This is the source code (ANSI C);

#include <stdio.h>
#include <sys/types.h>
#include <sys/signal.h>
#include <sys/syscall.h>
#include <sys/procfs.h>
#include <sys/param.h>
#include <fcntl.h>
#include <strings.h>
/*** prototyped functions  ***/
int lee_informacion (char *);
void salida (void);
void cabecera (void);
/*** Version  ***/
void salida (void)
{
  printf (“\nVersion 2, Antonio NAVARRO,@2001.\n”);
}
/*** Get information  ***/
int lee_informacion (char * fichero)
{
  int fd; /* descriptor de fichero */
  char camino [100]; /* camino al fichero */
  prusage_t pusage; /* structura del sistema */
  strcpy (camino, “/proc/”);
  strcat (camino, fichero);
  if ((fd = open (camino, O_RDONLY)) == NULL)
  {
    printf (“>>> Error, openning the process.\n”);   /*** Error condition ***/
    salida ();
    exit(-1);
  }
  if (ioctl (fd, PIOCUSAGE, & pusage) < 0)
  {
    printf (“>>> Error, performing ioctl.\n”);       /*** Error condition ***/
    salida ();
    exit (-1);
  }
  printf (“——————————————————-\n”);
  printf (“Timestamp : %d\n”, pusage.pr_tstamp);
  printf (“Creation timestamp : %d\n”, pusage.pr_create);
  printf (“Total time (elapsed) : %d\n”, pusage.pr_rtime);
  printf (“CPU user time: %d\n”, pusage.pr_utime);
  printf (“CPU system time : %d\n”, pusage.pr_stime);
  printf (“Wait-cpu (latency) time : %d\n”, pusage.pr_wtime);
  printf (“Minor page faults : %d\n”, pusage.pr_minf);
  printf (“Major page faults : %d\n”, pusage.pr_majf);
  printf (“Swaps : %d\n”, pusage.pr_nswap);
  printf (“Input blocks : %d\n”, pusage.pr_inblk);
  printf (“Output blocks : %d\n”, pusage.pr_oublk);
  printf (“Messages sent : %d\n”, pusage.pr_msnd);
  printf (“Messages received : %d\n”, pusage.pr_mrcv);
  printf (“Voluntary context switches : %d\n”, pusage.pr_vctx);
  printf (“Involuntary context switches : %d\n”, pusage.pr_ictx);
  printf (“Chars read and written : %d\n”, pusage.pr_ioch);
  printf (“——————————————————-\n”);
  return (0);
}
int main (int argc, char * argv [])
{
  if (argc != 2)
  {
    printf (“Usage: %s <PID> \n”, argv [0]);
    salida ();
    exit (-1);
  }
  lee_informacion (argv [1]);
  salida ();
  exit (0);
}

Here you can see a example;

FOTO1_IOT

Look for a process running;

FOTO2_IOT

Retry the command again;

FOTO3_IOT

HTH – Antonio NAVARRO

Ghost Process on Solaris

 

Running a ps command to see all pmon processes appears a “ora_pmon_DWH” that is not on this machine after searching his ORACLE_HOME (in this box, each database has its own software) does not appear.Is it a ghost?. After investigating the Sysadmin confirms to me that this machine uses Solaris zones.

proceso_fantasma

I should not see other machines’ processes, reading the man for ps command you can see

 

    -z zonelist

Lists only processes  in  the  specified

zones.  Zones can be specified either by

name or ID. This option is  only  useful

when executed in the global zone.

 

From the context we can conclude that by default ps shows all when we are in the “global zone” as it seems to be our case. If we connect to the machine where is the DWH instance and executing the same ps command;

proceso_fantasma_2

This confirms that we are in a local zone now and only see the pmon of this zone.

Antonio NAVARRO

 

 

Which operating system am I running?

This is an easy way to find the operating system where runs a script, of course, there are other ways more smart, but I created this function on Oracle 7.3, about 1999, then it was not so easy. Note that ASM does not fit. We use the first datafile created (System tablespace) to find the OS type. Works on WINDOWS, UNIX / Linux and OpenVMS. We need access to DBA_DATA_FILES.

This is the code;

CREATE OR REPLACE
FUNCTION GET_OPERATING_SYSTEM RETURN VARCHAR IS
  I                PLS_INTEGER;
  LongChain        PLS_INTEGER;
  FileName         VARCHAR2(513);
  Character        CHAR;
BEGIN
   SELECT FILE_NAME
   INTO FileName
   FROM DBA_DATA_FILES
   WHERE FILE_ID = 1;
   LongChain := LENGTH (FileName);
   I := 1;
   WHILE (I <= LongChain)  LOOP
     Character := SUBSTR (FileName, i, 1);
  IF Character = ‘\’ THEN
     RETURN (‘WINDOWS’);
  END IF;
  IF Character = ‘/’ THEN
     RETURN (‘UNIX/Linux’);
  END IF;
  IF Character = ‘[‘ THEN
     RETURN (‘OpenVMS’);
  END IF;
  I := I + 1;
   END LOOP;
END;
/

 

Eg.;

QUE_OS_ESTOY_2

 

Antonio NAVARRO