?
Solved

statspack.snap

Posted on 2009-05-20
13
Medium Priority
?
823 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 2000 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

764 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