Tuesday, November 3, 2015

Execution Plan and Statistics


http://nimishgarg.blogspot.ae/2015/09/how-to-get-execution-plan-and.html



How do you check EXECUTION PLAN of a QUERY? I got this question on my facebook chat many times. "EXPLAIN PLANE" The answer was quite simple, Right? Actually it depends on what I am looking for. "AUTOT TRACE" is my personal favourite but I use one of the following depending on various situations.
1) EXPLAIN PLAN
2) AUTOT TRACE
3) DBMS_XPLAN.DISPLAY_CURSOR
4) SQL TRACE (10046)and TKPROF

Lets execute them to have an idea on how these methods are different and what information one provides and other doesn't.

1) EXPLAIN PLAN (basic and simple)
SQL> explain plan for select * from dual;
Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2 |
----------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

11 rows selected.


2) AUTOT TRACE (PLAN + STATS)
SQL> set autot trace
SQL> select * from dual;

Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2 |
----------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
         25  recursive calls
         13  db block gets
         34  consistent gets
          1  physical reads
       3060  redo size
        208  bytes sent via SQL*Net to client
        362  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


3) DBMS_XPLAN.DISPLAY_CURSOR (PLAN + OTHER DETAILS)
SQL> SELECT * FROM DUAL;
D
-
X

SQL> SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM DUAL%';
SQL_ID
-------------
9g6pyx7qz035v

SQL> select * from table(dbms_xplan.display_cursor('9g6pyx7qz035v',NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  9g6pyx7qz035v, child number 0
-------------------------------------
SELECT * FROM DUAL

Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DUAL@SEL$1

Outline Data
-------------
  /*+
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DUAL"@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
   1 - "DUAL"."DUMMY"[VARCHAR2,1]

Note
-----
   - SQL plan baseline SQL_PLAN_c7fbanxudy9yvef6f73b0 used for this statement

41 rows selected.


4) SQL TRACE (10046)and TKPROF
Click here to know how to generate trace files and execute TKPROF in simple steps.
http://nimishgarg.blogspot.com/2010/10/oracle-tkprof-simple-steps.html

Tuesday, March 31, 2015

How To Install and manage Java JDK on Oracle Linux



taken from :
http://lorenzkeller.blogspot.ae/2015/01/how-to-install-and-manage-java-jdk-on.html


Every Time I set up an Oracle Linux server, I ask myself, how have I done it last time ...

1. Check which Java Version (JDK7, JDK8, ...) is certified with your software.
e.g. Oracle Fusion Middleware supported system configurations:
http://www.oracle.com/technology/software/products/ias/files/fusion_certification.html 

2. Check support policy, esp. end of life for your desired Java Version
http://www.oracle.com/technetwork/java/javase/downloads/eol-135779.html

3. Download JDK
http://www.oracle.com/technetwork/java/javase/downloads/index.html

4. Install JDK 

# ls -l jdk*
-rw-r--r--. 1 oracle oinstall 126679286 Jan  9 13:01 jdk-7u71-linux-x64.rpm
-rw-r--r--. 1 oracle oinstall 142191827 Jan  9 13:06 jdk-8u25-linux-x64.rpm


# rpm -Uhv jdk-7u71-linux-x64.rpm
Preparing...                ########################################### [100%]
   1:jdk                    ########################################### [100%]
Unpacking JAR files...
    rt.jar...
    jsse.jar...
    charsets.jar...
    tools.jar...
    localedata.jar...
    jfxrt.jar...


5. Check Java Version

# java -version
java version "1.7.0_71"
Java(TM) SE Runtime Environment (build 1.7.0_71-b14)
Java HotSpot(TM) 64-Bit Server VM (build 24.71-b01, mixed mode)


If this is your expected Java Version - fine! Your are done!
If you still have a pointer to an older version, move on ...

...

6. A non expected Java Version is present?     lets say, JDK8 is still present, but you need to move back to JDK7

# java -version
java version "1.8.0_25"
Java(TM) SE Runtime Environment (build 1.8.0_25-b17)
Java HotSpot(TM) 64-Bit Server VM (build 25.25-b02, mixed mode)


7. Check Java install locations

# ls -l /usr/java/
total 8
lrwxrwxrwx. 1 root root   16 Jan  9 13:24 default -> /usr/java/latest
drwxr-xr-x. 8 root root 4096 Jan  9 13:49 jdk1.7.0_71
drwxr-xr-x. 9 root root 4096 Jan  9 13:40 jdk1.8.0_25
lrwxrwxrwx. 1 root root   21 Jan  9 13:40 latest -> /usr/java/jdk1.8.0_25


8. Make use of alternatives system to switch between versions

8.1 Syntax of alternatives

# /usr/sbin/alternatives
alternatives version 1.3.49.3 - Copyright (C) 2001 Red Hat, Inc.
This may be freely redistributed under the terms of the GNU Public License.

usage: alternatives --install 
                    [--initscript ]
                    [--slave ]*
       alternatives --remove 
       alternatives --auto 
       alternatives --config 
       alternatives --display 
       alternatives --set 

common options: --verbose --test --help --usage --version
                --altdir --admindir 


8.2 Register your JDKs for alternatives

usage: alternatives --install 

# /usr/sbin/alternatives --install /usr/bin/java java /usr/java/jdk1.7.0_71/bin/java 17071

(where priority represents 17071 for jdk1.7.0_71)

# /usr/sbin/alternatives --install /usr/bin/java java /usr/java/jdk1.8.0_25/bin/java 18025

(where priority represents 18025 for jdk1.8.0_25)

8.3 Now to switch between the versions

8.3.1 Check current java version, again

# java -version
java version "1.8.0_25"
Java(TM) SE Runtime Environment (build 1.8.0_25-b17)
Java HotSpot(TM) 64-Bit Server VM (build 25.25-b02, mixed mode)


8.3.2 Change the current java version

 # /usr/sbin/alternatives --config java

There are 2 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
   1           /usr/java/jdk1.7.0_71/bin/java
*+ 2           /usr/java/jdk1.8.0_25/bin/java

Enter to keep the current selection[+], or type selection number: 1


8.3.3 Check your current java version has changed 

# java -version
java version "1.7.0_71"
Java(TM) SE Runtime Environment (build 1.7.0_71-b14)
Java HotSpot(TM) 64-Bit Server VM (build 24.71-b01, mixed mode)


9. Keep your path JDK dynamic

The idea is, when you install for example WebLogic Server, to use a dynamic location to your JDK.
I do this with a symbolic link.
I have a folder /opt/oracle/java where I create a symbolic link, that points to the jdk.
As normal user I create the symbolic link:

$ ln -s /usr/java/jdk1.8.0_25 /opt/oracle/java/java

When an updated jdk version in available, after install I only change the symbolic link:

$ rm /opt/oracle/java/java
$ ln -s /usr/java/jdk1.8.0_31 /opt/oracle/java/java

For the WebLogic Server, the path to java will remain. No reconfiguration is needed.
So I only have to restart the WebLogic Server to use the latest JDK.

Tuesday, March 24, 2015

Setting up Linux with public-yum for all updates

  • Log in as user root
  • Download the yum repo file from http://public-yum.oracle.com
  • # cd /etc/yum.repos.d
  • # wget http://public-yum.oracle.com/public-yum-ol6.repo
  • Run yum repolist 
Depending of you internet speed it could take several minutes to build the repository.

Wednesday, September 24, 2014

How to change the screen resolution in Oracle Enterprise Linux on Vmware


I found a lot of trouble related to screen resolution which was struck to 800x600 or 600x480 resolutions  after installation of oracle enterprise linux as guest OS on vmware de. Then i went through many blogs and forums to change this problem and at last i was able to fix it. I am summarizing the steps to fix this issue.

Install Oracle Enterprise Linux
Login as "root" user and install vmware tools
After installation of vmware tools, restart it and login as a root user
go to System -> Administration -> Screen. In Hardware tab, click Configure for Monitor Type and select a suitable monitor type of high resolution e.g. LCD Panel 1280x800 etc.
Now go to etc/X11 and open XORG.CONF file
There will be a section "Screen" in that file which will contain a subsection "Display". Inside that section there is a line  'Modes "800x600" "640x480"'. Add a suitable screen resolution which you want to add e.g. i added "1280x800" and "1024x768" for my LCD. Now new line will be "Modes "1280x800" "1024x768" "800x600" "640x480".
Save it and restart the OEL. Now go to System->Preferences->Screen Resolution and select a suitable screen resolution now and enjoy.


taken from : https://sites.google.com/site/technicalinfosite/tips---techniques/howtochangethescreenresolutioninoracleenterpriselinuxonvmware


Sunday, April 6, 2014

Oracle Error with Explain Plan | ORA-00904: "OTHER_TAG": invalid identifier

when we try to explan plan, we get the below error, this is becauae the plan table is corrupted
drop and recreate the plan table.


SQL> explain plan for   select * from id_meta_ticket_query;
Explained.


SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------
ERROR: an uncaught error in function display has happe  Please provide also a DMP file of the used plan
       ORA-00904: "OTHER_TAG": invalid identifier


SQL> drop table  plan_table;
Table dropped.

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

SQL> explain plan for  select * from id_meta_ticket_query;
Explained.

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 683485703

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |   867 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ID_META_TICKET_QUERY |     1 |   867 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

8 rows selected.

Wednesday, December 25, 2013

ORA-00600: internal error code, arguments: [723], [248344], [248344], [memory leak]



Solution:

1. For few leaks size: Ignore this ORA-600 [723] as it is more of a housekeeping message and is not really a cause for concern.

OR

2. In order to avoid the ORA-00600[723] , you can set the 10262 event;
SEE: (Doc ID 39308.1) ORA-600 [723] "PGA memory leak"

As a workaround, use event 10262 to suppress the error check. Set event 10262 at a level greater than the size of the leaked memory, e.g.:

-- > your error message: ORA-00600: internal error code, arguments: [723], [248344], [248344], [memory leak], [], [], [], [] <<<<<< LEAK 248344 ... 250Ko.

SQL> connect / as sysdba
SQL> alter system set events '10262 trace name context forever, level 300000';

This prevents the leak being reported which is not serious and is only signalled on logging out the session before freeing the memory, and so no memory is wasted.

OR

3. Apply the latest Patchset 10G Release2 database version (102050) which is fixing this issue. 

Patch Link: https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=8202632

Tuesday, December 24, 2013

Troubleshooting ORA-600 [723] "PGA memory leak


PURPOSE: This article discusses the internal error "ORA-600 [723]", what it means and possible actions. The information here is only applicable to the versions listed and is provided only for guidance.

ERROR: ORA-600 [723] [a] [b]

VERSIONS: versions 6.0 to 11.1

DESCRIPTION: This is a memory leak in the Program Global Area (PGA) PGA is checked for Space leaks at logoff time and a leak was found. There is no data corruption with this error.

ARGUMENTS: Arg [a] Logoff PGA size in bytes Arg [b] "memory leak"

FUNCTIONALITY: MEMORY COMPONENT

IMPACT:
PROCESS FAILURE - but only during session delete so impact is minimal
NON CORRUPTIVE - No underlying data corruption.

SUGGESTIONS: Event 10262 can be set to safely ignore small memory leaks.

Set the following in init.ora for example to disable space leaks less than 4000 bytes

event = "10262 trace name context forever, level 4000"
and stop and restart the database. Repeated errors can be diagnosed further by sending the alert.log and trace files to Oracle Support Services.

Known Issues:

Bug# 6749617
See Note 6749617.8 OERI[723] of "Global Blockers" memory on disconnect
Fixed: 10.2.0.5

Bug# 5891737
See Note 5891737.8Dump (kcblsod) / OERI:723 / Memory leak in ASM/RAC
Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.3.P17