Configure Huge Page in Linux for efficient memory utilization for performance tuning Oracle Database

Traditional Virtual Memory Page Size is only 4K and as as Linux does not have intimate shared memory model unlike Solaris the size of Page Table in Linux can grow significantly too large to become major slow down factor as number of dedicated connections(or shared servers - which is rare case)  grows and SGA has large size - slowing down system by consuming system in system CPU usage to scan large Page Table. Also as we know processor has very small portion set for TLB(a portion of page memory resides in processor - rest linked) it is very important to tune Page Memory size by reducing its size if it is slowing down system-Another good reason to tune Page table is incurs heavy overhead in context switching when portion of page table in CPU is flushed. System CPU usage on Linux box can be tracked by checking sys% vmstat and grep -i pagesize /proc/meminfo. If sys% is significantly high then check the size of Page Table during your typical or peak workload hours on Linux DB server.

Linux has option of configuring Large Page size 2MB so say if Page Table size for 8GB SGA with 100 connections is 1GB  then it reduces to less than 100MB with Huge pages. RHEL 6 Linux configures transparent huge pages also but Oracle does not go well with feature and recommends you to disable transparent Huge pages. Oracle's own flavour OEL has it disabled for UEK kernel.

Do you need Huge Pages:

Check system CPU usage by vmstat or sar .It should not be high. Check Page Table size by command:

 grep -i Page /proc/meminfo It should not be high.

If system CPU usage and Page Table both are re significantly high or Page Table size is too high then go for Huge pages. You can better check the pattern of system usage of CPU by history of sar and compare it with number of concurrent sessions in that window with AWR report. 

Advantages :

• Configuring Huge pages reduces the page table scan time and saves CPU resources

• By configuring Huge Pages the memory freed by small page table can be assigned to Oracle SGA and PGA so more better memory utilization. 

•  Another advantage is SGA is allocated from Huge Pages pool so it is locked in memory and is not swapped(you do not need to set LOCK_SGA=TRUE init parameter] [ make sure whole SGA fits in Huge Page poo, if some of SGA is allocated from Huge pages pool and some from normal memory then this will slow down system and it may restart the system(I obsever same in one my machine  RHEL 4.4 with less Huge pages and DB was Oracle 10.2.0.5 ) or DB won't be able to start Oracle instance ] 

• If your SGA is small and you do not have large number of dedicated connections your benefit from configure Huge pages may be insignificant. 

NOTE OF CAUTION : 

1) From Oracle 11g onward DB alert log begins with information with memory aread from SGA is allocated where Huge page on Linux system has been configure or not. How much of the Huge Menory pool is used by Oracle SGA and how much of the SGA memory from non Huge pool is allocated to SGA ].  Oracle 11g also comes with parameter use_large_pages and you can set it to 'ONLY' so that Oracle instance does not start if system lacks in required number of Huge pages so that Oracle instance memory area is not split in two - one portion in Huge pool and another in non Huge pool which slows down oracle database. Do not tempt to use parameter PRE_PGA_SGA=true, this parameter is not good for OLTP database, as it can slow down connection setup time. I've tested it with large number of concurrent connects and found it was 25% slower. It can be more with fewer connection due large cache effect of my heavily concurrent connection setup.

2) Huge Pages pool can not be used with AMM(automatic memory management introduced from 11g) but can be used with ASMM(automatic shared memory management 10g feature). I personally do not like either of these two and I like to configure SGA and PGA depending on typical need to avoid bugs(10gr2 has plenty of Shared Pool memory leakage bugs)  and overhead of shrink and expansion of SGA components at run time.

Configuring Huge Pages: It is simple. Changes needs in OS  setting only, so transparent to Database. You just optionally want to configure parameter use_large_page in 11g

1. To configure huge pages check output of SGA shared memory segment size shown in ipcs -m and divide it by the Huge Page size which is 2MB by default  and add 4 to this number and set this obtained number as parameter  vm.nr_Huge_Pages=X in /etc/systctl.conf.  Say your SGA is of size 8GB and ipcs is showing it 8194MB then divide it by 2 which gives 4097 now add 5 in this number(to be safe) and this 4102 will the Huge Pages required for you - this is X for you to set in mentioned parameter. This parameter can be enable dynamically by sysctl -p but it is good to restart system. Another way to set Huge pages temporarily is run echo 4012 > /proc/sys/vm/nr_hugepages but it may not free up space as Huge Pages needs contiguous free space and your system may not have this much contiguous free space so recommended is to set from sysctl.conf file followed by reboot as sysctl -p also may have issue.
2. Now parameter oracle soft memlock Y and oracle hard memlock Y needs to be set in /etc/security/limits.conf .Here Y is size of Huge pages memory pool in KB.  So for SGA of 8192 MB for whose number of Huge Pages came to be 4102 in above calculation in 1. it would  be 2*1024*4102 = 8400896. So add parameters as below and check its effectivness by ulimit -l after log off/ login

#### snippet of file /etc/security/limits.conf #######

oracle soft memlock  8400896
oracle hard memlock  8400896

 Check Huge Pages are being used by Oracle :
Check number of Huge Pages which should not be 0 now and check for free number of Huge pages by command: grep -i Page /proc/meminfo It should not be high.

--------------You are done ------------

You will also notice the reduced Page Table Size from output of above command. If your system allows you can benchmark before and after configuring Huge pages.


Another note of caution:
If you are system is very low on memory by configuring hugepages you are always grabbing memoy's defined portion and so free space may be low if you are short of free memory. So your PGA or any other processes may system may go for swapping. Anyway SGA will never be swapped. So make sure there are enough free memory in system after Hugepages have been allocated.


I first used huge pages in 2009 on 10.2.0.4 database It was Dataware house with 30GB SGA but there were approx only 25 concurrent sessions but memory foot print of page table was much less than other DB servers with similar configuration and I was happy though I did not have any bench mark as this Prod system 24*7 was build from scratch and there was no outage.

If you are using virtualized environment and you want more free memory you can set it for small SGA and fewer connections as well. I tested it with 1.4GB SGA and 32 connection in each 3 RAC instances and it saved me memory approx 70MB in each node.

You are wondering Whether Oracle EXADATA is equiped with pre configured Huge Pages. No it does not want because different clients may have different SGA sizing need depending on DB nature OLTP/Dataware house so it does not.

When you not want Huge Pages: If SGA is small or very few dedicated connections or many instances on same host

Best Linux I/O Scheduler(elevator) for Oracle database

There is no single answer for this question but it is easy to answer it depends on your workload.  Beginning from RHEL 4 (Completely Fair Queuing) scheduler is default for all RHEL flavors replacing default anticipatory scheduler and it is good change indeed as anticipatory scheduler was good only for slower disks and it did not go very well with swapping. 

• But Oracle Enterprise Linux has chosen deadline scheduler as default scheduler for its kernel. Deadline scheduler tends to favor read more than than write unlike CFQ scheduler which treats both read/write as equal priority. And It is considered good for heavy read intensive operations like Data ware house databases but it can go good with OLTP databases also. I would recommend to tweak its disk write timeout attribute and read/write priority to be set equal. 

• If you are using virtual machine then NOOP(no operation) scheduler is best for you as reordering and merging of disk I/O according to disk sectors etc all is taken care by your Virtual host in most of cases. Similarly NOOP is best for SAN storage for same reasons. Another advantages is NOOP scheduler works on basic elevator algorithm FIFO and consumes least CPU.

• If you are using Solid sate disks also then NOOP is the only option to you if you are are on versions prior to RHEL 6.[ did not get opportunity to test it ]

I tested my 3 node RAC database test workload on virtual machines with three VDI disks on three different physical disks (equal read and write and I found CFQ and NOOP schedulers) doing better than Deadline and NOOP better than CFQ. But performance of CFQ was consistent more than NOOP. { ofcourse all three RAC nodes had same schedulers ]

It is very easy to change scheduler just place elevator=cfq, or elevator= deadline or elevator=noop  or elevator=ancp in /etc/grub.conf file in kernel you want to boot with and reboot system.

#### snippet of grub.conf to  add elevator=deadline to use deadline I/O scheduler for all disks ###

title Red Hat Enterprise Linux Server (2.6.18-8.el5)
        root (hd0,0) 
        kernel /vmlinuz-2.6.18-8.el5 ro root=/dev/sda2 elevator=deadline
        initrd /initrd-2.6.18-8.el5.img

   Another option from RHEL 5 is you can change it on fly and for specific disks. COOL. so depending on different needs or disk types you can choose different scheduler for different disks. For e.g. if your Linux sever is running Apache server and databases both and both placed on different disks then you may use cfq for apache while deadline for Oracle database and noop for SSD disks.

e.g. If you want noop to be used for disk sdc then you can do this on fly as below:

 echo noop > /sys/block/sdc/queue/scheduler 

Similarly you check  value of above parameter to see what elevator is being used for specific disk device.

In nutshell you need to test system beginning with with theoretically good scheduler setting for each disks for your workload and decide which scheduler gives you best performance.

Performance Tuning Linux Virtual machine on VMWARE, Oracle Virtual box and Oracle VM for performance testing

Performance testing is generally not suggested on Virtualized machine. But if we  allocate resources properly, create test cases properly and evaluate performance accordingly then it is fairly good to go for virtualized machines. Since virtualization is slow and we may not always have best HW(cost matters) we may need to  tune the virtual machines and their operating systems very much.

If you want to build up virtual performance testing setup here are the check lists and steps for Oracle Virtual box, VMware and Oracle VM tuning so that you get maximum of available Hardware and software. Most of the suggestions I've tested practically.

Check List : 

1. If using Hardware virtualized machine HWM or HWM with paravirtualized drivers then

A) Does your processor supports HW virtualization of (VT-X feature, most do) and your CPU has physical virtualization enabled.

B) Does your processor supports HW virtualizated I/O  (VT-D feature),quite new HW feature(improves I/O tremendously) and your CPU has enabled it.

C) Does your processor supports nested paged table(most do) and your Hyper-visor is using it.

D) Have you installed the necessary add on services like VBOX add on or VMWARE tools.

E) Are you using paravirtualized Ethernet cards like VMXNET on vmware or Paravirtualized network- vitrio-net instead of Intel Pro in case of Oracle virtual box.

F) Have you checked which I/O scheduler is best for you. Most of the hyper-visors like VMWARE ESX-i takes care of disk I/O submission so NOOP gives best performance with less CPU overhead.

G) Make sure you are not over committing memory or processors than available, which is allowed by some virtualization like VMWARE.

2) A) If using pure paravirtualized machine then is your kernel uptodate.

B) If required have you disabled the HW virtualization supported by processor in BIOS.

C) Are you monitoring virtual machine from metal-ware i.e. Dom0 using specialized commands xentop etc in Oracle VM server.

3) If using Inetl i7 then disable hyper threading as hyper threads are actual not core physical Arithmetic and Logical units and virtual box performance slows down due it. Intel i5 may give performance as good as i7


Tune GUEST :

- In order to save resources are you running your Linux in run-level 3 or without x-window.

- remove unnecessary packages

- use virtual disks with fixed storage unless space is issue.

- Have you disabled the services not required.

- Instead of creating many disks under many controllers create few virual disks under few controllers ,preferably on separate physical hard disks. If using Virtual Box you can change mother board type to ACHI

- Have you tuned your file system( like ext4 or removing atime attribute ) and using the one offering best performance and less overheads[ Be aware of its crash and recovery as well ]

- when not required please put Virtual machines GUI in background[ not possible in Oracle Virtual box]

- If using Virtual Box then make sure you are using appropriate disk controller. SATA disk controller has advantage of less overhead while IDEL controller can leverage the DISK caching. Use SCSI only when you are in need to match it to your prod system you need to simulate.

- If you have enabled the disk caching then in abrupt termination of machine there is high chance of DB corruption so be ready with recovery solutions.

- Make sure there be fewer paging and no swapping at all in Guest. Anticipatory I/O scheduler does not go well with swapping. Make sure you are not using this one though this is the case only with older kernel say 2.4.1  Swapping at host level may be better than swapping at GUEST so may be over-committing memory better in few cases.

- If Oracle SGA is large say 4GB and many concurrent sessions say 100 then seriously consider using huge pages but do not pre-page SGA. Make sure whole SGA fits into Huge pages pool else it will rather slow down. Please monitor system for a good duration after setting up. Disable transparent Huge pages RHEL 6.
 It is not helpful. Please see post : Configure Huge Pages on Linux

- if using Oracle VM then increase VM metal-ware memory from default 512MB to 1024MB.

- If using Paravirtualized machine and RHEL then change I/O scheduler from CFQ to DEADLINE. You can changed write time-out of deadline scheduler attribute.

- If you installed Linux originally with 1 CPU and adding CPUs later it after then you need to install kernel-smp package also and use apic and smp switch in /etc/grub.conf .  Most of the cases single processors or two processors [ or two cores ]  out performs the four processors [ or four core ] due minimized context switching.
 
- Snapshots slows down the performance due required extra maintenance of machine state so make sure this is disabled while performance testing.

- Try to use less colors say 16 colors  and minimize the video memory say from 16MB to 8MB.

- use large pages(not the OS one ) in Oracle Virtual box. Increase it from command line using vboxsrvc command.  It can not be increased from GUI. Please read manual.

- Use appropriate kernel network parameters like default and maximum send and receive buffer size.

- Decrease the ticker frequncy  by adding divider=10 in /etc/grub.conf boot loader for corresponding kernel line. RHEL 6 is tickless so not required for that.


- OEL RHEL compatible kernel offers slightly better processor speed 3% in single task and 1% in multi tasking than OEL-UEK (not sure why - due processor scheduler or any reason) but it offers sligjly less performance than OEL UEK kernel (even though both schedulers are kept same deadline for testing). So you can test which one suits your CPU/I-O

 - If using performance testing then use CPU affinity to assign CPU to each node according to capacity so that if respective performance of nodes or cluster has to be checked then CPU's usage should be correct. Avoid CPU over commit as well.

 - Do not add many virtual hard disks if it is on same physical box or  it is not essential for test  as doing so will increase virtualization overhead.  If Virtual machine freezes under heavy I/O then consider increasing host memory and guest memory.   In such cases you will get warning like 'ATA disk waits exceed 61 secsetc.'

 - Few paging can be tolerated but swapping should not be. Increase Linux VM parameter to increase unit of swapped pages in single call which is logarithmic(natual).

- if You disk freezes(too slow I/O)  [confirmed by Virtual machine log reporting too much disk time warning for e.g. Virtual box then detach disks,readd them in different controller with changed caching and then add back to original controller.Else machine will continue to freeze even after reboot ]

- Some versions of Oracle has mutex bugs- manly due process scheduling issue which causes significant CPU wait- make sure you are not being hit by same.



--- Read manual of virtual machine provide and OS Tunning

-- Just for manageability : UEFI linux does not go well with Windows 7 with dual boot for want of signing boot-loader for security so when installing Linux make sure you does not boot from UEFI disk.

Tuning Inserts in RAC

If Oracle database is not I/O bounded and is scalable by adding more CPU to single instance database then  it is likely to scale very well in RAC. However this is always not true in some cases and scalability of RAC can be limited  by many well known factors like slow interconnect or too busy interconnect. But there are few cases when RAC does not scale well in-spite of having very good interconnect. One such case is batch jobs with massive inserts on tables if there are too many right hand indexes(using ordered sequences or not ordered sequence with low cache, or ordered values coming from any source other than sequence) which caused major cluster overheads. In case 1) of UN-ordered sequences these GC waits can be minimized by increasing cache to large size (in order of thousands) and in case 2) sequences must be ordered then using reverse indexes or Hash partitioned indexes can minimize GC waits. Case 1) does not have CPU and I/O overhead while case 2) has major CPU and I/O overheads. So it must be evaluated that reduced GC waits benefits over-weigh additional CPU/ and I/O overhead required for Reverse/Hash partitioned indexes.  

TOTAL CLUSTER LATENCY WAITS (Prepare time and transfer time)

in graph 7:46 to 7:56 Reverse key index, 8:00 to 8:07 Hash partitioned indexes, 8:19 to 8:29 Normal Iidexe

Prepare time: 

• Negligible in case of Reverse Indexes
• Modest in Hash Partitioned indexes
• Very High in Normal Index

Transfer time is highest in case of Reverse Indexes as keys are spread as random as number of rows [ cluster factor CF is so high in Reverse key indexes and optimize can tend to ignore this index due high CF ] in table and so high number of blocks are transferred and this is also the reason that Reverse key indexes requires large number of I/O may be ten fold or more higher than normal index. But provided there is no scarcity of buffered cache this request can be satisfied by normal GC waits (negligible GC busy waits ).

Normal indexes has severe BUFFER BUSY waits(gc buffer busy release and gc buffer busy acquire) contention and due large time of pinning and log flushing they have significantly highest CR Block creation time( preparation time).

It will become more clear from below graph which depicts Latency Preparation time :

Below graph depicts transfer latency for all cases which is less in HASH partitioned index case:

WORK LOAD both instances : highest in Reverse key index, least in Normal Index, modest in Hash

Latency of both RAC instances separately:

Cluster Latency :

Input - Output (I/O) summary Graphs:

Throughput(Waite event types) :

You can notice too high tower for single blocks reads coming from too many read calls for db file sequential reads for REVERSE key indexes while these are not high in case of HASH partitioned indexes and Normal Index. Later two's have very large REDO log write required for gcs log flush.


IO Latency: 

Throughput requests (File Type) :

Operating System statistics:

 CPU usage ( at node1[node2 has similar statistics] ):

Disk I/O ( at node1[node2 has similar statistics] ):

Load Average Run Queue ( at node1[node2 has similar statistics] ):

Network packet transfer rate ( at node1[node2 has similar statistics] ):

Conclusion: 

• Contrary to common belief Reverse Indexes caused more logical I/O(and potentially physical I/Os) and more Interconnect traffic.Their I/O time is much more than CPU time. They just save time of pinning buffer and flushing and thus relieve pressure of Log file I/O. But this gain may not be enough to overweigh too I/O and CPU they may incu
• Hash Indexes :   Best choice if there is heavy GC buffer busy events.
• Normal Index: May still be better than above cases if not lot of right hand indexes and have fewer concurrency or fewer number of nodes.

log file sync wait event

This wait event belongs to commits wait class and implies user session has issued commit but it is not completed as redo log writers has not yet finished writing into the redo log file i.e all redo log buffers are not flushed to redo log file disk. Excessive waits on log file sync wait event indicates either there are too many commits or slow I/O sub system on which redo log files have been placed.

User Actions that should be taken:

• Do not put redo logs on RAID 5. Now a days SSD disks have evolved in much bigger use but NAND flash based SSD disks should not be used to store redo log file as redo log writes are sequential writes for which these disks are not suitable. DDR based SSD can be used to used to store redo logfile.  Place log files on dedicated disks. Consider putting log files on striped disks.

• Be prudent with frequency of commits and commit batch size .  If there are lots of short duration transactions, see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be piggybacked by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.

•Determine whether any activity can safely be done with NOLOGGING / UNRECOVERABLE options. This may be an option in data ware house where data can be reproducabile.

what is cost in execution plan in oracle

What is meaning of COST in execution plan in Oracle:

COST in execution plan is a relative factor which tells how much single block read,multi block reads, temporay space operations will take place along with , CPU cycles (cpu cycle can differe in various operations). If two costs are closer than it does not mean their execution time will also be closer. Generally index scan needs more CPU cycles than FTS. In same way Nested loop join involves more CPU than Hash Join and Sort Merge join., while hash and sort-merge join needs temporay work area UGA(part of PGA)(as well temp space depends) which is not needed in Nested Loop join.

You should gather system statistics at representative workload so that oracle knows what is single block read time, what is multiblock read time and what is CPU speed etc. This way cost calclulated is absolutely best provided the table,index,columns statistics(histograms, if needed) are accurate.

e.g. Consider below two queries for which cost are closer but execution time is differing a lot

1. select count(*) from (select * from mytab union all select * from mytab); and
2 select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);

Their cost in execution plan is quite close but execution time of second query is less than half of first query.  Below uses dynamic sampling but it does not make difference.

SQL> conn scott/tiger
Connected.
SQL> create table mytab as select * from all_objects;
Table created.
SQL> explain plan for select count(*) from (select * from mytab union all select * from mytab);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1229214271
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 313 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | | 115K| 313 (1)| 00:00:04 |
| 3 | UNION-ALL PARTITION| | | | |
| 4 | TABLE ACCESS FULL | MYTAB  | 46942 | 159 (2)| 00:00:02 |
| 5 | TABLE ACCESS FULL | MYTAB| 46942 | 159 (2)| 00:00:02 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
16 rows selected.
SQL> explain plan for select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3291583229
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 316 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 2 | 26 | 316 (1)| 00:00:04 |
| 3 | UNION-ALL | | | | | |
| 4 | SORT AGGREGATE | | 1 | | | |
| 5 | TABLE ACCESS FULL| MYTAB| 46942 | | 158 (1)| 00:00:02 |
| 6 | SORT AGGREGATE | | 1 | | | |
| 7 | TABLE ACCESS FULL| MYTAB  | 46942 | | 158 (1)| 00:00:02 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected.
SQL> --now alter system flsuh buffer_cache from sys session
SQL> alter session set statistics_level=all;
Session altered.

You notice COST of both queries are very close. Now check actual execution time

SQL> set timing on
SQL> select count(*) from (select * from mytab union all select * from mytab);
COUNT(*)
----------
100192
Elapsed: 00:00:01.77
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 5wn0myv3kcsvs, child number 0
-------------------------------------
select count(*) from (select * from mytab union all select * from mytab)
Plan hash value: 1229214271
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.77 | 1386 |
| 2 | VIEW | | 1 | 115K| 100K|00:00:01.60 | 1386 |
| 3 | UNION-ALL PARTITION| | 1 | | 100K|00:00:01.10 | 1386 |
| 4 | TABLE ACCESS FULL | MYTAB  | 1 | 46942 | 50096 |00:00:00.15 | 693 |
| 5 | TABLE ACCESS FULL | MYTAB| 1 | 46942 | 50096 |00:00:00.10 | 693 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

20 rows selected.
Elapsed: 00:00:00.09
SQL> --now alter system flsuh buffer_cache from sys session
SQL> select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
SUM(C)
----------
100192
Elapsed: 00:00:00.61
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gq7ca01amc88s, child number 0
-------------------------------------
select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab)
Plan hash value: 3291583229
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.60 | 1386 | 689 |
| 2 | VIEW | | 1 | 2 | 2 |00:00:00.60 | 1386 | 689 |
| 3 | UNION-ALL | | 1 | | 2 |00:00:00.60 | 1386 | 689 |
| 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.35 | 693 | 689 |
| 5 | TABLE ACCESS FULL| MYTAB  | 1 | 46942 | 50096 |00:00:00.27 | 693 | 689 |
| 6 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.25 | 693 | 0 |
| 7 | TABLE ACCESS FULL| MYTAB  | 1 | 46942 | 50096 |00:00:00.15 | 693 | 0 |
-------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

22 rows selected.
Elapsed: 00:00:00.10
SQL> spool off

You see second query is more than twice faster than first query
 

Simplification of use of Oracle 10g/11g sql tuning advisory without using sql tuning set

Procedure Advisory in this post gives SQL Tuning advisory report based on parameters user name,starting date and end date. This procedure used DBA_HIST_ views to extract top n sqls from time period between starting date and end date This procedure required user executing this procedure has been granted advisory privilege along with the select privileges on views DBA_HIST_SQLTEXT,DBA_HIST_SNAPSHOT,DBA_HIST_SQLSTAT  Further this procedure uses another procedure Create_Exec_Task defined later in this post

CREATE OR REPLACE PROCEDURE Advisory(p_user VARCHAR2,p_date1 DATE,p_date2 DATE, n NUMBER) AS

sqlt CLOB;

v_report CLOB;

CURSOR c1 IS

SELECT  DBA_HIST_SQLTEXT.sql_text sqlt

FROM

(

SELECT

sub.module, parsing_schema_name,

ROUND(sub.seconds_since_date/60,2) elapsed_time_delta_mins,

sub.execs_since_date,

sub.gets_since_date,

sub.snap_id,

ROUND(sub.seconds_since_date/DECODE(execs_since_date,0,1,

execs_since_date)/60,2) avg_exec_time,

sub.sql_id

FROM

( -- sub to sort before rownum

SELECT module, parsing_schema_name,

sql_id,

ROUND(SUM(elapsed_time_delta)/1000000) AS seconds_since_date,

SUM(executions_delta) AS execs_since_date,

SUM(buffer_gets_delta) AS gets_since_date,

snap_id

FROM

dba_hist_snapshot NATURAL JOIN dba_hist_sqlstat

WHERE

dba_hist_sqlstat.parsing_schema_name IN (p_user)

AND

begin_interval_time BETWEEN p_date1 AND p_date2-- '21-sep-2010' AND '30-sep-2010'

AND module NOT LIKE '%exp%' AND module NOT LIKE '%imp%'

AND module NOT LIKE '%TOAD%'

GROUP BY

module,sql_id,snap_id,parsing_schema_name

ORDER BY snap_id DESC

) sub

WHERE ROWNUM <=n

)T1 , DBA_HIST_SQLTEXT , dba_hist_snapshot

WHERE T1.sql_id=DBA_HIST_SQLTEXT.sql_id

AND T1.snap_id=dba_hist_snapshot.snap_id

--AND ( LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%' --OR

-- LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%insert%' OR LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%')

AND sql_text NOT LIKE '%DBMS_STATS%'

AND sql_text NOT LIKE  '%parallel(t,2)%'

AND sql_text NOT LIKE '%maxbkt%'

AND sql_text NOT LIKE '%substrb%'

ORDER BY elapsed_time_delta_mins ;--T1.snap_id

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO sqlt;

Create_Exec_Task(sqlt,v_report);

EXIT WHEN c1% NOTFOUND;

END LOOP;

DBMS_OUTPUT.PUT_LINE(v_report);

CLOSE c1;

END;

/

CREATE OR REPLACE PROCEDURE Create_Exec_Task(v_sql IN CLOB,v_report OUT CLOB ) AS

v_task_id VARCHAR2(254);

v_task_name VARCHAR2(254) DEFAULT 'task_name' ;

BEGIN

BEGIN

DBMS_SQLTUNE.DROP_TUNING_TASK(v_task_name);

EXCEPTION

WHEN OTHERS THEN NULL;

END;

v_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => v_sql,

time_limit => 120,

SCOPE => 'COMPREHENSIVE',

task_name =>v_task_name ,

description => 'no description'

);

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task_name);

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_name') INTO v_report FROM dual;

DBMS_SQLTUNE.DROP_TUNING_TASK(v_task_name);

END;

/

SQL> set serveroutput on size 999999

SQL>  set long 999999

SQL> exec advisory(user,sysdate-1,sysdate,10)

spool c:\advisory.log

set long 999999

set serveroutput on size 999999

declare

v_Sql clob;

v_out clob;

begin

v_sql:=' select * from t1,t2 where t1.c1=t2.c1 and t1.c1<=500';

create_exec_task(v_sql,v_out);

dbms_output.put_line(v_out);

end;

/

spool off

I/O tuning in Oracle : test the I/O throughput and latency:

Part 2: This is part 2 of series I/O tuning

To test the I/O throughput and latency:

Step 1:  Verify if asynchronous I/O is enabled by query

set linesize 130
col name format a70
select
       name
        , asynch_io
    from
        v$datafile d
       inner join v$iostat_file f on d.file# = f.file_no;

NAME                                                                   ASYNCH_IO
---------------------------------------------------------------------- ---------
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_OFF
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_OFF
/u02/oradata/TESTDB/sysaux01.dbf                                       ASYNC_OFF
/u02/oradata/TESTDB/undotbs01.dbf                                      ASYNC_OFF
/u02/oradata/TESTDB/users01.dbf                                        ASYNC_OFF
Step 2:  set the direct and asynchronous I/O:

sqlplus / as sysdba
SQL>alter system set filesystemio_options=setall scope=spfile;
SQL>shutdown immediate
SQL>startup

Step 3: verify asynch I/O

NAME                                                                   ASYNCH_IO
---------------------------------------------------------------------- ---------
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_ON
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_ON
/u02/oradata/TESTDB/sysaux01.dbf                                       ASYNC_ON
/u02/oradata/TESTDB/undotbs01.dbf                                      ASYNC_ON
/u02/oradata/TESTDB/users01.dbf                                        ASYNC_ON

Step 4: run the I/O calibration

SET SERVEROUTPUT ON

 DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
      1 /* # of disks */
      , 10 /* maximum tolerable latency in milliseconds */
      , iops /* I/O rate per second */
      , mbps /* throughput, MB per second */
      , lat  /* actual latency in milliseconds */
  );
  DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps);
END;
/

max_iops = 161 [I/O rate per secomd]
latency  = 11 [ I/O latency in miliseconds]
max_mbps = 91 [ maximum throughput read]

Step 5:  check the performed I/O size:

SQL>  select
  2      d.name
  3      , f.file_no
  4      , f.small_read_megabytes
  5      , f.small_read_reqs
  6      , f.large_read_megabytes
  7      , f.large_read_reqs
  8   from
  9      v$iostat_file f
 10      inner join v$datafile d on f.file_no = d.file#
 11   ;
NAME                                   FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS
----------------------------------- ---------- -------------------- --------------- -------------------- ---------------
/u02/oradata/TESTDB/system01.dbf             1                  277           34308                 2866            3033
/u02/oradata/TESTDB/system01.dbf             1                    0              17                    0               0
/u02/oradata/TESTDB/sysaux01.dbf             2                  191           24235                 2541            2541
/u02/oradata/TESTDB/undotbs01.dbf            3                  269           34381                 3893            3893
/u02/oradata/TESTDB/users01.dbf              4                  302           34763                  579             579