Order of execution plan

Below reference clearly explains the order of execution plan.

http://www.dba-oracle.com/t_order_sequence_sql_execution_explain_plans_steps.htm


Cross verify results with below examples:

Example:
Query:
select /*+ gather_plan_statistics */
e.first_name,d.department_name,j.job_title,j.job_id
from
hr.employees e,
hr.departments d,
hr.jobs j
where
e.job_id=j.job_id
and e.department_id=d.department_id
and j.job_title in ('Purchasing Clerk','Marketing Manager','Administration Assistant')
--and e.first_name in ('Sigal','Alexander','Jennifer','Susan')
;
SELECT *  FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;

Plan:

Id	Operation	Name	Starts	E-Rows	A-Rows	A-Time	Buffers	OMem	1Mem	Used-Mem
0	SELECT STATEMENT		1		7	00:00.0	19
*  1	HASH JOIN		1	17	7	00:00.0	19	880K	880K	629K (0)
2	NESTED LOOPS		1		7	00:00.0	12
3	NESTED LOOPS		1	17	7	00:00.0	9
*  4	TABLE ACCESS FULL	JOBS	1	3	3	00:00.0	7
*  5	INDEX RANGE SCAN	EMP_JOB_IX	3	6	7	00:00.0	2
6	TABLE ACCESS BY INDEX ROWID	EMPLOYEES	7	6	7	00:00.0	3
7	TABLE ACCESS FULL	DEPARTMENTS	1	27	27	00:00.0	7

order of id execution: 4,5,3,6,2,7,1,0

It follows "Left-Right-Center" at every node from leaf to top in tree.

It follows post order traversal. Refer this.
https://www.tutorialspoint.com/python_data_structure/python_tree_traversal_algorithms.htm

Viewing the actual explain plan of Query

step1: add /*+ gather_plan_statistics */ hint in select statement as shown below.

select /*+ gather_plan_statistics */
e.first_name,d.department_name,j.job_title,j.job_id
from
hr.employees e,
hr.departments d,
hr.jobs j
where
e.job_id=j.job_id
and e.department_id=d.department_id
and j.job_title in ('Purchasing Clerk','Marketing Manager','Administration Assistant')
--and e.first_name in ('Sigal','Alexander','Jennifer','Susan')
;

step2: execute
 SELECT *  FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;
A-rows shows actually resulted query where as E-rows shows estimated row count.

Generating Calendar using Connect Prior by

Note: Replace $Start_dt, $End_dt with the actual start,end dates

SELECT

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'Day') DAY_NAME,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM) DAY_DT,

            TO_NUMBER(TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') ) CAL_WEEK_NUM,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') CAL_WEEK_START_DT,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') + 6 CAL_WEEK_END_DT,

            TO_NUMBER(TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'DDD') ) CAL_DAY_OF_YEAR,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM) - TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'Q'

) CAL_DAY_OF_QTR,

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'DD') CAL_DAY_OF_MONTH,

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'D') CAL_DAY_OF_WEEK

        FROM

            DUAL

        CONNECT BY

            ROWNUM <= TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - TO_DATE('$Start_Dt','MM/DD/YYYY HH24:MI:SS')

        ORDER BY

            DAY_DT;

Gather stats of a table sytax

begin

DBMS_STATS.GATHER_TABLE_STATS('owner', 'table_name',
                                       estimate_percent=>50,
                                       block_sample=>TRUE,
                                       degree=>4) ;

end  ;

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 1000 ROWS;

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 50 PERCENT;

SQL to find Monday and Friday of the week of a given date

select next_day (sysdate-7,'FRIDAY') Last_Friday, next_day (sysdate-7, 'MONDAY') Last_Monday from dual;

How to count # of occurrences of a character inside a string?

LEN(ColumnToCheck) - LEN(REPLACE(ColumnToCheck,"M",""))

Creating dblink to oracle database -Syntax

-- Rather than this.

CREATE DATABASE LINK hr_staff CONNECT TO hr_staff_link IDENTIFIED BY Password1 USING 'HRDEV1';

-- Use somethign link this.

CREATE DATABASE LINK hr_staff CONNECT TO hr_staff_link IDENTIFIED BY Password1 USING 'hr_staff_service';

The "tnsnames.ora" entry might look like this.

hr_staff_service=

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL=TCP)(HOST=server1.example.com)(PORT=1521))

    (CONNECT_DATA=(SERVICE_NAME=HRDEV1))

  )

Merge Statement Syntax

MERGE INTO employees e
    USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

Oracle Query to check whether difference in column names of 2 tables

select column_name,DATA_TYPE from (select column_name,DATA_TYPE from all_tab_cols where table_name='FIRST_TABLE_NAME' order by internal_column_id asc)
minus
select column_name,DATA_TYPE from (select column_name,DATA_TYPE from all_tab_cols where table_name='SECOND_TABLE_NAME' order by internal_column_id asc);

Changing position of a column in ORACLE Table

SQL> create table first_table (col1 number, col2 number, col3 number);



Table created.



SQL> desc first_table
 Name                                      Null?    Type

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

 COL1                                               NUMBER

 COL2                                               NUMBER

 COL3                                               NUMBER



SQL> create table sec_table as select col3, col2, col1 from first_table;



Table created.



SQL> drop table first_table;



Table dropped.



SQL> alter table sec_table rename to first_table;



Table altered.



SQL> desc first_table;

 Name                                      Null?    Type

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

 COL3                                               NUMBER

 COL2                                               NUMBER

 COL1                                               NUMBER



SQL> 

Alter session set nls_date_format

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Insert an Image File into Oracle Database

Photographs and pictures and Oracle BLOB data are easy to add to a Oracle table. There are two ways to load BLOBs and CLOBs into the database. The first method uses PL/SQL and the DBMS_LOB package and the BFILE datatype to transfer external LOB files into the database internal LOB structures. The second uses the Oracle Call Interface (OCI) to perform the same function. Here, we will use the first method . For inserting an image, we follow the folowing steps :


Step 1 : First, we  need to create a directory on the database (which is mapped to a directory in the server's filesystem). The user must be granted the create any directory  privilege.
SQL>create directory photo_dir as 'c:\photo_dir' ;
Directory created.

Step 2 : Then we need to create a table which is used by procedure to insert the image in our table . Here we have to use a BLOB to insert the image .
SQL> create table temp_photo
 (
 ID    NUMBER(3)     NOT NULL,
 PHOTO_NAME      VARCHAR2(50),
 PHOTO    BLOB
 );
Table created.

Step 3 : Now let's write the procedure to insert the image in the table above.

SQL> create or replace PROCEDURE load_file (
 p_id number,
 p_photo_name in varchar2) IS
 src_file BFILE;
 dst_file BLOB;
 lgh_file BINARY_INTEGER;
 BEGIN
  src_file := bfilename('PHOTO_DIR', p_photo_name);
  -- insert a NULL record to lock
  INSERT INTO temp_photo
  (id, photo_name, photo)
  VALUES
  (p_id , p_photo_name ,EMPTY_BLOB())
  RETURNING photo INTO dst_file;
  -- lock record
  SELECT photo
  INTO dst_file
  FROM temp_photo
  WHERE id = p_id
  AND photo_name = p_photo_name
  FOR UPDATE;
  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
  -- determine length
  lgh_file := dbms_lob.getlength(src_file);
  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
  -- update the blob field
  UPDATE temp_photo
  SET photo = dst_file
  WHERE id = p_id
  AND photo_name = p_photo_name;
  -- close file
  dbms_lob.fileclose(src_file);
 END load_file;
/


Step 4 :  We can test it from SQL*Plus 
SQL> execute load_file(1,'kartheek.jpg') ;


Note : Remember that the file kartheek.jpg should exist in the server's 'c:\photo_dir' directory .

Selecting ODD or EVEN rows from a table

NOTE: only '<' can be used with rownum.

Query for even number of rows

select a.* from (select mod(rownum,2) k,m.* from mtl_system_items_b m where rownum<5 a.k="0;<br" a="" where="">
Query for odd number of rows

select a.* from (select mod(rownum,2) k,m.* from mtl_system_items_b m where rownum<5 a.k="0;<br" a="" where="">

How to Unlock the Locked Table in ORACLE

Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.

SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that 'EMP' table is locked,

SELECT object_id FROM dba_objects WHERE object_name='EMP';

 OBJECT_ID

----------

   7401242

If there are no locks present for the table 'EMP' this query won’t return any values.

SELECT sid FROM v$lock WHERE id1=7401242

SID

----------

   3434

SELECT sid, serial# from v$session where sid=3434

       SID    SERIAL#

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

      3434      92193

ALTER SYSTEM KILL SESSION '3434,92193' ;

Once the session is killed you will be able to carry out any DDL activities on EMP table. Also you can check in TOAD if there are any active sessions associated to the SID that we killed, to make sure that the session has been killed.

Query to find the Contraints on table

select c.index_name, c.column_name
from
user_indexes i,
user_ind_columns c
where
i.index_name = c.index_name
and i.table_name = c.table_name
and i.table_name = upper('mtl_system_items_b');

Query to Check Concurrent Manager Status

Below mentioned is the query to check concurrent manager status in Oracle APPS.

SELECT DECODE(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','STANDARD','Standard Manager' ) AS "Concurrent Manager's Name",
max_processes  AS "TARGET Processes",
running_processes AS "ACTUAL Processes"
FROM apps.fnd_concurrent_queues
WHERE CONCURRENT_QUEUE_NAME IN ('FNDICM','FNDCRM','STANDARD');

If status of target process and actual process is non-zero then our concurrent manager is up and running.

QUERY TO KNOW THE VERSION OF EBS USED

SELECT * FROM FND_PRODUCT_GROUPS;

DBA TABLE TO KNOW THE SPACE OCCUPIED BY OBJECT

select sum(bytes/1024/1024/1024)||'$'||file_name from dba_data_files group by file_name;
 
  SELECT username,profile,default_tablespace,temporary_tablespace FROM dba_users;

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

 select SEGMENT_NAME, SUM (bytes) / 1024 / 1024 / 1024 AS GB from dba_segments WHERE UPPER(segment_name) LIKE 'MISO_CAT%' AND SEGMENT_TYPE ='TABLE'  GROUP BY SEGMENT_NAME ORDER BY 2 DESC;--SEGMENT_NAME IS OBJECT NAME

BASIC AND USEFUL DBA QUERIES

-- Database Details
SELECT * FROM v$database

-- Instance Details
SELECT * FROM v$instance

-- License Details
SELECT * FROM v$license

-- Version Details
SELECT * FROM v$version

--Release Details
SELECT * FROM apps.fnd_product_groups

-- Patch Details
SELECT * FROM ad_applied_patches
SELECT * FROM ad_bugs


-- Concurrent Manager

SELECT concurrent_queue_name,
       user_concurrent_queue_name,
       description,
       enabled_flag
  FROM apps.fnd_concurrent_queues_vl fcq
 WHERE user_concurrent_queue_name LIKE 'AA%'



-- Partitioning Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') partitioning
FROM (SELECT 1
FROM dba_part_tables
WHERE owner NOT IN ('SYSMAN', 'SH', 'SYS', 'SYSTEM') AND ROWNUM = 1);

-- Spatial Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') spatial
FROM (SELECT 1
FROM all_sdo_geom_metadata
WHERE ROWNUM = 1);

-- RAC Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') rac
FROM (SELECT 1
FROM v$active_instances
WHERE ROWNUM = 1);

-- Unix Product Top Value
SELECT variable_name, value
FROM apps.fnd_env_context
WHERE variable_name = 'AP_TOP'
AND concurrent_process_id =
(SELECT MAX (concurrent_process_id) FROM apps.fnd_env_context);

-- Command to Kill Session for Releasing Lock
ALTER SYSTEM KILL SESSION '(sid, serial#)';