Poorly Performing Queries 1

ddrillich · Jul 21, 2007

Good Day,

The software vendor asked us the following - "What I need to know is whether there is a query or a set of queries that is taking a long time to process. ... I need to you to profile the performance of your database server to see if there are any poorly performing queries that are "smoking guns". "

Where can I find this information in 8.17?

Regards,
Dan

ddrillich · Jul 23, 2007

Good Day,

The vendor pointed to the following -

http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php

Is there anything simpler than that?

Regards,
Dan

engineer2100 · Jul 23, 2007

DBMS_PROFILER is for Procedures/functions? Is that what you are looking for? this package just shows you portions of code that are becoming bottlenecks.

if you need queries that are performing bad, then that activity is not as simple as you think it is.

I would point you to this link instead.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/toc.htm

ddrillich · Jul 23, 2007

Good Day,

We found out that the software runs the following queries frequently (every time a new piece of content is created) and when running them from SQLPLUS, it takes them minutes to complete.
What can we do to improve the response time of such queries? Unfortunately, modifying the software is tough at the moment.

SELECT COUNT(*) FROM ikmapp.IKS_BINARYDOCUMENT WHERE iks_name='IntraspectTools.zip'; ran for 3:07 minutes.

SELECT COUNT(*) FROM ikmapp.IKS_ASCIIDOCUMENT WHERE iks_name='Dan - text note'; ran for 2:52 minutes.

Regards,
Dan

engineer2100 · Jul 23, 2007

Try creating non-unique (if iks_name holds duplicate entires) or unique(if unique values) indexes on the iks_name column.

Code:

CREATE {UNIQUE} INDEX <index_name> ON IKS_BINARYDOCUMENT(IKS_NAME);
CREATE {UNIQUE} INDEX <index_name> ON IKS_ASCIIDOCUMENT(IKS_NAME);

In the above example {UNIQUE} has been mentioned to denote that based on your case, if you need to use it or not.

{ & } are not included in the syntax and have been used to just denote that UNIQUE can be placed at that position with in the CREATE INDEX statement.

ddrillich · Jul 24, 2007

Hi engineer2100,

What is the syntax for a non-unique index?

Many thanks,
Dan

engineer2100 · Jul 24, 2007

Just say

CREATE INDEX <INDEX NAME> ON <TABLE>(COLUMN LIST);

And call me Engi!!!

- Engi

ddrillich · Jul 25, 2007

Thank you Engi!

ddrillich · Jul 25, 2007

I ran the following query -

select disk_reads, sql_text from (select * from v$sql order by DISK_READS DESC) where rownum < 100;

The top six queries, disk read wise, are of the form -

select LOID_SEQ_NO FROM iks_BinaryDocument WHERE iks_name = 'PAR_Mapped 07252007.zip'

They range from 166,805 to 361,739 disk reads each.

I assume that these queries run quite often - once a minute or more often.

What do you think the impact of running these queries might be on the Oracle instance? Or more plainly, is 361,739 an acceptable disk read number for a frquent query?

Regards,
Dan

engineer2100 · Jul 25, 2007

That definitely is not a healthy sign at all.

Actually you need to run the below to understand on an average how disk read have happened over the executions.

Code:

-- IDENTIFY BAD SQL's 
--HIGH CPU Statements 
select trunc(buffer_gets/executions),trunc(plsql_exec_time/executions),vsa.*  
from v$sqlarea vsa
where executions>0
order by 1 desc

--HIGH I/O Statements 
select trunc(disk_reads/executions),vsa.*
from v$sqlarea vsa
where executions>0
order by 1 desc

--HEAVY SORT Statements 
select trunc(sorts/executions),vsa.* 
from v$sqlarea vsa
where executions>0
order by 1 desc

--High Memory Usage Statements 
select trunc(sharable_mem/executions),vsa.*  
from v$sqlarea vsa 
where executions>0
order by 1 desc

Beilstwh · Jul 30, 2007

It was still taking 166,805 to 361,739 disk reads after you put the non unique index on iks_name?

Bill
Oracle DBA/Developer
New York State, USA

engineer2100 · Jul 30, 2007

Did you happen to run the above queries? Please send me the output of HIGH I/O Statements - atleast top 10-15 ones

ddrillich · Aug 1, 2007

Beilstwh,

I'm applying the index in production tonight.

Engi,

Got the following error message -

select trunc(buffer_gets/executions),trunc(plsql_exec_time/executions),vsa.* from v$sqlarea vsa where executions>0 order by 1 desc

ERROR at line 1:
ORA-00904: invalid column name

Pointing at the 'by 1 desc'.

Many thanks!
Dan

ddrillich · Aug 1, 2007

It's the column plsql_exec_time.

select plsql_exec_time from v$sqlarea
*
ERROR at line 1:
ORA-00904: invalid column name

ddrillich · Aug 1, 2007

The 'HIGH I/O Statements' one is great, the output is a bit messy. Here is the top part..

SQL> select trunc(disk_reads/executions),vsa.* from v$sqlarea vsa where executions>0 order by 1 desc;

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS
-------------------
79036

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS
-------------------
select LOID_SEQ_NO FROM iks_BinaryDocument WHERE iks_name = 'EMTS Runguide.doc'

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS
-------------------
5762 500 2008 0 1 1

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS
-------------------
0 0 1 0 1

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS
-------------------
2007-08-01/16:32:45 0 1 79036 97403

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS
-------------------
2 3 CHOOSE 25

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS
-------------------
25 0 14A2A6A0 3773589533

TRUNC(DISK_READS/EXECUTIONS)
----------------------------
SQL_TEXT
--------------------------------------------------------------------------------

SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS VERSION_COUNT LOADED_VERSIONS

------------ -------------- ----------- ---------- ------------- ---------------

OPEN_VERSIONS USERS_OPENING EXECUTIONS USERS_EXECUTING LOADS
------------- ------------- ---------- --------------- ----------
FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS BUFFER_GETS
------------------- ------------- ----------- ---------- -----------
ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE PARSING_USER_ID
-------------- ------------ ------------------------- ---------------
PARSING_SCHEMA_ID KEPT_VERSIONS ADDRESS HASH_VALUE
----------------- ------------- -------- ----------
MODULE MODULE_HASH
---------------------------------------------------------------- -----------
ACTION ACTION_HASH
---------------------------------------------------------------- -----------
SERIALIZABLE_ABORTS

engineer2100 · Aug 2, 2007

Did you gather latest statistics?

To find when the Statistics were gathered fire the below SQL

Code:

Select table_name, last_analyzed from user_tables 
where table_name in  ('IKS_BINARYDOCUMENT', 'IKS_ASCIIDOCUMENT')

If not quickly gather statistics and then try to execute the queries!

Let us know.

ddrillich · Aug 2, 2007

I looked at all_tables...

SQL> Select table_name, last_analyzed from all_tables where table_name in ('IKS_BINARYDOCUMENT', 'IKS_ASCIIDOCUMENT');

TABLE_NAME LAST_ANAL
------------------------------ ---------
IKS_ASCIIDOCUMENT
IKS_BINARYDOCUMENT

You see, it's out-of-the-box software - supposedly runs by itself. We have there over a terabyte of data now.

Beilstwh · Aug 2, 2007

Until you put in the index, you will not see a speed improvement, no matter what you do.

Bill
Oracle DBA/Developer
New York State, USA

SantaMufasa · Aug 2, 2007

DDrillich said:
You see, it's out-of-the-box software - supposedly runs by itself.

Your query tells the story that just because it's "out-of-the-box software", it doesn't mean that it covers all of the bases.

All "out-of-the-box software", using databases from any database vendor (e.g., Oracle, SQL Server, DB2, et cetera) should have utilities to regularly gather statistics. The fact that "LAST_ANALYZED" is NULL for your two tables tells us that the software is not performing a VITAL task -- statistics gathering.

In the absence of the software doing that job, you must do the job. Do you know how manually to gather statistics for all tables in the application-owner schema?

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

ddrillich · Aug 2, 2007

Beilstwh,

We applied the two indexes last night.

SantaMufasa,

Long time - no talk ;-)

I'm all with you about the fact that no software should run on automatic pilot. However, it is what it is....

I don't know how to manually gather statistics for all tables in the application-owner schema? I would appreciate any help.

Regards,
Dan

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Poorly Performing Queries 1

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Programmer

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor