Archive for the ‘SQLTXPLAIN (SQLT)’ Category
SQLTXPLAIN 11.4.5.0 is now available. SQLTXADMIN was born on November 21, 2012!
So, what is new in version 11.4.5.0?
There are some functional enhancements and a couple of small fixes. What really changed is the security model supporting SQLT. Now we have two users: SQLTXPLAIN and SQLTXADMIN. Legacy SQLTXPLAIN owns all SQLT Tables, Indexes and Sequences, while SQLTXADMIN owns all SQLT Packages and Views. Why? It has to do with restricting as much as possible access to a system. SQLTXPLAIN used to have several system privileges needed by its set of PL/SQL libraries. Once we split this account into two we can strip SQLTXPLAIN from all its system privileges and assign a subset of them to new SQLTXADMIN.
Why this change?
We needed SQLTXPLAIN with no system privileges since any SQLT user needs to provide the SQLTXPLAIN password every time SQLT is used. This latter requirement supports the export of the SQLTXPLAIN repository for the SQL being analyzed, which in turn is needed for Test Case (TC) creation. We also needed those system privileges to extract a Test Case Builder (TCB) and to perform several other “admin” tasks SQLT does in the background.
SQLTXADMIN has now some system privileges listed in the “Security Model” of the sqlt_instructions.html file that comes within the sqlt.zip file from MOS 215187.1. SQLTXPLAIN only has CREATE SESSION system privilege, and it is no longer granted SQLT_USER_ROLE. So, if you try to execute SQLTXPLAIN connected as SQLTXPLAIN you will get this error:
BEGIN * ERROR at line 1: ORA-20109: User "SQLTXPLAIN" lacks required "SQLT_USER_ROLE" role. Request to your DBA: "GRANT SQLT_USER_ROLE TO SQLTXPLAIN;". ORA-06512: at line 3
Do you want to grant SQLT_USER_ROLE to SQLTXPLAIN?
The anser is NO. What you want instead is to execute SQLT connected as the application user that generated the SQL statement you want to analyze. Be sure this application user has been granted the SQLT_USER_ROLE, or you will get the same error. If for some reason you cannot use the application user or you cannot grant SQLT_USER_ROLE to it, then and only then execute SQLT connecting as SYS or any DBA account.
Be also aware that SQLTXADMIN is created locked and with a random password. There is no need to unlock this account or reset the password. Of course SYS could do that but it is not needed nor wanted by SQLT. Think of SQLTXADMIN as the guardian of the system privileges SQLT needs. It uses those system privileges from within SQLT code in those PL/SQL libraries owned by SQLTXADMIN and mostly acting on top of SQLTXPLAIN Objects.
Regarding SQL_USER_ROLE.
This SQLT_USER_ROLE has also been stripped from system privileges preserving only two: ADVISOR and SELECT_CATALOG_ROLE. If granting those privileges to the application user through this role is a concern at your corporation, you can always strip this role from the application user when SQLT is not used by this user, or you can execute SQLT as SYS or any other DBA account.
What happens when you connect and execute SQLT as SYS or DBA instead of the application user that issued the SQL?
Well, you get almost everything, but what you don’t get is the plan produced by the EXPLAIN PLAN FOR command, which basically shows you the plan if you had disabled bind peeking. Not a big issue in most cases. So, if you don’t want to grant your applications users a couple of system privileges then don’t do it, and execute SQLT connecting as any DBA. You will also lose the EVENT 10053 Trace.
What about Trace Analyzer?
Trace Analyzer (MOS 224270.1) also known as TRCANLZR or TRCA for short, went through the same process. So TRCA 11.4.5.0 is also split into two accounts: TRCANLZR and TRCADMIN. Legacy TRCANLZR owns Tables, Indexes and Sequences, while TRCADMIN owns Views and Packages. Similar concepts as SQLTXPLAIN and SQLTXADMIN division of functions, but on a smaller scale. TRCA users must now be granted the new TRCA_USER_ROLE before executing the trcanlzr.sql script to analyze a Trace.
How this new “Security Model” affects SQLT or TRCA functionality?
The new security model is expected to have no effect in the functionality of these two tools. The only thing you may notice is that some operations that could be done connecting as SQLTXPLAIN cannot longer be done from this account. This means some operations like creating a SQL Plan Baseline for a SQL statement would require now a qualified account other than SQLTXPLAIN. But, for the most part like executing any of the main methods, a user connecting as such, should not notice any functional difference after 11.4.5.0 is applied.
Oracle SQL Tuning with Oracle SQLTXPLAIN
Stelios Charalambides book on SQLTXPLAIN is now announced in Apress website. You may want to check it out or pre-order it.
Mutating histograms, mutating number of rows, mutating blevel, mutating number of distinct levels… What do they mean?
Every so often I get an email from a SQLTXPLAIN user asking the meaning of these “mutating” observation from the health-checks engine on SQLT. On next SQLT release 11.4.5.0 (due on November 21, 2012), these messages will read “fluctuating” instead of “mutating”, but the health checks will remain the same. So, what do they mean?
Take “fluctuating number of histogram endpoints count”. What it means is the following: every time we gather CBO statistics on a Table, we have a new “version” of the statistics. We store the old one automatically (DBMS_STATS does that starting on 10g) and the new version becomes “current”. We store 31 days of history by default. So if we gather statistics on a Table lets say daily, we may have about 30 statistics versions for that Table and its columns (and Indexes). Now, if a column has lets say 5 buckets in its histogram and the next version has 6, there was a recorded change of 20%, which may be relevant for your investigation. If a column during its stored history has a number of buckets that fluctuates more than 10% from one version to the next, then it is flagged as “fluctuating” (use to be mutating in SQLT 11.4.4.8 and older). Changes of less than 10% are ignored.
Same concept applies to other elements like the number of distinct values in a column, or the height of an index, or the number of rows in a Table or Partition. If they fluctuate more than 10% from one version of the CBO statistics to the next, then they are flagged and reported in the Observations section of the main SQLT report.
Are we supposed to do anything with those health-checks? All depends. Lets say your SQL suddenly stop using index X and now uses index Y. If blevel on X has increased recently, you can suspect a cost increased due to the new blevel and investigate accordingly. Keep in mind that “Observations” section of SQLT, which reports outcome of health-checks, is basically a collection of heads-up. Things to investigate and maybe pointing to the root cause of a recent change in an execution plan for the SQL being investigated.
OOW12: Meeting Carlos Sierra face to face
This year I will be attending OOW. Besides presenting SQLTXPLAIN on October 4 at 12:45pm, I will be available at the Oracle Support Stars Bar in case you want to meet.
Moscone West, Lobby Level 2, Oracle Support Stars Bar for Oracle Database and Oracle Enterprise Manager.
- Mon, Oct 1 from 10:00am to 2:00pm
- Tue, Oct 2 from 10:00am to 2:00pm
- Wed, Oct 3 from 9:00am to 1:00pm
If you want to reserve some specific time please text me at 1.407.701.9355 or email me at carlos.sierra.usa@gmail.com, otherwise just stop by. We can chat about anything including SQLTXPLAIN or some SQL Tuning issue you may be working on. If the latter, please try to bring on a USB drive your SQLT files and I will review them with you.
So far I also plan to attend these two events (and any other cool one I get to hear about!):
- Oracle Bloggers Meetup 2012 (Wed, 3-Oct-2012, 5:30pm at Main Dining Room, Jillian’s Billiards @ Metreon)
- Oracle OpenWorld 2012 Tweet Meet! (Tuesday, October 2, from 4:30 p.m. to 6:30 p.m. at the OTN lounge)
I hope to see you there!
OOW OCT 4: SQL Tuning Made Easier with SQLTXPLAIN (SQLT)
Carlos Sierra will be presenting SQLT at OOW on October 4 at 12:45 at the Moscone West – 3022. If you are interested to attend this session please register soon. As of September 5 the assigned room is at 50% capacity. Also, once at OOW please verify room name since we may have had to move this SQLT session to a larger room. Please find below the abstract for this session.
SQL tuning is a daunting task. Too many things affect the Cost-Based Optimizer (CBO) when you’re deciding on an execution plan. CBO statistics, parameters, bind variables, their peeked values, histograms, and a few more are common contributors. The list of areas to analyze keeps growing. Over the past few years, Oracle has been using SQLTXPLAIN (SQLT) as part of a systematic way to collect all the information pertinent to a poorly performing SQL statement and its environment. With a consistent view of this environment, an expert on SQL tuning can perform more diligently, focusing more on the analysis and less on the information-gathering. This tool can also be used by experienced DBAs to make their life easier, at least when it comes to SQL tuning. Learn more in this session.
About DBA_TAB_HISTOGRAMS.ENDPOINT_VALUE
If you have looked at the values on DBA_TAB_HISTOGRAMS.ENDPOINT_VALUE you may have seen some like 281422806648623000000000000000000000 when you were actually expecting something like 63736. I have used in SQLT the two functions below for many years. I got the foundations of these two out of a public source which unfortunately I did not record. Since you may or not may have SQLT, I thought a good idea to share these two functions in case you need them.
CREATE OR REPLACE
FUNCTION get_internal_value (p_value IN VARCHAR2)
RETURN VARCHAR2
IS
temp_n NUMBER := 0;
BEGIN
FOR i IN 1..15
LOOP
temp_n := temp_n + POWER(256, 15 - i) * ASCII(SUBSTR(RPAD(p_value, 15, CHR(0)), i, 1));
END LOOP;
RETURN TO_CHAR(ROUND(temp_n, -21));
EXCEPTION
WHEN OTHERS THEN
RETURN p_value;
END get_internal_value;
/
CREATE OR REPLACE
FUNCTION get_external_value (p_value IN VARCHAR2)
RETURN VARCHAR2
IS
temp_n NUMBER;
temp_i INTEGER;
my_result VARCHAR2(32767) := NULL;
BEGIN
IF LENGTH(p_value) != 36 OR
SUBSTR(p_value, 16) != '000000000000000000000' OR
p_value > POWER(256, 15) OR
p_value < POWER(256, 14) THEN
RETURN p_value; -- cannot get external value
END IF;
temp_n := p_value / POWER(256, 14); -- get most significant digits
-- decoding most significant digits then shift multiplying by 256
FOR i IN 1..14
LOOP
temp_i := TRUNC(temp_n);
temp_n := (temp_n - temp_i) * 256;
IF temp_i NOT BETWEEN 32 AND 126 OR temp_n NOT BETWEEN 32 AND 126 THEN
EXIT; -- reached the tail
END IF;
my_result := my_result||CHR(temp_i); -- all but last byte
END LOOP;
IF temp_i NOT BETWEEN 32 AND 126 THEN
RETURN my_result||'?'; -- cannot decode
END IF;
-- scan to the right starting at temp_i
FOR i IN temp_i..126
LOOP
IF get_internal_value(my_result||CHR(i)) = p_value THEN
RETURN my_result||CHR(i); -- approximate value
END IF;
END LOOP;
-- scan to the left starting at temp_i
FOR i IN 32..temp_i
LOOP
IF get_internal_value(my_result||CHR(temp_i + 32 - i)) = p_value THEN
RETURN my_result||CHR(temp_i + 32 - i); -- approximate value
END IF;
END LOOP;
RETURN my_result||CHR(temp_i); -- this is the best we could do
EXCEPTION
WHEN OTHERS THEN
RETURN p_value;
END get_external_value;
/
SELECT get_external_value('281422806648623000000000000000000000')
FROM DUAL;
GET_EXTERNAL_VALUE('281422806648623000000000000000000000')
----------------------------------------------------------
63736
SELECT get_internal_value('63736') FROM DUAL;
GET_INTERNAL_VALUE('63736')
----------------------------------------------------------
281422806648623000000000000000000000
OOW: Best Practices: SQL Tuning Made Easier with SQLTXPLAIN (SQLT)
If you are attending Oracle Open World (OOW) this October, and you want to learn first-hand what is this SQLTXPLAIN (SQLT) tool and how it can benefit you, please join me at this conference:
Session ID: CON3053
Title: Get Proactive: Best Practices: SQL Tuning Made Easier with SQLTXPLAIN (SQLT)
Date: 10/4/2012
Start Time: 12:45 PM
Room: Moscone West – 3022
What is new on SQLTXPLAIN 11.4.4.7 released on July 2?
I uploaded a new release of SQLTXPLAIN (SQLT) today. The 20 changes for this 11.4.4.7 release are listed below, but I’d rather explain what is important and what is not.
Long ago my friend Lester Gutierrez suggested me an interesting addition to SQLT. In those days he was managing an EBS performance development team, now he does the same for Fusion performance. Anyways, he mentioned it would be nice to include with SQLT XTRACT some relevant AWR reports. In particular, at the time when the SQL had performed poorly. The idea here is this: since SQLT already presents lots of details about the SQL and its environment, some broader diagnostics at the time the SQL had poor execution times would help to better understand the system in terms of load and concurrency. This new feature is one of the 20 changes below. SQLT now includes up to 6 AWR reports (6 is a default value for a new configuration parameter). These 6 reports correspond to the periods between two consecutive AWR snapshots where the SQL has had the largest delta elapsed time, across all nodes in case of RAC.
So, why limit this functionality to AWR? On this new release of SQLT, besides the new set of relevant AWR reports, SQLT also produces up to 6 ADDM reports, some ASH reports from memory and from AWR (one set per instance), up to 12 SQL Monitor Active reports, and one SQL Detail report. All driven by the SQL being analyzed. Prior releases included only one SQL Monitor Active report and under some special cases one SQL Detail report. So this 11.4.4.7 is more aggressive in terms of including reports that sometimes are simply unknown or under-utilized. I am setting the defaults for the parameters controlling the number of these reports to some small values, at least on 11.4.4.7. I will monitor their performance as I get new cases, and I may adjust these default values on subsequent releases. In terms of SQLT performance, the impact of these new features seems marginal.
Another relevant change is an addition of a new health-check recently requested by Abel Macias. It detects bug 14013094, which in short it causes Index Partition statistics to go wrong. This is when the order of partition names on an index do not match their table at index creation time. I have already seen a couple of cases and I feel more will come. This new health-check is also included into the new SQL Health-Check script (SQLHC). So if the SQL being analyzed may suffer from this bug, SQLT and SQLHC will create an “Observation”, which are basically the outcome of the dozens of health-checks performed around the SQL.
That brings another important change. When for some reason or another it is hard to install SQLT on a system, I am recommending to use SQLHC. The former requires to install a schema SQLTXPLAIN and hundreds of objects on it. The latter requires no installation at all. The gap of the diagnostics generated is brutal: SQLT provides all we need to diagnose a SQL performing poorly, while SQLHC provides only some pieces. But these pieces have been carefully selected. During several interactions with a key developer from the Fusion performance team, we agreed to add some important new sections to SQLHC. So in this release of SQLHC (also 11.4.4.7), this script has several new features. It will never compete with SQLT, but now it provides a good starting point to a SQL tuning case. Think SQLHC as a lite version of SQLT. By the way, SQLHC had its own note 1366133.1 on My Oracle Support (MOS), but it is also packaged within SQLT under sqlt/utl.
Parallel Execution new tool (MOS 1460440.1) did not escape a few changes in this 11.4.4.7 release. Mostly minor bugs. Same about profiler.sql (243755.1), which produces a report for PL/SQL Profiler data. Both tools are also packaged inside SQLT under sqlt/utl. This way it is easier to access them. If you are not familiar with them, you may want to go to their own MOS notes and look for the output sample.
I would say the other changes not mentioned explicitly, are less relevant. Anyways, try always to use the latest version of SQLT out of 215187.1. If you are a seasoned user of SQLT you know it changes often, always with some new features and some fixes. I hope you start using 11.4.4.7 soon!
- BUG: Readme report for PeopleSoft was showing “Gathering Statistics” twice.
- BUG: Parameter “_db_file_optimizer_read_count” was showing an incorrect value on Main report.
- BUG: Number of executions above Execution Plans was showing zero when statistics_level was not set to ALL at the time the cursor was parsed.
- BUG: Avoid error below when DBMS_METADATA is not granted to PUBLIC. 1452/29 PLS-00201: identifier ‘DBMS_METADATA’ must be declared
- ENH: New HC to detect bug 14013094 on statistics gathering for partitioned tables and indexes where partition names are created in different order between table and index.
- ENH: New tool parameter tcb_export_data to control value of parameter exportData on API DBMS_SQLDIAG.EXPORT_SQL_TESTCASE.
- ENH: Handle non-stopper error: ORA-06550: line 4, column 18 with “FROM applsys.fnd_product_groups”.
- ENH: Scripts roxtract and roxecute are now deprecated. Use sqlhc.sql and sqlhcxec.sql instead.
- ENH: Main report, sqlhc.sql and sqlhcxec.sql include now Plan Line and Plan summaries for Active Session History from memory and AWR.
- ENH: Scripts sqlhc.sql and sqlhcxec.sql to include ASH summaries, Cursor Sharing, set of SQL Monitor reports, SQL Detail report, SQL Profiles, SQL Plan Baseline and SQL Patches.
- ENH: Readme for PSFT includes now instructions to gather statistics using both PSCBO_STATS and DBMS_STATS.
- ENH: SQL Text now shows in red these keywords: SELECT, INSERT, UPDATE, DELETE, MERGE, FROM, WHERE.
- ENH: COUNT(*) on tables accessed by SQL being analyzed is reduced to a sample size 1% of current values. This will make this COUNT less accurate but much faster.
- ENH: SQLT main methods produce now up to 6 AWR reports for snaphots where the SQL being analyzed produced considerable elapsed time.
- ENH: SQLT main methods produce now up to 12 most recent SQL Monitor reports for the SQL being analyzed.
- ENH: SQLT main methods produce now ASH reports from memory and from AWR.
- ENH: SQLT main methods produce now up to 6 ADDM reports for snaphots where the SQL being analyzed produced considerable elapsed time.
- ENH: Workaround: disconnect ORA-07445 on SYS.DBMS_SQLTUNE_INTERNAL, which happened in some 11.2 systems.
- ENH: SQL Test Case (TC) now provides a script tc_pkg.sql to generate a stand-alone TC out of an implemented SQLT TC.
- ENH: All main methods record now into sqltxhost.log a set of 5 vmstat and sar samples.
SQLTXPLAIN presentation in Orlando on June 25, 2012
There will be a 1-hr presentation on SQLTXPLAIN (SQLT) hosted by the Central Florida Oracle Users Group (CFOUG). There will be plenty of time for questions. This is an opportunity to discuss -face to face- any current SQL tuning issues in which you may have used SQLT.
How to start SQLT XTRACT on a SQL that executes at night?
Someone asked recently for an easy way to execute SQLT XTRACT on a SQL that happens to execute only out of a daily batch process. This person wanted to use SQLT to capture dynamic performance data from GV$ views, so method XTRACT would help. The problem was then “how to get SQLT XTRACT to start lets say at 10pm when I leave the office at 5:30pm?”.
Starting with SQLT 11.4.4.6, all main methods (including XTRACT) take the SQLTXPLAIN password as an inline parameter instead of an ACCEPT command, so we can now easily execute at 5:30pm a script where we pass to XTRACT both the SQL_ID and the SQLTXPLAIN password. In example below such password is sqltxplain (lower case):
-- script.sql which executes SQLT XTRACT with a delay of 4.5 hours host sleep 4.5h start run/sqltxtract.sql 51x6yr9ym5hdc sqltxplain
Carlos Sierra's Tools and Tips 