Example of bulk collect and performance using different values of limit
Sample code that shows using BULK COLLECT and performance of using different values for LIMIT.
declare
cursor l_cur is select * from scott.emp;
type emp_tbl is table of l_cur%rowtype index by pls_integer;
l_emp emp_tbl;
limit_in number;
i number;
begin
limit_in := &limit_param;
open l_cur;
loop
fetch l_cur bulk collect into l_emp limit limit_in;
for i in 1..l_emp.count
loop
null;
end loop;
exit when l_emp.count < limit_in;
end loop;
close l_cur;
end;
Limit value 1: 25.76 seconds
Limit value 10: 03.54 seconds
Limit value 100: 01.28 seconds
Limit value 1000: 00.95 seconds
Limit value 2000: 01.00 seconds
Limit value 3000: 01.03 seconds
Limit value 5000: 01.06 seconds
Limit value 10000: 01.20 seconds
As you notice in the case above as one increases the value of LIMIT after 1000, the execution time increases slightly. The following was timed using “set timing on” on SQL*Plus.
How to set vim/gvim on windows?
Using the following steps one can setup vim/gvim to run on windows.
1) Add the following new variable VIM on your system. Example: VIM=C:\apps\vim\vim73 (Where C:\apps\vim\vim73 is where vim/gvim is installed)
2) Copy the file %VIM%\vimrc_example.vim as _vimrc in %VIM% folder
3) Add the following 2 lines at the end in %VIM%\_vimrc so it sets up vim for windows
source $VIM/mswin.vim
behave mswin
Example of using tracefile_identifier
Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user.
Example: The following trigger will create a trace file with the user’s OSuser and machine part of the filename for any user who login with “SCOTT”
CREATE OR REPLACE TRIGGER sys.session_trace_on
– to be created by sys user
AFTER LOGON ON database
DECLARE
v_machinename VARCHAR2(64);
v_ora_username VARCHAR2(30) DEFAULT NULL;
v_os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE sid = userenv('sid')
and username = 'SCOTT';
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_os_username, v_machinename, v_program;
IF c1%FOUND THEN
— DBMS_SESSION.set_sql_trace (TRUE);
v_machinename := replace(replace(v_machinename, '\', '_'), '/', '_');
v_os_username := replace(replace(v_os_username, '\', '_'), '/', '_');
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''' || trim(v_os_username) || '''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
CLOSE c1;
END;
/
Trace file created:
ls testdb_ora_5480_CAL-AMIN_Administrator.trc
How to add a comment when changing a parameter?
In “ALTER SYSTEM” one can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record why parameter was changed.
Example:
SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ';
SQL> select value, update_comment from v$parameter where name = 'open_cursors';
VALUE
——————————————————————————–
UPDATE_COMMENT
——————————————————————————–
400
$ strings spfileTESTDB.ORA | grep open_cursors
*.open_cursors=400#07-Jan-2012 Changed AJ needed for application XYZ
How to add a new not null column in a table without providing a default value?
The following shows how to add in a table a new column that is NOT NULL when a table already has rows without providing a default value.
SQL> create table scott.testn ( x number not null);
Table created.
SQL> insert into scott.testn values ( 11 );
1 row created.
SQL> commit;
Commit complete.
-- First step add new column without the NULL constraint
SQL> alter table scott.testn add nn number;
Table altered.
-- Then modify the column to be NOT NULL using NOVALIDATE
SQL> alter table scott.testn modify nn not null novalidate;
Table altered.
-- Shows the table doesn't show the NOT NULL constraint on the column NN yet
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– ———————
X NOT NULL NUMBER
NN NUMBER
-- As shown above even though not shown the NOT NULL constraint is there as new values cannot be inserted in the table that are NULL
SQL> insert into scott.testn values (12, null);
insert into scott.testn values (12, null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SCOTT”.”TESTN”.”NN”)
-- Now we can enable the constraint by updating the rows that have NULL value so we need to find the constraint name
SQL> select column_name, constraint_name from dba_cons_columns where table_name = 'TESTN' and owner = 'SCOTT';
COLUMN_NAME CONSTRAINT_NAME
——————– ——————————
X SYS_C005261
NN SYS_C005262
-- Rows update
SQL> update scott.testn set nn = 1 where nn is null;
1 row updated.
SQL> commit;
Commit complete.
--
SQL> alter table scott.testn modify constraint SYS_C005262 validate;
Table altered.
-- Shows the NOT NULL constraint on the column NN
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– —————————-
X NOT NULL NUMBER
NN NOT NULL NUMBER
-- shows that one can't add a new column not null as table is not empty
SQL> alter table scott.testn add mm number not null;
alter table scott.testn add mm number not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
-- shows it can't add a new column not null with novalidate as table is not empty
SQL> alter table scott.testn add mm number not null novalidate;
alter table scott.testn add mm number not null novalidate
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
How to use PRAGMA EXCEPTION_INIT?
PRAGMA EXCEPTION_INIT – allows one to map ORA- error and it can be raised in PL/SQL code. The SQL Error number passed in “EXCEPTION_INIT” is the same as error code except for “NO_DATA_FOUND” ORA-01403 which is 100.
See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#BABGIIBI – Summary of Predefined PL/SQL Exceptions
Example:
declare
no_rows_found exception;
pragma exception_init(no_rows_found, 100);
begin
raise no_rows_found;
end;
/
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
declare
too_many_rows exception;
pragma exception_init(too_many_rows, -1422);
begin
raise too_many_rows;
end;
/
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
How to fix “Error accessing PRODUCT_USER_PROFILE”?
When logging in SQL*PLus if one receives the following message.
SQL> connect scott/tiger
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
This can be fixed by executing as SYSTEM
SQL> @?/sqlplus/admin/PUPBLD.SQL
..
And after the script is run reconnect as the user.
SQL> connect scott/tiger
Connected.
How to schedule a snap for STATSPACK?
One can automate taking of snapshots of STATSPACK by executing DBMS_JOB or by scheduling it through crontab.
Schedule automatic STATSPACK through DBMS_JOB – By executing @?/rdbms/admin/spauto.sql it will schedule to run statspack once every hour which is the default.
Output:
SQL> connect perstat@TESTDB
Password: **********
SQL> @?/rdbms/admin/spauto.sql
PL/SQL procedure successfully completed.
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
———-
2
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter – the value for this parameter must be greater
than 0 to use automatic statistics gathering:
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
———- ——— ——–
2 27-DEC-11 19:00:00
One can view the job by executing the following SQL.
SQL> select * FROM User_jobs WHERE job = 2;
JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- -
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/24,'HH')
statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1
To change the snap interval one can execute dbms_job.interval in the example below it changes to collect every 1/2 hr
SQL> exec dbms_job.interval(1, 'trunc(SYSDATE+1/48,''HH'')');
PL/SQL procedure successfully completed.
SQL> select * FROM User_jobs;
JOB LOG_USER PRIV_USER
———- —————————— ——————————
SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
—————————— ——— ——– ——— ——– ———
NEXT_SEC TOTAL_TIME B
——– ———- -
INTERVAL
——————————————————————————–
FAILURES
———-
WHAT
——————————————————————————–
NLS_ENV
——————————————————————————–
MISC_ENV INSTANCE
—————————————————————- ———-
1 SYS SYS
SYS 27-DEC-11
19:00:00 0 N
trunc(SYSDATE+1/48,'HH')
statspack.snap;
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC
Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LAN
GUAGE='AMERICAN' NLS_SORT='BINARY'
0102000202000000 1
To schedule it through crontab: One would need to invoke the package “statspack.snap” via SQL*Plus
How to delete or truncate Statspack data?
One can truncate data in STATSPACK or delete range of snaps stored.
To truncate STATSPACK data
Using $ORACLE_HOME/rdbms/admin/sptrunc.sql, one can truncate STATSPACK data. To run the script you will need to connect as PERFSTAT user.
perfstat@TESTDB> @?/rdbms/admin/sptrunc.sql
Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.
About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. &'exit&'), otherwise if you would like to begin
the truncate operation, press
Enter value for begin_or_exit:
To delete STATSPACK data – This will prompt for starting snap id and end snap id.
One can run the following SQL that will show all the snap ids on the current instance.
select s.snap_id
, to_char(s.snap_time,&' dd Mon YYYY HH24:mi:ss&') snap_date
, s.baseline
, s.snap_level #level#
, di.host_name host_name
, s.ucomment
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database v
, v$instance i
where s.dbid = v.dbid
and di.dbid = v.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
order by di.db_name, i.instance_name, s.snap_id
/
SNAP_ID SNAP_DATE B level
———- ——————— – ———-
HOST_NAME
—————————————————————-
UCOMMENT
——————————————————————————–
1 27 Dec 2011 18:30:56 5
MACHINE
2 27 Dec 2011 18:30:58 6
MACHINE
How to gather stats without histograms?
When passing method_opt as 'FOR ALL COLUMNS SIZE 1', it means no histogram is collected for columns on the table(s).
exec dbms_stats.gather_schema_stats('SCOTT', method_opt=>'FOR ALL COLUMNS SIZE 1');
One can find the default value by executing as follows:
select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
