FlimaTech Blog

Below are the 2 SQL statements, the first one uses the index AAA_IDX and then second one does a full table scan and in this case we want to make the first one that uses the index use the same execution plan as the 2nd one.

SQL statement:
select * from aaa e where ename = 'aaa';
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

By query v$sql we found the sql_id, child_number and plan_hash_value also checked there isn't a sql_profile attached to the SQL.

SQL> select sql_id, child_number, sql_profile, plan_hash_value, sql_text from v$sql where sql_id IN ('63cg18v928540', '0tjtg6yqqbbxk');

SQL_ID CHILD_NUMBER SQL_PROFILE PLAN_HASH_VALUE
————- ———— ————————————————- —————
SQL_TEXT
————————————————-
63cg18v928540 0 2022030255
select * from aaa e where ename = 'aaa'

0tjtg6yqqbbxk 0 864433273
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

Displaying the execution plan of the 2nd SQL with the outline option
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('0tjtg6yqqbbxk', 0, 'outline'));
PLAN_TABLE_OUTPUT
——————————————————————————–

SQL_ID 0tjtg6yqqbbxk, child number 0
————————————-
select /*+ FULL(e) */ * from aaa e where ename = 'aaa'

Plan hash value: 864433273

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Outline Data
————-

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
FULL(@”SEL$1″ “E”@”SEL$1″)
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ENAME”='aaa')

From the outline generated used the hint to add to first SQL to change its execution plan i.e. full table scan.
define SQL_ID = '63cg18v928540';

DECLARE
clsql_text CLOB;
BEGIN
SELECT sql_fulltext INTO clsql_text FROM V$sqlarea where sql_id = '&SQL_ID';
dbms_sqltune.import_sql_profile(sql_text => clsql_text,
profile=> sqlprof_attr('FULL(@SEL$1 E@SEL$1)'),
name=>'PROFILE_&SQL_ID',
force_match=>true);
end;
/

Shows now when we execute the SQL it uses the same execution plan as the 2nd one i.e. full table scan
SQL> select * from aaa e where ename = 'aaa';
Execution Plan
———————————————————-
Plan hash value: 864433273

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| AAA | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ENAME”='aaa')

Note
—–
– SQL profile “PROFILE_63cg18v928540″ used for this statement

Following example shows what happens when locks are disabled on the TABLE.

-- disable lock on a table
SQL> alter table t disable table lock;
Table altered.

-- shows one can’t drop table as table locks are disable
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- shows one can’t truncate table as table locks are disable
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- DML is okay to run
SQL> delete from t;
1 row deleted.

-- new columns can be added
SQL> alter table t add tt varchar2(1);
Table altered.

-- columns can’t be modified
SQL> alter table t modify tt varchar2(10);
alter table t modify tt varchar2(10)
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- columns can’t be dropped
SQL> alter table t drop column tt;
alter table t drop column tt
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- enable locking on the table
SQL> alter table t enable table lock;
Table altered.

-- table now can be dropped
SQL> drop table t;
Table dropped.

Using strace one can trace the system calls being executed by a running process. To stop the strace press control-C

To display the system calls being executed
$ strace -p <pid>

or

To display a summary of system calls being executed
$ strace -cfo <logfile> -p <pid>

Example: In this example smon process was being traced
$ strace -p 1234
Process 1234 attached – interrupt to quit
getrusage(RUSAGE_SELF, (ru….
getrusage(RUSAGE_SELF, (ru….
Pressed <control-C>

$ strace -cfo smon_strace.log -p 1234
Process 1234 attached – interrupt to quit
Process 1234 detached
Pressed <control-C> to detach.

$ cat smon_strace.log
% time seconds usecs/call calls errors syscall
—— ———– ———– ——— ——— —————-
nan 0.000000 0 22 getrusage
—— ———– ———– ——— ——— —————-
100.00 0.000000 22 total

Using the following query one can see the flashback data available.

SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME,
(sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;

CURRENT_TIME OLDEST_FLASHBACK HIST_MIN
—————- —————- ———-
2012-04-25 07:34 2012-04-25 05:48 106.066667

Using sys.dbms_scheduler.STOP_JOB one can stop scheduled job.

SQL> exec sys.dbms_scheduler.STOP_JOB(job_name=>'SYS.ORA$AT_OS_OPT_SY_12856', force=>true);

Output:
PL/SQL procedure successfully completed.

If it can’t find the job then you may see the following error, in the example below I hadn’t specified the user to it was looking for the job as the current user.
ORA-27475: “DBAUSER.ORA$AT_OS_OPT_SY_12856″ must be a job
ORA-06512: at “SYS.DBMS_ISCHED”, line 199
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 557
ORA-06512: at line 1

To see jobs running http://oraclespin.wordpress.com/2010/05/08/dba_scheduler_running_jobs/

Few examples of some scenarios of setting intervals for dba_jobs. Note: Updating the interval will not update the NEXT_DATE run of the job until the job runs.

Setting a job to run every monday at 10:00 am
SQL> exec dbms_job.interval(5, 'TRUNC(NEXT_DAY(SYSDATE, ''MONDAY''))+(10/24)');
SQL> commit;

– assume job was run and this SQL was run after it was run
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 19-MAR-2012 10:00:00

Setting a job to run every hour on the 1/2 hour
SQL> exec dbms_job.interval(5, 'TRUNC(SYSDATE, ''HH'')+(1.5/24)');
SQL> commit;

Setting a job to run every hour after the last time the job was started
SQL> exec dbms_job.interval(5, 'SYSDATE+(1/24)');
SQL commit;
– assume job was run and this SQL was run after it was run, the time NEXT_DATE would be a hour after the job was last started.
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 15-MAR-2012 22:45:00

DBMS_JOB allows one to create/manage jobs under user who has logged but using sys.dbms_ijob one can manage jobs all jobs scheduled in DBA_JOBS.

Here are some of the functions available in sys.dbms_ijob.
To execute/run job: You don’t have to be an owner of the job
SQL> exec sys.dbms_ijob.run(5);

If one tried executing the job not owned by the user using DBMS_JOB, one can’t run it and you will get the following error “ORA-23421: job number 21 is not a job in the job queue”

To enable/disable job:
-- to disable a job. Note one should set this when the job is not running by checking DBA_JOBS_RUNNING, because if the job is running you will have to wait till it finishes before disabling it and it has to be followed by a commit.
SQL> exec sys.dbms_ijob.broken(5, true);
SQL> commit;

-- Note the next_date value it initialized when the job is broken.
SQL> select broken, next_date from dba_jobs where job = 5;

B TO_CHAR(NEXT_DATE,’D
- ——————–
Y 01-JAN-4000 00:00:00

-- to enable a job, note it has to be followed by a commit and after the job is enabled the NEXT_DATE is updated based on the value of interval.
SQL> exec sys.dbms_ijob.broken(5, false);
SQL> commit;

SQL> select broken, next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
- ——————–
N 11-MAR-2012 19:31:45

To change the next date the job runs, again note if the job is running when one modifies this, it will get reset to the next interval instead of the value specified.
SQL> exec sys.dbms_ijob.next_date(5, sysdate + 1);
SQL commit;

SQL> select next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
- ——————–
N 12-MAR-2012 19:41:27

To change the interval
Example below changes the interval to run every Monday at midnight
SQL> exec sys.dbms_ijob.interval(5, 'TRUNC(NEXT_DAY(sysdate, ''MONDAY''))');
PL/SQL procedure successfully completed.
SQL> commit;

By querying v$database one can tell if the host is primary or standby

For primary
testdb> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

For Standby – Note you may need to connect to as sys as sysdba if the instance is in mount state
testd> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

OR
On Primary database the value of controlfile_type in V$database is “CURRENT” and standby is “STANDBY”

Value on Primary:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
CURRENT

Value on Standby:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
STANDBY

If you have an issue where the server where standby resides had to be restarted and when you try to enable Fast Start Failover, it wouldn’t enable due to ORA-16608 and when checking the primary database in dataguard it would return ORA-16817 “unsynchronized fast-start failover configuration”

Example:
$ dgmgrl /
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration verbose
Configuration
Name: FSF
Enabled: YES
Protection Mode: MaxAvailability
Databases:
testdb_a – Primary database
testdb_b – Physical standby database

Fast-Start Failover: DISABLED
Current status for “FSF”:
Warning: ORA-16608: one or more databases have warnings

So upon checking both the databases, the primary would report the following warning ORA-16817 and Standby was fine.
DGMGRL> SHOW DATABASE VERBOSE testdb_a
..
Current status for “testdb_a”:
Warning: ORA-16817: unsynchronized fast-start failover configuration.

Also then checking the primary it returned the following:
On Primary:
SQL> select PROTECTION_LEVEL, FS_FAILOVER_STATUS from v$database;
PROTECTION_LEVEL FS_FAILOVER_STATUS
——————– ———————-
RESYNCHRONIZATION UNSYNCHRONIZED

And checking V$archived_log

select * from (
select sequence#,dest_id,first_time,archived,applied,completion_time, name
from v$archived_log
where applied = ‘YES’
order by first_time desc
);

SEQUENCE# DEST_ID FIRST_TIM ARC APPLIED COMPLETIO NAME
———- ———- ——— — ——— ——— ———————-
…
15321 2 03-MAR-12 YES NO 03-MAR-12 testdb_b.amazon
15321 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15321-1.arc
15320 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15320-1.arc
15319 2 03-MAR-12 YES NO 03-MAR-12 testdb_b.amazon
15319 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15319-1.arc
15318 2 03-MAR-12 YES YES 03-MAR-12 avm1na_b.amazon
15318 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15318-1.arc
..

If you see above there is a gap for redo log sequence# 15320 and due to which primary is in the Resynchronization state and when one does a logfile switch on primary, it transmits the redo log to standby and standby applies the archive log (by monitoring the alert log of standby).

So fix this issue, resetting the value of log_archive_max_processes would fix the synchronize issue i.e. update FS_FAILOVER_STATUS to SYNCHRONIZED and update the protection_level in v$database.

SQL> show parameters log_archive_max_processes;
log_archive_max_processes integer 4

SQL> alter system set log_archive_max_processes=1;
System altered.

SQL> alter system set log_archive_max_processes=4;
System altered.

By setting the value of 0 to the parameter “job_queue_processes” one can suspend all jobs from executing in DBA_JOBS. The value of this parameter can be changed without instance restart.

-- see current value of the parameter
SQL> show parameter job_queue_processes;

NAME TYPE VALUE
———————————— ———– ———–
job_queue_processes integer 1000

-- Set the value of the parameter in memory, which will suspend jobs from starting
SQL> alter system set job_queue_processes=0 scope=memory;

System altered.