In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual.
Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps.
Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.
Wednesday, November 27, 2013
How to identify processes that are consuming lots of CPU power
Here is one way to identify processes that are consuming lots of CPU on your sever that I have used successfully in the past.
First, use the mpstat command, which will print the CPU statistics n times with n seconds interval.
For example, mpstat 5 5
would yield the following output:
In the output sample above, 4 of the 5 samples have CPU 0 with
* a combined user time and system time at 100
and
* idle time at 0 (column headings usr, sys, idl).
This indicates that the CPU is completely consumed on this system.
After gathering the data from mpstat, which indicates that the system CPU resources are overtaxed, you can use prstat to identify which processes are consuming the CPU resources.
The prstat -s cpu -n 5 command is used to list the five processes that are consuming the most CPU resources.
* The -s cpu flag tells prstat to sort the output by CPU usage.
* The -n 5 flag tells prstat to restrict the output to the top five processes.
First, use the mpstat command, which will print the CPU statistics n times with n seconds interval.
For example, mpstat 5 5
would yield the following output:
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 1 0 0 345 224 589 220 0 0 0 799 29 1 0 70 0 1 0 0 302 200 752 371 0 0 0 1191 99 1 0 0 0 0 0 0 341 221 767 375 0 0 0 1301 100 0 0 0 0 0 0 0 411 256 776 378 0 0 0 1313 99 1 0 0 0 0 0 0 382 241 738 363 0 0 0 1163 97 3 0 0
In the output sample above, 4 of the 5 samples have CPU 0 with
* a combined user time and system time at 100
and
* idle time at 0 (column headings usr, sys, idl).
This indicates that the CPU is completely consumed on this system.
After gathering the data from mpstat, which indicates that the system CPU resources are overtaxed, you can use prstat to identify which processes are consuming the CPU resources.
The prstat -s cpu -n 5 command is used to list the five processes that are consuming the most CPU resources.
* The -s cpu flag tells prstat to sort the output by CPU usage.
* The -n 5 flag tells prstat to restrict the output to the top five processes.
prstat -s cpu -n 5 PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 25906 oracle 2692M 1912M cpu2 0 0 0:34:39 3.1% oracle/11 25472 oracle 133M 130M cpu1 0 0 0:00:47 0.4% emagent/7 8709 oracle 33M 19M cpu28 10 0 0:00:03 0.4% perl/1 19404 root 88M 24M sleep 21 0 141:23:44 0.3% vxconfigd/1 9767 root 5752K 5224K sleep 59 0 0:00:01 0.2% vmstat/1
How to find size, free space, used space, allocated space for the tablespaces in your database
SELECT
F.FILE_ID,
F.FILE_NAME,
ROUND(F.BYTES/1024/1024) "Allocated(MB)",
NVL(TO_CHAR(ROUND(F.BYTES/1024/1024 - SUM(E.BYTES/1024/1024),1)),'File full') "Used (MB)",
NVL(TO_CHAR(ROUND(SUM(E.BYTES/1024/1024),1)), '0') "Free (MB)",
LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Autoextend?",
LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM DBA_DATA_FILES F LEFT OUTER JOIN DBA_FREE_SPACE E
ON E.FILE_ID = F.FILE_ID
GROUP BY F.FILE_ID,F.FILE_NAME,F.BYTES,AUTOEXTENSIBLE,MAXBYTES
ORDER BY 1;
For a specific tablespace:
alter session set nls_language='american';
SET LINES 400
COL "File ID" format 999
COL "Tablespace name" format a20
COL "File name" format a50
COL "Available space (MB)" format 9999999999
COL "Free space (MB)" format a20
COL "Percentage used" format a20
COL "Max size (MB)" format a20
COL "Can file grow?" format a20
WITH free_spc AS (
SELECT FILE_ID, SUM(BYTES/1024/1024) AS MBFree
FROM DBA_FREE_SPACE fre
WHERE TABLESPACE_NAME = UPPER('&&tablespace')
GROUP BY FILE_ID
)
SELECT F.FILE_ID "File ID",
F.TABLESPACE_NAME "Tablespace name",
F.FILE_NAME "File name",
ROUND(F.USER_BYTES/1024/1024) "Available space (MB)",
NVL( TO_CHAR(ROUND(FS.MBFree)), 'NADA') "Free space (MB)",
LPAD(NVL(ROUND(( ROUND(F.BYTES/1024/1024 - FS.MBFree)/ROUND(F.BYTES/1024/1024) ) * 100),100),15,' ') || ' %' "Percentage used",
LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Can file grow?",
LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM DBA_DATA_FILES F LEFT JOIN free_spc FS
ON F.FILE_ID = FS.FILE_ID
WHERE F.TABLESPACE_NAME = UPPER('&&tablespace')
ORDER BY F.FILE_ID ASC;
How to compare the content of memory parameter and spfile parameters
### Compare static (pfile) and dynamic (spfile) parameter setting:
set pages 100
set lines 400
col init_value format a36
col spfile_value format a36
col name format a38
SELECT P.NAME, P.VALUE INIT_VALUE, S.VALUE SPFILE_VALUE
FROM V$PARAMETER P
JOIN V$SPPARAMETER S ON P.NAME=S.NAME
WHERE P.ISBASIC='TRUE'
ORDER BY NAME;
How to clean away control character from a text file
I found this little example on the internet and saved it as a shorthand - it has come in handy many times. Unfortunately I have forgotten the name of the original author.
When files are sent from windows to unix, you will sometimes see that the when you open the files in a unix text editor has ^M's in them.
For example
lines that have a ^M and no carrage return (and so blend into one). There are two steps to clean this up.
1. replace all extraneous ^M:
:%s/^M$//g
BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that have carriage returns
after them with nothing. (The dollar ties the search to the end of a line)
2. replace all ^M's that need to have carriage returns:
:%s/^M/ /g
Once again: BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that didn't have
carriage returns after them with a carriage return.
Voila! Clean file. Map this to something if you do it frequently.
When files are sent from windows to unix, you will sometimes see that the when you open the files in a unix text editor has ^M's in them.
For example
------------------------------------------------------------------ import java.util.Hashtable; ^M import java.util.Properties; ^Mimport java.io.IOException; import org.xml.sax.AttributeList; ^M import org.xml.sax.HandlerBase; ^Mimport org.xml.sax.SAXException; /**^M * XMLHandler: This class parses the elements contained^M * within a XML message and builds a Hashtable^M ------------------------------------------------------------------Notice that some programs are not consistent in the way they insert the line breaks so you end up with some lines that have both a carrage return and a ^M and some
lines that have a ^M and no carrage return (and so blend into one). There are two steps to clean this up.
1. replace all extraneous ^M:
:%s/^M$//g
BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that have carriage returns
after them with nothing. (The dollar ties the search to the end of a line)
2. replace all ^M's that need to have carriage returns:
:%s/^M/ /g
Once again: BE SURE YOU MAKE the ^M USING "CTRL-V CTRL-M" NOT BY TYPING "CARROT M"! This expression will replace all the ^M's that didn't have
carriage returns after them with a carriage return.
Voila! Clean file. Map this to something if you do it frequently.
How to write a procedure that lets developers set the time for the database using the FIXED_DATE procedure
The following procedure may be used to let designated database developers change the system time according to their testing needs.
Note the AUTHID clause in the package declaration:
A unit whose AUTHID value is CURRENT_USER is called an "invoker's rights" unit, or IR unit. A unit whose AUTHID value is DEFINER is called a "definer's rights" unit, or DR unit.
The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time. It will check:
• If the context for name resolution is CURRENT_SCHEMA.
• If the privileges checked are those of the CURRENT_USER and the enabled roles.
At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.
Author: Tomasz Gozdz
Documentation for the AUTHID DEFINER SETTING
Documentation for the FIXED_DATE procedure
CREATE OR REPLACE PACKAGE admin_pack AUTHID DEFINER
AS
PROCEDURE set_fixed_date(vv_fixed_date VARCHAR2);
PROCEDURE unset_fixed_date;
END admin_pack;
/
CREATE OR REPLACE PACKAGE body admin_pack
AS
PROCEDURE set_fixed_date (vv_fixed_date VARCHAR2) IS
BEGIN
IF UPPER(vv_fixed_date) = 'NONE' THEN
EXECUTE IMMEDIATE 'alter system set fixed_date=none';
ELSE
EXECUTE IMMEDIATE 'alter system set fixed_date=''' || vv_fixed_date || '''';
END IF;
END;
PROCEDURE unset_fixed_date IS
BEGIN
EXECUTE IMMEDIATE 'alter system set fixed_date=none';
END;
END admin_pack;
/
CREATE PUBLIC SYNONYM ADMIN_PACK FOR SYS.ADMIN_PACK;
GRANT EXECUTE ON ADMIN_PACK TO SCOTT;
Note the AUTHID clause in the package declaration:
A unit whose AUTHID value is CURRENT_USER is called an "invoker's rights" unit, or IR unit. A unit whose AUTHID value is DEFINER is called a "definer's rights" unit, or DR unit.
The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time. It will check:
• If the context for name resolution is CURRENT_SCHEMA.
• If the privileges checked are those of the CURRENT_USER and the enabled roles.
At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.
Author: Tomasz Gozdz
Documentation for the AUTHID DEFINER SETTING
Documentation for the FIXED_DATE procedure
Friday, November 22, 2013
What is the difference between BY ACCESS and BY SESSION in Oracle Auditing?
The difference between BY ACCESS and BY SESSION lies in how the DBA_AUDIT_TRAIL data dictionary view records the actions that they capture:
BY ACCESS: Inserts one record in the audit trail for each audited statement.
BY SESSION: Inserts only one audit record in the audit trail, for each user and schema object, during a session that includes an audited action.
Example:
First, enable auditing on the table TABLE1:
AUDIT SELECT, UPDATE,DELETE,INSERT ON HR.TABLE1 BY SESSION WHENEVER SUCCESSFUL;
Secondly, assuming there is activity against TABLE1, check the audit trail:
SELECT timestamp, os_username, obj_name,userhost, username,action,action_name, ses_actions
FROM dba_audit_trail
WHERE obj_name IN ('TABLE1')
AND username = 'HR'
ORDER BY timestamp DESC;
The query in my case returned the following:| TIMESTAMP | OS_USERNAME | OBJ_NAME | USERHOST | USERNAME | ACTION | ACTION_NAME | SES_ACTIONS |
|---|---|---|---|---|---|---|---|
| 04.07.2011 10:35:20 | joe | TABLE1 | laptop-joe | HR | 103 | SESSION REC | ---------S------ |
| 08.02.2011 18:11:10 | joe | TABLE1 | appserver1 | HR | 2 | INSERT | |
| 08.02.2011 18:11:10 | joe | TABLE1 | appserver1 | HR | 6 | UPDATE | |
| 08.02.2011 18:11:10 | jack | TABLE1 | appserver1 | HR | 7 | DELETE | |
| 08.02.2011 14:50:41 | tina | TABLE1 | workstation-tina | HR | 103 | SESSION REC | ---S--S---S----- |
For statements which are audited BY SESSION, the column SES_ACTIONS will indicate a pattern to view which operation we are talking about.
The 16 characters string in the SES_ACTION column represents different actions, one for each action type in the order (note that positions 14, 15, and 16 are reserved for future use):
* ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE.
The characters are:
* - - None
* S - Success
* F - Failure
* B - Both
Finally, interpret the audit trail:
The first row in the table indicates that a successfull SELECT (the S is found on position 10) was executed on TABLE1, 04.07.2011 10:35:20.
The last row indicates a successful DELETE, INSERT and UPDATE (the S is found in position 4, 7 and 11) in TABLE1 during the session that was audited 08.02.2011 14:50:41.
The three rows where ACTION_NAME is INSERT, UPDATE and DELETE are self-explanatory.
At the tail of the AUDIT clause comes the directive WHENEVER [NOT] SUCCESSFUL which means that oracle should audit only SQL statements and operations that succeed, fail or result in errors. If you omit this clause, then Oracle Database performs the audit regardless of success or failure.
Subscribe to:
Posts (Atom)