Home » RDBMS Server » Performance Tuning » Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. (Oracle 10g Release 2)
Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582585] Fri, 19 April 2013 13:38 Go to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I was asked a question which I'm unable to figure it correctly. Please help me.

Question : We have executed a query in database which is running from long time. Usually it does not take so long. What could be the possible reason behind poor performance. And we also want to find out how many logical reads, physical reads, IO, Hard Parsing, Soft Parsing etc. have been done for that SQL. Any wait events etc. i.e. every information we have to find out.

Note : We do not have access to STATPACK, AWR, ADDM reports. We have to figure it out using SQL * Plus tool and data dictionaries.

Can you please help me to find out a solutions for this scenario.


Thanks & Regards
Manoj
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582586 is a reply to message #582585] Fri, 19 April 2013 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc 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(19 CHAR)
 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 CHAR)
 OPTIMIZER_COST                            NUMBER
 OPTIMIZER_ENV                             RAW(882)
 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 CHAR)
 LAST_ACTIVE_CHILD_ADDRESS                 RAW(4)
 REMOTE                                    VARCHAR2(1 CHAR)
 OBJECT_STATUS                             VARCHAR2(19 CHAR)
 LITERAL_HASH_VALUE                        NUMBER
 LAST_LOAD_TIME                            DATE
 IS_OBSOLETE                               VARCHAR2(1 CHAR)
 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)
 TYPECHECK_MEM                             NUMBER

SQL> desc v$sql
 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
 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(19 CHAR)
 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 CHAR)
 OPTIMIZER_COST                            NUMBER
 OPTIMIZER_ENV                             RAW(882)
 OPTIMIZER_ENV_HASH_VALUE                  NUMBER
 PARSING_USER_ID                           NUMBER
 PARSING_SCHEMA_ID                         NUMBER
 PARSING_SCHEMA_NAME                       VARCHAR2(30)
 KEPT_VERSIONS                             NUMBER
 ADDRESS                                   RAW(4)
 TYPE_CHK_HEAP                             RAW(4)
 HASH_VALUE                                NUMBER
 OLD_HASH_VALUE                            NUMBER
 PLAN_HASH_VALUE                           NUMBER
 CHILD_NUMBER                              NUMBER
 SERVICE                                   VARCHAR2(64)
 SERVICE_HASH                              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                               NUMBER
 CHILD_ADDRESS                             RAW(4)
 SQLTYPE                                   NUMBER
 REMOTE                                    VARCHAR2(1 CHAR)
 OBJECT_STATUS                             VARCHAR2(19 CHAR)
 LITERAL_HASH_VALUE                        NUMBER
 LAST_LOAD_TIME                            VARCHAR2(19 CHAR)
 IS_OBSOLETE                               VARCHAR2(1 CHAR)
 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)
 TYPECHECK_MEM                             NUMBER

SQL> desc v$sqlstats
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SQL_TEXT                                  VARCHAR2(1000)
 SQL_FULLTEXT                              CLOB
 SQL_ID                                    VARCHAR2(13)
 LAST_ACTIVE_TIME                          DATE
 LAST_ACTIVE_CHILD_ADDRESS                 RAW(4)
 PLAN_HASH_VALUE                           NUMBER
 PARSE_CALLS                               NUMBER
 DISK_READS                                NUMBER
 DIRECT_WRITES                             NUMBER
 BUFFER_GETS                               NUMBER
 ROWS_PROCESSED                            NUMBER
 SERIALIZABLE_ABORTS                       NUMBER
 FETCHES                                   NUMBER
 EXECUTIONS                                NUMBER
 END_OF_FETCH_COUNT                        NUMBER
 LOADS                                     NUMBER
 VERSION_COUNT                             NUMBER
 INVALIDATIONS                             NUMBER
 PX_SERVERS_EXECUTIONS                     NUMBER
 CPU_TIME                                  NUMBER
 ELAPSED_TIME                              NUMBER
 AVG_HARD_PARSE_TIME                       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
 SORTS                                     NUMBER
 SHARABLE_MEM                              NUMBER
 TOTAL_SHARABLE_MEM                        NUMBER
 TYPECHECK_MEM                             NUMBER

Regards
Michel
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582589 is a reply to message #582586] Fri, 19 April 2013 14:01 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

We can find out this information in these views when query is executing or query has finished execution ?

According to Oracle Docs

V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

Eg.
PARSE_CALLS
DISK_READS
DIRECT_WRITES
BUFFER_GETS

It means all this information is estimated.

How to find out the performance issue in a procedure then which is running for a long time and we do not have access to STATPACK, AWR, ADDM reports?

Thanks & Regards
Manoj

[Updated on: Fri, 19 April 2013 14:09]

Report message to a moderator

Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582590 is a reply to message #582589] Fri, 19 April 2013 14:24 Go to previous messageGo to next message
John Watson
Messages: 4403
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Eg.
PARSE_CALLS
DISK_READS
DIRECT_WRITES
BUFFER_GETS

It means all this information is estimated.
Whay do tou say "estimated"? Do you think the figures are wrong? Thay are not.
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582611 is a reply to message #582585] Sat, 20 April 2013 02:33 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

I mean V$SQLAREA description on oracle documentation says that it lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

Ready for execution that means not yet executed. So my question is let us say for a query BUFFER_GETS were 99 but when that query actually executed then few blocks were not present in buffer cache and actual BUFFER_GETS is not 95 is it possible?

Thanks & Regards
Manoj

Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582612 is a reply to message #582611] Sat, 20 April 2013 02:36 Go to previous messageGo to next message
John Watson
Messages: 4403
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Ready for execution that means not yet executed.
No it doesn't. You might want to look up the meaning of the column "executions".
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582613 is a reply to message #582612] Sat, 20 April 2013 02:57 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

Ok, Understood that all these stats are real.

Question : How to find out the performance issue in a procedure which is running for a long time and we do not have access to STATPACK, AWR, ADDM reports?

Thanks & Regards
Manoj
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582614 is a reply to message #582613] Sat, 20 April 2013 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You first use DBMS_OUTPUT or DBMS_PROFILE (this latter one is better but requires you learn it).

Regards
Michel
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582615 is a reply to message #582614] Sat, 20 April 2013 03:16 Go to previous messageGo to next message
John Watson
Messages: 4403
Registered: January 2010
Location: Global Village
Senior Member
@MC, I know dbms_profiler can be useful, but what do you think of dbms_hprof? It hasn't done much for me. But perhaps I haven't had a suitable case for it.
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582617 is a reply to message #582615] Sat, 20 April 2013 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what do you think of dbms_hprof?


OP is in 10g this is why I didn't mention it otherwise it is a better option than DBMS_PROFILE (better in the meaning it faster and more accurately leads you to the interesting points of the code).

Regards
Michel
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582728 is a reply to message #582617] Mon, 22 April 2013 05:36 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

Hello Manoj

Hope you are doing good !

The Another view (Dynamic View) which AWR Reports uses.ie., V$ACIVE_SESSION_HISTORY. you obtain every thing. And Ask your DBA to run the SQL tuning.Which gives a Good result as well.

Regards

Kapardi
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582730 is a reply to message #582728] Mon, 22 April 2013 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
V$ACIVE_SESSION_HISTORY


It is V$ACTIVE_SESSION_HISTORY.

Quote:
And Ask your DBA to run the SQL tuning


If you mean the reports that OP said he can't run, this is not a good advice.

regards
Michel
Re: Identify performance for a query manually i.e. without STATPACK, AWR, ADDM etc. [message #582742 is a reply to message #582730] Mon, 22 April 2013 06:30 Go to previous message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

Thank you Michel For Correcting me always.

For running the SQL tuning. I mean He will Suggest his DBA team to run and based on the output he will Invistigate with them.

Regards
kapardi

Previous Topic: ORA-03137
Next Topic: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention?
Goto Forum:
  


Current Time: Tue Jul 29 13:53:51 CDT 2014

Total time taken to generate the page: 1.47314 seconds