Solved

statspack.snap

Posted on 2009-05-20
13
786 Views
Last Modified: 2013-12-19
dba_job shows that there is 1 job
total_time  115409 (it is increasing after a short interval)
what shows   statspack.snap
broken shows  n  (we booted the system and the job is still there)
need ur help quick
thks
0
Comment
Question by:taaz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 11

Expert Comment

by:Andytw
ID: 24436976
Statspack is an Oracle utility which gathers information on database statistics, instance workload,load etc. for can be used for analysing the performance of an instance.  STATSPACK.SNAP is a procedure that takes a single  snapshot (e.g. database statistics).

In DBA_JOBS, the TOTAL_TIME is the execution time for that job.  However it is cumulative, so is the time for all runs of the job.  As such, I wouldn't worry about that number being high.  Do you have a problem with this job?

Can you speak to your DBA and find out who switched this job on?  It's generally not recommended to have statspack running indefinitely.

>>broken shows  n  (we booted the system and the job is still there)
Yes, the job will still be there (even after bouncing the database).  The job will keep running until it is either:
a). removed or
b). it is set to broken

Let me know if you need any more help.
0
 

Author Comment

by:taaz
ID: 24438137
Thanks Andytw for ur response I really appreciate that.
How can I remove,disable or terminate or stop the job. what is the command or procedure to stop this job.?

statspack.snap.
0
 

Author Comment

by:taaz
ID: 24438147
there is performance issue. the response time is slow.
Is it because of the job that is statspack.snap that is running for over 32hrs
0
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!

 
LVL 11

Expert Comment

by:Andytw
ID: 24439101
Tazz: you have 2 options, a). remove the job or b). set it to broken.
To do any of these you first need to find the job id of this job.
select job, what
from dba_jobs
where what = 'statspack.snap';

Then in SQL*Plus issue the following (substituting id for the number returned in query above):

EXEC dbms_job.remove(job => id)
COMMIT
OR
EXEC dbms_job.broken(job => id, broken => TRUE);
COMMIT

NB: Only the user that created the job can remove it.
0
 

Author Comment

by:taaz
ID: 24447593
Thanks for ur help but the query
select job,what from dba_jobs where what = 'statspack.snap':  does not give me anything.

how can I find the job_id?
0
 

Author Comment

by:taaz
ID: 24447654
the job   statspack.snap is running

total_time     116038
does this normal ?
Does this can cause performance issue?
Log_user is  perfstat.
priv_users is perfstat.
does perfstat is the owner of this job?
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24448440
You definitely should be able to see the job by querying DBA_JOBS.  Try:
select job, what
from dba_jobs
where upper(what) like '%SNAP%';
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24448575
>>total_time     116038
>>does this normal ?
32 hrs is a long time, but as I said before it is cululative.  Unfortunately with DBMS_JOB there is no way to tell the number of executions of a job.  I personally have never seen it take more that a few minutes to take a snapshot, but then all systems are different.

>> Does this can cause performance issue?
Statspack (snapshot.snap) is usually used to help analyse/solve a performance problem. It's not a massive load on the server.  But if it it then it's usually worth the extra performance hit (for a short time), since it's helping you to solve a bigger performance issue.

>> does perfstat is the owner of this job?
Yes, it statspack is installed in it's own schema with it's own set of objects.  The statspacks schema/user on our system is PERFSTAT.

You will need to log on as the PERFSTAT user to stop the job.
0
 

Author Comment

by:taaz
ID: 24450236
I really really appreciate you  Andytw, You have bben great help

what command to use to stop the job.?
can u please give me the syntax.?

thanks
0
 
LVL 11

Accepted Solution

by:
Andytw earned 500 total points
ID: 24450400
tazz, your welcome. :)
I've included the command (+output) of a session which removes the job.  It assumes that you are logged in as the PERFSTAT user.
SQL> show user
USER is "PERFSTAT"
SQL> l
  1* select job, what from user_jobs
SQL> /
       JOB WHAT
---------- -----------------------------------
      1516 statspack.snap(i_snap_level=>7);
 
SQL> exec dbms_job.remove(1516);
PL/SQL procedure successfully completed.
 
SQL> commit;
Commit complete.
SQL> select job, what from user_jobs
  2  /
no rows selected
SQL>

Open in new window

0
 
LVL 11

Expert Comment

by:Andytw
ID: 24450449
Now just in case you don't know the password for this user then you can do the following trick:
-- 1. The following query gets the encrypted password for PERFSTAT
select username, password
from dba_users
where username = 'PERFSTAT'
/
USERNAME                       PASSWORD
------------------------------ ------------------------------
PERFSTAT                       AC98877DE1297365

alter user PERFSTAT identified by PERFSTAT;
-- 2. Now you can log in as the PERFSTAT user run the command to drop the job
-- 3. Then Connect as a dba user then issue the following:
alter user PERFSTAT identified by values '2A4A720DB128C442';
-- The BY VALUES keyword allows you to reset the password back to what it was previously - even though you never knew the password.

0
 

Author Comment

by:taaz
ID: 24450635
Many thanks Andytw.
I am gona follow that. one thing I am always worried during resetting the user password in production,
because I had big trouble when once I was using the same apprach as u have mentioned to alter the password to the original one . It gave me an error that the password can not be reused. may be the profile assigned to that user has password resetting restrictions. But thanks again.
I will let u know the result
0
 

Author Closing Comment

by:taaz
ID: 31583660
thanks
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question