-
编辑 删除
push_pred, a powerful hint to speed up you inline view query 10X times
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 |
-
编辑 删除
A taste of SQL Performance Analyzer in oracle 11g
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
-
编辑 删除
steps to do real time apply in oracle 11g
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,ORA-01115,ORA-01110,ORA-27091,ORA-27072,IBM AIX RISC System/6000 Error
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)
-
编辑 删除
A more detailed threshold we should monitor on UNIX side
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
-
编辑 删除
V$sort_usage, where is the definition?
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
