[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1572
  • Last Modified:

Trace in oracle

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
imran_fast
Asked:
imran_fast
  • 9
  • 7
  • 2
4 Solutions
 
Mark GeerlingsDatabase AdministratorCommented:
(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
 
imran_fastAuthor Commented:
(I have no idea what "Profiler to sql" is.  By "sql" do you mean "SQL Server"?)

Yes sql server
0
 
schwertnerCommented:
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
imran_fastAuthor Commented:
Hi markgeer,
I have Enterprise Manager Console
please tell me how can i idenfity the query that performs the worst.
0
 
imran_fastAuthor Commented:
hi  schwertner,

isn't the view v$sqlarea contains the current queries executing on oralce only or it will maintain history.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
schwertnerCommented:
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
 
imran_fastAuthor Commented:
It created a file C:\temp\readstat.txt on my local machine but thats empty i wonder why??
0
 
imran_fastAuthor Commented:
hi schwertner ,
What about Oracle10g
0
 
Mark GeerlingsDatabase AdministratorCommented:
What values did you provide for "disk_reads" and "executions" when you were prompted?
0
 
imran_fastAuthor Commented:
for disk_reads = -1
executions = 0
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
imran_fastAuthor Commented:
Hi
the cpu_time and elapsed time is it in milliseconds?
0
 
imran_fastAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
imran_fastAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now