Strict Standards: Redefining already defined constructor for class ftp in /home/yumianfeilong/ on line 8
May 23rd, 2014 | Categories: Boring | Tags:

CBO hint:no_unnest,push_subq,push_pred的用法

当涉及exist  in 等子查询SQL性能问题的时候,如上hint都试一遍,大抵会有合适的。

May 3rd, 2014 | Categories: US, economy | Tags:


在Walmart 买东西,超过50$可以免快递费。于是我每次都买多件东西。不过Walmart经常拆单,比方说,我一个订单有5件东西,它可以拆成4单,送4次货。每次货物,就算一双塑料拖鞋,它也拿个纸盒子包装好。算是快递的成本,真的怀疑walmart的货物价格有多少水分。


April 26th, 2014 | Categories: Boring | Tags:

Somebody sent me a file to load into Oracle. But sqlldr reports “ORA-01722: invalid number” and put correct data into discard files. VI the import data file and find  “^M” at end of each line. All right. Good to know that the sender prepare its data on windows.

  • The easiest way is probably to use the stream editor sed to remove the ^M characters. Type this command: % sed -e “s/^M//” filename > newfilename
    To enter ^M, type CTRL-V, then CTRL-M. That is, hold down the CTRL key then press V and M in succession.
  • You can also do it in vi: % vi filename
    Inside vi [in ESC mode] type: :%s/^M//g
    To enter ^M, type CTRL-V, then CTRL-M. That is, hold down the CTRL key then press V and M in succession.
  • the Unix command dos2unix
  • fix line endings in vi:

    :set fileformat=unix


April 20th, 2014 | Categories: Boring | Tags:

Recently I did an Oracle 11g upgrade,it takes 4 hours and 50 minutes. What a nightmare.

Oracle Database 11.2 Post-Upgrade Status Tool

Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
Oracle Server.                            VALID  00:38:34
JServer JAVA Virtual Machine.             VALID  00:12:19
Oracle Workspace Manager.                 VALID  00:02:09
OLAP Analytic Workspace.                  VALID  00:00:56
OLAP Catalog.                             VALID  00:03:57
Oracle OLAP API.                          VALID  00:01:19
Oracle XDK.                               VALID  00:02:06
Oracle Text.                              VALID  00:02:06
Oracle XML Database.                      VALID  00:10:47
Oracle Database Java Packages.            VALID  00:01:00
Oracle Multimedia.                        VALID  01:51:21
Spatial.                                  VALID  00:17:41
Oracle Expression Filter.                 VALID  00:00:29
Oracle Rules Manager.                     VALID  00:00:28
Oracle Application Express.               VALID  00:51:14
Gathering Statistics.                                            00:28:13
Total Upgrade Time: 04:49:32

My final conclusion is using less Oracle components will give better work-life balance.

April 11th, 2014 | Categories: Boring | Tags: ,

How to stop a query or transaction in another session without killing the session

Option 1 : DBMS_SYSTEM ( as sys )

We grap the session identifier sid and serial# from v$session.

SQL_DBA > select v.sid,v.serial#,p.spid
2  from v$session v,v$process p
3  where v.paddr = p.addr
4  and v.username = ‘TUNING’;
—————- —————- ————————
29                 5                  6277

We stop the execution of the query / transaction

SQL_DBA > exec dbms_system.set_ev(29,5,10237,1,”);
PL/SQL procedure successfully completed.

The session receives

ERROR:ORA-01013: user requested cancel of current operation

After this the session is not able to initiate other queries / transactions the session keeps on receiving

SQL_USER > select * from global_name;select * from global_name
*ERROR at line 1:
ORA-01013: user requested cancel of current operation

However we can disable event 10237

SQL_DBA > exec dbms_system.set_ev(29,5,10237,0,”);
PL/SQL procedure successfully completed.

Once done the session is able to continue without the need to reconnect

SQL_USER >  select * from global_name;


Option 2  : ORADEBUG ( as sys )

SQL_DBA > oradebug setospid 6277;
Oracle pid: 30, Unix process pid: 6277, image: oracle@pcguyl (TNS V1-V3)

SQL> oradebug session_event 10237 trace name context forever, level 1;
Statement processed.

The session receives

ORA-01013: user requested cancel of current operation

After this the session is not able to initiate other queries / transactions, the session keeps on receiving

SQL_USER > select * from global_name;
select * from global_name
ERROR at line 1:
ORA-01013: user requested cancel of current operation

However we can disable event 10237

SQL_DBA > oradebug session_event 10237 trace name context off;
Statement processed.

Once done the session is able to continue without the need to reconnect

SQL_USER >  select * from global_name;

Note that one can alse suspend and resume queries / transactions in another’ s user session using oradebug

SQL_DBA > oradebug suspend;
Statement processed.

SQL_DBA > oradebug resume;
Statement processed.

November 10th, 2013 | Categories: Boring | Tags:

Adaptive Log File sync was introduced in 11.2. The parameter controlling this feature, _use_adaptive_log_file_sync, is set to false by default in and

In the default is now true. When enabled, Oracle can switches between the 2 methods:

  • Post/wait, traditional method for posting completion of writes to redo log

LGWR explicitly posts all processes waiting for the commit to complete.
The advantage of the post/wait method is that sessions should find out almost immediately when the redo has been flushed to disk.

  • Polling, a new method where the foreground process checks if the LGWR has completed the write.

Foreground processes sleep and poll to see if the commit is complete.
The advantage of this new method is to free LGWR from having to inform many processes waiting on commit to complete thereby freeing high CPU usage by the LGWR.

when mode switches, it will write message to LGWR trace file.

*** 2012-10-02 08:15:47.049
kcrfw_update_adaptive_sync_mode: post->poll long#=316 sync#=1719 sync=247657
poll=12382 rw=6191 rw+=13890 ack=0 min_sleep=10023

But per my experience, “Polling” mode will cause higher average wait time of “log file sync” and is not a stable LGWR algorithm for heavy OLTP workload. It can be disabled by “alter system”.

Read more…

November 10th, 2013 | Categories: Boring | Tags:

当compatible升级为11g的时候,_memory_imm_mode_without_autosga默认为TRUE;在这种情况下,即使没有设置SGA_target; sga_max_size等SGA自动管理的参数,在必要的时候,比方说某个pool内存紧缺,Oracle仍然会自动调整各个pool (db cache/shared pool/large pool etc)的大小,Oracle将该参数设置为TRUE的目的应该是尽量避免ORA-4031的发生。

当Oracle自动调整SGA的时候,的版本,Oracle不会写任何警示信息到alert.log;但相应信息可以从 V$SGA_RESIZE_OPS查看。

SELECT component, oper_type, oper_mode, parameter,  initial_size/1024/1024 init_MB,target_size/1024/1024 target_MB, final_size/1024/1024 final_MB,status, start_time, end_time FROM V$SGA_RESIZE_OPS;

通过disable这个参数,可以绝对保证各个pool size为手工分配(db_cache_size/shared_pool_size etc)。

connect / as sysdbaalter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;


Read more…

October 26th, 2013 | Categories: Favorites | Tags:

This post explains you the information on the TCP retransmission measurement using netstat -s for Solaris. A system with a high retransmission rate indicates network congestion and can cause network oriented applications (like web servers) to operate slowly.

Procedure to Calculate TCP retransmission rate:

TCP expects acknowledgments from the destination system when it successfully receives segments from the sender. If it does not receive the acknowledgment within a certain time, it will retransmit the segment.

To implement this scheme of retransmission, TCP starts a timer  for each packet transmitted.  Unless the acknowledgment is received before this timer expires, TCP will assume that the packet is lost and will retransmit the packet.  The rate at which packets are retransmitted is called the ”retransmission rate”, and is an indicator of network health.

To calculate the retransmission rate, use the output of the ‘netstat -s -P tcp‘ command. We need the values for the counters

  • tcpOutDataBytes
  • tcpRetransBytes
  • tcpOutDataSegs
  • tcpRetransSegs.

The retranmission rate is given either in terms of bytes or segments. To calculate the byte retransmission rate:

%retrans = ( tcpRetransBytes / tcpOutDataBytes )  * 100

To calculate the segments retransmission rate:

%retrans = ( tcpRetransSegs / tcpOutDataSegs )  * 100

As a rule of thumb, retransmission  rates over 10% can indicate degraded  network performance on a LAN.  The internet may vary between 10 and 20 percent depending upon traffic conditions.  In some environments, delaying the retransmission to accommodate slower networks may be needed.

General Retransmission Rules:

moderate retransmissions < 10%

warning > 15% ( > 2/sec )

excessive retransmissions > 25%

action required > 40%

High rates (greater than 30%) may be relieved by adjusting the tcp_rexmit_interval* timers in Solaris. Some of the reasons for a high retransmission rate are:

  • Congested network, packets dropped. This is the most common cause.
  • Bad network hardware. Check ‘netstat -i‘ output for collisions or errors, also check the various network components involved.
  • Missing or out of date TCP or IP patches.
  • Incorrectly tuned TCP parameters (tcp_rexmit_interval_min,tcp_rexmit_interval_maxtcp_rexmit_interval_initial, tcp_ip_abort_interval).
  • Clients accessing server have slow or error-prone connections.

Notes: Be aware that these counters are 32 bits, and on a system with a high network load. They can experience an overflow and result in rates over 100%. In those cases, monitoring the system (with the command ‘netstat -s’) at periodical intervals or after the next downtime would be the recommended action to differentiate between a system with a high network load and a real retransmission problem.

The most acurate way to get the correct rate is by taking the values twice and using the deltas of each counter.