Tuesday, April 18, 2017

how to find sqltext by using old_hash_value

In Oracle standard edition, AWR isn't there, as workaround, we still rely on STATSPACK.

one issue facing is incompleted SQL text listed in statspack report, when it is long ...

below is my method, to dig out the completed sql text.
1. with old_hash_value, I find sql_id from STATS$SQL_PLAN_usage
2. with sql_id, pull from dbms_xplan.display_cursor (if still in memory), or get from STATS$SQLTEXT


Enter value for ohv: 3353767585
old   1: select distinct sql_id from STATS$SQL_PLAN_usage where OLD_HASH_VALUE=&ohv
new   1: select distinct sql_id from STATS$SQL_PLAN_usage where OLD_HASH_VALUE=3353767585


Enter value for sqlid: 6mtr4m135zf22
old   1: select * from table(dbms_xplan.display_cursor('&sqlid'))
new   1: select * from table(dbms_xplan.display_cursor('6mtr4m135zf22'))

SQL_ID: 6mtr4m135zf22, child number: 0 cannot be found

Enter value for sql_id: 6mtr4m135zf22
old   1: select sql_text from STATS$SQLTEXT where sql_id='&sql_id' order by piece
new   1: select sql_text from STATS$SQLTEXT where sql_id='6mtr4m135zf22' order by piece


11 rows selected.

Next, can use below sql to find out historical execution plan too.

>  cat sp_hist_sqlplan_oldhash.sql

set lines 180
select snap_id, id,parent_id, position,operation,object_name, bytes,cardinality, cost,cpu_cost,temp_space,plan_hash_value from STATS$SQL_PLAN
where plan_hash_value in (select distinct plan_hash_value from  STATS$SQL_PLAN_usage where
order by snap_id, id,parent_id, position, plan_hash_value

Monday, April 10, 2017

sqlplus hang after linux server cloning

Cloned a server to AWS EC2 server, including oracle client software .

firewall port is open, however tnsping/sqlplus hangs.

Even tried instant client, same symptoms.

Googled, check against latest installation guide , and finally found some packages is missing.  In my case, they are :

compat-libcap1-1.10-1 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)

this may happen when cloned by sysadmin, as packages need to be installed with both 32 and 64 bits.

Monday, March 06, 2017

Samsung M2070 wireless printer installation , even supporting Linux

Excited with my very first new laser printer, started to set it up.

Followed the quick installation guide, insert the CD to ROM, choose wireless, however the stuck at connecting printer, time out after waiting for 10 minutes. intially suspect it is related to default OOTB 1 minute autosleep, adjust to longer time. still the same problem  -- the installation from the CD failed to detect the printer, although I had the USB cable connected. Real bad user experience, I started to regret chose this brand.

Thinking not right time to call the hot line at weekend night, while daytime I need to work.  Does it mean I need to take half day to stay at to call service centre and follow the instruction.

Searched to hard copy manual comes with the box, have to read the  guide again and again, finally I paid attention the sentence "use the CDROM and download installer from www.samsung.com/printersetup "  . I started to guess something with the installer from CDROM.

Tried my luck to download the installer online, in few steps, the new installer detect the wireless printer in few seconds.  I had spent new two hours for that buggy installer from CDROM.

What if customer not so techy like me? Isn't that very bad user experience?

Next I tested with mobile app "samsung mobile print" smoothly.

My final challenge is print from my Ubuntu PC, which I never thought when I decided to buy a laser print.

Tried to add a printer from Ubuntu, but failed to find right model.
Googled and find there is Driver for Linux, thanks to Samsung.


The filename I downloaded is ULD_v1.00.29.tar.gz.

tar xvfz it

cd uld

sudo  ./install.sh


If you have any questions regarding this agreement and other products, please co
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

**** Do you agree ? [y/n] : y
**** Are you going to use network devices ? If yes, it is recommended to configure your firewall.
**** If you want to configure firewall automatically, enter 'y' or just press 'Enter'. To skip, enter 'n'. : n
**** Registering CUPS backend ...
**** CUPS restart OK.
**** Print driver has been installed successfully.
**** Registering SANE backend ...
**** Restarting udev ...
**** Scan driver has been installed successfully.
**** Install finished.
hmc@hmc-P55A-UD3:~/Downloads/uld$ sudo

Added the printer again , the correct printer model is shown.

Print the Test Page!

Thanks! I shall have no more worry about dry ink when I want to print!

Other References:




From the http://www.samsungsetup.com/TS/Client/en/Install.html , I even see there is installer for more platforms like Linux, AIX , Solarix, HPUX !  Appreciate this .