Oracle SQL Tuning with Oracle SQLTXPLAIN by Stelios Charalambides

By Stelios Charalambides

Oracle SQL Tuning with SQLTXPLAIN is a pragmatic advisor to SQL tuning the way in which Oracle's personal specialists do it, utilizing a freely downloadable software known as SQLTXPLAIN. utilizing this straightforward device you'll the best way to track even the main complicated SQL, and you'll learn how to do it speedy, with no the large studying curve often linked to tuning as a whole.
Firmly established in genuine international difficulties, this publication is helping you reclaim method assets and keep away from the most typical bottleneck in performance, badly tuned SQL. You'll find out how the optimizer works, tips on how to make the most of its newest gains, and while it's higher to show them off.

* speedy music any SQL assertion regardless of how complex.
* construct and track try out circumstances with out affecting production.
* Use the newest tuning positive aspects with self assurance.

Show description

Read Online or Download Oracle SQL Tuning with Oracle SQLTXPLAIN PDF

Best oracle books

Oracle Web Applications: PL/SQL Developer's Intro: Developer's Introduction

It is a very good publication, transparent and well-written. it is easy sufficient for these with out a lot adventure, but in addition presents sufficient aspect when you don't desire coddling. .. certainly, well worth the $.

Oracle Database 11g Underground Advice for Database Administrators

This day DBAs are anticipated to installation and deal with huge databases with caliber provider and little to no downtime. The DBA’s major concentration is on expanding productiveness and putting off idle redundancy during the company. although, there isn't any magic set of most sensible practices or not easy and quick ideas that DBAs have to stick with, and this may make lifestyles tough.

RMAN Recipes for Oracle Database 11g

It’s occasionally stated that the real task of an Oracle database administrator should be summed up in a single, crucial ability: with the intention to get better your database.
There’s a lot knowledge in that assertion. Of every thing you're answerable for as a database administrator, not anything is extra very important than the information itself. love it or now not, the fearsome accountability of defending your firms most crucial info falls squarely upon your shoulders:
- Lose that information and your organization may perhaps fail.
- Lose that information and also you may be out of a job.
Scared? you have to be. yet theres aid. you're not on my own. Oracle specialists Darl Kuhn, Sam Alapati, and Arup Nanda have come jointly during this booklet to teach you the ability of restoration supervisor, or RMAN, that is Oracles backup and restoration software of selection. RMAN Recipes for Oracle Database 11g is helping you are taking good thing about all that RMAN has to provide by way of supplying easy-to-understand suggestions to business-critical backup and restoration operations. this useful guide
- Demystifies the stairs required to guard what you are promoting data
- offers ready-made and step by step ideas to easy and complicated backup, fix, and restoration operations
- Is task-oriented, making it effortless so that you can uncover the answer to urgent backup and restoration problems
- Respects it slow by means of supplying example-based ideas, now not unending examining material
When the pressure’s on, you dont have time for lengthy discussions of idea. This e-book will get correct to the purpose with quickly and easy-to-read, step by step suggestions that could assist you backup and get well your information with confidence.
What you’ll learn
Oracle Database specialists Darl Kuhn, Sam Alapati, and Arup Nanda convey you the way to
- Reliably again up and get well your database utilizing Oracles restoration Manager.
- make the most of New Oracle Database 11g beneficial properties equivalent to the information restoration Advisor.
- permit Oracle Database deal with your backup records through the Flash restoration Area.
- Automate backup and restoration projects via writing scripts.
- Troubleshoot RMAN difficulties and optimize RMAN performance.
- Interface with a Media administration Layer to regulate your backup tape library.
- get over the lack of a keep an eye on dossier, lack of an internet redo log, and different (hopefully! ) strange situations.
Who is that this e-book for?
Oracle database directors answerable for database backup and restoration operations.

Achieving Extreme Performance with Oracle Exadata

Maximize Oracle Exadata functions Leverage all the strong gains on hand within the Oracle Exadata Database computer utilizing the confirmed strategies inside of this Oracle Press advisor. Written by way of Oracle specialists, attaining severe functionality with Oracle Exadata indicates you ways to take complete good thing about this entire, optimized package deal of software program, servers, and garage.

Additional info for Oracle SQL Tuning with Oracle SQLTXPLAIN

Sample text

Figure 2-11. A small part of the execution plan, with 96 steps 31 CHAPTER 2 N THE COST-BASED OPTIMIZER ENVIRONMENT How do we handle an execution plan that has 96 steps, or more? Do we hand that plan over to development and tell them to tune it? With SQLT you don’t need to do this. Let’s look at this page in more detail, by zooming in on the top right hand portion and look at the over and under estimates part of the screen (see Figure 2-12). Figure 2-12. The top right hand portion of the section showing the execution plan’s over and under estimates We know that Figure 2-12 is a SQLT XECUTE report, (we know this because we have over and under estimate values in the report).

Other information from the system includes the following: u Speed of the disks u Speed of the CPU u Number of CPUs u Database block size These metrics can be easily extracted from the system and are shown in the SQLT report also (under the “Environment” section). The amount of I/O and CPU resource used on the system for any particular step can now be calculated and thus used to derive a definite cost. This is the key concept for all tuning. The optimizer is always trying to reduce the cost for an operation.

INDEX RANGE SCAN actually returned 948 rows 2. INDEX RANGE SCAN actually returned 948 rows 3. The result of step 1 and 2 was fed into a NESTED LOOP which actually returned 948 rows 4. INDEX RANGE SCAN actually returned 948 rows 5. NESTED LOOP (of the previous step with result of step 3) And so on. The best way to approach this kind of problem is to read the steps in the execution plan, understand them, look at the over and under estimates and from there determine where to focus your attention. Now look at Figure 2-13.

Download PDF sample

Rated 5.00 of 5 – based on 4 votes