Solved

Trace in oracle

Posted on 2006-11-28
18
1,553 Views
Last Modified: 2010-05-18
Hi,

I would like to know if there is a way in oracle 9i to identify quries taking long time to run from application
something similar to Profiler to sql.
0
Comment
Question by:imran_fast
  • 9
  • 7
  • 2
18 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18027653
(I have no idea what "Profiler to sql" is.  By "sql" do you mean "SQL Server"?)

Yes, Oracle offers tools to help identify problem queries, but the Oracle tools are not considered as easy to use as the similar tools from Microsoft for SQL Server.  Oracle includes OEM (Oracle Enterprise Manager) as a GUI tool that can help manage Oracle databases.  That can show you the SQL statements that are currently being executed, and can identify the ones that perform the worst.

Also, Quest Software (www.quest.com) offers a tool for Oracle called: TOAD (Tool for Oracle Application Developers) that many people find easier to use than the free tools from Oracle.

Oracle also offers a command-line utility (tkprof) for tracing individual database sessions and/or statements, but this must be run on the server.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18027658
(I have no idea what "Profiler to sql" is.  By "sql" do you mean "SQL Server"?)

Yes sql server
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 18027673
This is task of Oracle Enterprise Manager - Top Sessions and other parameters.
You can take the sessions that consume biggest amount of RAM, Physical Disk Reads.

For particular queries you can query the view

SQL> descr v$sqlarea;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 SQL_TEXT                                           VARCHAR2(1000)
 SQL_FULLTEXT                                       CLOB
 SQL_ID                                             VARCHAR2(13)
 SHARABLE_MEM                                       NUMBER
 PERSISTENT_MEM                                     NUMBER
 RUNTIME_MEM                                        NUMBER
 SORTS                                              NUMBER
 VERSION_COUNT                                      NUMBER
 LOADED_VERSIONS                                    NUMBER
 OPEN_VERSIONS                                      NUMBER
 USERS_OPENING                                      NUMBER
 FETCHES                                            NUMBER
 EXECUTIONS                                         NUMBER
 PX_SERVERS_EXECUTIONS                              NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 USERS_EXECUTING                                    NUMBER
 LOADS                                              NUMBER
 FIRST_LOAD_TIME                                    VARCHAR2(76)
 INVALIDATIONS                                      NUMBER
 PARSE_CALLS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 BUFFER_GETS                                        NUMBER
 APPLICATION_WAIT_TIME                              NUMBER
 CONCURRENCY_WAIT_TIME                              NUMBER
 CLUSTER_WAIT_TIME                                  NUMBER
 USER_IO_WAIT_TIME                                  NUMBER
 PLSQL_EXEC_TIME                                    NUMBER
 JAVA_EXEC_TIME                                     NUMBER
 ROWS_PROCESSED                                     NUMBER
 COMMAND_TYPE                                       NUMBER
 OPTIMIZER_MODE                                     VARCHAR2(10)
 OPTIMIZER_COST                                     NUMBER
 OPTIMIZER_ENV                                      RAW(818)
 OPTIMIZER_ENV_HASH_VALUE                           NUMBER
 PARSING_USER_ID                                    NUMBER
 PARSING_SCHEMA_ID                                  NUMBER
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 KEPT_VERSIONS                                      NUMBER
 ADDRESS                                            RAW(4)
 HASH_VALUE                                         NUMBER
 OLD_HASH_VALUE                                     NUMBER
 PLAN_HASH_VALUE                                    NUMBER
 MODULE                                             VARCHAR2(64)
 MODULE_HASH                                        NUMBER
 ACTION                                             VARCHAR2(64)
 ACTION_HASH                                        NUMBER
 SERIALIZABLE_ABORTS                                NUMBER
 OUTLINE_CATEGORY                                   VARCHAR2(64)
 CPU_TIME                                           NUMBER
 ELAPSED_TIME                                       NUMBER
 OUTLINE_SID                                        VARCHAR2(40)
 LAST_ACTIVE_CHILD_ADDRESS                          RAW(4)
 REMOTE                                             VARCHAR2(1)
 OBJECT_STATUS                                      VARCHAR2(19)
 LITERAL_HASH_VALUE                                 NUMBER
 LAST_LOAD_TIME                                     DATE
 IS_OBSOLETE                                        VARCHAR2(1)
 CHILD_LATCH                                        NUMBER
 SQL_PROFILE                                        VARCHAR2(64)
 PROGRAM_ID                                         NUMBER
 PROGRAM_LINE#                                      NUMBER
 EXACT_MATCHING_SIGNATURE                           NUMBER
 FORCE_MATCHING_SIGNATURE                           NUMBER
 LAST_ACTIVE_TIME                                   DATE
 BIND_DATA                                          RAW(2000)

SQL>


Concentrate on:
CPU_TIME                                           NUMBER
ELAPSED_TIME                                       NUMBER

Sorting the queries on these columns will give you the
longest running queries.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18027679
Hi markgeer,
I have Enterprise Manager Console
please tell me how can i idenfity the query that performs the worst.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18027694
hi  schwertner,

isn't the view v$sqlarea contains the current queries executing on oralce only or it will maintain history.
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 300 total points
ID: 18027744
The view v$sqlarea contains both current and recent SQL statements.  It is cleared whenever the Oracle instance is shut down, but it usually contains some history.  How much history does it contain?  That will vary based on the size of your shared_pool, and on how busy the database is (that is: how many different SQL statements have been executed recently) and on whether the application uses bind variables or not.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18027793
Sorry, I rarely use OEM, so I'm not very familiar with it.  I usually use this query (in SQL*Plus) to identify poorly-performing SQL statements in Oracle:

-- Note: The script "readexcl.sql" produces a comma-delimited file that can be opened by Excel
--  with less manual effort.  This script produces output that is more easily viewed directly
--  in SQL*Plus or in a text editor.
set linesize 1200;
set pagesize 999;
column Text format a1000;
column Ratio format 99999.99;
column Hash format 999999999999;
column buffers format 999999999999;
prompt Normally use "5" for executions, but enter "0" to see all statements
prompt Normally use "1" for disk_reads, but enter "-1" to see all statements
-- The hash_value column displays the "key" that can be used to retrieve the full text of
--  the sql statement, since the view, v$sqlarea, displays only the first 1000 characters.
spool C:\temp\readstat.txt;
select disk_reads "Reads", executions "Executed",
rows_processed "Rows", round(disk_reads / executions,2) "Ratio", buffer_gets "Buffers", hash_value "Hash",
sql_text "Text"
FROM v$sqlarea
where executions > &executions
and disk_reads > &disk_reads
and sql_text not like 'analyze table%'
and sql_text not like 'SELECT /*+NESTED%'
and sql_text not like 'explain plan%'
order by 4 desc, 5 desc;
spool off;
prompt 'Output saved to file C:\temp\readstat.txt'
set linesize 200;

Here is the "readexcl" script that can be used if you prefer to view the results in Excel:
-- This script produces a comma-delimited file that can be opened by Excel
set linesize 1200;
set pagesize 999;
set head off;
set feed off;
column Ratio format 99999.99;
column Hash format 999999999999;
-- The hash_value column displays the "key" that can be used to retrieve the full text of
--  the sql statement, since the view, v$sqlarea, displays only the first 1000 characters.
spool C:\temp\readstat.txt;
prompt Reads,Executions,Rows,Ratio,Buffers,Text
select disk_reads||','||executions||','||rows_processed||','||
round(disk_reads / decode(executions,0,1,executions),2)||','||buffer_gets||',"'||replace(sql_text,'"','')||'"' "Reads"
FROM v$sqlarea
where executions > &executions
and disk_reads > &disk_reads;
spool off;
prompt Output saved to file C:\temp\readstat.txt
set linesize 200;
set head on;
set feed on;
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 18027823
Yes, Oracle will not store the whole history of the execution
of the SQL statements. It stores only the latest.

You are using 9i. Go to Sessions and you will be able to sort them
1. By time
2. By CPU usage
3. By RAM usage
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18027861
It created a file C:\temp\readstat.txt on my local machine but thats empty i wonder why??
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Author Comment

by:imran_fast
ID: 18027912
hi schwertner ,
What about Oracle10g
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18027933
What values did you provide for "disk_reads" and "executions" when you were prompted?
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18027953
for disk_reads = -1
executions = 0
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 300 total points
ID: 18027973
Then it should certainly give you a file with data!  Did you leave the "spool off" line in the script when you ran it?  That must be there, or you will have to exit SQL*Plus to close the file.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18028021
Hi
the cpu_time and elapsed time is it in milliseconds?
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18028088
hi markgeer,
actually i ran it in Oracle sql developer!
because sql plus gave me so many errors
should i put it in a transaction
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18028161
I don't know.  I don't consider it a "transaction" and I am sure that the Oracle database doesn't either.  I have used SQL Developer a bit, but I haven't tried executing SQL statements through SQL Developer.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18028349
Ok Markgeer,
It worked for me know

One last question

Can i clear contents of v$sqlarea without shutting down oracle?

the reson is i want to know all the sql's when i run or execute an event on front end application.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18028392
Not quite, but almost.  The SQL command is:
alter system flush shared_pool;

That will clear most of the statements from v$sqlarea.

You will need to be logged in as a DBA (or have the "alter system" privilege) to execute this.  Also, be aware that this could have a performance impact on SQL statements executed after this, since none of them will be cached, so they may execute a little slower.  (This assumes that your application uses bind variables - if yours doesn't, then you have other performance issues!)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now