Gavinsoorma.com.au



Performance Tuning Workshop Lab Exercise ThreeObtain the current port for Oracle Database ExpressSQL> select dbms_xdb_config.gethttpsport from dual;GETHTTPSPORT------------ 5500Launch Oracle Database Express (use the port returned by the query above) the Performance Hub home page via the Performance menu as shown Flush the current contents of the Shared Pool SQL> alter system flush shared_pool;System altered.Connect as the DEMO user [oracle@host01 ~]$ . oraenvORACLE_SID = [oracle] ? orclThe Oracle base has been set to /u01/app/oracle[oracle@host01 ~]$ sqlplus demo/oracleSQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 7 23:12:02 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Tue Feb 06 2018 23:18:47 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionRun the following Pl/SQL block which will perform a number of reads of the ALL_OBJECTS table but uses bind variables. Note the execution timeSQL> set serverout onSQL> declare type rc is ref cursor; l_rc rc; l_dummy all_objects.object_name%type; l_start number default dbms_utility.get_time; begin for i in 1 .. 10000 loop open l_rc for 'select /* USING_BINDS */ object_name from all_objects where object_id = :x' using i; fetch l_rc into l_dummy; close l_rc; -- dbms_output.put_line(l_dummy); end loop; dbms_output.put_line (round((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' );end;/ 1.35 Seconds...PL/SQL procedure successfully completed.Query V$SQLAREA and see the number of SQL_ID values for the SQL statement executed in the above PL/SQL block SQL> select sql_id,sql_text,hash_value,plan_hash_value from v$sqlarea where sql_text like 'select /* USING_BINDS */ object_name%';SQL_ID-------------SQL_TEXT------------------------------------------------------------------------------------------------------------------------HASH_VALUE PLAN_HASH_VALUE---------- ---------------ddu3tm5s01v2xselect /* USING_BINDS */ object_name from all_objects where object_id = :x1879108701 614408479Take a manual AWR SnapshotSQL> exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completed.Execute the following PL/SQL block which will perform a number of reads of the ALL_OBJECTS table. In this case bind variables are not usedSQL> declare type rc is ref cursor; l_rc rc; l_dummy all_objects.object_name%type; l_start number default dbms_utility.get_time; begin for i in 1 .. 10000 loop open l_rc for 'select /* HARD_PARSE */ object_name from all_objects where object_id = ' || i; fetch l_rc into l_dummy; close l_rc; -- dbms_output.put_line(l_dummy); end loop; dbms_output.put_line (round((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' ); end;/ While the PL/SQL block is still running from another session as SYS user query the V$SQLAREA and note the number of SQL_ID values as well as the PLAN_HASH_VALUE and HASH_VALUE columnsSQL>select sql_text,sql_id,hash_value,plan_hash_value from v$sqlarea where sql_text like 'select /* HARD_PARSE */ object_name%';SQL_TEXT------------------------------------------------------------------------------------------------------------------------SQL_ID HASH_VALUE PLAN_HASH_VALUE------------- ---------- ---------------select /* HARD_PARSE */ object_name from all_objects where object_id = 31272n1509wg80tm 2029257523 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 32116tm61g2jc3h2 3306556930 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 303aw9n9dxr5s67c 1851529452 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 3454cd3yh69tc7ak 2476088658 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 3084ru6j2392wg35 3526245477 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 3385xfbqb88p4jqg 290604751 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 3444t8wqh9zt0tsy 2140170014 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 3013ym6fqhkp8u6a 626288842 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 3116mapwdcx24wyw 975336412 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 305af7b9wcs753ja 812813866 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 339fjb447maad5dv 3567687099 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 318g6uvhcvzvd617 4290156583 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 327a8hrpw4yvp8fn 1035641300 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 317bd4h7mdpg9csh 1794421520 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 3231c68afyf0thga 2618081770 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 322crrn6n5mctkxv 1724697531 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 316597hck4215sn9 68346505 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 349b78ac9dfj9vbf 1561652590 0select /* HARD_PARSE */ object_name from all_objects where object_id = 3461k3j7c4k62ft9 610351913 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 343gt4pzgphjumkh 1629310544 614408479select /* HARD_PARSE */ object_name from all_objects where object_id = 340dnccnqr7vg6cs 3484916120 614408479Run this query to identify SQL statements which are almost similar in text where the first 50 characters are exactly the same. These are SQL queries which are candidates to possibly make use of bind variables instead of literalsSQL> select plan_hash_value,substr(sql_text,1,80) from v$sqlarea where substr(sql_text,1,50) in (select substr(sql_text,1,50) from v$sqlarea having count (*) > 10 group by substr(sql_text,1,50)); SQL> PLAN_HASH_VALUE---------------SUBSTR(SQL_TEXT,1,80)------------------------------------------------------------------------------------------------------------------------ 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 0select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh22 rows selected.SQL> set linesize 120SQL> /PLAN_HASH_VALUE---------------SUBSTR(SQL_TEXT,1,80)------------------------------------------------------------------------------------------------------------------------ 614408479select /* HARD_PARSE */ object_name from all_objects wh 0select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects whPLAN_HASH_VALUE---------------SUBSTR(SQL_TEXT,1,80)------------------------------------------------------------------------------------------------------------------------ 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 0select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh 614408479select /* HARD_PARSE */ object_name from all_objects wh41 rows selected.Open the Database Express Performance page. Note the increase in CPU activity caused by the hard parses shown by the green colour shadingView the main database wait events at this point in time We can see that the majority of the database wait events are related to CPU.Create another manual AWR snapshotSQL> exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completed.After the AWR snapshot has been completed, view the ADDM report for the period interval between the last two AWR snapshotsNote the ADDM findings related to significant hard parse activity In the DEMO user session wait till the PL/SQL block completes and note the execution time as compared to the execution time using bind variables SQL> declare type rc is ref cursor; l_rc rc; l_dummy all_objects.object_name%type; l_start number default dbms_utility.get_time; begin for i in 1 .. 10000 loop open l_rc for 'select /* HARD_PARSE */ object_name from all_objects where object_id = ' || i; fetch l_rc into l_dummy; close l_rc; -- dbms_output.put_line(l_dummy); end loop; dbms_output.put_line (round((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' ); end;/ 669.04 Seconds...PL/SQL procedure successfully completed. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download