statspack.snap

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
taazAsked:
Who is Participating?
 
AndytwConnect With a Mentor Commented:
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
 
AndytwCommented:
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
 
taazAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
taazAuthor Commented:
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
 
AndytwCommented:
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
 
taazAuthor Commented:
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
 
taazAuthor Commented:
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
 
AndytwCommented:
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
 
AndytwCommented:
>>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
 
taazAuthor Commented:
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
 
AndytwCommented:
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
 
taazAuthor Commented:
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
 
taazAuthor Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.