Thursday, March 20, 2014

SQL monitor hidden parameters

Parameter session value instance value description
_sqlmon_binds_xml_format     default     default     format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan     80     80     Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines     300     300     Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time     60     60     Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold     5     5     CPU/IO time threshold before a statement is monitored. 0 is disabled

Monday, December 30, 2013

What happens when DATAFILE RESIZE?

There is a need to understand what will be the impact when resizing some 8000 plus datafiles consecutively.  After some search did some test like enabled 10046 trace and captured what will happen when there is a datafile resize.

Used tkprof to format and provided the output to customer explaining the behavior.

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

SQL> show user
SQL> oradebug setmypid
Statement processed.
SQL> alter session set tracefile_identifier='mytrace_10046';

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m;

Database altered.

SQL> ALTER SESSION SET EVENTS='10046 trace name context off';

Session altered.

SQL> oradebug tracefile_name

TKPROF Output.

alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\TESTDRP1.DBF' resize 150m

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.01       1.96          0          0          3           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       1.99          0          0          3           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                   31        0.03          0.10
  db file sequential read                         2        0.02          0.04
  Disk file operations I/O                        3        0.01          0.04
  Data file init write                            1        0.00          0.00
  db file single write                            1        0.00          0.00
  control file parallel write                     3        0.00          0.00
  rdbms ipc reply                                 1        0.21          0.21
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       10.41         10.41


Recommendation given ,
-- See the above test, when there is a resize it will go through all this events
-- This test is done on my local machine where there is a very minimal load.
-- As it took 1.99s for a single file there will be more time considering your server.
-- Considering other factors like OS/ storage performance, it will take more time
-- Do it in a small batches and in a sequential manner. So that it will not harm database performance. 
-- Also it is best to test a small batch to estimate run  time in non business hours and proceed with the activity.

Thanks for reading, post your suggestions / comments on this. Happy reading.

how to filter records with symbol ~ (tilt)

There is one requirement for customer to select rows which has tilt at the beginning and at the end. After some searching found REGEXP_LIKE New Operator in 10G. Did some test case and seems so simple. Thought to post as I didn't get much detailed info on google.

Rows begins with ~
18:06:37 SQL> select * from test where REGEXP_LIKE (ID,'^~');


Rows begin with ~ and number and ~
18:09:55 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]~$');


Rows begin with ~ and any no. of digits and ends with ~
18:11:06 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]*~$');


{m} Matches exactly m times
Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A nonmatching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.
^-- beginning of line
$ End of line.

Rows begin with ~ and then 2 numbers and ends with ~
18:11:42 SQL> select * from test where REGEXP_LIKE (ID,'^~[0-9]{2}~$');


Thanks for reading, Please comment on post. Happy reading.


REGEXP_LIKE New Operator in 10G (Doc ID 255953.1)

Tuesday, October 29, 2013

Now 11g OCP certified


I have upgraded certification from 10g OCP to 11g OCP ...

Saturday, September 7, 2013

12c Cannot create service/ sid using ORADIM (DIM-00019: create service error O/S-Error: (OS 5) Access is denied.)

Creating new sid in windows using ORADIM throws the below error. After spending some research found the simple solution. From 12.1 oracle introduced owner for oracle home. See the below description from Doc. When calling oradim it will prompt for that OH owner name.

C:\Users\mmeerha>oradim -new -sid testdb12c
Enter password for Oracle service user:
DIM-00019: create service error
O/S-Error: (OS 5) Access is denied.

Starting with Oracle Database 12c Release 1 (12.1), Oracle Database supports the use of Oracle home user, specified at the time of installation. Oracle home user is the owner of Oracle services that run from Oracle home and cannot be changed post installation. On a system, different Oracle homes can share the same Oracle home user or use different Oracle home user names.

Oracle home user can be a Windows built-in account or a standard Windows user account (not an Administrator account). This account is used for running the Windows services for the Oracle home. For a database server installation, Oracle recommends that you use a standard Windows user account (instead of a Windows built-in account) as the Oracle home user for enhanced security.

For Oracle RAC Database, the Oracle home user must be a Windows domain user account and must be an existing Windows account.

Even when providing the correct password it throws the same error.

Then I realised to run cmd prompt as administrator.

Also Oracle Doc mentioned it
  • Windows Vista, Windows Server 2008, and Windows 7 require Administrator privileges to run ORADIM.

So what if I forget Oracle home user password, the sid will be created but it will not be started. See the below snaps

So how can I find who owns the Oracle home, and how to modify the password of it.

From windows registry HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDB12Home1

As other windows users, reset password on it and run as administrator to create Sid.

Note :
In 11g We will get different error in oradim when not run cmd prompt as administrator.

Happy reading :), post your comments if anything to improve.