V$SQL , V$SQLTEXT,V$SQLAREA, V$SQL_PLAN, V$SESSION_LONGOPS

V$SQL , 
V$SQLAREA, 
V$SQLTEXT,

V$SQLTEXT_WITH_NEWLINES 

V$SQL_PLAN,
V$SESSION_LONGOPS

===============================
V$SQL 
===============================
V$SQL lists statistics on shared SQL area.
One row for each child cursor per SQL string.  
Statistics displayed in V$SQL are normally updated at the end of query execution. 
For long running queries , statistics are updated every 5 seconds. 
V$SQL Reference

===============================
V$SQLAREA
===============================
V$SQLAREA lists statistics on shared SQL area. 
One row for all child cursors, per SQL string. 
It provides statistics on SQL statements that are in memory.
V$SQLAREA Reference

V$SQL and V$SQLAREA main fields:

sql_id       - Id of the parent cursor in the library cache

child_number - Only for V$SQL: Number of this child cursor

version_count- Only for V$SQLAREA: Number of child cursors

hash_value   - Hash value of the parent statement in the library cache

address      - Address of the handle to the parent for this cursor

sql_fulltext - CLOB - full sql text

sql_text     - VARCHAR2(1000) - first 1000 characters.

to get sql_fulltext


V$SQLAREA main fields
executions   - Total number of executions
disk_reads   - Sum of physical disk reads
buffer_gets  - Sum of DB block gets (memory+physical)
cpu_time     - CPU time in microseconds
parse_calls  - Sum of all parse calls. (number of times SQL was re-parsed)
first_load_time - Timestamp of parent cursor creation

===============================
V$SQLTEXT
===============================
V$SQLTEXT holds text of SQL statements from shared SQL cursors in the SGA.
In V$SQLTEXT newlines and other control characters are replaced with whitespaces.



V$SQLTEXT Reference

V$SQLTEXT fields:
address      - Used with hash_value to uniquely identify a cached cursor
hash_value   - Used with address to uniquely identify a cached cursor
sql_id       - SQL identifier of a cached cursor
command_type - Code for the type of SQL statement (SELECT, INSERT, and so on)
piece        - Number used to order the pieces of SQL text
sql_text     - One piece of the SQL text is only VARCHAR2(64)


===============================
V$SQLTEXT_WITH_NEWLINES
===============================
V$SQLTEXT is same as V$SQLTEXT, only newlines and other control characters are NOT replaced with whitespaces.

===============================
V$SQL_PLAN
===============================
V$SQL_PLAN holds the execution plan information per each child cursor.
V$SQL_PLAN Reference

V$SQL_PLAN fields
address      - Address of the handle to the parent for this cursor
hash_value   - Hash value of the parent cursor.
sql_id       - SQL identifier of the parent cursor.
plan_hash_value - Unique identifier for SQL plan.
child_number - Number of the child cursor.

To get Explain Plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1qqtru155tyz8',2));

Join to V$SQLAREA
with address and hash_value

Join to V$SQL
with address, hash_value, and child_number.

===============================
V$SESSION_LONGOPS
===============================
This table lists long running sessions, with their sql_address, sql_hash_value, sql_id, sql_plan_hash_value, and the time consuming step (full_scan, fast full scan, etc.)

SELECT SQLAREA.sql_text,
       LONG.sid,
       LONG.serial#,
       LONG.time_remaining time_remaining_sec,
       LONG.sql_plan_operation,
       LONG.sql_plan_options
WHERE LONG.sql_id = SQLAREA.sql_id
AND LONG.start_time > TRUNC(SYSDATE)
AND SQLAREA.sql_text LIKE 'SELECT kuku%'

===============================
Common fields
===============================
sql_id       - VARCHAR2(13)
               Unique identifier, per SQL TEXT.('1qqtru155tyz8')
address      - RAW(4 | 8)
hash_value   - NUMBER
               address + hash_value Uniquely identify cursor.
child_number - NUMBER
               Number of child cursor(0,1,2,3,4...)

===============================
What is Child Cursor?
===============================
Child Cursors are simply cursors that reference the same exact sql_text - but are different in some fashion.
The first child cursor is numbered 0 (the parent), then 1 (first child), then 2 and so on. 
In what way do cursors different? - Well, this varies. 
For example:
A. Two users have table EMPLOYEES - and both run SELECT * FROM EMPLOYEES;
sql_id - would be same, It is same sql text.
child_cursor would be different.
B. For some reason - same SQLs have different execution plan. 

===============================
What is unique SQL identifier?
===============================
address + hash_value + child_number

===============================
How to connect V$SQLAREA with V$SESSION?
===============================
hash_value and address fields uniquely identify the SQL cursor.
They are used to connect to V$SESSION

===============================
Top SQLs from V$SQL and V$SQLAREA
===============================

Get top SQLs with the V$SESSION info.

SELECT * 
FROM(
SELECT SESSIONS.sid,
       SESSIONS.username,
       SESSIONS.osuser,
       SESSIONS.machine,       
       SESSIONS.module,
       SQLAREA.executions,
       SQLAREA.disk_reads,
       SQLAREA.buffer_gets,
       ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) AS memory_gets_pct,
       ROUND(SQLAREA.cpu_time/1000/1000) AS cpu_time_sec,
       ROUND(SQLAREA.user_io_wait_time/1000/1000) AS io_time_sec,             
       SQLAREA.parse_calls,
       SQLAREA.first_load_time,
       SQLAREA.sql_text
  FROM 
       V$SQL SQLAREA,
--       V$SQLAREA SQLAREA, 
       V$SESSION SESSIONS
 WHERE SESSIONS.sql_hash_value = SQLAREA.hash_value
   AND SESSIONS.sql_address    = SQLAREA.address
   AND SESSIONS.USERNAME IS NOT NULL
   AND SQLAREA.executions > 0
   ORDER BY 
--   SQLAREA.disk_reads DESC
--   ROUND(SQLAREA.cpu_time/1000) DESC
--   ROUND(SQLAREA.user_io_wait_time/1000) DESC
--   SQLAREA.executions DESC  
     SQLAREA.parse_calls DESC
--   ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) ASC
)WHERE ROWNUM < 21;


===============================
Get SQL Text from V$SQLTEXT
===============================
SELECT SQLTEXT.sql_text, 
       SQLTEXT.piece ,
       SQLTEXT.address,  
       SS.sid, 
       SS.username,  
       SS.schemaname, 
       SS.osuser, 
       SS.process, 
       SS.machine, 
       SS.terminal, 
       SS.program, 
       SS.type, 
       SS.module, 
       SS.logon_time, 
       SS.event, 
       SS.service_name, 
       SS.seconds_in_wait
  FROM V$SESSION SS, 
       V$SQLTEXT SQLTEXT
 WHERE SS.sql_address = SQLTEXT.address(+)
   AND SS.service_name = 'SYS$USERS'
ORDER BY SQLTEXT.address, SQLTEXT.piece

===============================
Oracle memory structures.
===============================

V$SQL, V$SQLAREA, V$SQLTEXT - All query Shared SQL area.
V$SQL_PLAN - Query Library Cache.

===============================
Reference
===============================
Ask Tom: What is the diference between V$SQL* views
Ask Tom: On Seeing Double in V$SQL

Linux Reference

Reference for everything in Programming!
www.tutorialspoint.com

Linux
http://www.tutorialspoint.com/unix/index.htm
All commands reference

ASH Tables by Example I - Why did TEMPORARY Tablespace grew in size?

======================
General
======================
Customer complained that database size suddenly grew by 10Gb
Checking datafiles, it was found that temporary tablespace size is 15Gb!!

-rw-r----- 1 oracle dba  12G Apr  6 07:06 ora_temporary_01.dbf

TEMPORARY tablespaceis used for heavy sorts operations.

ora_temporary_01.dbf  indeed grew from 2600Mb to 12000Mb.

Maybe one heavy SQL run by a user, with sort option...?

======================

Investigation
======================
First - Get historical data for tablespace size growth. 

SELECT V$TABLESPACE.name  
         AS TABLESPACE_NAME,
       (HIST_USAGE.tablespace_size*DBA_TABLESPACES.block_size)/1024/1024 
         AS TABLESPACE_SIZE_MB,
       (HIST_USAGE.tablespace_usedsize*DBA_TABLESPACES.block_size)/1024/1024 
         AS USED_SIZE_MB,
       HIST_USAGE.rtime
FROM  DBA_HIST_TBSPC_SPACE_USAGE HIST_USAGE, 
      V$TABLESPACE,
      DBA_TABLESPACES
WHERE HIST_USAGE.tablespace_id = V$TABLESPACE.ts#
  AND DBA_TABLESPACES.tablespace_name = V$TABLESPACE.name
  AND HIST_USAGE.tablespace_usedsize > 0
  AND V$TABLESPACE.name = 'TEMPORARY'
ORDER BY HIST_USAGE.snap_id DESC;

TABLESPACE_NAME   TABLESPACE_SIZE_MB USED_SIZE_MB RTIME                    
----------------- ------------------ ------------ --------------------
TEMPORARY                      12000            1 04/07/2015 17:00:05      
TEMPORARY                      12000           15 04/07/2015 03:00:41      
TEMPORARY                      12000           15 04/06/2015 03:00:39      
TEMPORARY                      12000            1 04/05/2015 23:00:59      
TEMPORARY                      12000            1 04/05/2015 22:00:57      
TEMPORARY                      12000            1 04/05/2015 21:00:55      
TEMPORARY                      12000           15 04/05/2015 03:00:19      
TEMPORARY                      12000            4 04/04/2015 07:00:40      
TEMPORARY                      12000           15 04/04/2015 03:00:23      
TEMPORARY                       2600           15 04/02/2015 03:00:18      
TEMPORARY                       2600           15 04/01/2015 03:00:14      
TEMPORARY                       2600           15 03/31/2015 03:00:39      

12 rows selected.

Check out the time when the change took place

Find Top SQLs that use 'ORDER BY' at that time period.

SELECT TOP_SESSIONS.sql_id,

       TOP_SESSIONS.sql_plan_hash_value, 

       HIST_SQLTEXT.sql_text

FROM(

SELECT SESS_HISTORY.sql_id,

       SESS_HISTORY.sql_plan_hash_value,  

       SUM(10) ash_secs

  FROM DBA_HIST_SNAPSHOT HIST_SNAPSHOT,

       DBA_HIST_ACTIVE_SESS_HISTORY SESS_HISTORY

WHERE 1=1

  AND SESS_HISTORY.sample_time BETWEEN (SYSDATE-5) AND (SYSDATE-3)

  and SESS_HISTORY.snap_id = HIST_SNAPSHOT.snap_id

  AND SESS_HISTORY.dbid = HIST_SNAPSHOT.dbid

  AND SESS_HISTORY.instance_number = HIST_SNAPSHOT.instance_number

      --  AND SESS_HISTORY.module = 'MY_MODULE'

    GROUP BY 

          SESS_HISTORY.sql_id,

          SESS_HISTORY.sql_plan_hash_value

    ORDER BY ash_secs DESC

    )TOP_SESSIONS,

    DBA_HIST_SQLTEXT HIST_SQLTEXT

WHERE TOP_SESSIONS.sql_id = HIST_SQLTEXT.sql_id

  AND HIST_SQLTEXT.sql_text LIKE '%ORDER BY%' 

  AND ROWNUM < 30;

SQL_ID        SQL_PLAN_HASH_VALUE SQL_TEXT                                               
------------- ------------------- ----------------------------------------------------c4cya7khk8ymx          3149935409 SELECT ROWID, F.DATE_STRING, F.KEY1, F.KEY2, F.KEY3,   
93y6r6ymvs29h           859439110 SELECT ROWID, F.DATE_STRING, F.KEY1, F.KEY2, F.KEY3, b5vd6xtzncv5v           305948821 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling    5czr9gg38at4a          3990208161 SELECT RECID, RECID, STAMP, THREAD#, SEQUENCE#, NAME, b7ad44vds3w05           658679650 select rowid, MODULE_NAME, VERSION_NUMBER  FROM                                                         

Get the execution plan of one of the Top SQLs:

SELECT * FROM TABLE(DBMS_XPLAN.display_awr('b3hb5zg0jw3g5', 3149935409,NULL,'ADVANCED'));

And this is the output:

PLAN_TABLE_OUTPUT                                                               

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

SQL_ID b3hb5zg0jw3g5                                                            

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

SELECT     ROWID, F.DATE_STRING, F.KEY1, F.KEY2,     F.KEY3,                    

F.SCENARIO_ID, F.CAMPAIGN_ID,     F.CATEGORY_ID, F.COUNTRY_ID,                  

F.CAMPAIGN_SENT_DATE,     F.CAMPAGIN_RECD_DATE, F.TS_LAST_MODIFIED,             

F.MESSAGE_TYPE,     F.MESSAGE_ID, F.NETWORK_ID, F.VLR_NUMBER,                   

F.PARTITION_KEY_ID, F.MONTH_STRING, F.YEAR_STRING,     F.MESSAGE_TEXT,          

F.MSISDN FROM MY_SCHEMA.MY_TABLE F ORDER BY          

10 DESC NULLS FIRST                                                             

                                                                                

Plan hash value: 3149935409                                                     

                                                                                

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

| Id  | Operation            | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

PLAN_TABLE_OUTPUT                                                               

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

|   0 | SELECT STATEMENT     |                       |       |       |       |  3890K(100)|         |                                                                                 

|   1 |  SORT ORDER BY       |                       |    24M|    15G|    17G|  3890K  (1)| 12:58:01 |                                                                                 

|   2 |   PARTITION RANGE ALL|                       |    24M|    15G|       |   483K  (1)| 01:36:40 | 

|   3 |    TABLE ACCESS FULL | FACT_ROAMER_CAMPAIGNS |    24M|    15G|       |   483K  (1)| 01:36:40 |                                                                                 

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

==========================
To see full text from DBA_HIST_TEXT
==========================
SELECT TO_CHAR(DBMS_LOB.SUBSTR(DBA_HIST_SQLTEXT.sql_text,4000,1)) AS SQL_TEXT
FROM DBA_HIST_SQLTEXT 
WHERE sql_id = '6rdwj7pktnp58';

==========================
To see execution plan from DBA_HIST_SQL_PLAN
==========================
SELECT parent_id||':'||id parent_and_id , operation, cost, ROUND(bytes/1024/1024) as Mb, io_cost, cpu_cost, TO_CHAR(timestamp,'yyyymmdd hh24:mi:ss') as TIMESTAMP 
FROM DBA_HIST_SQL_PLAN 
WHERE sql_id = '6rdwj7pktnp58' 
ORDER BY id;

PARENT_AND_ID  OPERATION         COST         MB    IO_COST   CPU_COST TIMESTAMP
-------------- ----------------- ---- ---------- ---------- ---------- -----------------
:0             SELECT STATEMENT  1624                                  20160101 02:08:04
0:1            PX COORDINATOR                                          20160101 02:08:04
1:2            PX SEND           1624         19       1615  114770591 20160101 02:08:04
2:3            SORT              1624         19       1615  114770591 20160101 02:08:04
3:4            PX RECEIVE        1622         19       1615   88213932 20160101 02:08:04
4:5            PX SEND           1622         19       1615   88213932 20160101 02:08:04
5:6            HASH JOIN         1622         19       1615   88213932 20160101 02:08:04
6:7            PX RECEIVE           2          0          2      23169 20160101 02:08:04
7:8            PX SEND              2          0          2      23169 20160101 02:08:04
8:9            PX BLOCK             2          0          2      23169 20160101 02:08:04
9:10           TABLE ACCESS         2          0          2      23169 20160101 02:08:04
6:11           PX BLOCK          1619         19       1613   79844933 20160101 02:08:04
11:12          TABLE ACCESS      1619         19       1613   79844933 20160101 02:08:04

=============================
Optional - Shrink TEMP Tablespace
=============================

Shrink the TEMP tablespace:

ALTER TABLESPACE TEMPORARY SHRINK SPACE KEEP 10000M;

ALTER TABLESPACE TEMPORARY SHRINK TEMPFILE '/software/oracle/db1/orainst/ora_temporary_01.dbf' KEEP 10000M;

Before
oracle@my_server:~>% find /oracle_db/db1 -type f -printf '%s %p\n'| sort -nr | head -10
16882081792 /oracle_db/db1/db_igt/ora_igt_table_01.dbf
15414075392 /oracle_db/db1/db_igt/ora_igt_table_02.dbf
15099502592 /oracle_db/db1/db_igt/ora_igt_table_03.dbf
12582920192 /oracle_db/db1/db_igt/ora_temporary_01.dbf
6501179392 /oracle_db/db1/db_igt/ora_igt_index_01.dbf
6291464192 /oracle_db/db1/db_igt/ora_undotbs_01.dbf
576724992 /oracle_db/db1/db_igt/ora_sysaux_01.dbf
419438592 /oracle_db/db1/db_igt/ora_system_01.dbf
104865792 /oracle_db/db1/db_igt/ora_workarea_01.dbf
104865792 /oracle_db/db1/db_igt/ora_dwh_table_01.dbf

After

oracle@my_server:~>% find /oracle_db/db1 -type f -printf '%s %p\n'| sort -nr | head -10
16882081792 /oracle_db/db1/db_igt/ora_igt_table_01.dbf
15414075392 /oracle_db/db1/db_igt/ora_igt_table_02.dbf
15099502592 /oracle_db/db1/db_igt/ora_igt_table_03.dbf
10486808576 /oracle_db/db1/db_igt/ora_temporary_01.dbf
6501179392 /oracle_db/db1/db_igt/ora_igt_index_01.dbf
6291464192 /oracle_db/db1/db_igt/ora_undotbs_01.dbf
576724992 /oracle_db/db1/db_igt/ora_sysaux_01.dbf
419438592 /oracle_db/db1/db_igt/ora_system_01.dbf
104865792 /oracle_db/db1/db_igt/ora_workarea_01.dbf
104865792 /oracle_db/db1/db_igt/ora_dwh_table_01.dbf

=============================
After Shrink TEMP Tablespace
=============================
After using ALTER TABLESPACE TEMPORARY SHRINK SPACE command, both the actual allocated space for TEMPORARY tablespace, and the max size limit are reduced.

Example:
Before 
cd /oracle_db/db1/db_orainst
ls -l | grep temp

-rw-r----- 1 oracle dba 31457288192 Jan 10 11:29 ora_temporary_01.dbf

SELECT  A.tablespace_name tablespace, 
        D.mb_total,
        SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used
FROM     v$sort_segment A,
(
  SELECT B.name, 
         C.block_size, 
         SUM (C.bytes) / 1024 / 1024 mb_total
    FROM v$tablespace B, 
         v$tempfile C
   WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED
------------------------------- ---------- ----------
TEMPORARY                            30000          1

SELECT * FROM dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE    FREE_SPACE
------------------------------ --------------- --------------- -------------
TEMPORARY                          31457280000     20971520000   31455182848

SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, AUTOEXTENSIBLE, ROUND(MAXBYTES/1024/1024) AS MAX_Mb, ROUND(USER_BYTES/1024/1024) AS user_bytes_Mb
 FROM DBA_TEMP_FILES;

FILE_NAME                                                    
TABLESPACE_NAME              MB AUTOEXTENSIB     MAX_MB USER_BYTES_MB
------------------------------------------------------------ 
-------------------- ---------- ------------ ---------- -------------
/oracle_db/db1/db_igt/ora_temporary_01.dbf 
TEMPORARY                 30000 YES               20000         29999


ALTER TABLESPACE TEMPORARY SHRINK SPACE KEEP 10000M;

Tablespace altered.

After
cd /oracle_db/db1/db_orainst
ls -l | grep temp
-rw-r----- 1 oracle dba 10871635968 Jan 10 12:45 ora_temporary_01.dbf

SELECT FILE_NAME, TABLESPACE_NAME, ROUND(BYTES/1024/1024) AS Mb, AUTOEXTENSIBLE, ROUND(MAXBYTES/1024/1024) AS MAX_Mb, ROUND(USER_BYTES/1024/1024) AS user_bytes_Mb
 FROM DBA_TEMP_FILES;

FILE_NAME                                                    
TABLESPACE_NAME              MB AUTOEXTENSIB     MAX_MB USER_BYTES_MB
------------------------------------------------------------ 
-------------------- ---------- ------------ ---------- -------------
/oracle_db/db1/db_igt/ora_temporary_01.dbf                   
TEMPORARY                 10368 YES               20000         10367


SELECT  A.tablespace_name tablespace, 
        D.mb_total,
        SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used
FROM     v$sort_segment A,
(
  SELECT B.name, 
         C.block_size, 
         SUM (C.bytes) / 1024 / 1024 mb_total
    FROM v$tablespace B, 
         v$tempfile C
   WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED
------------------------------- ---------- ----------
TEMPORARY                       10367.9922          1

SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME      TABLESPACE_SIZE ALLOCATED_SPACE    FREE_SPACE
-------------------- --------------- --------------- -------------
TEMPORARY                10871627776         2088960   10869538816