Synopsis. Oracle Database 11g free up 1 (11gR1) aspects the new SQL performance Analyzer that promises to ease enormously the workload of busy Oracle DBAs since it offers a way to accurately consider finished database workloads for “before versus after” performance alterations. this article – the subsequent in a sequence on new Oracle Database 11g SQL efficiency development facets – discusses the way to use the SQL performance Analyzer to consider a SQL workload generated from an Oracle 10gR2 database to detect competencies efficiency concerns for that workload if it have been executed within an Oracle 11g database gadget.
The outdated article in this sequence illustrated the fundamentals of the usage of Oracle Database 11g’s new SQL efficiency Analyzer (SPA) tool to:
in this article, I’ll exhibit how the SQL efficiency Analyzer can analyze more advanced Oracle environment changes – exceptionally, the upgrade of the cost-primarily based optimizer (CBO) between Oracle database releases -- and that i’ll show the way to transfer a SQL workload generated from an Oracle 10gR2 database to an Oracle 11gR1 database. in addition, on account that I targeting the PL/SQL interface to measure SQL workload efficiency in the ultimate article, I’ll focal point on the usage of Oracle 11g’s business supervisor (EM) Database control interface for the SQL performance Analyzer to reveal how convenient it's to research a SQL workload with a couple of mouse clicks.SQL performance evaluation situation: Simulating an Optimizer improve
1.) prepare for Simulation. I’ve built an Oracle 10gR2 database named DB10G the usage of the commonplace “seed” database inside an Oracle 10gR2 domestic. To carry ahead the similar instance from the outdated article, I then developed the, SH.SALES_AGENTS desk on that database, loaded it with the same pattern set of approximately 420,000 rows, after which constructed the identical 5 indexes towards that table. I used the similar DDL that I offered in list 1.1 for the outdated article to accomplish this, and i utilized the identical DML statements (LoadSalesAgents.sql) to function an preliminary load of this table.
2.) put together for SQL Workload Gathering. subsequent, I’ll create a SQL Workload on the DB10G database. I’ll use the identical SQL statements that entry the SH.SALES_AGENTS table that I used within the outdated article, together with a few further SQL statements that’ll entry probably the most different demo schema tables. The statements that include this SQL workload are shown in GenerateSQLWorkload.sql. observe that the final 4 SQL statements – those tagged as LDGN 4.1 via LDGN four.four - make use of older optimizer pointers that are nevertheless purchasable in Oracle 9i but now not totally supported in Oracle 10g. I’ll use these statements to exhibit how the SQL efficiency Analyzer can simulate performance the usage of cost-based optimizer settings from different older Oracle database environments. The code shown in listing 2.1 captures these SQL statements into a SQL Tuning Set named STS_SPA_200.
three.) Stage and Export SQL Tuning Set. Now that I’ve achieved the SQL workload and have gathered the ensuing statistics into the STS_SPA_200 SQL Tuning Set, I’ll transfer that workload and its corresponding execution facts to my Oracle 11gR1 database. I’ll do that with the aid of creating staging tables to cling that assistance, transferring the SQL workload and its performance suggestions into that table, after which transporting these facts to the targeted Oracle 11g database. checklist 2.2 shows how I captured (or “packed”) the SQL Tuning Set into staging tables with the DBMS_SQLTUNE.PACK_STGTAB_SQLSET method. I then used Oracle DataPump to export those staging tables into a DataPump Export dumpset named DumpStagingTable.dmp.
four.) Import, unpack, and prepare SQL Tuning Set for test execution. After copying the DataPump dump set into the default DataPump listing for my Oracle 11g database, I’ll deserve to transfer the assistance contained inside the staging tables for the SQL Tuning Set into my Oracle 11g goal database for analysis. record 2.3 illustrates how I imported the staging tables from Oracle 10gR2 into the goal Oracle 11gR1 database using Oracle DataPump Import and an acceptable parameter file. I then used the DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET technique to “unpack” the SQL Workload stored in those staging tables into my goal Oracle 11gR1 database.
5.) Simulate an Optimizer improve From 10.2.0.1 to 184.108.40.206. Now that my preparations are completed, I’m ready to turn the SQL performance Analyzer free to let it discover any important performance impacts on the SQL statements within the imported SQL Workload if it were performed towards the goal Oracle 11gR1 database. I’ll use the Oracle Database 11g’s commercial enterprise manager Database handle interface to function this analysis. figure 2.1 suggests the preliminary state of the SQL efficiency Analyzer panel.
crackingweb.com | crackingforum - crackingweb | cracking downloads, cracking tutorial, free premium accounts, sentry mba | cracking, hack, crack, programming, proxies, program, combos, spotify, hacking, configs, software, development, sentry, netflix, dumps
passcometrue.com | passcometrue - pass all it certification exams and make your dream come true | try passcometrue free demo of cisco, comptia, hp, ibm, microsoft exams and so on. | dumps, brain, exams, brainitdump, certification
braindumpsdeals.com | brain-dumps deals | prepare with actual questions for 100% results in exam | |
validdumps.com | download valid 1Z0-054 dumps certification 1Z0-054 dumps 2016-pass guaranteed questions and answers | |
ccnapass4sure.com | ccna pass4sure exam questions dumps: ccnapass4sure | get ccna pass4sure braindumps and pass your exam easily. real exam questions dumps |
it-certification-dumps.blogspot.in | it certification 1Z0-054 dumps : oracle, ibm and many more | it certification 1Z0-054 dumps : oracle, ibm and many more | dumps, certification, oracle, cisco, certifications, ocpjp, free, actual, download, scjp, brain