ORACLE-FORU - ORACLE DBMS_PROFILER

DBMS_PROFILER

The DBMS_PROFILER package was introduced in Oracle 8i to allow developers to profile the run-time behaviour of PL/SQL code, 

Making it easier to identify performance bottlenecks which can then be investigated more closely.

1. The first step is to install the DBMS_PROFILER package (profload.sql) 
2. create the tables to hold the profiler results (proftab.sql). 
3. The profiler tables can be created in your local schema, or in a shared schema, as shown below.

-- Install the DBMS_PROFILER package.

CONNECT sys/password@service AS SYSDBA

@$ORACLE_HOME/rdbms/admin/profload.sql

-- Install tables in a shared schema.

CREATE USER profiler IDENTIFIED BY profiler DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CREATE SESSION TO profiler;

GRANT CREATE TABLE, CREATE SEQUENCE TO profiler;

CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;

CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;

CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;

CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

CONNECT profiler/profiler@service

@$ORACLE_HOME/rdbms/admin/proftab.sql

GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;

Make sure the user that needs to run the profiler has the correct privileges on the DBMS_PROFILER package.

GRANT EXECUTE ON dbms_profiler TO test;

Next we connect to the test user and create a dummy procedure to profile.

CONN test/test@service

CREATE OR REPLACE PROCEDURE do_something (p_times  IN  NUMBER) AS

  l_dummy  NUMBER;

BEGIN

  FOR i IN 1 .. p_times LOOP

    SELECT l_dummy + 1

    INTO   l_dummy

    FROM   dual;

  END LOOP;

END;

/

Next we start the profiler, run our procedure and stop the profiler.

DECLARE

  l_result  BINARY_INTEGER;

BEGIN

  l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);

  do_something(p_times => 100);

  l_result := DBMS_PROFILER.stop_profiler;

END;

/

With the profile complete we can analyze the data to see which bits of the process took the most time, with all times presented in nanoseconds. First we check out which runs we have.

SET LINESIZE 200

SET TRIMOUT ON

COLUMN runid FORMAT 99999

COLUMN run_comment FORMAT A50

SELECT runid,

       run_date,

       run_comment,

       run_total_time

FROM   plsql_profiler_runs

ORDER BY runid;

RUNID RUN_DATE  RUN_COMMENT                        RUN_TOTAL_TIME

----- --------- ---------------------------------- --------------

    1 21-AUG-03 do_something: 21-AUG-2003 14:51:54      131072000

We then use the appropriate RUNID value in the following query.

COLUMN runid FORMAT 99999

COLUMN unit_number FORMAT 99999

COLUMN unit_type FORMAT A20

COLUMN unit_owner FORMAT A20

SELECT u.runid,

       u.unit_number,

       u.unit_type,

       u.unit_owner,

       u.unit_name,

       d.line#,

       d.total_occur,

       d.total_time,

       d.min_time,

       d.max_time

FROM   plsql_profiler_units u

       JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number

WHERE  u.runid = 1

ORDER BY u.unit_number, d.line#;

RUNID UNIT_NU UNIT_TYPE       UNIT_OWNER  UNIT_NAME    LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME

----- ------- --------------- ----------- ------------ ----- ----------- ---------- -------- --------

    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      4           1          0        0        0

    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      5           1          0        0        0

    1       1 ANONYMOUS BLOCK <anonymous> <anonymous>      6           1          0        0        0

    1       2 PROCEDURE       MY_SCHEMA   DO_SOMETHING     4         101          0        0        0

    1       2 PROCEDURE       MY_SCHEMA   DO_SOMETHING     5         100   17408000        0  2048000

5 rows selected.

The results of this query show that line 4 of the DO_SOMETHING procedure ran 101 times but took very little time, while line 5 ran 100 times and took proportionately more time. 

We can check the line numbers of the source using the following query.

SELECT line || ' : ' || text

FROM   all_source

WHERE  owner = 'MY_SCHEMA'

AND    type  = 'PROCEDURE'

AND    name  = 'DO_SOMETHING';

LINE||':'||TEXT

---------------------------------------------------

1 : PROCEDURE do_something (p_times  IN  NUMBER) AS

2 :   l_dummy  NUMBER;

3 : BEGIN

4 :   FOR i IN 1 .. p_times LOOP

5 :     SELECT l_dummy + 1

6 :     INTO   l_dummy

7 :     FROM   dual;

8 :   END LOOP;

9 : END;

As expected, the query took proportionately more time than the procedural loop. Assuming this were a real procedure we could use the DBMS_TRACE or the SQL trace facilities to investigate the problem area further.

Read More

ORACLE-FORU - How to handle Exception in Bulk Collection Method.

SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

We saw how the FORALL syntax allows us to perform bulk DML operations.

 But what happens if one of those individual operations results in an exception? If there is no exception handler, all the work done by the current bulk operation is rolled back. 

If there is an exception handler, the work done prior to the exception is kept, but no more processing is done. 

So instead we should use the SAVE EXCEPTIONS clause to capture the exceptions and allow us to continue past them. 

We can subsequently look at the exceptions by referencing the SQL%BULK_EXCEPTION cursor attribute. 

create the following table.

CREATE TABLE exception_test (

  id  NUMBER(10) NOT NULL

);

The following code creates a collection with 100 rows, but sets the value of rows 50 and 51 to NULL. 

Since the above table does not allow nulls, these rows will result in an exception. 

The SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete. 

In this case, the exception handler just loops through the SQL%BULK_EXCEPTION cursor attribute to see what errors occurred.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();

  l_error_count  NUMBER;

  

  ex_dml_errors EXCEPTION;

  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

BEGIN

  -- Fill the collection.

  FOR i IN 1 .. 100 LOOP

    l_tab.extend;

    l_tab(l_tab.last).id := i;

  END LOOP;

  -- Cause a failure.

  l_tab(50).id := NULL;

  l_tab(51).id := NULL;

  

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.

  BEGIN

    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS

      INSERT INTO exception_test

      VALUES l_tab(i);

  EXCEPTION

    WHEN ex_dml_errors THEN

      l_error_count := SQL%BULK_EXCEPTIONS.count;

      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);

      FOR i IN 1 .. l_error_count LOOP

        DBMS_OUTPUT.put_line('Error: ' || i || 

          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||

          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

      END LOOP;

  END;

END;

/

Number of failures: 2

Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()

Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL>

As expected the errors were trapped. If we query the table we can see that 98 rows were inserted correctly.

SELECT COUNT(*)

FROM   exception_test;

  COUNT(*)

----------

98

1 row selected.

SQL>

Read More

Utility / Math related queries

Utility / Math related queries

Convert number to words
More info: Converting number into words in Oracle


SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;

Output:
one thousand five hundred twenty-six


Find string in package source code
Below query will search for string ‘FOO_SOMETHING’ in all package source. This query comes handy when you want to find a particular procedure or function call from all the source code.

--search a string foo_something in package source code
SELECT *
FROM dba_source
WHERE UPPER (text) LIKE '%FOO_SOMETHING%'
AND owner = 'USER_NAME';


Convert Comma Separated Values into Table
The query can come quite handy when you have comma separated data string that you need to convert into table so that you can use other SQL queries like IN or NOT IN. Here we are converting ‘AA,BB,CC,DD,EE,FF’ string to table containing AA, BB, CC etc. as each row. Once you have this table you can join it with other table to quickly do some useful stuffs.

WITH csv
AS (SELECT 'AA,BB,CC,DD,EE,FF'
AS csvdata
FROM DUAL)
SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;


Find the last record from a table
This ones straight forward. Use this when your table does not have primary key or you cannot be sure if record having max primary key is the latest one.

SELECT *
FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

(OR)

SELECT * FROM employees
MINUS
SELECT *
FROM employees
WHERE ROWNUM < (SELECT COUNT (*) FROM employees);


Row Data Multiplication in Oracle
This query use some tricky math functions to multiply values from each row. Read below article for more details.
More info: Row Data Multiplication In Oracle

WITH tbl
AS (SELECT -2 num FROM DUAL
UNION
SELECT -3 num FROM DUAL
UNION
SELECT -4 num FROM DUAL),
sign_val
AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
FROM tbl
WHERE num < 0)
SELECT EXP (SUM (LN (ABS (num)))) * val
FROM tbl, sign_val
GROUP BY val;


Generating Random Data In Oracle
You might want to generate some random data to quickly insert in table for testing. Below query help you do that. Read this article for more details.
More info: Random Data in Oracle

SELECT LEVEL empl_id,
MOD (ROWNUM, 50000) dept_id,
TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F') gender,
TO_DATE (
ROUND (DBMS_RANDOM.VALUE (1, 28))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1, 12))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
'DD-MM-YYYY')
dob,
DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
FROM DUAL
CONNECT BY LEVEL < 10000;


Random number generator in Oracle
Plain old random number generator in Oracle. This ones generate a random number between 0 and 100. Change the multiplier to number that you want to set limit for.

--generate random number between 0 and 100
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;


Check if table contains any data
This one can be written in multiple ways. You can create count(*) on a table to know number of rows. But this query is more efficient given the fact that we are only interested in knowing if table has any data.

SELECT 1
FROM TABLE_NAME
WHERE ROWNUM = 1;

Read More

Database administration queries

Database administration queries

Database version information
Returns the Oracle database version.

SELECT * FROM v$version;


Database default information
Some system default information.

SELECT username,
profile,
default_tablespace,
temporary_tablespace
FROM dba_users;


Database Character Set information
Display the character set information of database.

SELECT * FROM nls_database_parameters;

Get Oracle version

SELECT VALUE
FROM v$system_parameter
WHERE name = 'compatible';


Store data case sensitive but to index it case insensitive
Now this ones tricky. Sometime you might querying database on some value independent of case. In your query you might do UPPER(..) = UPPER(..) on both sides to make it case insensitive. Now in such cases, you might want to make your index case insensitive so that they don’t occupy more space. Feel free to experiment with this one.

CREATE TABLE tab (col1 VARCHAR2 (10));

CREATE INDEX idx1
ON tab (UPPER (col1));

ANALYZE TABLE a COMPUTE STATISTICS;


Resizing Tablespace without adding datafile
Yet another DDL query to resize table space.

ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;


Checking autoextend on/off for Tablespaces
Query to check if autoextend is on or off for a given tablespace.

SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;

(OR)

SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;


Adding datafile to a tablespace
Query to add datafile in a tablespace.

ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'
SIZE 1000M AUTOEXTEND OFF;


Increasing datafile size
Yet another query to increase the datafile size of a given datafile.

ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;


Find the Actual size of a Database
Gives the actual database size in GB.

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;


Find the size occupied by Data in a Database or Database usage details
Gives the size occupied by data in this database.

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;


Find the size of the SCHEMA/USER
Give the size of user in MBs.

SELECT SUM (bytes / 1024 / 1024) "size"
FROM dba_segments
WHERE owner = '&owner';

Last SQL fired by the User on Database
This query will display last SQL query fired by each user in this database. Notice how this query display last SQL per each session.

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
s.sid || '/' || s.serial# sid,
s.status "Status",
p.spid,
sql_text sqltext
FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
WHERE t.address = s.sql_address
AND p.addr = s.paddr(+)
AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;

Performance related queries
CPU usage of the USER
Displays CPU usage for each User. Useful to understand database load by user.

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
AND ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

Long Query progress in database
Show the progress of long running queries.

SELECT a.sid,
a.serial#,
b.username,
opname OPERATION,
target OBJECT,
TRUNC (elapsed_seconds, 5) "ET (s)",
TO_CHAR (start_time, 'HH24:MI:SS') start_time,
ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid
AND b.username NOT IN ('SYS', 'SYSTEM')
AND totalwork > 0
ORDER BY elapsed_seconds;


Get current session id, process id, client process id?
This is for those who wants to do some voodoo magic using process ids and session ids.


SELECT b.sid,
b.serial#,
a.spid processid,
b.process clientpid
FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

V$SESSION.SID AND V$SESSION.SERIAL# is database process id
V$PROCESS.SPID is shadow process id on this database server
V$SESSION.PROCESS is client PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

Last SQL Fired from particular Schema or Table:
SELECT CREATED, TIMESTAMP, last_ddl_time
FROM all_objects
WHERE OWNER = 'MYSCHEMA'
AND OBJECT_TYPE = 'TABLE'
AND OBJECT_NAME = 'EMPLOYEE_TABLE';


Find Top 10 SQL by reads per execution

SELECT *
FROM ( SELECT ROWNUM,
SUBSTR (a.sql_text, 1, 200) sql_text,
TRUNC (
a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE ROWNUM < 10;


Oracle SQL query over the view that shows actual Oracle connections.

SELECT osuser,
username,
machine,
program
FROM v$session
ORDER BY osuser;

Oracle SQL query that show the opened connections group by the program that opens the connection.

SELECT program application, COUNT (program) Numero_Sesiones
FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones DESC;


Oracle SQL query that shows Oracle users connected and the sessions number for user

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC;


Get number of objects per owner

SELECT owner, COUNT (owner) number_of_objects
FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;

Read More

ORACLE-FORU - SQL%BULK_ROWCOUNT

SQL%BULK_ROWCOUNT

The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribute.

Lets see example you will clearly understand.

create one table.

CREATE TABLE bulk_rowcount_test AS

SELECT *

FROM   all_users;

Below example will clearly understand how it will work through sql%bulk_rowcount.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_array_tab IS TABLE OF VARCHAR2(30);

  l_array t_array_tab := t_array_tab('SCOTT', 'SYS',

                                     'SYSTEM', 'DBSNMP', 'BANANA'); 

BEGIN

  -- Perform bulk delete operation.

  FORALL i IN l_array.first .. l_array.last 

    DELETE FROM bulk_rowcount_test

    WHERE username = l_array(i);

  -- Report affected rows.

  FOR i IN l_array.first .. l_array.last LOOP

    DBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ') ||

      ' Rows affected: ' || SQL%BULK_ROWCOUNT(i));

  END LOOP;

END;

/

Element: SCOTT           Rows affected: 1

Element: SYS             Rows affected: 1

Element: SYSTEM          Rows affected: 1

Element: DBSNMP          Rows affected: 1

Element: BANANA          Rows affected: 0

PL/SQL procedure successfully completed.

SQL>

So we can see that no rows were deleted when we performed a delete for the username "BANANA".

Read More

ORACLE-FORU - Oracle FORALL Function in Bulk Collection.

FORALL in Bulk Collection.

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.

lets try to understand with example.

create one table to test how forall function will work and performance of the function we can see.

CREATE TABLE forall_test (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE forall_test ADD (

  CONSTRAINT forall_test_uk UNIQUE (code));

Below code compares the time taken to insert 10,000 rows using regular FOR..LOOP and a bulk bind.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_tab    t_forall_test_tab := t_forall_test_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size LOOP
    l_tab.extend;

    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time regular inserts.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    INSERT INTO forall_test (id, code, description)
    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' || 
                       (DBMS_UTILITY.get_time - l_start));
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time bulk inserts.  
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO forall_test VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  COMMIT;
END;
/
Normal Inserts: 305
Bulk Inserts  : 14

PL/SQL procedure successfully completed.


SQL>


By seeing output we can able to see how much performance will increase FORALL function.

Below example uses the ROW keyword, when doing a comparison of normal and bulk updates.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_id_tab  t_id_tab          := t_id_tab();
  l_tab     t_forall_test_tab := t_forall_test_tab ();
  l_start   NUMBER;
  l_size    NUMBER            := 10000;
BEGIN
  -- Populate collections.
  FOR i IN 1 .. l_size LOOP
    l_id_tab.extend;
    l_tab.extend;

    l_id_tab(l_id_tab.last)       := i;
    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  -- Time regular updates.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    UPDATE forall_test
    SET    ROW = l_tab(i)
    WHERE  id  = l_tab(i).id;
  END LOOP;
  
  DBMS_OUTPUT.put_line('Normal Updates : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  l_start := DBMS_UTILITY.get_time;

  -- Time bulk updates.
  FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test
    SET    ROW = l_tab(i)
    WHERE  id  = l_id_tab(i);
  
  DBMS_OUTPUT.put_line('Bulk Updates   : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  COMMIT;
END;
/
Normal Updates : 235
Bulk Updates   : 20

PL/SQL procedure successfully completed.


SQL>

we can see the output shows the performance improvements you can expect to see when using bulk binds.

Read More