Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Oracle stats on a schema, AWR reports a total shared usage stats but not for a single schema/usr
fryder
post Sep 28 2017, 11:22 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 28-September 17
Member No.: 52,187



AWR reports a total shared usage stats but not for a single schema/usr.
I am trying to see why a particular batch program that executes a bunch of select queries took more time at 1 day and very less time on other day.
need to check whether the select queries were waiting or the execution took more time.

the queries are spawned on multiple tables. but there is a key table that is used in maximum queries.

Requesting help/guidance from masters here
Go to the top of the page
 
+Quote Post
burleson
post Sep 28 2017, 01:26 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi Fryder, and welcome to the forum!

Yes, AWR reports are for the instance, but some of the dba_hist tables have more granular data.

Try querying dba_hist_sqlstat!

This will hel you to get started:

http://www.dba-oracle.com/oracle10g_tuning...onary_views.htm

For a batch job, consider taking two AWR snapshots five minutes apart. This should show detail on the SQL.

I have lots of dba_hist scripts in my script collection or my tuning book:

http://www.dba-oracle.com/oracle_scripts.htm

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
fryder
post Sep 28 2017, 02:05 PM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 28-September 17
Member No.: 52,187



Thank you for the guidance. I will be looking for more details. while going through the hist table i did not find all queries except an insert query related to my batch job.
but will go through the links provided before coming back smile.gif.
also i observed that the dba confgured all table's related to all schema's under only 1 table space and 1 index space. So there is only 1 table space and 1 index space.
does this effect performance? its strange that the batch program executed in 9 minutes and and an hr later the re-execution took 24 minutes. with only a few 100 inserts more .

QUOTE (burleson @ Sep 28 2017, 02:26 PM) *
Hi Fryder, and welcome to the forum!

Yes, AWR reports are for the instance, but some of the dba_hist tables have more granular data.

Try querying dba_hist_sqlstat!

This will hel you to get started:

http://www.dba-oracle.com/oracle10g_tuning...onary_views.htm

For a batch job, consider taking two AWR snapshots five minutes apart. This should show detail on the SQL.

I have lots of dba_hist scripts in my script collection or my tuning book:

http://www.dba-oracle.com/oracle_scripts.htm

Good luck!

Go to the top of the page
 
+Quote Post
burleson
post Sep 28 2017, 05:20 PM
Post #4


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi Fryder,

Mapping to only one tablespace might matter, but only if all of the underlying data files are on the same disk. Check dba_data_files:

http://dba-oracle.com/googlesearchsite_pro...572j21194672j26

Is the batch job doing inserts?

If so, contention is possible, please read:

http://www.dba-oracle.com/oracle10g_tuning..._contention.htm

Also see these articles on insert contention:

http://dba-oracle.com/googlesearchsite_pro...327j35574003j36


Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 03:45 PM