How to Check Oracle Database Listener Port Status from Sql & OS Level

Hi Guy's,

You can check listener status in windows environment by using Sql & Os using Netstat.


SQL> select * from gv_$listener_network;

  

C:\Users\oracle> netstat -na |find "1521"

  TCP    0.0.0.0:1521                0.0.0.0:0                       LISTENING
  TCP    10.10.20.244:1521      10.10.20.244:49928     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49929     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49933     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49934     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49939     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:49947     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.244:50037     TIME_WAIT
  TCP    10.10.20.244:1521      10.10.20.246:50605     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:50613     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:55062     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57671     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57675     ESTABLISHED
  TCP    10.10.20.244:1521      10.10.20.246:57680     ESTABLISHED



C:\Users\oracle> netstat -a -o -n

Active Connections

  Proto  Local Address    Foreign Address       State           PID
  TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       752
  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       4
  TCP    0.0.0.0:1158           0.0.0.0:0              LISTENING       2828
  TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       1984
  TCP    0.0.0.0:1831           0.0.0.0:0              LISTENING       14504
  TCP    0.0.0.0:3389           0.0.0.0:0              LISTENING       5316


Happy Learning..

Gathering Table Stats in Oracle11g

Hi Guy's,

• When we gather stats we are updating Optimizer to choose optimal execution plan which enhance the performance of the database.
• We can gather stats at database level,schema level, Table level,Index level.

Example for Table Level Stats :

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'SCOTT',
         tabname =>'EMPLOYEE',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /


Parameters:-

•  Owner-name = Schema Name which holds the table.
• Tabname = Table Name
• Degree  = When you set degree to some value oracle will invoke slave process which speedup the performance.
• cascade = The cascade option can be used to analyze al the indexes.
• METHOD_OPT (FOR COLUMNS SIZE AUTO) = Auto is the Oracle default and this option will analyze histograms  on what the optimizer considers to be "important columns". The optimizer does this by examining your current workload when you run the dbms_stats command,interrogating all current SQL in the library cache to see which columns might benefit from a histogram.
•  estimate_percent = This argument is a new way to allow Oracle's dbms_stats to automatically   estimate  the "best" percentage of a segment to sample when gathering statistics.

Happy Learning...