Performance Tuning Tools
This is the continuation to the previous article about the Tools used in the performance Tuning.
Please find the previous article here.
In this article we will discuss about
- Oracle SQL Tuning advisor and baseline.
- OS Monitoring.
Oracle SQL Tuning Advisor and Baselines:
Oracle SQL Tuning Advisors can be used for the instance level tuning.
SQL tuning advisor invokes the Automatic Tuning Optimizer to perform the SQL tuning on the statements that it takes as an input. It takes one or more SQL statements to perform this action.
There is an option enable the maintenance window when you install the Oracle DB. One of the jobs of this maintenance window is to run the SQL tuning advisor. This will identify the problematic SQLs which has ran earlier in the 24 hours and try to automatically fix the SQL statements if that can be fixed automatically by the instance. It will fix the issues related to the statistics and optimizer.
To Enable the automatic SQL tuning advisor, follow the below query.
|
1 2 3 4 5 6 7 |
BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( Client_name => ‘sql tuning advisor’, Operation => NULL, Window_name =>NULL); END; / |
To disable the automatic SQL tuning advisor, follow the below query.
|
1 2 3 4 5 6 7 |
BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( Client_name => ‘sql tuning advisor’, Operation => NULL, Window_name =>NULL); END; / |
The part of the automatic SQL profiles is, from the AWR snapshots that has been captured, it will identify the SQL statements which require the tuning. Then it will generate the recommendations in the background and test the profile of the SQLs with the fixes, implements them and automatically tunes the reports.
So, it will create the SQL profiles and apply them. When there is any SQL statement is being executed, with that profiles it will take the best optimizer plan and runs it with the better performance and better execution plan.
We can run the SQL Tuning advisor manually using the below statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
SQL> @?/rdbms/admin/sqltrpt 15 Most expensive SQL in the cursor cache ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID ELAPSED SQL_TEXT_FRAGMENT ------------- ---------- ------------------------------------------------------- axxtbj8m7udpc ########## SELECT IL.ITEM, IL.LOC FROM RMS.ITEM_LOC IL WHERE 1 dn8bxn402h09b ########## SELECT IL.ITEM, IL.LOC FROM RMS.ITEM_LOC IL WHERE 1 6mcpb06rctk0x ########## call dbms_space.auto_space_advisor_job_proc ( ) 74cpnuu24wmx7 ########## SELECT TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK, SEGMEN b6usrg82hwsa3 7,671.41 call dbms_stats.gather_database_stats_job_proc ( ) 8p6ytz7ujkr9x 4,715.07 begin :1 := ALC_CALC_SETUP_SQL.SETUP_CALCULATION(:2 , 0tktq240d34xv 4,028.10 BEGIN RMSMFM_ITEMS.GETNXT(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,: f63rfdamawhv2 3,167.05 select ts#, file#, block# from seg$ where type# =11 3xjw1ncw5vh27 2,564.22 SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYP 47hah3dzz7ba9 2,268.85 BEGIN RMSMFM_ITEMLOC.GETNXT(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 8t373z5u9ztup 1,296.90 begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6, SQL_ID ELAPSED SQL_TEXT_FRAGMENT ------------- ---------- ------------------------------------------------------- 3q0bfpsw4uhps 1,208.36 SELECT IL.ITEM, IL.LOC FROM RMS.ITEM_LOC IL WHERE 207s9qc5gcv5c 1,139.50 begin RPM_MBC_ATTRIBUTE_SQL.SEARCH(:1 , :2 , :3 , :4 , dx36agst9x6ra 1,078.46 BEGIN API_LIBRARY.GET_RIB_SETTINGS(:1 ,:2 ,:3 ,:4 ,:5 , 5762udhh3us7c 1,074.65 select 1 from user_objects where object_name = 'API_LIB 15 Most expensive SQL in the workload repository ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID ELAPSED ------------- ---------- SQL_TEXT_FRAGMENT -------------------------------------------------------------------------------- ajkwmumdzgnvd ########## SELECT 'PO' AS SOURCE_TYPE, OH.CREATE_ID, OH.ORDER_NO, dn8bxn402h09b ########## SELECT IL.ITEM, IL.LOC FROM RMS.ITEM_LOC IL WHERE 1 axxtbj8m7udpc ########## SELECT IL.ITEM, IL.LOC FROM RMS.ITEM_LOC IL WHERE 1 SQL_ID ELAPSED ------------- ---------- SQL_TEXT_FRAGMENT -------------------------------------------------------------------------------- 18v9czscf9m5q ########## SELECT 'PO' AS SOURCE_TYPE, OH.CREATE_ID, OH.ORDER_NO, 6mcpb06rctk0x ########## call dbms_space.auto_space_advisor_job_proc ( ) 74cpnuu24wmx7 ########## SELECT TABLESPACE_ID, HEADER_FILE, HEADER SELECT TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK, SEGMEN SQL_ID ELAPSED ------------- ---------- SQL_TEXT_FRAGMENT -------------------------------------------------------------------------------- 3xjw1ncw5vh27 4,650.25 SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYP b6usrg82hwsa3 3,976.96 call dbms_stats.gather_database_stats_job_proc ( ) 6zda9fh0p6n3z 3,518.16 SELECT MIN(MIN_WRITETIME), MAX(MAX_WRITETIME), SYSDATE SQL_ID ELAPSED ------------- ---------- SQL_TEXT_FRAGMENT -------------------------------------------------------------------------------- 5g6ctsc68kzvk 3,377.22 SELECT COUNT(1) FROM ( SELECT IL.ITEM, IL.LOC FROM f63rfdamawhv2 3,179.48 select ts#, file#, block# from seg$ where type# =11 dk53hkxkxq7sp 2,658.81 SELECT /*+ PARALLEL(tab, 10) */ TAB.ITEM, TAB.LOC, 'P' SQL_ID ELAPSED ------------- ---------- SQL_TEXT_FRAGMENT -------------------------------------------------------------------------------- 7xfjgkaqpgdrg 1,633.71 INSERT INTO NEX_ITEM_LOC_SOH_STG (ITEM,ITEM_PARENT,IT gcza3qh47u708 1,481.68 SELECT /*+ PARALLEL(OIL,6) */ IM.DEPT, IM.CLASS, IM.S 5762udhh3us7c 1,419.98 select 1 from user_objects where object_name = 'API_LIB Specify the Sql id ~~~~~~~~~~~~~~~~~~ Enter value for sqlid: 0tktq240d34xv Sql Id specified: 0tktq240d34xv Tune the sql ~~~~~~~~~~~~ GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : TASK_1447 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 07/11/2018 05:25:40 Completed at : 07/11/2018 05:25:42 ------------------------------------------------------------------------------- Schema Name : RMS Container Name: RMSCNV SQL ID : 0tktq240d34xv SQL Text : BEGIN RMSMFM_ITEMS.GETNXT(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ); END; ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - Type of SQL statement not supported. ------------------------------------------------------------------------------- SQL> |
Tuning recommendations include:
- Collection of object statistics
- Creation of indexes
- Rewriting SQL statements
- Creation of SQL profiles
- Creation of SQL plan baselines
This will only give the recommendations and it is totally our call to actually implement the recommendations or not.
Baselines are used when there is a SQL statement behaving different at different times.
OS MONITORING:
OS Monitoring is also an important task in Performance tuning. The 2 major resources to be monitored in OS are CPU and memory. Other than these 2 resources, I/O and network monitoring are also important in terms of the performance tuning.
Few Commands to monitor CPU and Memory on Linux platform are:
To identify the Top 10 PIDs and commands using CPU .
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[oracle@lexdty9063 ~]$ ps -eo pcpu,pid -o args= | sort -k1 -r | head -10 %CPU PID 99.6 14903 oracleCNVCDB (LOCAL=NO) 99.5 13936 oracleCNVCDB (LOCAL=NO) 99.4 5264 oracleCNVCDB (LOCAL=NO) 2.0 2631 /opt/BESClient/bin/BESClient 0.9 5298 ora_vktm_CNVCDB 0.5 23870 oracleCNVCDB (LOCAL=NO) 0.2 5328 ora_dia0_CNVCDB 0.2 26802 oracleCNVCDB (LOCAL=NO) 0.2 26746 oracleCNVCDB (LOCAL=NO To get the top 10 commands. [oracle@lexdty9063 ~]$ ps -eo pcpu,pid -o comm= | sort -k1 -r | head -10 %CPU PID 99.6 14903 oracle_14903_cn 99.5 13936 oracle_13936_cn 99.4 5264 oracle_5264_cnv 2.0 2631 BESClient 0.9 5298 ora_vktm_cnvcdb 0.5 23870 oracle_23870_cn 0.2 5328 ora_dia0_cnvcdb 0.2 26746 oracle_26746_cn 0.2 26742 oracle_26742_cn |
To Identify the total usage of CPU using SAR:
|
1 2 3 |
[oracle@lexdty9063 ~]$ sar -P ALL 1 2 |grep 'Average.*all' | awk -F" " '{print 100.0 -$NF}' 38.36 [oracle@lexdty9063 ~]$ |
To identify the total CPU usage excluding SYSTEM using TOP
|
1 2 |
[oracle@lexdty9063 ~]$ top -b -n1 | grep "Cpu(s)" | awk '{print $2 + $4}' 36.4 |
Memory monitor commands:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[oracle@lexdty9063 ~]$ vmstat -n 1 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 3 0 1478264 493696 192612 28548652 0 0 89 87 1 0 36 1 62 1 0 3 0 1478264 493672 192612 28548680 0 0 0 32 4654 2466 38 0 62 0 0 3 0 1478264 493672 192612 28548680 0 0 0 0 4744 2469 38 0 62 0 0 3 0 1478264 493672 192620 28548672 0 0 0 36 4958 2753 39 1 61 0 0 3 0 1478264 493672 192620 28548680 0 0 0 36 4931 2689 39 0 61 0 0 3 0 1478264 493672 192620 28548680 0 0 0 0 4869 2498 38 0 62 0 0 3 0 1478264 493672 192620 28548680 0 0 0 0 4673 2510 38 0 62 0 0 3 0 1478264 493672 192620 28548684 0 0 0 72 4757 2501 38 0 61 0 0 8 0 1478264 493680 192620 28548684 0 0 4 4 4696 2445 38 0 62 0 0 3 0 1478264 512280 192628 28548680 0 0 0 52 4833 2628 38 1 61 0 0 4 0 1478264 512592 192628 28548688 0 0 0 32 4636 2419 38 0 62 0 0 [oracle@lexdty9063 ~]$ free -m total used free shared buffers cached Mem: 32109 31685 423 8437 188 27880 -/+ buffers/cache: 3616 28492 Swap: 29296 1442 27854 |
By this we conclude the topic of the Performance Tuning Tools and we will discuss more in depth on how to use these tools in my upcoming articles.
Thank you…
|
1 |
Loading...
