 |
Oracle DBA Tips
As of January 2008, the Pipelines will no longer publish monthly tips. However, we welcome and will post user-submitted tips on this page. Please send your tips to webmaster@quest-pipelines.com. Our archive of monthly tips will be maintained, so feel free to browse them below. The Database "Role" Does Matter This tip was inspired by a real world scenario involving the execution of a function and the creation of a sequence. A few days back, I was working with my teammate on a test database to create a small Oracle procedure that creates a sequence using "EXECUTE IMMEDIATE". What happened next was totally unexpected: create or replace procedure prc_create_seq
as
v_seqname varchar2(15) :='seq_students';
n1 number;
begin
EXECUTE IMMEDIATE 'CREATE SEQUENCE' ||' '||v_seqname||'
'||'increment by 1 start with 1 maxvalue 1500';
for i in 1..2 loop
EXECUTE IMMEDIATE 'select seq_students.nextval from dual' INTO n1;
dbms_output.put_line(n1);
end loop;
end;
/
.... Error Message ......
I checked for the roles that this user had. This user had a number of privileges as well as the execute procedure privilege. The sequence creation works now: create or replace procedure prc_create_seq
as
v_seqname varchar2(15) :='seq_students';
n1 number;
begin
EXECUTE IMMEDIATE 'CREATE SEQUENCE' ||' '||v_seqname||'
'||'increment by 1 start with 1 maxvalue 1500';
for i in 1..2 loop
EXECUTE IMMEDIATE 'select seq_students.nextval from dual' INTO n1;
dbms_output.put_line(n1);
end loop;
end;
/
Procedure created.
execute prc_create_seq;
PL/SQL procedure successfully completed.
show user
USER is "TEST_USER"
And why this works can be seen in the following: /export/home/oracle> sys SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jan 5 16:13:49 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production TIME --------------------------------------------------------------------------- 05-JAN-07 04.13.49.424044 PM +05:30 SQL> ! stty erase ^? SQL> grant create sequence to test_user ; Grant succeeded. So to summarize, if create sequence and similar permissions are given through roles, it won't work. But once you grant a direct privilege, it works. This is the same for other database objects such as procedures, functions, etc. as in this case: create or replace procedure prc_create_tab 2 as 3 v_tabname varchar2(50) := 'ITEM_DETAILS' ; 4 n1 number ; 5 begin 6 EXECUTE IMMEDIATE 'CREATE TABLE '|| v_tabname ||' '|| '(ITEM_ID NUMBER(10) )' ; 7 END; 8 / Procedure created. SQL> SHOW ERR No errors. SQL> EXECUTE prc_create_tab ; BEGIN prc_create_tab ; END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "TEST_USER.PRC_CREATE_TAB", line 6 ORA-06512: at line 1 SQL> EXECUTE prc_create_tab ; PL/SQL procedure successfully completed. SQL> GRANT CREATE TABLE TO test_user ; Grant succeeded. Previous Tips of the Month 2007 Tips:
January's Tip of the Month Script to Show Accounts Unused for X Days Here's a script that shows accounts and number of days since last use. It assumes session auditing is enabled. ------------------------------------------------------------------------ -- -- File: audit_unused_accounts.sql -- -- Description: Check for open accounts that haven't been used in X days. -- -- Parameters: 1 - Number of days of inactivity for which to check (input). -- ------------------------------------------------------------------------ column status format a16 SELECT RPAD(da.username, 12) "Username", TRUNC(SYSDATE - MAX(da.timestamp)) "Days Inactive", LPAD(du.account_status, 16) "Status", LPAD(TO_CHAR(MAX(da.timestamp), 'DD-MON-YYYY'), 16) "Last Login" FROM dba_users du, dba_audit_session da WHERE da.action_name LIKE 'LOG%' -- AND da.username NOT IN ('SYS','SYSTEM') -- itemize accounts to exclude -- AND du.profile != '' -- or profiles to exclude AND du.username = da.username -- AND du.account_status = 'OPEN' -- could look at just OPEN if desired GROUP BY da.username, du.account_status HAVING MAX(da.timestamp) <= SYSDATE - &1 ORDER BY 2,1 DESC For example, to show accounts which haven't been used for at least 30 days: SQL> @audit_unused_accounts 30 It produces results like this: Username Days Inactive Status Last Login ---------------- ------------- ---------------- ---------------- ALPHA 34 OPEN 17-MAY-2006 BETA 89 OPEN 23-MAR-2006 GAMMA 97 OPEN 15-MAR-2006 DELTA 118 OPEN 22-FEB-2006 EPSILON 118 EXPIRED(GRACE) 22-FEB-2006 PHI 126 OPEN 15-FEB-2006 ETA 134 OPEN 07-FEB-2006 February's Tip of the Month Subtle Stats Collection Just the smallest changes in how you work can have a significant impact on how your optimizer plans work out: SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
SQL> drop table T;
Table dropped.
SQL> create table T (
2 x varchar2(20) , y varchar2(100));
Table created.
SQL> insert into T
2 select 'x' , rpad('z',100) from all_objects;
50033 rows created.
SQL> insert into T
2 select 'X' , rpad('z',100) from all_objects;
50033 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
PL/SQL procedure successfully completed.
SQL> create index TX on T ( upper(x) ) ;
Index created.
SQL> exec dbms_stats.gather_index_stats(user,'TX')
PL/SQL procedure successfully completed.
SQL> explain plan for select * from T where upper(x) = :b1
2 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 22936165
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1014 | 101K| 189 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1014 | 101K| 189 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | TX | 405 | | 182 (0)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("X")=:B1)
14 rows selected.
Now we change from "gather table stats, create index, gather index stats" to "create index, gather table stats, gather index stats" and watch what happens: SQL>
SQL> drop table T;
Table dropped.
SQL> create table T (
2 x varchar2(20) , y varchar2(100));
Table created.
SQL> insert into T
2 select 'x' , rpad('z',100) from all_objects;
50035 rows created.
SQL> insert into T
2 select 'X' , rpad('z',100) from all_objects;
50035 rows created.
SQL> commit;
Commit complete.
SQL> create index TX on T ( upper(x) ) ;
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_index_stats(user,'TX')
PL/SQL procedure successfully completed.
SQL> explain plan for select * from T where upper(x) = :b1
2 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98835 | 9941K| 337 (3)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 98835 | 9941K| 337 (3)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("X")=:B1)
13 rows selected.
The secret is when hidden column statistics are calculated. In the first example above, the table column statistics look like this: COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------------------ ------------ ------------ ------------ ---------- X 2 58 78 .5 Y 1 7A2020202020 7A2020202020 1 SYS_NC00003$ So the all important stats on the hidden column are missing. Whereas in the second example, the table column statistics look like this: COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------------------ ------------ ------------ ------------ ---------- X 2 58 78 .5 Y 1 7A2020202020 7A2020202020 1 SYS_NC00003$ 1 58 58 1 March's Tip of the Month Getting Information About Previously Run Commands with statspack When you setup statspack with level 6 you can have information on the sql_plan associated with a sql (statspack on Oracle Version 9i and later). To set up Level 6: exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');
EXECUTE STATSPACK.SNAP(i_snap_level=>6); <-- this is used if you want a one shot.
EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level => 6);
When you run the statspack report (spreport) script you will get information like: CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
299,980 11 27,270.9 33.5 6.51 7.68 2676219584
SELECT * FROM (SELECT /*+ ALL_ROWS MeasurementApprovalOptimizedV
O */ T0.OL_SAMPLE_ID AS F0, T0.OL_ORDINV_ID AS F1, T0.OL_MSR_STA
TUS AS F2, T0.OL_QCCID_ID AS F3, T0.OL_MSR_SUPPLIER_SMP_ID AS F4
, T0.OL_MSR_SUPPLIER_TEST AS F5, T0.OL_MSR_TIME AS F6, T0.OL_MSR
_RECEIVED_TIME AS F7, T0.OL_SPL_ID AS F8, T0.OL_MSR_IS_CONTROL A
Notice the value of Old Hash Value. The value of Old Hash Value can be used to get more information about the sql, when running the script $ORACLE_HOME/admin/rdbms/sprepsql.sql as user perfstat. SQL>connect perfstat/perfstat SQL>@?/rdbms/admin/sprepsql ..... Now comes the normal list of statspack reports. 35 06 Feb 2007 12:11 5 36 06 Feb 2007 12:13 6 37 06 Feb 2007 12:14 6 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 36 Begin Snapshot Id specified: 36 Enter value for end_snap: 37 End Snapshot Id specified: 37 Specify the old (i.e. pre-10g) Hash Value ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for hash_value: 2676219584 <-- this was the value seen in the statspack report. Hash Value specified is: 2676219584 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_36_37_2676219584. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: Using the report name sp_36_37_2676219584 STATSPACK SQL report for Old Hash Value: 2676219584 DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ---------------- PLAB 113542438 PLAB 1 10.1.0.4.0 NO p570b1 Start Id Start Time End Id End Time Duration(mins) --------- ------------------- --------- ------------------- -------------- 36 06-Feb-07 12:13:47 37 06-Feb-07 12:14:19 0.53 SQL Statistics ~~~~~~~~~~~~~~ -> CPU and Elapsed Time are in seconds (s) for Statement Total and in milliseconds (ms) for Per Execute % Snap Statement Total Per Execute Total --------------- --------------- ------ Buffer Gets: 299,980 27,270.9 33.46 Disk Reads: 0 0.0 .00 Rows processed: 167 15.2 CPU Time(s/ms): 7 591.9 Elapsed Time(s/ms): 8 698.2 Sorts: 11 1.0 Parse Calls: 7 .6 Invalidations: 0 Version count: 3 Sharable Mem(K): 195 Executions: 11 SQL Text <-- Notice that full sql text is displayed ~~~~~~~~ SELECT * FROM (SELECT /*+ ALL_ROWS MeasurementApprovalOptimizedV O */ T0.OL_SAMPLE_ID AS F0, T0.OL_ORDINV_ID AS F1, T0.OL_MSR_STA TUS AS F2, T0.OL_QCCID_ID AS F3, T0.OL_MSR_SUPPLIER_SMP_ID AS F4 , T0.OL_MSR_SUPPLIER_TEST AS F5, T0.OL_MSR_TIME AS F6, T0.OL_MSR _RECEIVED_TIME AS F7, T0.OL_SPL_ID AS F8, T0.OL_MSR_IS_CONTROL A S F9, T0.OL_MSR_ALARM_STATUS AS F10, T0.OL_MSR_CORRECTED AS F11, T0.OL_MSR_TUBE_POSITION AS F12, T0.OL_MSR_INT_COMMENT AS F13, T 0.OL_MSR_COMMENT AS F14, T0.OL_FRML_ID AS F15, T0.OL_MSR_FLAG_SU MMARY AS F16, T0.OL_MSR_FLAG_ACTION AS F17, T0.OL_MSR_RESULT AS F18, T0.OL_MSR_REPLY AS F19, T0.OL_MSR_CALCULATION_TYPE AS F20, T0.OL_RUN_ID AS F21, T0.OL_RUN2_ID AS F22, T0.OL_MSR_RESOLVED_SA MPLE_ID AS F23, T0.OL_INVER_ID AS F24, T0.OL_MSR_DOUBLE_MSR AS F 25, T0.OL_MSR_STATE AS F26, T0.OL_MSR_GM AS F27, T0.OL_MSR_SHOW_ INSTR_SAMPLE_ID AS F28, T0.OL_MSR_MEASUREMENT_SET AS F29, T0.OL_ MSR_ID AS F30, T0.TS AS F31, T0.OL_UNIT_ID AS F32, T0.OL_RM_ID A S F33, T1.OL_RUN_QC_FAILED AS F34, T1.OL_RUN_ACTION AS F35, T1.O L_RUN_START_TIME AS F36, T1.OL_RUN_STATUS AS F37, T1.OL_SPLINV_I D AS F38, T1.OL_RUN_ID AS F39, T1.TS AS F40, T2.OL_SOI_ID AS F41 , T2.OL_SAMPLE_SOI_TYPE AS F42, T2.OL_SAMPLE_NUMBER AS F43, T2.O L_SAMPLE_EXT_COMM AS F44, T2.OL_SAMPLE_INT_COMM AS F45, T2.OL_SA MPLE_START_TIME AS F46, T2.OL_HCPCDE_ID AS F47, T2.OL_SAMPLE_ID AS F48, T2.TS AS F49, T3.OL_QCCID_ID AS F50, T3.TS AS F51, T3.CA NCEL_TS AS F52, T4.OL_SOI_ID AS F53, T4.OL_SOITYPE_ID AS F54, T4 .OL_ORDINV_REPLY_WITHIN AS F55, T4.OL_ORDINV_EXPECTED_TIME AS F5 6, T4.OL_ORDINV_DEEPENING AS F57, T4.OL_MSR_ID AS F58, T4.OL_ORD INV_ALARM_STATUS AS F59, T4.OL_ORDINV_DEEP_DONE AS F60, T4.OL_OR DINV_REPLY_SEEN AS F61, T4.OL_ORDINV_LIST_ITEM AS F62, T4.OL_INV _ID AS F63, T4.OL_ACTSTATE_ID AS F64, T4.OL_ORDINV_ID AS F65, T4 .TS AS F66, T4.CANCEL_TS AS F67, T4.OL_PRIORITY_ID AS F68 FROM O L_MEASUREMENT T0 LEFT OUTER JOIN OL_RUN T1 ON T0.OL_RUN_ID=T1.OL _RUN_ID LEFT OUTER JOIN OL_SAMPLE T2 ON T0.OL_SAMPLE_ID=T2.OL_SA MPLE_ID LEFT OUTER JOIN OL_QC_CONTROL_ID T3 ON T0.OL_QCCID_ID=T3 .OL_QCCID_ID LEFT OUTER JOIN OL_ORDERED_INV T4 ON T0.OL_ORDINV_I D=T4.OL_ORDINV_ID WHERE T0.OL_WORKPLACE_ID = :1 AND T0.OL_SPL_I D IN (:2) AND T0.OL_MSRTYPE = :3 AND NOT (T0.OL_MSR_STATUS IN (: 4, :5, :6, :7, :8, :9, :10, :11, :12, :13)) AND (T0.OL_MSR_DOUBL E_MSR IS NULL OR T0.OL_MSR_DOUBLE_MSR=:14 OR T0.OL_MSR_DOUBLE_MS R = :15 AND (T0.OL_MSR_STATE IS NULL OR T0.OL_MSR_STATE=:16)) AN D (T0.OL_QCCID_ID IS NOT NULL OR T0.OL_ORDINV_ID IS NOT NULL OR T0.OL_MSR_STATUS IN (:17, :18, :19)) AND (T0.OL_RUN_ID IS NULL O R T1.OL_RUN_END_TIME IS NOT NULL) ORDER BY T0.OL_MSR_RECEIVED_TI ME, T0.OL_MSR_SUPPLIER_SMP_ID, T0.OL_MSR_MEASUREMENT_SET, T0.OL_ INVER_ID) FOO WHERE ROWNUM <= 1001 Known Optimizer Plan(s) for this Old Hash Value ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Shows all known Optimizer Plans for this database instance, and the Snap Id's they were first found in the shared pool. A Plan Hash Value will appear multiple times if the cost has changed -> ordered by Snap Id First First Plan Snap Id Snap Time Hash Value Cost --------- --------------- ------------ ---------- 36 06 Feb 07 12:13 3841969555 12 Plans in shared pool between Begin and End Snap Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Shows the Execution Plans found in the shared pool between the begin and end snapshots specified. The values for Rows, Bytes and Cost shown below are those which existed at the time the first-ever snapshot captured this plan - these values often change over time, and so may not be indicative of current values -> Rows indicates Cardinality, PHV is Plan Hash Value -> ordered by Plan Hash Value -------------------------------------------------------------------------------- | Operation | PHV/Object Name | Rows | Bytes| Cost | -------------------------------------------------------------------------------- |SELECT STATEMENT |----- 3841969555 ----| | | 12 | |COUNT STOPKEY | | | | | | VIEW | | 1 | 6K| 12 | | SORT ORDER BY STOPKEY | | 1 | 363 | 12 | | NESTED LOOPS OUTER | | 1 | 363 | 11 | | NESTED LOOPS OUTER | | 1 | 306 | 9 | | FILTER | | | | | | NESTED LOOPS OUTER | | 1 | 259 | 7 | | NESTED LOOPS OUTER | | 1 | 204 | 5 | | TABLE ACCESS BY INDEX RO|OL_MEASUREMENT | 1 | 189 | 4 | | INDEX RANGE SCAN |OL_MSR_SPL_FK | 1 | | 3 | | TABLE ACCESS BY INDEX RO|OL_QC_CONTROL_ID | 1 | 15 | 1 | | INDEX UNIQUE SCAN |OL_QCCID_PK | 1 | | 0 | | TABLE ACCESS BY INDEX ROW|OL_RUN | 1 | 55 | 2 | | INDEX UNIQUE SCAN |OL_RUN_PK | 1 | | 1 | | TABLE ACCESS BY INDEX ROWID|OL_SAMPLE | 1 | 47 | 2 | | INDEX UNIQUE SCAN |OL_SAMPLE_PK | 1 | | 1 | | TABLE ACCESS BY INDEX ROWID |OL_ORDERED_INV | 1 | 57 | 2 | | INDEX UNIQUE SCAN |OL_ORDINV_PK | 1 | | 1 | -------------------------------------------------------------------------------- End of Report SQL> If you run the sprepsql.sql script on level 5 reports you will only get the full sql text. So even if you don't get the explain plan, you will still be able to get more information about the sql when you use the script. April's Tip of the Month Understanding Asynchronous COMMIT Before Oracle 10g Release 2, the life cycle of a transaction was as follows:
The main point to understand here is that once a COMMIT is issued, Oracle does not return the control to the user until the redo entries corresponding to that commited transaction have been physically written to disk. This way applications can be 100% sure that once the transaction is commited, it is protected against instance failure and commited data can be recovered by "rolling forward" log files. In other words, we can call this synchronous COMMIT. Now asynchronous COMMIT. In Oracle 10g Release 2, with a very high speed transaction environment in mind, Oracle has introduced two options:
The full syntax of the new WRITE clause is: COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ] By default, if no WRITE clause is specified, a normal COMMIT is equivalent to: COMMIT WRITE IMMEDIATE WAIT; The COMMIT statement has a new clause, WRITE, which indicates these options. The default COMMIT statement is the same as: COMMIT WRITE IMMEDIATE WAIT; To specify that the transaction should be written individually but that the application should not wait for it, specify: COMMIT WRITE IMMEDIATE NOWAIT; or just COMMIT WRITE NOWAIT; Likewise, to specify that Oracle's log writer process (LGWR) is allowed to batch multiple transactions together before writing, specify: COMMIT WRITE BATCH NOWAIT; The option can also be set system-wide by modifying the commit_write instance parameter. Multiple options are separated by commas, as: ALTER SYSTEM SET commit_write = BATCH, NOWAIT; May's Tip of the Month BLOCK CHANGE TRACKING Once enabled, this 10g feature records the modified data since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks. Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file. To enable BLOCK CHANGE TRACKING: ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'c:\oracle\product\flash_recovery_area\ukoug\bctf01.log'; To verify the status of block change tracking: col filename format a60 SELECT filename, status, bytes FROM v$block_change_tracking; To disable block change tracking: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; SELECT filename, status, bytes FROM v$block_change_tracking; June's Tip of the Month Use Multiple Oracle Homes My favorite best practice is the one about multiple Oracle Homes. Here it how it goes. When applying a patch or a patchset, I recommend against applying to the existing Oracle Home. Instead, I suggest creating a new Oracle Home, and apply the patches there. I create the first Oracle Home at /app/oracle/db_1, for instance. When a patch comes out, I install the whole Oracle software in a different home -- /app/oracle/db_2 -- and then apply the patch there. During the process of installation and patch application, the database is up and running, as it runs off the home /db_1. When the outage window comes, all I have to do is to shut down Oracle; change Oracle Home to db_2 and bring the database up. If there is a problem, I can reset the Oracle Home back to the old one. So, here is the conventional approach:
Steps 2 and 6 could take as much as three hours depending on the amount of patching. The database is down during these times. In the new approach:
The database is down only during steps 4 and 8, which takes a couple of minutes at the most, not hours. So, here are the advantages:
The only negative part is the space consumption -- you need space for two Oracle Homes. But considering a typical Oracle Home takes about 4 GB or less, this aspect of the suggestion is trivial. July's Tip of the Month Good Practices for the Oracle Environment The first good practice around the environmental aspects of Oracle is to use Optimal Flexible Architecture (OFA) standards to lay out your database. This provides for a logical as well as a physical setup and should even be used under Stripe and Mirror Everything (SAME) setups. The OFA principles break down simply to: isolate redo, rollback, temp, data and index files as much as possible. For SAME configurations use the proper stripe width and depth or performance will suffer (it must be a factor of db_block_size*db_file_multiblock_read_count). The next critical environmental good practice is to verify proper operating system set points. For example, in Unix/Linux environments, set shared memory and semaphores properly. Shared memory segments should be set to at least the desired System Global Area (SGA) size + 10% but not more than total memory. Set semaphores to (n1 + n2 + n3...)+(2* nx) where n1...n3 are the semaphores for each instance on the box and nx is the largest value from any instance on the box. For Unix and Linux set swap area size to at least the size of the largest expected SGA or to a multiple of the size of physical memory. For Windows, set proper background/foreground settings and virtual memory, and set the server for application serving and not file serving. Another frequently overlooked good practice is to utilize file system tunings. Some examples of this are: use asynchronous I/O (where appropriate, verify OS and file system support this) and turn off write-ahead caching. Tied in with this is the good practice of reducing buffering at the OS level. Generally speaking the closer to the disk you can place the buffer (for read operations) the better off you are. With more and more shops running Linux on commodity-priced Intel boxes, you may also need to consider tuning the IDE interface (if you use IDE drives for data or temporary areas). On Linux the hdparm command is used to both view and set the IDE parameters and I have found that they are usually set inefficiently by default installations. With the environment comes the responsibility for such things as disk controllers. Disk controllers should utilize multiple channels and use channel load balancing, for example, use Veritas fastpath. As was said above, use proper stripe width and depth -- a good practice is to set stripe width (amount of each stripe on each disk) to at least db_block_size*db_file_multiblock_read_count. Set stripe depth (number of disks in the stripe set) to at least (expected maximum IO/sec)/90 for RAID1+0 or (expected maximum IO/sec)/50 for RAID5. The DBA should always review all provided disk array configurations for proper stripe width and depth. For example EMC has provided preconfigured arrays in the past set at 8K stripe width, but this size is not acceptable for Oracle applications. The environment extends beyond disks and memory. The connectivity to the outside world comes from the network. Use the fastest Network Interface Cards (NICs) possible. In order to gain from fast NICs, you must tune TCP buffers to attain maximum throughput; on Unix and Linux this is accomplished through setting the proper buffers (for example on Linux setting the values for /proc/sys/net/ipv4/tcp_wmem and /proc/sys/net/ipv4/tcp_rmem can make a significant difference in performance, setting as high as 4 meg are possible). The other side of this is tuning the Oracle packet sizes through the use of TDU and SDU settings. These can be set as high as 32K and can provide instant performance increases for data-intensive operations across the network. For Oracle RAC (which uses the Unsecure Data Packet protocol) tune the UDP buffers; these are set in a similar manner to the TCP buffers. You should also utilize the CLUSTER_INTERCONNET setting for multiple NIC card systems and RAC to ensure that the proper NIC is being utilized for the cluster interconnect. Some general good practices for the environment are:
August's Tip of the Month Enabling Archive Logs in a RAC Environment - (Oracle10g) Whether a single instance or clustered database, Oracle tracks (logs) all changes to database blocks in online redolog files. In an Oracle RAC environment, each instance will have its own set of online redolog files known as a thread. Each Oracle instance will use its set (group) of online redologs in a circular manner. Once an online redolog fills, Oracle moves to the next one. If the database is in "Archive Log Mode", Oracle will make a copy of the online redo log before it gets reused. A thread must contain at least two online redologs (or online redolog groups). The same holds true for a single instance configuration. The single instance must contain at least two online redologs (or online redolog groups). The size of an online redolog file is completely independent of another intances' redolog size. Although in most configurations the size is the same, it may be different depending on the workload and backup / recovery considerations for each node. It is also worth mentioning that each instance has exclusive write access to its own online redolog files. In a correctly configured RAC environment, however, each instance can read another instance's current online redolog file to perform instance recovery if that instance was terminated abnormally. It is therefore a requirement that online redo logs be located on a shared storage device (just like the database files). As already mentioned, Oracle writes to its online redolog files in a circular manner. When the current online redolog fills, Oracle will switch to the next one. To facilitate media recovery, Oracle allows the DBA to put the database into "Archive Log Mode" which makes a copy of the online redolog after it fills (and before it gets reused). This is a process known as archiving. The Database Creation Assistant (DBCA) allows users to configure a new database to be in archive log mode, however most DBA's opt to bypass this option. In cases like this where the database is in no archive log mode, it is a simple task to put the database into archive log mode. Note however that this will require a short database outage. From one of the nodes in the Oracle10g RAC configuration, use the following tasks to put a RAC enabled database into archive log mode. For the purpose of this article, I will use the node linux1 which runs the orcl1 instance:
After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs! September's Tip of the Month Sizing Your Undo Tablespace It is always a puzzle for a DBA to look into the user's complaint of getting "ORA-01555 Snapshot too old : rollback segment number x with name "_SYSSMUx$" too small " error. You have looked into the database. If your UNDO_MANAGEMENT is set to AUTO, you can not do anything to size the rollback segments manually since it is being managed by Oracle. All the associated tables and indexes have been analyzed and statistics are up to date. The undo tabelspace is almost full. You may advise the user that there should be frequent commits (if it is a data loading process) or if there is a long running query and other users change the data that is being selected by the query, this can happen and in that case, if possible, advise the user not to run these two at the same time. Well! No improvement in the situation though you made your effort as above. It is time to look into the issue from a different point. There may be other jobs running in the database which are taking up the undo space. The complaining user's session is not getting his share of undspace which he deserves. You have the remedy for that. Add more space to UNDO tablespace. But, how much? V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. This view is available in both automatic undo management mode and manual undo management mode. Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 1008 rows, spanning a 7 day cycle. With the help of this view (mainly) we can estimate the total undospace you require and expand it accordingly. Sizing an UNDO tablespace requires three pieces of data.
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24) Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT. The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds: SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat; Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day. The result of the query returns the number of undo blocks per second. This value needs to be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE. The following query calculates the number of bytes needed: SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), (select block_size as DBS from dba_tablespaces where tablespace_name= (select value from v$parameter where name = 'undo_tablespace')); Follow the output as guideline to size your undo tablespace. References used:
October's Tip of the Month Avoid Updating the Entire Table when Adding a Column Default If you add a column with a default, Oracle dutifully goes back and updates every single existing row. (Which might include a nice trip to row migration heaven!) Can we avoid this? Yes, just don't do it "all at once"... SQL> create table T
2 as select * from all_objects;
Table created.
SQL> alter table T add NEW_COL1 number default 10;
Table altered.
Elapsed: 00:00:02.08
SQL> alter table T add NEW_COL2 number;
Table altered.
Elapsed: 00:00:00.02
SQL> alter table T modify NEW_COL2 default 10;
Table altered.
Elapsed: 00:00:00.03
SQL> select count(new_col1), count(new_col2)
2 from T;
COUNT(NEW_COL1) COUNT(NEW_COL2)
--------------- ---------------
50199 0
Elapsed: 00:00:00.05
November's Tip of the Month CR/LF Added to Binary File When Using UTL_FILE.PUT_RAW Two new procedures: UTL_FILE.PUT_RAW and UTL_FILE.GET_RAW were added in Oracle9i to read/write binary files. (The UTL_FILE package in 8i only works with text files.) I noticed an error with the UTL_FILE.PUT_RAW procedure while attempting to write a BLOB value to a binary file. But, when the file is closed using UTL_FILE.FCLOSE, <CR> + <LF> is added in the end, which I didn't want because it was originally a binary IMAGE (.GIF) file. This is a documented bug (Bug:2546782 - UTL_FILE.PUT_RAW WRITES WRONG NUMBER OF BYTES) It has
been fixed in 10i. The new file open modes like "RB", "WB", "AB" are introduced. If the binary file
is opened in these modes then the December's Tip of the Month Oracle 11g Password Features Oracle 11g introduces several new features related to password security. It's important to be aware of these new features and enhancements, for they can have potential upgrade impacts. New features and enhancements include:
Password Settings and the Default Profile If a specific profile is not assigned to a given database user, then the default profile will be assigned to that user. The password related settings for the default profile have changes in Oracle 11g as shown in the following table:
You can see these new settings by issuing the following query: SELECT profile, resource_name, limit FROM dba_profiles WHERE profile='DEFAULT'; The setting of the password_life_time parameter can have implications when you upgrade to Oracle 11g. Once the upgrade is complete Oracle will start enforcing the password lifetime of 180 days, rather than the previous default of unlimited. You will need to consider the implications of this change in your upgrade plans and either change this setting or inform your users on how to change their passwords if they do not already know how to do so. Password Complexity Oracle has provided a password verification routine for some time. This routine can be found in the script $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. The password verification function is not loaded or enabled in Oracle 11g by DBUA when upgrading a database, or by DBCA when creating a new database. Therefore, as with previous versions of the Oracle Database, you will need to manually run this script to take advantage of this new feature. The password verification function has been improved in Oracle 11g. Now within utlpwdmg.sql you will find the old version of the password verification routine and a new version. The new version is called verify_function_11G. The old version is still called verify_function. If you run the utlpwdmg.sql script, both copies of the password verification routine will be loaded into your database. The script also includes the ALTER PROFILE commands to update the default profile with the correct default profile settings. Note that the default profile will be configured to use the verify_function_11G verification function and not the verify_function. The following password checks occur in the new password verification function, verify_function_11G:
Password Case Sensitivity and Multi-Byte Characters The Oracle 11g feature of password case sensitivity adds an additional level of password complexity. The sec_case_sensitive_login parameter controls case sensitivity, and defaults to TRUE, enabling case sensitivity. If you upgrade a database with DBUA, you will find the sec_case_sensitive_login parameter will be added to your SPFILE with a TRUE setting. You can disable case sensitivity by setting sec_case_sensitive_login to FALSE. When you upgrade from a previous version of Oracle Database, existing user passwords will remain case-insensitive until the passwords are changed (this includes upgrade via export/import). You can determine if a password is case-sensitive by referencing the PASSWORD_VERSIONS columns of the DBA_USERS view as seen in this example: SELECT username, password_versions FROM dba_users WHERE username='SCOTT'; USERNAME PASSWORD ------------------------------ -------- SCOTT 10G 11G Valid values in the PASSWORD_VERSIONS column would be 10G for passwords assigned in 10g that are not case sensitive yet in 11g (because they have not been changed) and 10G 11G (as is the case in the previous example) that indicates a case sensitive password. To support mixed case password names, the orapwd program has been modified to allow or disallow case-sensitive passwords and the ignorecase parameter has been added to the command line. Ignorecase should be set to Y to enable password case sensitivity for SYS and SYSDBA connections. You will need to be careful about implementing this new feature. Because Oracle Database versions prior to Oracle 11g would always uppercase all passwords (even when presented in lowercase), you must consider the impacts of enabling case sensitivity when upgrading to Oracle 11g. Enabling case sensitivity can impact a great many things including, for example, scripts with passwords stored in lowercase (where in fact the password is an uppercase password) and database links. Even your application may not fully support mixed-case passwords. Test carefully if you are upgrading and intend to use this new feature. If you can't use case sensitive passwords, simply change the sec_case_sensitive_logon parameter, which is dynamic, to FALSE as seen here: ALTER SYSTEM set sec_case_sensitive_logon = false; This will cause Oracle to follow the pre-11g behavior, which is case insensitivity. Additionally you can now include multi-byte characters in Oracle passwords without quoting. Characters such as $, _, and # can also be included in a password without quoting when you use the SQL*Plus connect command, or if you enter the password from the SQL*Plus password prompt. As with password case sensitivity, you should carefully test the use of multi-byte characters and special characters within your applications to ensure that they are supported. Hacking Prevention with Failed Logon Delays A hacker may attempt a brute force hack to break into your Oracle Database. This is where they try constant logons to the database using some form of a word list. To try to make brute force hacking more difficult, Oracle 11g includes a logon delay that takes effect after the third failed password entry attempt. After the third failed logon attempt, Oracle will incrementally delay subsequent logon or password prompts up to a maximum of 10 seconds. No delay will occur if the logon is successful. Oracle 11g has added other features that improve security. You can configure actions related to bad packet reception, or the trace action that should occur should a bad packet be received, as well as the maximum number of failed logon attempts that a client can make before it's connection is dropped. Finally you can opt to enable or disable communication of the release banner to a client when it connects. These are all controlled by the following new parameters:
Password Hashing Changes Oracle passwords are hashed using SHA1 encryption in Oracle 11g. The SHA1 algorithm produces a 160-bit hashed output of the database password. Additionally, hashed passwords are "salted," which ensures that the resulting hash value for each hashed password is different, even if the password is the same. Default Password Use Oracle creates default accounts such as scott, HR and so on. In previous versions of the database product each of these accounts was associated with a default password. This caused a security risk if the accounts were not locked. To help the DBA deal with this issue, Oracle 11g introduces the DBA_USERS_WITH_DEFPWD data dictionary view. This view can be used to determine if a given user account is using a default password. In this example, we find an 11g database with a few accounts that are using default passwords. You should probably change the passwords for these accounts and insure that they are locked too: SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD; USERNAME ------------------------------ DIP MDSYS WK_TEST CTXSYS OLAPSYS OUTLN EXFSYS SCOTT
For questions
or comments on this site: webmaster@quest-pipelines.com
|