Find Oracle Database session from OS process

Execute the following query and find out the Database session

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and  b.username is not null
order by a.spid asc;  2    3    4    5

SERVER                   CLIENT                   USERNAME                       STATUS
------------------------ ------------------------ ------------------------------ --------
14674                    2868:6052                SYS                            INACTIVE
14679                    2868:4792                SYS                            INACTIVE
14754                    14753                    SYS                            ACTIVE

SQL>

Execute the following command and find out the OS process mapping with Database session

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL

oracle   14674  0.0  1.0 1682420 48532 ?       Ss   11:13   0:00 oracleorcl (LOCAL=NO)
oracle   14679  0.0  0.4 1679844 21852 ?       Ss   11:13   0:00 oracleorcl (LOCAL=NO)
oracle   14754  0.0  0.4 1679844 23068 ?       Ss   11:20   0:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@saidrasel ~]$

Now create three new connection in Database and execute the query

SQL> select a.spid server, b.process client, b.username,b.status
from v$process a , v$session b
where (a.addr = b.paddr)
and  b.username is not null
order by a.spid asc  2    3    4    5  ;

SERVER                   CLIENT                   USERNAME                       STATUS
------------------------ ------------------------ ------------------------------ --------
14674                    2868:6052                SYS                            INACTIVE
14679                    2868:4792                SYS                            INACTIVE
14754                    14753                    SYS                            ACTIVE
14785                    2868:4792                SAID                         INACTIVE
14787                    2868:4792                SAID                         INACTIVE
14790                    3944:3764                RASEL                  INACTIVE

6 rows selected.

SQL>

Now find the these Database session from OS process also

[oracle@saidrasel ~]$ ps -auxww | grep $ORACLE_SID | grep LOCAL
oracle   14674  0.0  1.0 1682420 48540 ?       Ss   11:13   0:00 oraclestlbas (LOCAL=NO)
oracle   14679  0.0  0.4 1679844 21852 ?       Ss   11:13   0:00 oraclestlbas (LOCAL=NO)
oracle   14754  0.0  0.4 1679844 23096 ?       Ss   11:20   0:00 oraclestlbas (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   14785  0.1  0.8 1682420 40408 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
oracle   14787  0.1  0.8 1680868 42848 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
oracle   14790  0.0  0.4 1679844 22528 ?       Ss   11:23   0:00 oraclestlbas (LOCAL=NO)
[oracle@saidrasel ~]$

Create a query to display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings

Create a query to display the total number of employees and, of that total, the number of
employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings


select count(DECODE (to_char(hire_date,'YYYY'), '2005',  'test'))Yr2005,
count(DECODE (to_char(hire_date,'YYYY'), '2006', 'test'))Yr2006,
count(DECODE (to_char(hire_date,'YYYY'), '2007', 'test'))Yr2007,
count(DECODE (to_char(hire_date,'YYYY'), '2008', 'test'))Yr2008
from employees
where to_char(hire_date,'YYYY') in ('2005','2006','2007','2008')

Basic Difference between CASE and DECODE Function in SQL (Oracle)

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.


Difference between DECODE and CASE:

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Differences between them are listed below:

1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
3. CASE expects datatype consistency, DECODE does not.
4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
5. CASE executes faster in the optimizer than does DECODE.
6. CASE is a statement while DECODE is a fucntion.



----Example of CASE----

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "REVISED_SALARY"
FROM   employees;

----Example of DECODE----

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
              salary)
       REVISED_SALARY
FROM   employees;

Ref: http://theprofessionalspoint.blogspot.com/2012/05/decode-function-vs-case-statement-in.html

Find position of current day in a year (Oracle SQL)

---Find the day against any number

select to_date('365','ddd') find_date from dual;

SQL> select to_date('365','ddd') find_date from dual;

FIND_DATE
---------
31-DEC-14

SQL> select to_date('366','ddd') find_date from dual;
select to_date('366','ddd') find_date from dual
               *
ERROR at line 1:
ORA-01848: day of year must be between 1 and 365 (366 for leap year)


Note: ORA-01848: day of year must be between 1 and 365 (366 for leap year)

---Find position of current day in a year

select to_char(sysdate,'ddd') postion from dual;

POSITION
---
168

Find Top 100 SQL Statement based on CPU utilization

-----Find Top 100 SQL ID in Oracle----

select * from (select s.sql_id, RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL)) DESC) cpu_rank,
RANK() OVER (ORDER BY (max(s.ELAPSED_TIME_TOTAL)) DESC) elapsed_rank
from
   dba_hist_sqlstat s,
   dba_hist_snapshot sn
where
   sn.begin_interval_time between to_date('05-may-2014 0001','dd-mon-yyyy hh24mi')
and
   to_date('12-may-2014 0600','dd-mon-yyyy hh24mi')
and
   sn.snap_id=s.snap_id
group by
   s.sql_id
   ) where cpu_rank <=100 and elapsed_rank<=100;
   





  
-----Find Top 100 SQL text in Oracle----

select sql_id,sql_text from dba_hist_sqltext where sql_id in ( select sql_id from (select s.sql_id, RANK() OVER (ORDER BY (max(s.CPU_TIME_TOTAL)) DESC) cpu_rank,
RANK() OVER (ORDER BY (max(s.ELAPSED_TIME_TOTAL)) DESC) elapsed_rank
from
   dba_hist_sqlstat s,
   dba_hist_snapshot sn
where
   sn.begin_interval_time between to_date('05-may-2014 0001','dd-mon-yyyy hh24mi')
and
   to_date('12-may-2014 0600','dd-mon-yyyy hh24mi')
and
   sn.snap_id=s.snap_id
group by
   s.sql_id
   ) where cpu_rank <=100 and elapsed_rank<=100);