ITPub博客

What the new 11gR1 RDBMS changes benefit our application, you can read the article for a taste[@more@]Architecture:Hash Group By 10g: Hash Group By aggregation enabled instead of SORT GROUP BY _gby_hash_aggregation_enabledParallel query 10g: Parallel Query (PQ) has been enhanced to allow more queries to be parallelized New background:11g:DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks. DIA0 (diagnosab

Changes that affect SQL execution plans, such as upgrading a database or addingnew indexes, can severely impact SQL performance. As a result, DBAs spendenormous amount of time and effort identifying and fixing SQL statements thathave regressed due to the changes. SQL Performance Analyzer (SPA), a key featureof the Real Application Testing option introduced in Oracle Database 11g, canpredict and prevent SQL execution performance problems caused by systemchanges.[@more@]here is a simple step by st

ASCII: function converts a single character to its corresponding ASCII code.[@more@]CHARASCIICHARASCIICHARASCIINull 0+43 V 86 Start of Heading 1 ' 44W 87 Start of Text2 -45X 88 End of Text 3 . 46Y89 End of Transmission 4 /47 Z 90Enquiry 5 0 48 [ 91 Acknowledge 6 1 49 / 92 Bell 72 50 ]93Backspace 8 3 51 ^ 94Tab 94 52_95 Line Feed 10 5 53` 96 Vertical Tab 11 6 54 a97Form Feed127 55b 98 Carriage Return 13 856 c 99 Shift Out 14 9 57d100Shift In 15:58 e 101 Data Link Escape 16 ; 59f 102 Device Contro

There are two ways to use dynamic sampling: The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command. The DYNAMIC_SAMPLING query hint can be added to specific queries. eg:/*+ dynamic_sampling(t 0) */ level 0The Dynamic Sampling Levels[@more@]The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from the OPTIMIZER_DYNAMIC_SAMPLING initialization param

wbiadm@wbidevrk1p:[/home/wbiadm]# tnspingNL-00605: national language initialization failedNL-00605: national language initialization failedwbiadm@wbidevrk1p:[/home/wbiadm]# tnspingNL-00605: national language initialization failedNL-00605: national language initialization failedwbiadm@wbidevrk1p:[/home/wbiadm]# sqlplusError 5 initializing SQL*PlusNLS initialization error[@more@]nutshell for 2hrs searching the relative information, making a lot of change on binary, env, etc. Finally got the root c

when we need to use it:Instructs the optimizer to push a join predicate into the view.condition: non-mergeable views like outer joinfor 10g, another alternative hint you may try:/*+ opt_param('_optimizer_cost_based_transformation','off') */also note that you need to specify view name in 10g version.[@more@]without hint:|* 28 | TABLE ACCESS FULL | ORDHEAD | 61850 | 1932K| | 401 | | ||* 29 | HASH JOIN |

we got a case when trying to change a table property, before we need to do thorough testing by collecting all related sql from production, then simulate the sql and test the performance. In 11g, it can be handled by oracle engine[@more@]Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> show userUSER is "SZHAO"SQL> cr

the feature can make 11g standby database more powerful and flexible[@more@]real time apply:before:check log file:Successful mount of redo thread 1, with mount id 965009577Physical Standby Database mounted.Lost write protection disabledCompleted: alter database mountalter database recover managed standby database disconnect from sessionAttempt to start background Managed Standby Recovery process (CAT11GST)Tue Jul 22 14:32:15 2008MRP0 started with pid=23, OS id=2175024MRP0: Background Managed Sta

ORA-00604: error occurred at recursive SQL level 1ORA-01115: IO error reading block from file 1 (block # 81)ORA-01110: data file 1: '/u03/oradata/rmspprd/system01.dbf'ORA-27091: skgfqio: unable to queue I/OORA-27072: skgfdisp: I/O errorIBM AIX RISC System/6000 Error: 5: I/O error[@more@]but when checked filesystem and datafile, it is healthy and no single block of errors.DBVERIFY - Verification completeTotal Pages Examined : 4096000Total Pages Processed (Data) : 1691912Total Pages Failing (Data)

this is an example of SUN solaris data, it may be a bit different on other platform.FYI:[@more@]The iostat output contains summary information for all devices.FieldDescriptionr/sShows the number of reads/secondw/sShows the number of writes/secondkr/sShows the number of kilobytes read/secondkw/sShows the number of kilobytes written/secondwaitAverage number of transactions waiting for service (queue length)actvAverage number of transactions actively being servicedwsvc_tAverage service time in wait

GV$SORT_USAGEyou may need it sometimes when your temp gets blew without any reason during the nite :-)[@more@]selectx$ktsso.inst_id, -- INST_ID NUMBERusername, -- USERNAME VARCHAR2(30)username, -- USER VARCHAR2(30)ktssoses, -- SESSION_ADDR RAW(4)ktssosno, -- SESSION_NUM NUMBERprev_sql_addr, -- SQLADDR RAW(4)prev_hash_value, -- SQLHASH NUMBERktssotsn, -- TABLESPACE VARCHAR2(31)decode

SQL> select * from V$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64biPL/SQL Release 10.2.0.2.0 - ProductionCORE 10.2.0.2.0 ProductionTNS for Solaris: Version 10.2.0.2.0 - ProductionNLSRTL Version 10.2.0.2.0 - ProductionSQL> ANALYZE TABLE "SAPKBW"."/BI0/F0TCT_C21" PARTITION ("/BI0/F0TCT_C210000000052") ESTIMATE STATISTICS SAMPLE 60 PERCENT FOR TABLE FOR ALL LOCAL INDEX

The following operations support the UPDATE INDEXES clause:ADD PARTITION | SUBPARTITIONCOALESCE PARTITION | SUBPARTITIONDROP PARTITION | SUBPARTITIONEXCHANGE PARTITION | SUBPARTITIONMERGE PARTITION | SUBPARTITIONMOVE PARTITION | SUBPARTITIONSPLIT PARTITION | SUBPARTITIONTRUNCATE PARTITION | SUBPARTITIONSQL> alter table SAPKBW."/BIC/FZSD_C71" move partition "/BIC/FZSD_C710000000123" tablespace PSAPKBWFACT update indexes;Table altered.SQL> set linesize 200SQL> set page

X$ Table This is a summary list of X$ table definitions. The last revision was for Oracle 7.3.2, however, the list is still relevant to later Oracle releases (including Oracle 10g). The main purpose of this list is to show naming conventions. This is also handy when trying read Oracle trace files. For example, if you see a function name starting with k2t%, you know is if from the [K]ernel Layer, [2]-Phase Commit, [T]ransaction handling code. [@more@]Here is the list: [K]ernel Layer [2]-Phas

got an issue that my orcladmin account was not able to login. Here is the approach to reset it:donot get panic when you hit:oracle$ oidpassd unlock_su_acct=TRUE"unable to locate message file: ldap<language>.msb"set $ORACLE_HOME=/u01/home/oracle/product/10gASstzhao:pts/1:drno:rep > oidpasswd connect=tsys unlock_su_acct=trueOID DB user password:OID super user account unlocked successfully.it works then :)[@more@]Usage: oidpasswd [connect=<Net8 Connect Descriptor>] [change_

when change of fail-over host:change the entry accordingly$ pwd/u01/home/aw/site$ cat sosite# Copyright 1992-2003 AppWorx Corporation# All Rights ReservedSQLOPER_HOME=/u01/home/aw;export SQLOPER_HOMEAW_HOME=/u01/home/aw;export AW_HOMEAWWEB_HOME=/u01/home/aw;export AWWEB_HOMESO_OPERATOR=BWPRD-FO;export SO_OPERATOR. $AW_HOME/bin/sqloper.ini# End of system maintained section$ cat awenv.ini[default]AWCOMM_PORT=2136Master=SVRPAWMaster_Ip_Address=svrpawAgent=BWPRD-FOAgntMgr=NoOsType=UNIX[Install]remov

no time to read the manual but want to taste how stream works. here is what you can go through in 30min.[@more@]stream step-by-step1. prerequisite on both databases:stzhao:pts/6:sambar:hdvl > sqlplus "/ as sysdba"SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 14 16:52:40 2007Copyright (c) 1982, 2006, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> sho

archivelog format：(9i,10g)%s log sequence number%S log sequence number, zero filled%t thread number%T thread number, zero filled%a activation ID%d database ID[@more@]%c : copy number of backup piece.when duplexing of backup piece is in use备份片的拷贝数%d : name of database数据库名称%D date of month 位于该月中的第几天 (DD)%F: DBID+yyyymmdd+sequence number hex%F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为日期，QQ是一个1-256的序列%M

The areas you'll want to review and monitor on your database server using Unix utilities are:CPU performance. How busy are the CPUs? How big is the backlog of processes waiting to run on your CPUs? Memory usage. How much memory is being used? Are you swapping out to disk because too much is being used? Do you have unused memory that would allow you to increase the size of your database buffers? Disk output. What is the disk I/O throughput? Is a particular disk a bottleneck? Network usage. What's

Everybody handling a Unix operating system would very well know what chmod 777 means. That the owner, group and the user of the file is given all permissions (Read, Write and Execute on a particular file). This could otherwise be written as “chmod ugo+rwx “. Meaning that you are giving User, Group and Owner of the file, the rights to Read, Write and Execute the file.Here comes the rws scenario. Best example that is available for this rws is /usr/bin/passwd command (just issue a “ls -l /usr/bi