Performance turning

Finding Information About Currently Running Jobs:

You can check a job's state by issuing the following statement:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS

WHERE JOB_NAME = 'MY_EMP_JOB1';

JOB_NAME                 STATE

------------------------------ ---------

MY_EMP_JOB1          DISABLED

In this case, you could enable the job using the ENABLE procedure. Table 28-1 shows the valid values for job state.

Table 28-1 Job States

Job State Description

-----------------     --------------------

disabled         The job is disabled.

scheduled      The job is scheduled to be executed.

running            The job is currently running.

completed       The job has completed, and is not scheduled to run again.

stopped           The job was scheduled to run once and was stopped while it was running.

broken             The job is broken.

failed               The job was scheduled to run once and failed.

retry schedu  The job has failed at least once and a retry has been scheduled to be executed.

succeeded     The job was scheduled to run once and completed successfully.

chain_stalled  The job is of type chain and has no steps running, no steps scheduled to run, and no event steps waiting on an event, and the chain evaluation_interval is set to NULL. No progress will be made in the chain unless there is manual intervention.

You can check the progress of currently running jobs by issuing the following statement:

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

Note that, for the column CPU_USED to show valid data, the initialization parameter RESOURCE_LIMIT must be set to true.

You can find out information about a job that is part of a running chain by issuing the following statement:

SELECT * FROM ALL_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME='MY_JOB1';

You can check whether the job coordinator is running by searching for a process of the form cjqNNN.

Monitoring and Managing Window and Job Logs:

The Scheduler supports two kinds of logs: the job log and the window log.

Job Log

You can view information about job runs, job state changes, and job failures in the job log. The job log is implemented as the following two data dictionary views:

*_SCHEDULER_JOB_LOG

*_SCHEDULER_JOB_RUN_DETAILS

You can control the amount of logging that the Scheduler performs on jobs at both the job class and individual job level. Normally, you control logging at the class level, as this offers you more control over logging for the jobs in the class.

See "Viewing the Job Log" for definitions of the various logging levels and for information about logging level precedence between jobs and their job class. By default, the logging level of job classes is LOGGING_RUNS, which causes all job runs to be logged.

You can set the logging_level attribute when you create the job class, or you can use the SET_ATTRIBUTE procedure to change the logging level at a later time. The following example sets the logging level of jobs in the myclass1 job class to LOGGING_FAILED_RUNS, which means that only failed runs are logged. Note that all job classes are in the SYS schema.

BEGIN

  DBMS_SCHEDULER.SET_ATTRIBUTE (

   'sys.myclass1', 'logging_level', DBMS_SCHEDULER.LOGGING_FAILED_RUNS);

END;

You must be granted the MANAGE SCHEDULER privilege to set the logging level of a job class.

Medhod 1:

Re-execute the query once again to check the performance of the query.

---------------------------------------------------------------------------
Medhod 2:

Check whether is there any locks on the table or not

 1. select username, sid from v$session where username like 'HR';

2.  select sid, name, value from v$sysstat, v$sesname where name like 'Application wait time';
SID   NAME                     VALUE
41    Application wait time    73520
54    Application wait time      0

3. select sid, event, time_waited from V$session_event
SID    EVENT                TIMED_WAITED
41     TX-row lock          85505

4. same as v$session_wait
it used for currently waiting.

5. select blocking_session from v$session where sid= 41;
BLOCKING_SESSION
54

6. select row_wait_obj# OBJECT_ID, row_wait_file# RELATIVE_FILE, row_wait_block# BLOCK_NUMBER
from v$session where sid=54;
OBJECT_ID   RELATIVE_FILE BLOCK_NUMBER
73520            5                      207

7. select object_name, object_type, object_id from dba_objects where object_id=73520;

8. select sid, osuser, mechine, terminal, logon_time, sql_id,, serial#, username
from v$session where username like 'HR';

9. select sql_fulltext from v$sql where sql_id=41;

---------------------------------------------------------------------------
Medhod 3:

Collect the statistics for a object may solve the Problem.

Statistics Gathering Procedures in the DBMS_STATS Package

Procedure	Collects
GATHER_INDEX_STATS	Index statistics
GATHER_TABLE_STATS	Table, column, and index statistics
GATHER_SCHEMA_STATS	Statistics for all objects in a schema
GATHER_DATABASE_STATS	Statistics for all objects in a database
GATHER_SYSTEM_STATS	CPU and I/O statistics for the system

--------------------------------------------------------------------------
Medhod 4:

Examining an explain plan lets you look for throw-away in cases such as the following:

• Full scans
• Unselective range scans
• Late predicate filters
• Wrong join order
• Late filter operations

For example, in the following explain plan, the last step is a very unselective range scan that is executed 76563 times, accesses 11432983 rows, throws away 99% of them, and retains 76563 rows. Why access 11432983 rows to realize that only 76563 rows are needed?

Looking for Throw-Away in an Explain Plan

Rows      Execution Plan
--------  ----------------------------------------------------
      12  SORT AGGREGATE
       2   SORT GROUP BY
   76563    NESTED LOOPS
   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DT_ALL
   76570       INDEX RANGE SCAN (object id 178321)
   76563     TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WS_ALL
11432983      INDEX RANGE SCAN (object id 186024)

-------------------------------------------------------------------------

1. Take an explain plan for the query
TABLE ACCESS FULL or INDEX FASTFULL SCAN --> db file scattered read
INDEX ACCESS BY INDEX KEY  --> db file sequential read.

2. select owner, segment_name, p1, p2, p3 where dba_extents
where owner like 'HR';

3. Find problem SQL with explain plans. find a file
find_awr_sql.sql

---------------------------------------------------------------------------

1. select event, p1, p2, p3 from v$session_wait where
sid in (select sid from v$session where username = 'HR');

p1 is file# , p2 is block#

2. select name from v$datafile where file# = 8;

3. alter system dump datafile 8 block 16543;
means it will dump the trace to tracefile.

4. select spid from v$process where addr=
(select paddr from V$session where sid=
(select distinct sid from V$sesstat));
SPID
6028

5. show parameter dump
Goto tracefile location
find the tracefile name like xxxx6028.trc

-------------------------------------------------------------------------