Home » Other » General » SQL Text (Oracle 10g, 11g, 12c, any platform)
| SQL Text [message #655267] |
Thu, 25 August 2016 22:22  |
trantuananh24hg
Messages: 744
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  |
 |
Littlefoot
Messages: 21823
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:
--------------------------------------------------------------------------------
--------
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).
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 15:20:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
