To output in real time or not?

Just yesterday I encountered a wrong conclusion based on a misunderstanding about the use of the dbms_output.put_line function.  The writer of a SQL script that spooled its output to a log file used the function to generate ‘start’ and ‘end’ messages within a PL/SQL block and was confused when the ‘start’ message was not seen in the log file while the main section of the PL/SQL block was executing.  He was under the assumption that calls to this function were carried out immediately.

This is a common misconception regarding dbms_output.put_line.  This example of the same functionality they were attempting reinforces this conclusion.

set serveroutput on

spool ./output.log

begin

  dbms_output.put_line('Start - '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));

  execute immediate 'select * from tab';

  dbms_lock.sleep(30);

  dbms_output.put_line('End - '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
  
end;
/

spool off

In this example, the spooling of the output is started inside the PL/SQL block.  This code will execute for about 30 seconds because of the dbms_lock.sleep function call.  During execution, the output.log file will be empty until the code finishes, either successfully or unsuccessfully, and ‘spool off’ is executed.  You can verify this with a ‘tail -f’ command in Linux/Unix or the ‘type’ command in Windows.

What happens if you place the ‘start’ and ‘end’ messages outside the PL/SQL block?

set serveroutput on

spool ./output.log

select 'Start - '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
from dual;

begin

  execute immediate 'select * from tab';

  dbms_lock.sleep(30);
  
end;
/

select 'End - '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')
from dual;

spool off

Even though you see the ‘start’ message at the onset in your SQL session, the log file will still not be populated until the PL/SQL block execution is finished and the ‘spool off’ command runs.

If you truly want to see the ‘start’ message before ANY code executes, then using OS commands to start spooling a log file and generate the ‘start’ message is probably your only alternative.  Here is an example for a Linux-based system:

echo "Start - $(date)" > ./output.log

This command, executed before the script is called, will create the log file and write a ‘start’ message to the file with the current date and time in the current directory.  This information will show up in the log file immediately.   Next, call the script to be executed:

$ORACLE_HOME/bin/sqlplus -S username/password @script.sql >> ./output.log

This command will start a SQL*Plus session in silent mode (-S), suppressing header and prompt output, call the script.sql script, and send the output to the log file opened earlier.  Using this method, you don’t need to use any ‘spool’ commands in the script.  Notice I used “>>” to redirect the output in this command and not “>” for the command when I opened the log file.  The “>>” redirect appends to an existing file, opening it if it does not exist.  If you use “>” for the redirect, an existing file will be overwritten and you will lose any information previously written to the file.

For a Windows-based system, the command is almost the same:

echo Start - %date% %time% > ./output.log

This command, executed before the script is called, will create the log file and write a ‘start’ message to it with the date and time in the current directory.  This information will show up in the log file immediately.  The “>>” and “>” redirect options even work the same as a Linux/Unix environment.

From here there a few different ways to execute a SQL script from a Windows command prompt.  If this is going to be an ongoing process in Windows, then I recommend writing the scripts in Perl.  It will save time, especially if you have to port the code between Windows and Linux/Unix environments.

One last point.  You should not use this ‘start’ and ‘end’ message method in a production system.  While it is fine for time tracking while testing the script, it should not be relied on outside of a test environment.  Even when these messages are sent to a log file, that file needs to be parsed for error messages at the conclusion of the process.  A better solution is to send the messages to a table used for auditing and monitoring.  Just don’t forget to commit each time you send a message to the table and remember to purge the information from the table based on audit requirements.

 

 

Advertisements

Get in the Penalty Box! (Redux version)

Note – The commands and processes listed have been tested and verified in a Oracle 11g release 2 (11.2.0.4) environment.

Sometimes, there exist database users with no regard for what type of queries they run or how long they run for.  These users typically kick off these ill-behaved queries and then go for coffee, lunch, or leave for the day.  Their query sits out there spinning and consuming resources that you know could be better used elsewhere.  This can be especially problematic in a production system if end-of-the-month aggregation processes or reports are being executed at the same time.

Maybe you have talked to them before about this behavior or even helped them tune a query or two, but they don’t seem to get the message.  In extreme cases, these users even terminate sessions when the query does not come back in a timely manner only to reconnect and retry it in a new session, leaving the old one (possibly) still executing.

It’s time to let these users know that their behavior is not acceptable in an environment where resources are finite and they need to share with other higher priority processes.  They need to spend some time in the penalty box.  For those unfamiliar with this term, it is a term used in ice hockey where misbehaving players have to sit out the game in a segregated area for a certain length of time, depending on the seriousness of their misdeeds.  The team with the misbehaving player is not allowed to send in a substitute player during the penalty time, so it leaves them one player short and vulnerable to be scored against.

The Oracle Resource Manager is the keeper of the database penalty box.  It has the ability to enforce limits on resources such CPU and I/O consumption, as well as execution time limits.  We are going to use this last feature to create our own penalty box.  I realize that most database managers don’t want to put constraints on users, especially developers, but in all honesty, if they are writing queries that take an hour or more to execute on a regular basis, then the managers need to put their foot down and the users or developers may need a remedial SQL tuning course.

A common misconception about resource plans is that they will limit all users in the database.  While it is true that only one resource plan can be active at any time, users can be segregated into consumer groups under the plan and each consumer group can be tailored with the use of directives to make sure that users or applications do not suffer due to the actions of a few users.

Here is a simple process to create a resource plan that will limit execution time to 60 seconds.  This limit is just for testing purposes as even I have been known to write queries that run for over a minute ;).  Administering the resource manager requires the ADMINISTER_RESOURCE_MANAGER privilege, go figure.  A SYSDBA user has this privilege already, but if you need to assign the privilege to a non-SYSDBA user, you can use this command (as a properly privileged user):

exec dbms_resource_manager_privs.grant_system_privilege( -

grantee_name => '<USER_NAME>', -

admin_option => true);

Check for a currently active resource plan:

SQL> show parameter resource_manager_plan

NAME                     TYPE     VALUE

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

resource_manager_plan   string

Since no resource plan is active, we can continue with the rest of the penalty box steps to create one.  Later on, we will look at our options if there is an actual resource plan already in place.

Create a new pending area for plan, consumer group, and directives creation.  The pending area is where our resource plan work will be kept until it is validated and submitted.

begin

 dbms_resource_manager.create_pending_area();

end;

/

After the pending area is created, if your session abruptly ends or you make a mistake, simply run this next command to clear the pending area and start again.

begin

 dbms_resource_manager.clear_pending_area();

end;

/

Create the penalty box resource plan:

begin

 dbms_resource_manager.create_plan(

  plan => 'PENALTY_BOX_PLAN',

  comment => 'Resource plan to limit execution time');

end;

/

Create the penalty box resource consumer group:

begin

 dbms_resource_manager.create_consumer_group(

  consumer_group => 'PENALTY_BOX_GROUP',

  comment => 'Resource consumer group to limit execution time');

end;

/

 

Create the penalty box resource plan directives.  Every resource plan must contain a directive to OTHER_GROUPS.  This consumer group contains all sessions that have not been assigned to a consumer group.  If you try to submit the resource plan without the OTHER_GROUPS option, you will get this error message when attempting to validate the pending area:

begin

*

ERROR at line 1:

ORA-29377: consumer group OTHER_GROUPS is not part of top-plan PENALTY_BOX_PLAN

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3640

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3691

ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3703

ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 746

ORA-06512: at line 2

 

As mentioned previously, we are setting an execution limit of 60 seconds.

begin

  dbms_resource_manager.create_plan_directive(

    PLAN=>'PENALTY_BOX_PLAN',

    GROUP_OR_SUBPLAN=>'PENALTY_BOX_GROUP',

    COMMENT=>'Kill statement after exceeding total execution time',

    SWITCH_GROUP=>'CANCEL_SQL',

    SWITCH_FOR_CALL=>TRUE,

    SWITCH_TIME_IN_CALL=>60,

    SWITCH_ESTIMATE=>false);

  dbms_resource_manager.create_plan_directive(

    PLAN=> 'PENALTY_BOX_PLAN',

    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',

    COMMENT=>'leave others alone',

    CPU_P1=>100);

end;

/

The CANCEL_SQL group that will be switched to when the SWITCH_TIME_IN_CALL of 60 seconds is reached is actually an internal call introduced in 11g that will just cancel the SQL.  The KILL_SESSION group can also be used, but that would be a bit extreme in this case.

Validate the pending area for the new plan, consumer group, and directives.  If there is a problem with the plan, it will appear during validation

begin

 dbms_resource_manager.validate_pending_area();

end;

/

 

Submit the pending area for plan, consumer group, and directives

begin

 dbms_resource_manager.submit_pending_area();

end;

/

 

Now we have to grant privileges in order to assign consumer groups to users.

Create a new pending area for the users we want to put in our newly-created penalty box:

begin

 dbms_resource_manager.create_pending_area();

end;

/

 

Grant the switch privilege for resource consumer groups to users or roles.  We need to execute this command for every user we want to be eligible to put into the penalty box.

begin

 dbms_resource_manager_privs.grant_switch_consumer_group(

   grantee_name => 'SCOTT',

   consumer_group => 'PENALTY_BOX_GROUP',

   grant_option => FALSE);

end;

/

 

Assign the desired users to the new resource consumer group

begin

 dbms_resource_manager.set_initial_consumer_group(

  user => 'SCOTT',

  consumer_group => 'PENALTY_BOX_GROUP');

end;

/

 

Validate the pending area before submitting it:

begin

 dbms_resource_manager.validate_pending_area();

end;

/

 

Submit the pending area:

begin

 dbms_resource_manager.submit_pending_area();

end;

/

 

Finally, specify the new plan to be used by the instance:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = PENALTY_BOX_PLAN;

System altered.

To clear the resource plan from the instance, use this command.  Note the command uses two single quotation marks, not a double-quote.  If you just want to switch to a different plan and not disable resource plans altogether, you can just supply the resource plan name within the quotation marks.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

Once our user’s behavior has been adjusted, we can remove them from the plan and put them back into their original plan:

begin

 dbms_resource_manager.set_initial_consumer_group(

  user => 'SCOTT',

  consumer_group => 'DEFAULT_CONSUMER_GROUP');

end;

/

 

To place other users in the penalty box, follow these abbreviated steps:

begin

 dbms_resource_manager_privs.grant_switch_consumer_group(

   grantee_name => 'USER',

   consumer_group => 'PENALTY_BOX_GROUP',

   grant_option => FALSE);

end;

/

begin

 dbms_resource_manager.set_initial_consumer_group(

  user => 'USER',

  consumer_group => 'PENALTY_BOX_GROUP');

end;

/

 

Now to test out our penalty box.  This query was executed as user SCOTT:

NOTE – This is my “hammer” query which will execute until it runs out of TEMP space:

set timing on

select distinct owner

from dba_constraints

where owner = (select owner

from dba_constraints

where constraint_type = 'C'

and owner in(select owner

from dba_constraints

where constraint_type = 'P'

and owner in(select owner

from dba_constraints

where constraint_type = 'R'

and owner in(select owner

from dba_constraints

where constraint_type = 'U'))))

/

If the penalty box is working properly, you should see this error if the query exceeds the time limit set in the resource plan directive.  The elapsed time output was the result of executing ‘set timing on’ to enable this SQL*Plus option.

ERROR at line 14:

ORA-00040: active time limit exceeded - call aborted

Elapsed: 00:01:31.20

Notice that the query did not end EXACTLY when the sixty-second time limit had been reached.  The time limit is an approximation.  The query will look to end after the time limit at the next convenient interrupt as determined by the database, much like times when you kill a database session and instead of ending immediately, it gets ‘marked for kill’ instead.  The database is waiting for the next convenient interrupt.  As a side note, you can add the word ‘immediate’ to the end of your ‘alter system kill session’ to speed things along.

If you want to make changes to the resource plan, consumer group, or resource plan directives, you can use the following commands.  Please note that you will need to create a pending area, execute the desired command, then submit the pending area in order for the command to take affect.

To remove a consumer group:

begin

DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP('PENALTY_BOX_GROUP');

end;

/

To remove a resource plan:

begin

DBMS_RESOURCE_MANAGER.DELETE_PLAN('PENALTY_BOX_PLAN');

end;

/

To remove a resource plan and all associated sub-plans and consumer groups:

begin

DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE('PENALTY_BOX_PLAN');

end;

/

To delete plan directives:

begin

DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (

plan=>'PENALTY_BOX_PLAN',

group_or_subplan=>'PENALTY_BOX_GROUP');

end;

/

 

In the event that there is an active resource plan already in place, there are two options to consider.  Either you can create a new consumer group and assign the offending user to the new group, or you can add a plan directive to the offending user’s assigned consumer group.

The DBA_RSRC_CONSUMER_GROUP_PRIVS view displays the consumer groups granted to users or roles. Specifically, it displays the groups to which a user or role is allowed to belong or be switched.  In the example shown below, user SCOTT always starts in the SALES consumer group (INITIAL_GROUP is YES) and can switch to the MARKETING group through a specific grant, and can also switch to the DEFAULT_CONSUMER_GROUP (OTHER_GROUPS) and LOW_GROUP groups because they are granted to PUBLIC.  SCOTT also can grant the SALES group but not the MARKETING group to other users (GRANT_OPTION is YES for SALES, NO for MARKETING).

SELECT * FROM dba_rsrc_consumer_group_privs;

GRANTEE            GRANTED_GROUP                  GRANT_OPTION INITIAL_GROUP
------------------ ------------------------------ ------------ -------------
PUBLIC             DEFAULT_CONSUMER_GROUP         YES          YES
PUBLIC             LOW_GROUP                      NO           NO
SCOTT              MARKETING                      NO           NO
SCOTT              SALES                          YES          YES
SYSTEM             SYS_GROUP                      NO           YES

Using this information, let’s look at our database after the PENALTY_BOX_PLAN has been created and enabled:

 

select *
from dba_rsrc_consumer_group_privs
order by grantee;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
PUBLIC                         LOW_GROUP                      NO  NO
SCOTT                          PENALTY_BOX_GROUP              NO  YES
SYSTEM                         SYS_GROUP                      NO  YES

Of the two options I mentioned earlier, it may be safer to create a new consumer group for the particular user with all the directives of their currently-assigned initial group.  The script in My Oracle Support (MOS) note 1388634.1 has the ability to reverse-engineer a complete resource plan including all included consumer groups.

With this information, you can create a new consumer group with the addition of the execution time limiting directive mentioned earlier and then assign it to the user as their initial consumer group.

The second option – adding the execution time limiting directive to their initial consumer group, is more risky because the directive would affect ALL the users assigned to that consumer group.

This post has been a small sampling of what the Oracle Resource Manager can do.  Over the years, I have seen nearly no shops where this feature was actively used.  Using this feature in non-production environment where resources are usually more limited can be beneficial.  I have also seen instances where using it in a production environment where some users are allowed to execute ad-hoc queries would have also removed some problems.

Enjoy!

Update 2/1/19

Upcoming Oracle version 19c takes the penalty box concept one step further:

Quarantine for SQL statements consuming excessive system resources

You can now configure quarantine for SQL statements terminated by the Resource
Manager due to their excessive consumption of system resources, such as CPU and I/O.
The quarantined SQL statements are not allowed to run again, thus protecting the
database from performance degradation.

The return of the Recyclebin

Last Friday, I received an email concerning a problem a DBA was having migrating objects from one tablespace to another.  They thought they had migrated all the objects, but they found some indexes when querying the DBA_SEGMENTS view.  These indexes were not visible in the DBA_INDEXES view, which was my first clue to the problem.  The next clue was when I found out all the indexes had a prefix of “BIN$”.  The DBA did not realize that these were objects that had been dropped previously and were sitting in the recyclebin.

The reyclebin  is a little-mentioned feature of the Oracle database that I blogged about back in 2011 when it saved me from an embarrassing and tricky recovery.  In honor of this unsung feature, I found the old blog, dusted it off, and updated it where needed.  Here we go…

What is recyclebin?

The Oracle recyclebin has been around since version 10g release 1 as part of a feature called Flashback Drop and in my opinion, continues to be a little-known and little-understood feature. It has saved me from potential data loss on at least one occasion, though, so I think it deserves some consideration.

The recyclebin is actually a data dictionary table containing information about dropped objects. Each named user account has their own recyclebin. Unless a user has the SYSDBA privilege, the only objects that the user has access to in the recyclebin are those that the user owns. A user can view his objects in the recyclebin using the following statement:

SELECT * FROM RECYCLEBIN;

When the recyclebin is enabled, dropped tables and any associated objects such as indexes, constraints, and nested tables are removed from the DBA_<objects> views that track database objects.  During this process, the table is renamed.  The new name has a telltale “BIN$” prefix placed on it.  These objects still occupy space in the tablespace where they originated. They continue to count against user space quotas, until purged from the recyclebin either manually or by the database because of tablespace space constraints.

When you drop an entire tablespace including its contents, the objects in the tablespace are NOT placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the dropped tablespace. The database also purges any recycle bin entries for objects in a tablespace you drop. Likewise:

  • When you drop a user, any objects belonging to the user are NOT placed in the recyclebin and any objects in the recyclebin owned by that user are purged.
  • When you drop a cluster, its member tables are NOT placed in the recyclebin and any former member tables in the recyclebin are purged.
  • When you drop a type, any dependent objects such as subtypes are NOT placed in the recyclebin and any former dependent objects in the recyclebin are purged.

Recyclebin Settings

In 10g release 1, the recyclebin was enabled by default and there was only an undocumented parameter to disable it.  This parameter was _recyclebin which defaulted to a value of TRUE.  The recyclebin initialization parameter was introduced in 10g release 2 and had a default setting of ON It was a dynamic parameter and could be enabled or disabled (ON or OFF) with either an ‘alter session’ or an ‘alter system’ command.  Oracle 11g release 1 made no changes to the basic feature, except to add the ‘deferred’ option to the ‘alter session’ command to allow subsequent, but not the current, session to be affected.  Release 2 remained unchanged from release 1.  These features remain unchained into 12c release 2.  Also, 12c includes more table-associated objects that is moves to the recyclebin when a table is dropped.  The full list of these objects are:

Nested tables

LOB segments

Indexes

Constraints (excluding foreign key constraints)

Triggers

Clusters

To find out if the recyclebin is enabled, except for 10g release 1, you can execute ‘show parameter recyclebin’ from a SQL prompt as a user with sysdba privileges.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

I would recommend keeping the recyclebin enabled at all times, except during an upgrade, to add an extra layer of recovery options to the existing environment.

Using Recyclebin

Once the recyclebin is enabled, you don’t have to give much thought to its use. Every time a ‘drop table’ command is executed, the table and its associated objects are placed in the recyclebin and renamed with a system-generated naming standard. Again, the objects actually remain in their native tablespaces.  Because objects age out of the recyclebin based on demands for space, it is always a good idea to back up a table with an export or execute create table as select (CTAS) to create a backup table before dropping it. This is especially true for dropping very large tables which may not fit in the recyclebin.

I was unable to find any information on exactly how much space is allocated to the recyclebin, but suspect that it can consume all available space in the respective tablespace where the objects reside.

To find out how much space per owner and tablespace recyclebin data is using, you can use this query:

select owner, tablespace_name, round(sum(bytes)/1024/1024/1024,1) "RECYCLE_GB"

from dba_segments

where segment_name like 'BIN$%'

group by owner, tablespace_name 

order by owner, tablespace_name;

In the event that you do not want a table to be preserved in the recyclebin, the addition of the ‘purge’ option at the end of the ‘drop table’ statement ensures that the recyclebin bin is bypassed and the table as well as the associated objects are dropped from the database.  Just ensure you have a backup of the table, just in case.

 

Recovering tables from the recyclebin

To recover a table from the recyclebin, you need to know if it is available. Use the following query to get this information (the owner was designated to limit the output):

select owner, object_name, original_name, droptime

from dba_recyclebin

where owner = ‘CMN’;




OWNER                          OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

CMN                            BIN$o5lg4yWjH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:12:42

CMN                            BIN$o5lg4yWiH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT_PK              2011-05-18:23:12:42

CMN                            BIN$o5icV6rtByXgRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:22:51:07

CMN                            BIN$o5icV6rfByXgRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:22:17:45

CMN                            BIN$o5lg4yWvH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:15:11

CMN                            BIN$o5icV6rgByXgRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:22:49:58

CMN                            BIN$o5mU5fo6KF/gRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:23:30:15

CMN                            BIN$o5icV6ruByXgRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:22:53:23

CMN                            BIN$o5mU5foqKF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:27:15

CMN                            BIN$o5mU5fo5KF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:30:08

CMN                            BIN$o5mU5fo4KF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT_PK              2011-05-18:23:30:08

CMN                            BIN$o5mU5fpGKF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:32:02

CMN                          BIN$o5mU5fpHKF/gRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:23:32:02

Here you can see which tables for the CMN user are in the recyclebin and when they were dropped.  You may have noticed that there are several versions of the BASE_CONTACT table in this recyclebin differentiated by their droptime value.

To also help determine which table holds the data you want recovered, you can query recyclebin tables like regular tables.

 select count(*)

from cmn."BIN$o5mU5fpGKF/gRAAUT0rhuQ==$0";




  COUNT(*)

----------

        89

It is important to use both the owner identifier (CMN in this case) and double-quotation marks because of the special characters in the recyclebin table name.

Once the table you want to recover has been identified, there are a few different methods by which you can recover the table or the individual data. The ‘flashback table <table_name> to before drop’ command will recover the latest copy of the table in the recyclebin. You can also specify the ‘rename to <alt_table_name>’ at the end of the command to recover the table to a different table name to recover specific data. In the case where there are several copies of the table in the recyclebin and you are not interested in the latest copy, you can do a simple create table as select (CTAS) operation using the recyclebin table name, not the original name. Remember to use double-quotation marks around the recyclebin table name due to the special characters.

When you restore a table from the recyclebin, dependent objects such as indexes DO NOT get their original names back; they retain their system-generated recyclebin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object’s system-generated recyclebin name before you restore the table.

Purging the recyclebin

You can purge the recyclebin completely or for objects belonging to individual users. As stated earlier, each user account in the database will automatically have a recyclebin assigned to it. A user can simply execute the ‘purge recyclebin’ command to purge their own recyclebin. Users with the sysdba privilege can purse the entire recyclebin with the ‘purge dba_recyclebin’ command. Be careful, though, as this command also purges ALL users recyclebins as well. Purging the entire reyclebin should be reserved for upgrade situations, or unless otherwise directed by Oracle Support.

You can also perform selective purges of the recyclebin as a sysdba-privileged user. For example ‘purge tablespace tmpdata’ only purges those objects in the recyclebin belonging to the tmpdata tablespace regardless of which user owns them. Also, ‘purge tablespace tmpdata user jon’ will purge only those objects in the recyclebin belonging to the tmpdata tablespace owned by the named user jon. You can also purge individual tables from the recyclebin with the command ‘purge table <table_name>’. The table name in this command can be either the system-generated recyclebin name or the original name of the table.  As of Oracle version 12c release 2, you need to purge individual user’s objects out of the recyclebin by tablespace as a sysdba-privileged user.

The recyclebin is one of those features that you forget, but are very thankful when it is available and you need it.

Don’t modify your spfile (unless you are sure)

I have seen it twice in the past week, so I need to get the word out.  You need to test parameter changes BEFORE modifying your spfile.  In at least one of the cases I have seen in the past week, the DBA created a backup of their spfile before modifying it.  This is smart, but they did not realize they could use this backup copy for testing changes.

The environment was an Oracle 11g release 2 (11.2.0.4) three-node RAC system.  The DBA needed to change a non-modifiable (non-dynamic) parameter and restart the database to make the change permanent.  They created a copy of the spfile, which is always a GREAT idea before modifying it, brought down the three instances, but was unable to bring them back up due to a problem with the parameter setting.  Now the spfile is bad and the database won’t start.  Now they need to recreate the spfile with the backup and start the process over.

What they could have done is make the parameter changes to the spfile backup and try to start one more of the instances using it instead of the spfile. In that case, if the parameter setting is bad, the spfile is unaffected and corrections can be made more swiftly with a regular editor.

In order to start the instance with the backup spfile (pfile), this command could have been used while connected to one of the instances through SQL*Plus as SYSDBA:

startup pfile=<path_and_name_of_pfile>

Once the instance starts with the parameter change, you can shut it down, modify the spfile by either changing the parameter with an ‘alter system’ command or simply replacing the spfile with the contents of the modified pfile with the ‘create spfile from pfile’ command and you are done.

Enjoy!

 

Database ‘shutdown immediate’ is slow or hangs

I was contacted by a DBA on a project I am on and they told me that a database they had executed a ‘shutdown immediate’ command on had been hanging for 20 minutes without any progress.

This problem is typical in an environment where a remote application maintains a pool of connections to the database.  It is these remote connection processes that are preventing the database from closing immediately.

I have not seen this issue in an Oracle-on-Windows environment, so I will just concentrate on resolving this issue in a Unix-type environment.  First, you need to see if you do indeed have remote connections:

$ >ps -ef | grep -v grep | grep LOCAL=NO
 oracle 29257 18960   0 05:31:03 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13255 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 29187 18960   0 05:31:01 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13277 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29283 18960   0 05:31:07 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13285 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 29191 18960   0 05:31:02 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29290 18960   0 05:31:18 ?     0:05 oracleDUMMY (LOCAL=NO)
 oracle 29182 18960   0 05:31:00 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13269 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13249 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 13251 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29288 18960   0 05:31:17 ?     0:17 oracleDUMMY (LOCAL=NO)
 oracle 29292 18960   0 05:31:18 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29281 18960   0 05:31:07 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13283 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29285 18960   0 05:31:07 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 17496 18960   0 10:27:26 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 13267 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 13288 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 10164 18960   0 12:41:18 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle  3579 18960   0 09:00:08 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 12425 18960   0 12:56:18 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13275 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 29185 18960   0 05:31:01 ?     0:00 oracleDUMMY (LOCAL=NO)

Now that we know there are remote connections, they need to be removed so the database can continue the shutdown process.  It would take some time to issue ‘kill -9’ commands individually for each of these processes, so I came up with method to kill them automatically.  You just need to isolate the OS process for each remote connection:

$ >ps -ef | grep -v grep | grep LOCAL=NO | awk '{print $2}'
 29257
 13255
 29187
 13277
 29283
 13285
 29191
 29290
 29182
 13269
 13249
 13251
 29288
 29292
 29281
 13283
 29285
 17496
 13267
 13288
 10164
 3579
 12425
 13275
 29185

Once we isolate the OS process ID’s, we can plug them into a command that will loop through and perform a ‘kill -9’ on each:

for i in $(`ps -ef | grep -v grep | grep LOCAL=NO | awk '{print $2}'`)
 do
 kill -9 $i
 done

The command will execute immediately after pressing <ENTER> after the word ‘done’.  I gave this process to the DBA who contacted me and it resolved their issue.  Try this the next time your database will not shutdown immediately.

Generating test data sets with Swingbench

Reading about new features is great, but finding data to test them out on is  sometimes troublesome.  As DBAs, we’ve created test databases countless times, but the majority of the time they are empty.

Oracle does provide sample schemas that can be installed automatically by using the database configuration assistant (DBCA) when creating a database or manually by executing scripts under the Oracle software home directory.  Consult the documentation of your particular version for more details.  While these sample schemas do provide a framework that mimics a common layout, they lack the volume of data that typical production systems have.  If your employer allows “sandboxes” to be created to test new versions and features then you can stop reading here because you are one of the fortunate ones.  However, if you do not have this opportunity but would still like to create a sizeable data set to work with, there is a utility called Swingbench that fills this need very well.

Swingbench was created by Dominic Giles (http://dominicgiles.com/index.html) as an Oracle database load generator, but has since become a very useful suite of tools including Data Generator, Trace Analyzer, and several monitoring tools.  Best of all, these tools are free to use.  I would recommend that you provide a small donation if you find them useful, though.

Quoting the creator, “Data Generator is a utility to populate, create, and load tables with semi random data. Users can specify sequences, random text, random numbers, database columns, and files as sources for tables.”  This sounds pretty good.

I recently downloaded Swingbench version 2.5 which includes the Data Generator utility and worked with it on a Oracle VirtualBox virtual machine running Oracle Enterprise Linux (OEL) version 6 update 6 with and Oracle version 12.1.0.2 non-container database.  Here are the steps I followed to create my test data set:

  • Swingbench requires Java 8 JDK.  Download the jdk-8u111-linux-x64.rpm and install it (as the root user) with the command: “rpm -Uvh jdk-8u111-linux-x64.rpm”. Note that this file is for the 64-bit version of OEL.
  • Download the swingbench25971.zip file for version 2.5 from the website mentioned previously and place it in a location where the oracle user account has read, write, and execute privileges.
  • Unzip the swingbench25971.zip file.  it creates a “swingbench” directory in this location
  •  As the oracle user, set the following environment variables if they are not already set (actual values depend on your environment):
    • JAVA_HOME=/usr/bin/java
    • PATH=$JAVA_HOME/bin:$PATH
  • Go to the swingbench/bin directory.  You have the option to create the sales history schema (shwizard), the calling center schema (ccwizard), or the order entry schema (oewizard).
  • Once you execute the desired wizard file, the interface appears

dg1

 

  • After clicking Next, you have the option to create or drop your desired schema.

dg2

  • For the connection string, use //<servername>/<database_sid>. Make sure you can ping the host by the <servername> used and that there is a listener present providing service for the <database_sid>.  Leave the connection type as the default (jdbc thin).  Leave DBA username as ‘sys as sysdba’ and make sure the password is correct.  Press Next to connect.

dg3

  • If the connection is successful, you will see the next screen where you can choose the username, password, schema tablespace, and tablespace datafile of your data set schema.  I like to keep my data separated for easier management, so I elect to create a separate tablespace and datafile.

dg4

  • The next screen allows you to choose database options such as partitioning (range or none), compression (yes or no), tablespace type (bigfile or normal), and indexing (all, primary key, or none).  Be careful here.  Do not select options that you are not licensed for in systems that are eligible for license audits.

dg5

  • Sizing options come next.  I don’t suppose I need to tell you not to size your schema any bigger than you have space available.  What is good here is that two counters at the bottom will let you know the size of the schema tablespace and the size of the temp tablespace required based on your selection.  Click next when you are done.

dg6

  • Finally, select the level of parallelism and click finish to create your schema.

dg7

  • The wizard log popup appears and the main wizard updates to tell you the progress of the creation process

dg10

  • Once the build is over, you should see a “successful” message popup window.  Click Ok to acknowledge the message and close the window.  You then have the option to review the wizard log in the wizard log window or save it to a file.  Close both the wizard log and main wizard windows after you are done.

dg8

At this point, you have the option to generate other data sets if you want to.  Enjoy.

 

Sequences are not gap free!

mind the gap

Yesterday I encountered a trace file on a “poorly-performing” process with some interesting waits.  The trace was incomplete because the trace process was started once the process was identified as “poorly-performing”.  Even so, it was interesting.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     78      0.03       0.05          0        106        106          53
Fetch       25      0.01       0.03          0         75          0          25
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.05       0.08          0        181        106          78


********************************************************************************
Trace file: PROD2_ora_2406.trc
Trace file compatibility: 11.1.0.7
Sort options: exeela  fchela  prsela  
       1  session in tracefile.
       0  user  SQL statements in trace file.
       2  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
  384089  lines in trace file.
    1507  elapsed seconds in trace file.

 

Ok, we have 1507 seconds of tracing, or slightly over 25 minutes.  Of that, only .08 seconds are accounted for between the recursive (system) and non-recursive (user) statements.  Where did the time go?  There were no event waits under the non-recursive statements section, but the wait events for the recursive statements was very telling.  Note that this is not a complete listing, I just filtered out any waits that did not add up to more than 1 second.

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  enq: SV -  contention                      289471        0.48       1210.91
  gc current grant 2-way                      22811        0.13         17.21
  db file sequential read                     57139        0.53         83.05
  gc current block 2-way                       2094        0.02          1.94

We now see that about 1211 seconds, or about 20 minutes of the trace, was spent on “enq: SV – contention”.  But what is that?  This event is sequence value (SV) contention and appears when there is a high demand for sequence values across multiple instances in a RAC environment.  I typically see “log file sync” waits that point to sequence contention issues in non-RAC environments.

When sequences are created they have a default cached value of 20.  This means that every time a sequence is accessed, the next 20 values are cached in memory for quick access.  This value needs to be much higher when the sequence is used to generate values for inserts when hundreds of thousands or millions of records are inserted in one process or routine.

I always recommend increasing the size of the sequence cache values when I see these type of situations, but I am often met with one of two very weak arguments.  One is that the client does not want to have to recreate the sequences to increase the cache value while the other is that the client wants to keep the cache small in order to avoid gaps in sequence values.  Let’s take these arguments one at a time.

First,  altering the sequence cache value dynamically has been available since Oracle database version 10g release 1.  In fact, there is a quote in the Oracle 10g release 1 SQL reference guide which states “Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.”  The rest of this argument is disproved by creating a test sequence and changing the cache value with a simple “alter sequence” command.

The second argument is the most exasperating for me because the client usually won’t budge from their position.  Although the client is correct when they state that a smaller cache size reduces the risk of sequence value gaps, they are mistaken when they assume that a reduced cache will ensure no sequence gaps.

If you are relying on sequences to generate primary keys for newly-inserted records, there is NO guarantee that there will be no sequence value gaps.  Even Tom Kyte is in agreement here (https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:29059659958024).

The closest you can get to no gaps using a sequence in this fashion is creating the sequence with the “INCREMENT BY 1”,  “NOCACHE”, and “ORDER” options together.  There will be a MAJOR performance cost using a sequence in this fashion for generating values during large insert activity.  Even with these values set, there is still the possibility of sequence value gaps.  Here is a version 11.2.04 demonstration of that fact:

SQL> create table jontab2 (jonkey number, jonstring varchar2(20));

Table created.

SQL> create sequence jonseq start with 1 increment by 1 nomaxvalue nocycle order nocache;

Sequence created.

SQL> insert into jontab2 values(jonseq.nextval, 'String');


1 row created.

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

<I crash the instance here by killing the PMON process>

SQL> insert into jontab2 values(jonseq.nextval, 'String');
insert into jontab2 values(jonseq.nextval, 'String')
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 3657
Session ID: 125 Serial number: 7

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


<I restart the instance and login>

[oracle@rac1 ~]$ sqlplus jontest/semperf1

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 4 09:10:01 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into jontab2 values(jonseq.nextval, 'String');

1 row created.

SQL> commit;

Commit complete.

SQL> select *
  2  from jontab2
  3  order by jonkey;

    JONKEY JONSTRING
---------- --------------------
         1 String
         2 String
         9 String                        --------GAAAAAAP!!!!!!!

SQL> select jonseq.nextval
  2  from dual;

   NEXTVAL
----------
        10

 

Because I did not commit some newly-inserted records before the instance crashed, they were not written to the table even though the sequence incremented and created a gap in the key values of the table.  Even if you have a batch process that commits after every record inserted, you can’t account for instance crashes.

If you STILL intend to use a sequence alone to generate a “no gap” primary key, what happens if you have to delete records from the table?  Guess what, you now have gaps in your primary key values.  What are you going to do, re-index all the remaining records to remove the gaps?  I doubt it.  The best bet is to use the sequence and one or more other values in the table, perhaps even a timestamp, as the basis for a primary key value.  This way, you can increase the sequence cache and still have solid primary keys.  You just need to get over the “no gap sequence values” idea.