Gavinsoorma.com.au



Performance Tuning Workshop Lab Exercise SevenLaunch Oracle Enterprise Manager Cloud Control From the Targets> Databases menu select the ORCL database From the Performance menu select Top Activity Connect as the DEMO user and create the TEST_PROFILE tableSQL> conn demo/oracleConnected.SQL> create table test_profile (id number );Table created.Insert rows into the table, create an index and then gather statistics on the tableSQL> declare begin for i in 1 .. 10000 loop insert into test_profile values(i); end loop; end;/ PL/SQL procedure successfully completed.SQL> create index test_idx on test_profile(id);Index created.SQL> exec dbms_stats.gather_table_stats('','TEST_PROFILE');PL/SQL procedure successfully completed.Execute a costly SQL statement in a loop which will disable the use of the index on the TEST_PROFILE tableSQL> declarex number;beginfor i in 1 .. 500000loopselect /*+ no_index(test test_idx) */ * into x from test_profile where id=1;end loop;end;/ While the PL/SQL block is still running, refresh the Top Activity page and note the Top SQL statement. The green colour indicates high CPU utilization Select the query and in the Actions field select Schedule SQL Tuning Advisor and then click on Go Create and schedule an SQL Tuning Task Click on SubmitNote that the SQL Tuning Advisor has recommended the use of a SQL ProfileClick on the spectacles icon in the Compare Execution Plans columnNote the elapsed time, CPU time and I/O resource usage between the original execution plan and the new execution plan if a SQL Profile was implemented The original statement was performing a FULL TABLE SCAN because the use of the index had been disabled via a hintThe new execution plan with the SQL Profile implemented uses the index (in spite of the NOINDEX hint) Click on Yes Create the SQL Profile Click on the Implement button Now the explain plan shows that an index on the table is being used in spite of the NO_INDEX hint and also we can see that a SQL Profile is being used for this SQL statementSQL> SQL> explain plan for select /*+ no_index(test_profile test_idx) */ * from test_profile where id=1;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Plan hash value: 2882402178-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------- 1 - access("ID"=1)Note----- - SQL profile "SYS_SQLPROF_0161c36810d70000" used for this statement17 rows selected.SQL> ................
................

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

Google Online Preview   Download