Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

edb360 taking a long time

with 2 comments

In most cases edb360 takes less than 1hr to execute. But I often hear of cases where it takes a lot longer than that. In a corner case it was taking several days and it had to be killed.

So the question is WHY edb360 takes that long?

Well, edb360 executes thousands of SQL statements sequentially (intentionally). Many of these queries read data from AWR and in particular from ASH. So, lets say your ASH historical table has 2B rows, and on top of that you have not gathered statistics on AWR tables in years, thus CBO under-estimates cardinality and tends to use index access and nested loops. In such extreme cases you may end up with suboptimal execution plans that expect to return a few rows, but actually read a couple of billion rows using index access operations and nested loops. A query like this may take hours to complete!

As of version v1515, edb360 has a shortcut algorithm that ends an execution after 8 hours. So you may get an incomplete output, but it ends normally and the partial output can actually be used. This is not a solution but a workaround for those long executions.

How to troubleshoot edb360 taking long?

Steps:

1. Review files 00002_edb360_dbname_log.txt, 00003_edb360_dbname_log2.txt, 00004_edb360_dbname_log3.txt and 00005_edb360_dbname_tkprof_sort.txt. First log shows the state of the statistics for AWR Tables. If stats are old then gather them fresh with script edb360/sql/gather_stats_wr_sys.sql

2. If number of rows on WRH$_ACTIVE_SESSION_HISTORY as per 00002_edb360_dbname_log.txt is several millions, then you may not be purging data periodically. There are some known bugs and some blog posts on this regard. Review MOS 387914.1 and proceed accordingly. Execute query below to validate ASH age:

SELECT TRUNC(sample_time, 'MM'), COUNT(*)
  FROM dba_hist_active_sess_history
 GROUP BY TRUNC(sample_time, 'MM')
 ORDER BY TRUNC(sample_time, 'MM')
 /

3. If edb360 version (first line on its readme) is older than 1 month, download and use latest version: https://github.com/carlos-sierra/edb360/archive/master.zip (link is also provided on the right-hand side of this blog under downloads).

4. Consider suppressing text and or csv reports. Each for an estimated gain of about 20%. Keep in mind that when suppressing reports, you start loosing some functionality. To suppress lets say text and csv reports, place the following two commands at the end of script edb360/sql/edb360_00_config.sql

DEF edb360_conf_incl_text = ‘N';
DEF edb360_conf_incl_csv = ‘N';

5. If after going through steps 1-4 above, edb360 still takes longer than a few hours, feel free to email author carlos.sierra.usa@gmail.com and provide 4 files from step 1.

Written by Carlos Sierra

May 6, 2015 at 8:19 pm

What is new on edb360 v1510?

with 2 comments

Every release of edb360 includes some “new goodies”. Latest version v1510 reduces the number of execution parameters from 2 to just 1. It also incorporates a new configuration file which is mostly static, but it can be user-modified for special cases. And new sections below are all now part of edb360. Enjoy!

Interconnect Ping Latency and Performance


 

 

 

 

 

 

 

 

System Metric History and Summary

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

System Statistics

Written by Carlos Sierra

March 31, 2015 at 6:58 am

Posted in edb360

Discovering if a System level Parameter has changed its value (and when it happened)

with 4 comments

Quite often I learn of a system where “nobody changed anything” and suddenly the system is experiencing some strange behavior. Then after diligent investigation it turns out someone changed a little parameter at the System level, but somehow disregarded mentioning it since he/she thought it had no connection to the unexpected behavior.  As we all know, System parameters are big knobs that we don’t change lightly, still we often see “unknown” changes like the one described.

Script below produces a list of changes to System parameter values, indicating when a parameter was changed and from which value into which value. It does not filter out cache re-sizing operations, or resource manager plan changes. Both would be easy to exclude, but I’d rather see those global changes listed as well.

Note: This script below should only be executed if your site has a license for the Oracle Diagnostics pack (or Tuning pack), since it reads from AWR.

WITH
all_parameters AS (
SELECT snap_id,
       dbid,
       instance_number,
       parameter_name,
       value,
       isdefault,
       ismodified,
       lag(value) OVER (PARTITION BY dbid, instance_number, parameter_hash ORDER BY snap_id) prior_value
  FROM dba_hist_parameter
)
SELECT TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(s.end_interval_time, 'YYYY-MM-DD HH24:MI') end_time,
       p.snap_id,
       p.dbid,
       p.instance_number,
       p.parameter_name,
       p.value,
       p.isdefault,
       p.ismodified,
       p.prior_value
  FROM all_parameters p,
       dba_hist_snapshot s
 WHERE p.value != p.prior_value
   AND s.snap_id = p.snap_id
   AND s.dbid = p.dbid
   AND s.instance_number = p.instance_number
 ORDER BY
       s.begin_interval_time DESC,
       p.dbid,
       p.instance_number,
       p.parameter_name
/

Sample output follows, where we can see a parameter affecting Degree of Parallelism was changed. This is just to illustrate its use. Enjoy this new free script! It is now part of edb360.
Screen Shot 2015-03-25 at 19.15.26

Written by Carlos Sierra

March 25, 2015 at 7:25 pm

SQLTXPLAIN under new administration

leave a comment »

During my 17 years at Oracle, I developed several tools and scripts. The largest and more widely used is SQLTXPLAIN. It is available through My Oracle Support (MOS) under document_id 215187.1.

SQLTXPLAIN, also know as SQLT, is a tool for SQL diagnostics, including Performance and Wrong Results. I am the original developer and author, but since very early stages of its development, this tool encapsulates the expertise of many bright engineers, DBAs, developers and others, who constantly helped to improve this tool on every new release by providing valuable feedback. SQLT is then nothing but the collection of many good ideas from many people. I was just the lucky guy that decided to build something useful for the Oracle SQL tuning community.

When I decided to join Enkitec back on 2013, I asked Mauro Pagano to look after my baby (I mean SQLT), and sure enough he did an excellent job. Mauro fixed most of my bugs, as he jokes about, and also incorporated some of his own :-). Mauro kept SQLT in good shape and he was able to continue improving it on every new release. Now Mauro also works for Enkitec, so SQLT has a new owner and custodian at Oracle.

Abel Macias is the new owner of SQLT, and as such he gets busy maintaining and enhancing this tool among other duties at Oracle. So, if you have enhancement requests, or positive feedback, please reach out to Abel at his Oracle account: abel.macias@oracle.com. If you come across some of my other tools and scripts, and they show my former Oracle account (carlos.sierra@oracle.com), please reach out to Abel and he might be able to route your concern or question.

Since one of my hobbies is to build free software that I also consume, my current efforts are on eDB360, eAdam and eSP. The most popular and openly available is eDB360, which basically gives your a 360-degree view of a database without installing anything. Then, Mauro is also building something cool on his own free time. Mauro is building the new SQLd360 tool, which is already available on the web (search for SQLd360). This SQLd360 tool, similar to eDB360, provides a 360-degree view, but instead of a database its focus is one SQL. And similarly than eDB360 it installs nothing on the database. Both are available as “free software” for anyone to download and use. That is the nice part: everyone likes free! (specially if any good).

What is the difference between SQLd360 and SQLT?

Both are exceptional tools. And both can be used for SQL Tuning and for SQL diagnostics. The main differences in my opinion are these:

  1. SQLT has it all. It is huge and it covers pretty much all corners. So, for SQL Tuning this SQLTXPLAIN is “THE” tool.
  2. SQLd360 in the other hand is smaller, newer and faster to execute. It gives me what is more important and most commonly used.
  3. SQLT requires to install a couple of schemas and hundreds of objects. SQLd360 installs nothing!
  4. To download SQLT you need to login into MOS. In contrast, SQLd360 is wide open (free software license), and no login is needed.
  5. Oracle Support requires SQLT, and Oracle Engineers are not exposed yet to SQLd360.
  6. SQLd360 uses Google charts (as well as eDB360 does) which enhance readability of large data sets, like time series for example. Thus SQLd360 output is quite readable.
  7. eDB360 calls SQLd360 on SQL of interest (large database consumers), so in that sense SQLd360 enhances eDB360. But SQLd360 can also be used stand-alone.

If you ask me which one would I recommend, I would answer: both!. If you can use both, then that is better than using just one. Each of these two tools (SQLT and SQLd360) has pros and cons compared to the other. But at the end both are great tools. And thanks to Abel Macias, SQLT continues its lifecycle with frequent enhancements. And thanks to Mauro, we have now a new kid on the block! I would say we have a win-win for our large Oracle community!

Written by Carlos Sierra

March 18, 2015 at 12:37 pm

Learn how free new tool sqld360 can tell you so much about your favorite SQL!

with 3 comments

New tool sqld360 is now available! Mauro Pagano released this cool new tool a few hours ago. And yes, it is free for all!

So, what is sqld360? Well, it is an install-nothing free software that tells you a lot about one SQL statement. And if your site has an Oracle Tuning Pack or Diagnostics Pack license, then you get a lot more from sqld360. Sample snapshot below shows you the dynamic menu for a simple SQL. This sqld360 new tool uses similar techniques than edb360, so it displays information as html, text, csv and in some cases it uses some cool Google charts.

sqld360 screen shot

The obvious question is why would I use this sqld360 instead of SQLT or SQLHC? The answer is: you can use them all. What makes sqld360 different to SQLT is that sqld360 installs nothing on the database. And what makes sqld360 different to SQLHC is that sqld360 is available as free software on a GitHub repository, so you don’t have to have a MOS account available. Any Oracle user can download and use sqld360 starting today!

In terms of content, I can say that SQLHC gives you some Observations and sqld360 does not (yet). Besides that, I think sqld360 is superior to SQLHC simply because it is more mature and developed from scratch using newer techniques.

SQLT is a different animal. It provides tons of functionality developed over a decade. This sqld360 is on its first release, but it will certainly grow over time but only in the right directions. From the beginning, sqld360 focuses on what is important for tuners and for those in need to diagnose a SQL misbehaving. So simply put, sqld360 is faster and better focused than SQLT and SQLHC. We can call it “the next generation”.

Why free? The answer is: Why not? When Mauro and I started discussing sqld360, we decided to continue developing cool scripts and tools on our own personal time, on our own hardware, and without taking anything from anyone. So this is kind of our contribution to our Oracle community. I have provided edb360 and Mauro is providing sqld360. Mauro and myself would love to blog more often, but if we have to decide where to put our personal time, we both are more inclined to invest on free tools (after of course our family duties).

It is hard to appreciate with a sample execution from one of our systems, but if you look at chart below you may get to see how a SQL of interest compares to the system load. This kind of chart is helpful when you are trying to understand how a particular SQL affects a given load for a certain time window. Or when you need to documents your findings to business leaders.

SQL Execute Time

I could continue telling more about sqld360, but the best way to appreciate it is by actually trying it on one of your own SQL statements. You can go to Mauro’s blog post about sqld360 and download this tool following instructions there. Then give it a shot, after all you have nothing to loose.

Forgot to mention this: sqld360 as well as edb360 work through a SQL*Plus connection, either on the database server or on a PC client. This is important since quite often we road-warriors and even in-house developers may not have access to OEM or some other persistently-connected tools. Then, with sqld360 and edb360, using a SQL*Plus connection you can extract enough metadata to analyze and comprehend what is happening on the entire database or around one particular SQL, all without having to be persistently-connected to the database. Yes, you can do all your analysis remotely from home! yay!

And, by the way, as of today edb360 calls sqld360 on SQL of interest. So if you are already using edb360 you may want to download version v1504 dated February 15, 2015. This edb360 v1504 contains sqld360.

I hope you enjoy both tools. Life is Good!

Written by Carlos Sierra

February 16, 2015 at 12:17 am

Posted in General

Some eDB360 Frequently Asked Questions

leave a comment »

As I get frequent questions about eDB360 I decided to post some FAQ here. First, some links to prior posts:

  1. Video: Introducing the eDB360 Tool
  2. What to do if edb360 takes long to run
  3. eDB360
  4. What is new with EDB360?
  5. How to execute eAdam and eDB360
  6. Meet “edb360″: a free tool that provides a 360-degree view of an Oracle database
  7. Presentations

Q: When is the best time to execute edb360?

A: At the end of a regular working day. Since it may take several hours to execute, some customers schedule it for a Friday evening.

Q: How long will edb360 take to execute on my production environment?

A: I don’t know. Best way to predict this is by executing edb360 in a lower but similar environment, like QA or TEST.

Q: Are there any risks on executing edb360 on a production environment?

A: No that I know. Think edb360 as a long sequence of SQL statements against DBA and GV$ views. Are they going to break anything? In theory not. So far I haven’t see edb360 breaking any environment. And if it does most probably it would a database bug. Most conservative approach is to try it first on a lower environment.

Q: Does edb360 install anything on my database?

A: No.

Q: Does edb360 changes the state of my database?

A: No.

Q: My edb360 seems to be hanging. How can I tell for sure?

A: Use any normal method: OEM, query long operations, look at V$SQL for active SQL. If you see edb360 is “stuck” at one SQL for let’s say more than one hour, you may want to report this to me. You can also find the SQL, kill the session, comment out that SQL out of edb360 code, and retry. When I have seen this, it happens to be either a known database bug, or lack of good statistics on schema objects behind particular query.

Q: Why is the output of edb360 so extensive?

A: We don’t know what we don’t know. Since edb360 is used for Oracle databases health-checks, trying to minimize the number of round trips to collect diagnostics, this tool collects many pieces of information. Sometimes we use most, sometimes we use a small subset. It all depends what we see.

Q: How do I read edb360?

A: Personally, I read everything. It takes me at least one day for an initial review. When I look at it for the 2nd or 3rd time, my search is narrower and deeper. While I look at it the 1st time I take some light notes, else I forget what I have seen so far.

Q: Can I use edb360 on any Oracle database?

A: Yes, as long as it is 10g, 11g or 12c. It works on any platform, but it works better on Linux and UNIX. It works for any application, including EBS, PSFT, Siebel, SAP and any custom one.

Q: Can I use edb360 on a non-Oracle database?

A: No.

Q: Is edb360 certified to run on 12c?

A: Not yet. It should work fine for single-tenant databases. I haven’t tested it on 12c although. I will do soon.

Q: Can I use edb360 for sizing and/or capacity?

A: Yes and no. edb360 captures sizing metadata using the eSP collector. The eSP application for Sizing and Provisioning is Enkitec’s proprietary (now Accenture’s). So we can use edb360 for sizing, but only internally and when our client asks for it.

Q: Is edb360 free software?

A: Yes. look for license file included on tool.

Q: Where do I get the latest version of this tool?

A: You can go to Enkitec’s Products page and look for download link, or look at the column at the right of this entry. There is a section with a “Download” title. Both links will take you to the same place (a GitHub location).

Q: Can I use edb360 for SQL Tuning?

A: Actually, for that you may want to get SQLd360, which is work in progress by Mauro Pagano. In the meantime you can use planx.sql, sqlmon.sql, sqlash.sql (under my cscripts) or download SQLTXPLAIN and/or SQLHC from MOS.

Q: Can I share edb360 tool with others?

A: Absolutely! I developed this tool thinking on our Oracle community.

Q: I have some ideas to improve edb360. How can I provide them?

A: Send me an email, or post them here. Every so often I get some good ideas, which I put on my list and one day I get to them. Keep in mind that I work on edb360 on my free personal time, so some ideas take longer to implement than others.

Q: Where can I learn more about edb360?

A: Look at links at the top. There is a 1hr presentation that goes over what this tool does. There is also a fresh sample output available.

Written by Carlos Sierra

January 7, 2015 at 2:56 pm

Posted in edb360

Why do you need to gather CBO Statistics?

with 4 comments

As I help a peer with a SQL Tuning engagement, I face the frequent case of: “We do not want to gather CBO schema object statistics because we don’t want our Execution Plans to change”. Well, the bad news is that: not gathering stats only gives you a false sense of safety because your Execution Plans can change anyways. The reason has to do with Predicates referencing values out of range. Typical cases include range of dates, or columns seeded with values out of a sequence (surrogate keys). Most applications use them both. Example: predicate that references last X days of data. Imagine that date column on this predicate actually has statistics with low and high value that are outdated, lets say the high value refers to last time we gather stats (several months old). In such cases, the CBO uses some heuristics starting on 10g, where the cardinality of the Predicate is computed according to range of low/high and how far the value on Predicate is from this low/high range as per the stats. In short, the cardinality changes over time, as the Predicate on the last X days of data changes from one day to the next, and the next, and so on. At some point, the CBO may decide for a different Plan (with lower cost) and the performance of such SQL may indeed change abruptly. Then we scratch our heads and repeat to ourselves: but we did not gather statistics, why did the plan change?

So, if you understand the rationale above, then you would agree with the fact that: not updating CBO schema stats do not offer any real Plan Stability. So, my recommendation is simple: have reasonable CBO statistics and live with the possibility that some Plans will change (they would change anyways, even if you do not gather stats). Keep always in mind this:

The CBO has better chances to produce optimal Plans if we provide reasonable CBO statistics.

Now the good news: if you have some business critical SQL statements and you want them to have stable Plans, then Oracle already provides SQL Plan Management, which is designed exactly for Plan Stability. So, instead of gambling everyday, hoping for your Plans not to change preserving outdated stats, rather face reality, then gather stats, and create SQL Plan Baselines in those few SQL statements that may prove to have an otherwise unstable Plan and are indeed critical for your business. On 10g you can use SQL Profiles instead.

On 10g and 11g, just let the automatic job that gathers CBO schema statistics do its part. In most cases, that is good enough. If you have transient data, for example ETL tasks, then you may want to have the process workflow to gather stats on particular Tables as soon as the data is loaded or transformed and before it is consumed. The trick is simple: “have the stats represent the data at all times”. At the same time, there is no need to over do the stats, just care when the change on the data is sensible.

Written by Carlos Sierra

November 11, 2014 at 11:58 am

Follow

Get every new post delivered to your Inbox.

Join 1,956 other followers