OraFAQ Forum: SQL & PL/SQL » Wait class snapshot wise using dba_hist_system

Home » SQL & PL/SQL » SQL & PL/SQL » Wait class snapshot wise using dba_hist_system_event (Aix )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Wait class snapshot wise using dba_hist_system_event [message #632551]

Tue, 03 February 2015 01:40

saurabhshukladba
Messages: 5
Registered: February 2015

Junior Member

Hi folks
I need a script which can provide me total_waits_fg for wait_class snapdhot wise. We would need to join dba_hist_system_event and
dba_hist_snapshot views. I tried but not getting desired output.

Cheers
Saurabh

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632555 is a reply to message #632551]

Tue, 03 February 2015 02:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

saurabhshukladba wrote on Tue, 03 February 2015 13:10

I tried but not getting desired output.

And we don't know what you tried and what output you expect, since you haven't posted.

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632560 is a reply to message #632551]

Tue, 03 February 2015 03:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your problem exactly? How to join the 2 views?
You have to join them on the 3 first columns:
SNAP_ID
DBID
INSTANCE_NUMBER

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632592 is a reply to message #632551]

Tue, 03 February 2015 07:49

saurabhshukladba
Messages: 5
Registered: February 2015

Junior Member

Hi

I want the results as below :

SNAP_ID BEGIN_TIME Instance_number User_io User_io_avg Others Other_Avg Commit Commit_avg Concurrency Concurrency_avg Administrative Administrative_avg Cluster Cluster_avg Configuration Configuration_avg Network Network_avg Scheduler Scheduler_avg

Where,

tmfg = end_snapshot.time_waited_micro_fg - begin_snapshot.time_waited_micro_fg

wtfg = end_snapshot.total_waits_fg - begin_snapshot.total_waits_fg

(wait_class) column will have value of (tmfg)

(wait_class)_avg column will have value of (tmfg/wtfg)

Kindly let me know if anything else is required.

Cheers
Saurabh

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632593 is a reply to message #632592]

Tue, 03 February 2015 07:56

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Kindly let me know if anything else is required.

Maybe you answer my questions.

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632594 is a reply to message #632592]

Tue, 03 February 2015 07:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

we speak SQL.
Do you speak SQL? If so, please show us & use SQL

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632596 is a reply to message #632594]

Tue, 03 February 2015 08:15

saurabhshukladba
Messages: 5
Registered: February 2015

Junior Member

Output should be as below:

SNAP_ID BEGIN_TIME Instance_number User_io User_io_avg Others Other_Avg Commit Commit_avg Concurrency Concurrency_avg Administrative Administrative_avg Cluster Cluster_avg Configuration Configuration_avg Network Network_avg Scheduler Scheduler_avg

Where,

tmfg = end_snapshot.time_waited_micro_fg - begin_snapshot.time_waited_micro_fg

wtfg = end_snapshot.total_waits_fg - begin_snapshot.total_waits_fg

(wait_class) column will have value of (tmfg)

(wait_class)_avg column will have value of (tmfg/wtfg)

wait_class = user_io, commit etc

Please advise on this.

Cheers,
Saurabh

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632602 is a reply to message #632596]

Tue, 03 February 2015 08:40

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 03 February 2015 14:56

Quote:
Kindly let me know if anything else is required.

Maybe you answer my questions.

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632606 is a reply to message #632602]

Tue, 03 February 2015 08:56

saurabhshukladba
Messages: 5
Registered: February 2015

Junior Member

Quote:

What is your problem exactly?

I have time in my script, I need to incorporate snap_id in the output. Output is not formatted.

Quote:

How to join the 2 views?

Script is very complex to analyze and it is not easy for me to amend further. I am joining on snap_id, dbid & instance_number.

Sorry I am a novice to use this forum hence committing mistakes.

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632608 is a reply to message #632606]

Tue, 03 February 2015 08:59

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post the query you use/have and we will help you to go further.

Report message to a moderator

Re: Wait class snapshot wise using dba_hist_system_event [message #632609 is a reply to message #632596]

Tue, 03 February 2015 09:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

saurabhshukladba wrote on Tue, 03 February 2015 06:15

Output should be as below:

SNAP_ID BEGIN_TIME Instance_number User_io User_io_avg Others Other_Avg Commit Commit_avg Concurrency Concurrency_avg Administrative Administrative_avg Cluster Cluster_avg Configuration Configuration_avg Network Network_avg Scheduler Scheduler_avg

Saurabh

>We would need to join dba_hist_system_event and dba_hist_snapshot views.
Some, many, most of columns stated at top of this post do NOT exist in either view above?
From where do they originate?

Please explain, elaborate & show us exactly how to compute any "average" value when only BEGIN_TIME exists.
How do you or we decide which is appropriate or desired END_TIME should be?

[Updated on: Tue, 03 February 2015 09:09]

Report message to a moderator

Previous Topic:	GET EXACT STRING MATCH
Next Topic:	I want find how many hour,minutes,seconds is taken from task_stared and task_end.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 04:15:58 CDT 2024