Home » Other » General » SQL Text (Oracle 10g, 11g, 12c, any platform)
SQL Text [message #655267] Thu, 25 August 2016 22:22 Go to next message
trantuananh24hg
Messages: 705
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I usually use my script to detect the SQL statement details, I named that as "sqltext.sql". So, if you have any idea to make this better, thank you.

Script contents
Prompt -- Tracking SQL details
Prompt -- trantuananh24hg@gmail.com

var objid number;
col username format a15
col program format a18
col terminal format a12
col wait_class format a12
col sql_text format a28
col elap_per_exec format 99999
col elap_exec format 99999
undefine sql_id

select sid, serial#, username, program, terminal, wait_class
from v$session a
where sql_hash_value in (select hash_value from v$sql where sql_id='&&sql_id')
/


select sql_text, sql_id, executions, parse_calls, elapsed_time/1000000 elap_exec
from v$sql
where sql_id='&&sql_id'
/

col TOTAL_ELAPSED_TIME_SEC format 999999
col ELAPSED_TIME_SEC_PER_EXEC format 999999
col TOTAL_CPU_TIME_SEC format 999999
col CPU_TIME_SEC format 999999

SELECT 
	EXECUTIONS,
	ELAPSED_TIME/1000000 elap_exec,
	ELAPSED_TIME/1000000/EXECUTIONS elap_per_exec,
	CPU_TIME/1000000 TOTAL_CPU_TIME_SEC,
	CPU_TIME/1000000/EXECUTIONS CPU_TIME_SEC
FROM
	V$SQL
WHERE sql_id='&&sql_id'
/


select program_id, program_line# from v$sql
where sql_id='&&sql_id'
/

set serveroutput on
undefine sql_id
prompt -- Get the plan associated with the sql
select * from TABLE(dbms_xplan.display_awr('&&sql_id'));

prompt -- Press any key to define which object associated 
pause
col owner format a12
col object_name format a15
col object_type format a12

select owner, object_name, object_type from dba_objects
where object_id=&&objid
/

undefine objid
col username clear
col program clear
col terminal clear
col wait_class clear
col sql_text clear
col elap_per_exec clear
col owner clear
col object_name clear
col object_type clear

Example:

sys@VASOL> ! more addmrpt_1_20479_20536.txt
          ADDM Report for Task 'TASK_52329'
          ---------------------------------

Analysis Period
---------------
AWR snapshot range from 20479 to 20536.
Time period starts at 24-AUG-16 12.00.57 AM
Time period ends at 26-AUG-16 09.00.48 AM
....
Recommendation 1: SQL Tuning
   Estimated benefit is .9 active sessions, 34.64% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "3uyzm4v36ruxf".
      Related Object
         SQL statement with SQL_ID 3uyzm4v36ruxf.
         select * from(
         select p.pro_id, p.pro_name, p.pro_code, p.pro_img, p.price,
         p.singer, p.singer_id, p.updated_by, p.num_view, p.num_buy,
         t.type_id, t.type_name
         from product_info p
         inner join product_type t on p.type_id = t.type_id
         where p.cate_id =:1 and p.active =1
         and (p.IS_SHOW=1)
         ORDER BY dbms_random.value) where rownum <=:2
   Rationale
      The SQL spent 99% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 76% for SQL
      execution, 0% for parsing, 24% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "3uyzm4v36ruxf" was executed 89925 times and
      had an average elapsed time of 2 seconds.

sys@VASOL> @sqltext
-- Tracking SQL details
-- trantuananh24hg@gmail.com
Enter value for sql_id: 3uyzm4v36ruxf
old   3: where sql_hash_value in (select hash_value from v$sql where sql_id='&&sql_id')
new   3: where sql_hash_value in (select hash_value from v$sql where sql_id='3uyzm4v36ruxf')

no rows selected

old   3: where sql_id='&&sql_id'
new   3: where sql_id='3uyzm4v36ruxf'

SQL_TEXT                     SQL_ID        EXECUTIONS PARSE_CALLS ELAP_EXEC
---------------------------- ------------- ---------- ----------- ---------
select * from(            se 3uyzm4v36ruxf      88305       88286    178146
lect p.pro_id, p.pro_name, p
.pro_code, p.pro_img, p.pric
e, p.singer, p.singer_id, p.
updated_by, p.num_view, p.nu
m_buy,            t.type_id,
 t.type_name            from
 product_info p            i
nner join product_type t on
p.type_id = t.type_id
     where p.cate_id =:1 and

SQL_TEXT                     SQL_ID        EXECUTIONS PARSE_CALLS ELAP_EXEC
---------------------------- ------------- ---------- ----------- ---------
 p.active =1            and
(p.IS_SHOW=1)            ORD
ER BY dbms_random.value) whe
re rownum <=:2

old   9: WHERE sql_id='&&sql_id'
new   9: WHERE sql_id='3uyzm4v36ruxf'

EXECUTIONS ELAP_EXEC ELAP_PER_EXEC TOTAL_CPU_TIME_SEC CPU_TIME_SEC
---------- --------- ------------- ------------------ ------------
     88305    178146             2             177360            2

old   2: where sql_id='&&sql_id'
new   2: where sql_id='3uyzm4v36ruxf'

PROGRAM_ID PROGRAM_LINE#
---------- -------------
     76716            89

-- Get the plan associated with the sql
Enter value for sql_id: 3uyzm4v36ruxf
old   1: select * from TABLE(dbms_xplan.display_awr('&&sql_id'))
new   1: select * from TABLE(dbms_xplan.display_awr('3uyzm4v36ruxf'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3uyzm4v36ruxf
--------------------
select * from(            select p.pro_id, p.pro_name, p.pro_code,
p.pro_img, p.price, p.singer, p.singer_id, p.updated_by, p.num_view,
p.num_buy,            t.type_id, t.type_name            from
product_info p            inner join product_type t on p.type_id =
t.type_id            where p.cate_id =:1 and p.active =1            and
(p.IS_SHOW=1)            ORDER BY dbms_random.value) where rownum <=:2

Plan hash value: 558904790


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------
| Id  | Operation               | Name         | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT        |              |       |       |     4 (100)|
       |
|   1 |  COUNT STOPKEY          |              |       |       |            |
       |
|   2 |   VIEW                  |              |    10 |  5900 |     4   (0)| 00

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
:00:01 |
|   3 |    SORT ORDER BY STOPKEY|              |    10 |  1090 |     4   (0)| 00
:00:01 |
|   4 |     NESTED LOOPS        |              |    10 |  1090 |     4   (0)| 00
:00:01 |
|   5 |      TABLE ACCESS FULL  | PRODUCT_TYPE |     7 |   105 |     2   (0)| 00
:00:01 |
|   6 |      TABLE ACCESS FULL  | PRODUCT_INFO |    10 |   940 |     2   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
-- Press any key to define which object associated

Enter value for objid: 76716
old   2: where object_id=&&objid
new   2: where object_id=76716

OWNER        OBJECT_NAME     OBJECT_TYPE
------------ --------------- ------------
MSOCIAL_VAS  WEB_PK_PRODUCT  PACKAGE BODY

Purpose:
1. It defined the SQL statement
select * 
	from(select p.pro_id, p.pro_name, p.pro_code,
				p.pro_img, p.price, p.singer, 
				p.singer_id, p.updated_by, 
				p.num_view,p.num_buy,            
				t.type_id, t.type_name            
					from product_info p            
					inner join product_type t 
					on p.type_id = t.type_id            
					where p.cate_id =:1 
					and p.active =1            
					and(p.IS_SHOW=1)            
					ORDER BY dbms_random.value) 
	where rownum <=:2

2. How many sessions do this
3. How many second per elapsed, how many total second of total execution
EXECUTIONS ELAP_EXEC ELAP_PER_EXEC TOTAL_CPU_TIME_SEC CPU_TIME_SEC
---------- --------- ------------- ------------------ ------------
     88305    178146             2             177360            2

The ELAPSED_TIME is refered to: https://docs.oracle.com/cd/B12037_01/server.101/b10755/dynviews_2097.htm

4. What is the object in which SQL Statement belonged to?
PROGRAM_ID PROGRAM_LINE#
---------- -------------
     76716            89

The program ID is 76766, and this statement is in the line 89

5. The plan table
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3uyzm4v36ruxf
--------------------------------------------------------------------------------
select * 
	from(select p.pro_id, p.pro_name, p.pro_code,
				p.pro_img, p.price, p.singer, 
				p.singer_id, p.updated_by, 
				p.num_view,p.num_buy,            
				t.type_id, t.type_name            
					from product_info p            
					inner join product_type t 
					on p.type_id = t.type_id            
					where p.cate_id =:1 
					and p.active =1            
					and(p.IS_SHOW=1)            
					ORDER BY dbms_random.value) 
	where rownum <=:2
Plan hash value: 610082498
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------
| Id  | Operation               | Name         | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT        |              |       |       |   179 (100)|
       |
|   1 |  COUNT STOPKEY          |              |       |       |            |
       |
|   2 |   VIEW                  |              |     9 |  5310 |   179   (1)| 00
:00:03 |
|   3 |    SORT ORDER BY STOPKEY|              |     9 |  1827 |   179   (1)| 00
:00:03 |
|   4 |     HASH JOIN           |              |     9 |  1827 |   179   (1)| 00
:00:03 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   5 |      TABLE ACCESS FULL  | PRODUCT_TYPE |     7 |   105 |     3   (0)| 00
:00:01 |
|   6 |      TABLE ACCESS FULL  | PRODUCT_INFO |     9 |   846 |   176   (1)| 00
:00:03 |
--------------------------------------------------------------------------------
--------


6. Define the name, owner of object
OWNER        OBJECT_NAME     OBJECT_TYPE
------------ --------------- ------------
MSOCIAL_VAS  WEB_PK_PRODUCT  PACKAGE BODY

Thank you.
Re: SQL Text [message #655269 is a reply to message #655267] Fri, 26 August 2016 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 21263
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are 3 things you might consider (just to make the output prettier):
- SET VER OFF
- SET PAGE 1000
- SET LINE 120

The first one will not display
old   9: WHERE sql_id='&&sql_id'
new   9: WHERE sql_id='3uyzm4v36ruxf'

The second one will omit the second headings, here:
SQL_TEXT                     SQL_ID        EXECUTIONS PARSE_CALLS ELAP_EXEC
---------------------------- ------------- ---------- ----------- ---------
select * from(            se 3uyzm4v36ruxf      88305       88286    178146
lect p.pro_id, p.pro_name, p
.pro_code, p.pro_img, p.pric
e, p.singer, p.singer_id, p.
updated_by, p.num_view, p.nu
m_buy,            t.type_id,
 t.type_name            from
 product_info p            i
nner join product_type t on
p.type_id = t.type_id
     where p.cate_id =:1 and

SQL_TEXT                     SQL_ID        EXECUTIONS PARSE_CALLS ELAP_EXEC
---------------------------- ------------- ---------- ----------- ---------
 p.active =1            and
(p.IS_SHOW=1)            ORD
ER BY dbms_random.value) whe
re rownum <=:2

The third one will fix this "new line" issue, such as this one:
--------------------------------------------------------------------------------
--------
Furthermore, in that case you might enlarge
col sql_text format a28
and save some space on the screen.

Note that values I used as examples in SET PAGE and SET LINE can be changed. Test it with several different values and choose the "best" one (i.e. the one that looks OK).
Re: SQL Text [message #655274 is a reply to message #655269] Fri, 26 August 2016 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And also and always (put it in login.sql):
SET TRIMOUT ON TRIMSPOOL ON

Re: SQL Text [message #655324 is a reply to message #655274] Sat, 27 August 2016 23:20 Go to previous message
trantuananh24hg
Messages: 705
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, LittleFoot & Michel Caldot.
Happy weekend
Previous Topic: Please tell me cost of Software
Next Topic: invalid specification for system parameter LOCAL_LISTENER
Goto Forum:
  


Current Time: Mon Dec 11 01:28:37 CST 2017

Total time taken to generate the page: 0.02871 seconds