Friday, December 13, 2013

How to get actual explain plan of query in oracle

dbms_xplan- The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display    the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and SQL_PLAN_STATISTICS_ALL fixed views.

DISPLAY_CURSOR
- to format and display the contents of the execution plan of any loaded cursor.

    how to use
    -- first execute the query
select e.ename , e.sal, d.dname from emp e ,dept d where e.deptno = d.deptno;
    -- then search sql id for the query that is executed
select sql_id,sql_text from v$sqlarea where sql_text like '%select e.ename , e.sal, d.dname from emp e ,dept d where e.deptno = d.deptno;%'
 
SQL_ID        SQL_TEXT
------------- ---------------------------------------------------
1rk5afg9tcn4f select sql_id,sql_text from v$sqlarea where sql_tex
dmrp9jvfxd89f select e.ename , e.sal, d.dname from emp e ,dept d
4fdqm0tbnycyb select sql_id,sql_text from v$sqlarea where sql_tex

    -- chose the correct sql_id wrt to your query, then excute the below query to get actual plan
select * from table(dbms_xplan.display_cursor('dmrp9jvfxd89f'));

  

For any modification , updatation in post please comment

Difference between explain plan and autotrace

The main difference between explain plan and autotrace is only that expalin plan produces execution plan without execute the statement where as autrace gave execution plan after execution of the statement.

EXPLAIN PLAN

SQL> select count(*) from emp;
  COUNT(*)
----------
        14

SQL> explain plan for delete from emp where ename = 'SMITH';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 161811703
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  DELETE            | EMP  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    19 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
  COUNT(*)
----------
        14 
 


(OUTPUT SPACING HAS BEEN MODIFIED , dont try to match it)

AUTOTRACE

SQL> select count(*) from emp;
  COUNT(*)
----------
        14
SQL> set autotrace on
SQL> delete from emp where ename = 'SMITH';
1 row deleted.

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Ti
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    19 |     3   (0)| 00
|   1 |  DELETE            | EMP  |       |       |            |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    19 |     3   (0)| 00
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          7  db block gets
          7  consistent gets
          2  physical reads
       1152  redo size
        673  bytes sent via SQL*Net to client
        610  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

SQL> select count(*) from emp;
  COUNT(*)
----------
        13


Now You can see the difference that in case of explain plan the row count of table remain same ,
but in case of autotrace one row has been deleted and then it produce the execution plan.

Execution plan of query in Oracle

Today i study about the area of execution plans and this is the post , that what i collect after reading several
websites and blog and youtube and presentation from scribd. My main motive to get actual execution plan and below is the notes.

We can get the query execution plan by below ways:-
1. explain plan
2. autotrace
3. tkprof
4. dbms_xplan.display_cursor

First three ways are used for estimated execution plan of query in oracle and fourth give us acual execution plan.
1. explain plan
->explain plan statement that produces execution plan without execute the statement
    - where to use
        - in long running queries.
        - if we need a fair idea of flow of execution plans.

    how to use:
    -
    explain plan for select * from emp;
    -
    select * from table(dbms_xplan.display);

2. autotrace  
->autotrace on same as explain plan (generation of plan is similler as "explain plan")
    - autotrace statement produces execution plan after execution of the statement.
    - need to wait till query is not executed
  how to use:


 
sql> set autotrace on               [block 1,2,3 will display]
sql> set autotrace trace           [block 2,3 will display]
sql> set autotrace on explain   [block 1,2 will display]
sql> set autotrace on statistics [block 1,3 will display]

--enable autotrace from sql*plus window
see here
Difference between explain plan and autotrace

3. tkprof  
-> tkprof
    - get reports after only when we enable trace
    - have execution plan , more detail info all disk inputs and outputs
    - get info from trace file

    Detail usability

4.dbms_xplan.display_cursor
->dbms_xplan.display_cursor show actual execution plan from dynamic performance views

    More details See here :
    How to get actual execution plan in oracle



For any modification , updatation in post please comment

Thursday, December 12, 2013

why query is get faster after create a index

Index is method, applied on table to access the data faster
If we do not create index on table, and we execute the query and then oracle engine do the full table scan to return the required data.

figure explanation

Branch Blocks and Leaf Blocks

A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure , the root branch block has an entry 0-40, which points to the leftmost block in the next branch level. This branch block contains entries such as 0-10 and 11-19. Each of these entries points to a leaf block that contains key values that fall in the range.

Branch blocks store the minimum key prefix needed to make a branching decision between two keys. This technique enables the database to fit as much data as possible on each branch block. The branch blocks contain a pointer to the child block containing the key. The number of keys and pointers is limited by the block size.
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In Figure  the leftmost leaf block (0-10) is linked to the second leaf block (11-19).
 --------------

-> After create a index, Database retrieve the rows by traverse the index , instead of traverse the table. It help to to save the data input output reads and disk loads.

If sql statement only access indexed columns in select clause, then Database will load the index and start the index range scan.( it fetch the data after index scan).
example
select ename from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain only ename column in select clause of query, then database will do index scan and return desired result.

If sql statement have the others column instead of indexed columns in select clause of sql statement
then database retrive the other data with help of rowid (as u can see the figure , leaf block store rowid with each indexed item , which point the table rowid).
example
select ename,empno, mgr, sal, job from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain other columns in select clause of query, then database will do index scan and use rowid (which point to table data) and return the desired result.

Enable log in mysql

To enable query log in mysql execute the below steps:

First of all we need to locate my.ini file where we can set the variables for mysql server.

my.ini file will reside at path "C:\ProgramData\MySQL\MySQL Server 5.*\"

--permanent changes
Open file in editor (notepad, npp , editplus any of one) and look for below variables

Default setting of variables:-
# General and Slow logging.
log-output=NONE
general-log=0
general_log_file="machine_name.log"
slow-query-log=0
slow_query_log_file="machine_name-slow.log"
long_query_time=2

# General and Slow logging.
log-output=FILE
[enable all type of logging, Can also set to TABLE]
general-log=0
[Set it to 1 , to enable it]
general_log_file="machine_name.log"
[any filename, default path "C:\ProgramData\MySQL\MySQL Server 5.*\data"]
slow-query-log=1
[Set it to 1 , to enable it]
slow_query_log_file="machine_name-slow.log"
[any filename, default path "C:\ProgramData\MySQL\MySQL Server 5.*\data"]
long_query_time=2
[slow query time, query will logged into file if it take >2 sec]


--Temporary changes
show variables like '%log_file%';
    note down file name
set global log-output='FILE';
set global slow-query-log=1;
set global long_query_time=2;


Note:

same Queries can be used in linux and the path to log file is "var/lib/mysql"

Sunday, December 1, 2013

Uninstall mysql sever from Linux machine

1. Get the mysql installed rpm and its dependency.

[root@localhost ~]# rpm -qa | grep -i mysql
MySQL-devel-* 
MySQL-test-*
MySQL-server-*
MySQL-client-* 
MySQL-shared-*
MySQL-shared-compat-*
MySQL-embedded-*

2. Above query return some result as shown and then remove all rpms i.e. return by query

[root@localhost ~]# rpm -e MySQL-* 

OR [remove them one by one]

[root@localhost ~]# rpm -e MySQL-devel-* (full rpm name)
[root@localhost ~]# rpm -e MySQL-test-*
[root@localhost ~]# rpm -e MySQL-server-*
[root@localhost ~]# rpm -e MySQL-client-*
[root@localhost ~]# rpm -e MySQL-shared-*
[root@localhost ~]# rpm -e MySQL-shared-compat-*
[root@localhost ~]# rpm -e MySQL-embedded-* 


3. After uninstall rpm check for the directory existence at path /var/lib/mysql if exists remove directory.

[root@localhost ~]# cd /var/lib 
[root@localhost lib]# rmdir -p mysql

Mysql command on linux terminal move to bash shell

[root@localhost ~]# mysql
bash3.2
 
The Problem might be that mysql server is not installed or it is not installed
properly on linux machine.
 
When i check the log files i got the error as 
mysql: relocation error: mysql: symbol strmov, version libmysqlclient_16 not defined in file 
libmysqlclient.so.16 with link time reference 
 
So i uninstall the mysql completely and reinstall it. 




Install mysql in Linux

1. Download the Mysql package from :
mysql>downloads>MySQL Cluster Community Edition
Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive (292mb))

2. Extract the downloaded archive to some folder and remember the path

3.Open terminal and move to extracted path and execute below command

[root@localhost mysql]# rpm -ivh MySQL-*.rpm

Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [ 14%]
   2:MySQL-devel            ########################################### [ 29%]
   3:MySQL-embedded         ########################################### [ 43%]
   4:MySQL-shared-compat    ########################################### [ 57%]
   5:MySQL-shared           ########################################### [ 71%]
   6:MySQL-server           ########################################### [ 86%]
   7:MySQL-test             ########################################### [100%]
completed

4.Now start the mysql service
/etc/init.d/mysqld start    or
/etc/init.d/mysql start

5.(you might got this error)
[root@localhost ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@localhost ~]# /usr/bin/mysqladmin -u root -p password 'somepassword'
Enter password:
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'


How to resolve above error
(Execute below commands one by one)
#/etc/init.d/mysql stop
Shutting down MySQL.. SUCCESS!
    #mysqld_safe --skip-grant-tables &;
    #mysql -u root
    mysql> use mysql;
    mysql> update user set password=PASSWORD("newPassword") where User='root';
    mysql> flush privileges;
    mysql> quit
    /etc/init.d/mysqld stop [or     /etc/init.d/mysql stop]
    /etc/init.d/mysqld start [or    /etc/init.d/mysql start]


6. you may also got this error
mysql> use test
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql>

[SOLUTION]

mysql> SET PASSWORD = PASSWORD('new_password');
Query OK, 0 rows affected (0.00 sec)

Postgres Create drop constraint script with the help of function


Create drop script for constraints in postgres sql :

CREATE or REPLACE FUNCTION fn_get_fk(tbl_name varchar, col_name varchar) RETURNS varchar
language PLPGSQL AS $$
DECLARE
   Vtbl varchar(150);
   Vqrys varchar(150);
   Vcol varchar(150);
   Vcon varchar(150);
   Vqryd varchar(300);
   Vqrya varchar(300);
   Vsch varchar(150);
   VqryAdd varchar(30000) = '';
   VqryDrp varchar(30000) = '';
   Vrtn varchar;

   Ctbl_list refcursor;
   Ctblcon information_schema.table_constraints %rowtype;
   Vtblkcu information_schema.key_column_usage;
BEGIN
-- OPEN CURSOR WITH REFERENCING TABLE FOR GIVEN PARENT TABLE
 open Ctbl_list for execute 'SELECT *
    FROM information_schema.table_constraints tc
    right JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_catalog = ccu.constraint_catalog
      AND tc.constraint_schema = ccu.constraint_schema
      AND tc.constraint_name = ccu.constraint_name
      and ccu.table_name in ('||quote_literal(tbl_name)||')
      and ccu.column_name in ('||quote_literal(col_name)||')
    WHERE lower(tc.constraint_type) in (''foreign key'');' ;
 loop fetch next from Ctbl_list into Ctblcon ;
 exit when not found;
    Vtbl = Ctblcon.table_name;
    Vcon = Ctblcon.constraint_name;
    Vsch = Ctblcon.constraint_schema;
--GENERATE DROP CONSTRAINT STATEMENT
    Vqryd = 'alter table '|| Vsch||'.'||Vtbl ||' drop constraint ' || Vcon;
    VqryDrp = VqryDrp ||';'||chr(10)||Vqryd;
    raise info 'Qry : %; ',Vqryd;
--GET REFRENCING COLUMN NAME
    Vqrys = 'select * from information_schema.key_column_usage where constraint_name='||quote_literal(Vcon) ;
  EXECUTE Vqrys into Vtblkcu;
-- GENERATE ADD CONSTRAINT STATEMENT
Vqrya = 'alter table '|| Vsch||'.'||Vtbl ||' add constraint ' || Vcon ||' foreign key('||Vtblkcu.column_name||')
references personal.m_pis_master(str_empno)';
raise notice 'add constraint : %; ',Vqrya;
VqryAdd = VqryAdd || chr(10) || Vqrya || ';';
end loop;
-- CONCATENATE BOTH ADD & DROP STATEMENT
Vrtn ='DROP CONSTRAINT : '|| VqryDrp ||chr(10)||'ADD CONSTRAINT : '||chr(10)||VqryAdd;
RETURN Vrtn;
END;
$$; 
 
Execute the above function by adding table and its column name on which we need to find the dependency
SELECT * FROM fn_get_fk('language','language_id')

This script will be helpful in finding parent child relationship of tables depending foreign keys or you can modify the function accordingly.......................

web stats