Home » RDBMS Server » Performance Tuning » SQL Tuning
SQL Tuning [message #259264] Tue, 14 August 2007 21:53 Go to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

My environment:
01 * 10gR2 (10.2.0.1) server (win32bit_platform);
01 * client (XP) with oracle client + front-end application;
Just 02 computers above on a single network.

I have imported data to 10gR2 database. So it is a populated database.
I open the front-end application on the client (XP) and try to view a report on screen. At this moment and always... the whole system is me and I... none shares and competing resources.
The report takes 7 minutes to be generated.

I went to EM_DBConsole... and from performance screen I asked to start an ADDM job. Then I ran the "advisor"... (from EM DBConsole).
The advisor suggested me some changes and I selected and applied.
Than the report that took 7min now takes 2secs.

Happiness!!! And I asked for a new report... changing the period of the report. This new report.. took almost 4min... too much time.

I am giving up this tool... and I want to do as the professional dbas do. But from where to start? I am reading now "spdoc.txt" (statspack) from {oracle_home}\rdbms\admin (as this orafaq article)... slowly but trying to understand what is happening. This is the way I want to learn.

Well... Srs.. having this scenery how can I start my first "sql tuning" exercise?

Thanks a lot,


mson77
Re: SQL Tuning [message #259265 is a reply to message #259264] Tue, 14 August 2007 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
By reading & FOLLOWING the suggestions in the STICKY post at top of this forum.
The STICKY posts are there for a reason. USE THEM!
Re: SQL Tuning [message #259267 is a reply to message #259265] Tue, 14 August 2007 22:24 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

I have already read these 3 stickies before. The first one Kyle Hailey (and his site and his free tool ashmon); the second sticky where I learned to use "code" tag and to pre-format the code before post it here; the last which is the oracle manual which I downloaded and printed to ease reading.

I was reading this forum... and this thread http://www.orafaq.com/forum/t/86233/110238/... Michel Cadot asked to anamika_025 to run a script... then execute tkprof... and I don't know if my exercise/problem is similar.

Truly... I expected someone to guide me to the solution of this case step by step... for example: do this.. then this... and so on.

Regards,

mson77
Re: SQL Tuning [message #259271 is a reply to message #259264] Tue, 14 August 2007 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE
-- then invoke slow running code

above will generate a "trace" file; typically in the udump folder

The you use the TKPROF utility to process the trace file.

The output from TKPROF will show where all the time is being spent.

One should ALWAYS post the actual SQL, the EXPLAIN_PLAN, table DESCRIPTIONS, & enumerate indexes (if any) on all tables in the SQL.

Upon further review, (even though I am responsible for at least 1 of the STICKY posts) they are deficient in presenting any organized resource, IMO.

http://people.aapt.net.au/roxsco/tuning/
Above come closest in presenting a HOW TO TUNE guideline.

[Updated on: Tue, 14 August 2007 22:39] by Moderator

Report message to a moderator

Re: SQL Tuning [message #259277 is a reply to message #259271] Tue, 14 August 2007 22:54 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

Thank you!
Regarding:
Quote:
One should ALWAYS post the actual SQL, the EXPLAIN_PLAN, table DESCRIPTIONS, & enumerate indexes (if any) on all tables in the SQL.

Sorry but I will fail because... what I have here is just the application (visual front-end application) and I don't have access to the sql statements neither in its original format. Neither "explain_plan", table_description nor indexes...

What I have seen (using EM DBconsole) about "the bad sql" was in the decoded sql statement format... very large (in length)... to read and understand.

Maybe is it better to still try using EM DBConsole tool?

I am reading "spdoc.txt" and statspack does as AWR. Both generate reports only. They don't offer advice... and maybe I should start with advice (EM DBConsole) to see the whole track/way to the solution sometimes (from the beginning until the solution of the case)?

Quite lost... I don't know how (from where) to start... DBconsole/statspack/tkprof... any ideia?
Regards,

mson77
Re: SQL Tuning [message #259288 is a reply to message #259264] Tue, 14 August 2007 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Sorry but I will fail because...
If somebody has "DBA" access to this Oracle database, all the answers can be obtained.
If nobody can change neither the SQL nor the underlying schema objects, then your options are sorely limited.

Can you/someone have the ability to place/create a LOGON trigger on this DB?
If you the LOGON trigger can enable SQL_TRACE & capture all the SQL & generate the EXPLAIN_PLAN.

If you can change initSID.ora parameter & bounce the DB, you can enable SQL_TRACE for all sessions in the DB.
Re: SQL Tuning [message #259296 is a reply to message #259288] Tue, 14 August 2007 23:29 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hi anacedent,

Step1:
I have "dba" access. I logon with "sys as sysdba". The whole system is at home_lab. But... considering that via sqlplus I connect to the database and issue "alter session set sql_trace=true;"

Step2:
If I... via client computer... order a report to the database... using the application... I understand that this application will generate a new session (different that one via sqlplus) and the session initiated by the application will not have sql_trace=true.

I must have "sql_trace=true" on the session initiated by the application... or not? To be able to gather the sql_statements.
Regards,

mson77
Re: SQL Tuning [message #259299 is a reply to message #259264] Tue, 14 August 2007 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE TRIGGER LOGON_TRACE_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
BEGIN
if ( USER = 'APP_USER' )
then
   sys.dbms_support.START_TRACE(true,true);
end if;
END;
/

http://julian.dyke.users.btopenworld.com/Oracle/Diagnostics/Packages/DBMS_SUPPORT/DBMS_SUPPORT.html
You'll need to install DBMS_SUPPORT as direct by URL above.
Then you'll be able to modify & install the LOGON trigger.
Then you'll be able to run your application & generate the trace file.
Then you'll run TKPROF & post the results back here.
Re: SQL Tuning [message #259301 is a reply to message #259299] Tue, 14 August 2007 23:49 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
I understood!
THANK YOU VERY MUCH!
I will do it! (too much happy)
And will post the result! (happy)

mson77
Re: SQL Tuning [message #259336 is a reply to message #259301] Wed, 15 August 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As you are in 10g, don't use dbms_support, use dbms_monitor.session_trace_enable

Regards
Michel

Re: SQL Tuning [message #259397 is a reply to message #259299] Wed, 15 August 2007 09:08 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello,
Regarding:
CREATE OR REPLACE TRIGGER LOGON_TRACE_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
BEGIN
if ( USER = 'APP_USER' )
then
   sys.dbms_support.START_TRACE(true,true);
end if;
END;
/

This trigger will start every time a logon on the database with user='app_user' occurs... and then start_trace the session owned by this 'app_user'. Is it right? If so how can I stop this trigger when I don't want to trace anymore this 'app_user'?

Well... having start_trace enabled... until when it will trace? I mean... when/how can I stop tracing?

Regarding the suggestion from Michel Cadot about using "dbms_monitor.session_trace_enable"... the usage syntax for this case would be as below?
execute dbms_monitor.session_trace_enable();

Is code above right for this case?
Again... how can I disable this session_trace with this dbms_monitor package? Also... I have already installed DBMS_SUPPORT: May I keep this package installed or should I remove it to not conflict each other. If I should remove... how may I accomplish?
Thanks you.

mson77
Re: SQL Tuning [message #259398 is a reply to message #259264] Wed, 15 August 2007 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The tracing terminates with the end of the session.
>If so how can I stop this trigger when I don't want to trace anymore this 'app_user'
SQL> DROP TRIGGER LOGON_TRACE_TRIGGER;
You can leave DBMS_SUPPORT in the system & not hurt anything.
As with any package do:
SQL> DESC DBMS_MONITOR
to see what your options may be
Re: SQL Tuning [message #259400 is a reply to message #259398] Wed, 15 August 2007 09:18 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

YOU are really fast!
Thank you again.

The code below is correct?
execute dbms_monitor.session_trace_enable();
I mean... no arguments.
Regards,

mson77
Re: SQL Tuning [message #259401 is a reply to message #259264] Wed, 15 August 2007 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>execute dbms_monitor.session_trace_enable;
Works from SQL*Plus but to invoke from inside (PL/SQL) trigger just:
if ( USER = 'APP_USER' )
then
   sys.dbms_monitor.session_trace_enable();
end if;
END;
/
Re: SQL Tuning [message #259403 is a reply to message #259400] Wed, 15 August 2007 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
With the name of procedure I gave a link, just click on it and you have the syntax.

Regards
Michel
Re: SQL Tuning [message #259408 is a reply to message #259403] Wed, 15 August 2007 09:42 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello Michel,

Thank you!
I did it. I also realize that tuning issue is very close with application development (looking at words in the URL). Then I thought to download this oracle book... (4600 pages). First I intended to print... but I will try to use as pdf file.

Regarding your recommendation... I have read at the web page... but as I am starting oracle... I am still a bit afraid. I hope this period be short.

THANK YOU AGAIN.

mson77
Re: SQL Tuning [message #259528 is a reply to message #259408] Wed, 15 August 2007 20:33 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

I created the trigger as you taught me.
It has generated a trace file.
I ran tkprof as below:
tkprof cm3b_ora_2884.trc tkprof_result_2884.txt
and I attached the result here in this post.
The weird thing was:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       35      0.25       0.24          0          4          0           0
Execute     35      0.00       0.00          0          1          7           1
Fetch      237   1194.45    1266.61       5761   43457140          3        4171
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      307   1194.70    1266.85       5761   43457145         10        4172

Would you mind recommend me the next step?
Regards,

mson77
Re: SQL Tuning [message #259529 is a reply to message #259264] Wed, 15 August 2007 21:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Where you are spending your time is doing the SQL that generated these stats
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.25       0.23          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2   1194.35    1266.54       5761   43455614          0          33
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4   1194.60    1266.77       5761   43455614          0          33

BIG & UGLY SQL with a couple of UNION ALL throw in the mix.
tkprof cm3b_ora_2884.trc tkprof_result_2884.txt explain=app_user/password
above line using valid username/password will generate EXPLAIN_PLAN in output text file.
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

above are all options for tkprof.
Are statistic current on table & indexes involved with BIG SQL?
Re: SQL Tuning [message #259530 is a reply to message #259529] Wed, 15 August 2007 21:24 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

I didn't understand your question:
"Are statistic current on table & indexes involved with BIG SQL?"

Regarding explain_plan... I ran as your tip and attached the output.
Thank you very much!

mson77
Re: SQL Tuning [message #259532 is a reply to message #259264] Wed, 15 August 2007 21:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I saw many full table scans (FTS) in the EXPLAIN_PLAN for the BIG SQL.
In some cases FTS should be avoided.
In order for the Cost Based Optimizer to make intelligent choices it needs to details about the data being queried.
DBMS_STATS is a package that Oracle provides that "collects statistic" object objects.
Both the Performance Tuning Guide & http://asktom.oracle.com have examples & advice on (best?) use of DBMS_STATS.

SQL> DESC USER_TABLES
some/many of the columns are populated by DBMS_STATS
& the same for USER_INDEXES
Re: SQL Tuning [message #259571 is a reply to message #259532] Thu, 16 August 2007 00:32 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

I was reading about "dbms_stats".
I understood that the 2 main procedures from this package are:
  • dbms_stats.gather_table_stats
  • dbms_stats.gather_index_stats

Can I run:
execute dbms_stats.gather_table_stats(ownname=>'cm3b',estimate_percent=>5,cascade=>true);

1) This will generate new statistics for all tables and indexes owned by 'cm3b'?
2) After the new statistics has been generated... oracle will use this information to optimize the execution plan?
Thank you,

mson77
Re: SQL Tuning [message #259578 is a reply to message #259264] Thu, 16 August 2007 00:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can I run
This is where reality hits theory & reality does not necessarily conform to theory.
What you need to realize is this was cut & dried, nobody would need DBAs & the whole mess would be 100% automated.
You can run. One of 3 outcomes will result.
1) No change
2) It will be better
3) It will not be better.

Do you feel LUCKY?
Please note 2 out of 3 are not what you want.
Are you sure this is what you want to do for weeks/months/years/decades into the future?
Re: SQL Tuning [message #259590 is a reply to message #259578] Thu, 16 August 2007 01:12 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

What you say is:
In theory... this command should create a new collection of information (updated information) to be used by oracle optimizer when oracle parses a statement and creates the execution plan.
But... in the reality it does not happen... as desired or as proposed by automated tools.

Is this what are you saying to me?

===============================================

Now starting from the beginning again:
1) I traced a session to generate a trace file... to be converted in a readable and summarized form using tkprof;
2) The tkprof report shows a big sql statement doing several full table scans;
3) I was reading at b14221.pdf (Performance Tuning Guide) that... "The worst type of view use is when a view references other views, and when they are joined in queries", and I suspect that this is my case. Do you agree?
4) Now an escape (simple and easy solution) may be optimizing the oracle knowledge by creating new statistical data to oracle creates good execution plans; (Is this true?)
5) Last alternative: Rewrite the sql statement as you wrote before: "The most "bang for the buck" (biggest return for least effort) is obtained by tuning single SQL statements; one at a time."
Now an advice please: If you were me... what would you do?
Thank you!

mson77
Re: SQL Tuning [message #259845 is a reply to message #259578] Thu, 16 August 2007 12:02 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello anacedent,

Regarding this issue I forgot to answer:
"Are you sure this is what you want to do for weeks/months/years/decades into the future?"

YES! What I really want is "oracle knowledge". And I am realize that this depends on the dedication/dedication... time spent on it...et cetera... Truly this is not my primary job... but I understand that as the world speaks oracle... I should learn this "well spoken language"... and this is what I am doing since August 1st.
Also... instead of passing time in front of a TV... watching soup opera... my soup opera is "orafaq/forum"... whenever I have spare time I read other threads... and sometimes I laugh a lot... besides being funny... brings me a lot of knowledge.
I really appreciate your attention to me.
THANK YOU VERY MUCH.
Regards,

mson77
Re: SQL Tuning [message #260282 is a reply to message #259845] Sat, 18 August 2007 00:46 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Initially I posted in this thread a BIG SQL.
I started working with that big sql... dividing it into several smaller sql statements... Well, the original big one took 22sec to execute. And this one (smaller) after cutting parts of that big one... takes 21sec. I guess that the solution may rise from deep analysis of this sql statement below.

Below is the code:
SELECT m.codartigo,    m.codalmoxarifado,    MAX(idmov) AS
       idmov
       FROM moviment m,    almox a,
          (SELECT m.codartigo,    m.codalmoxarifado,    MAX(m.datamov) AS
         datamov
         FROM moviment m,    almox a
         WHERE(m.idpessoa = 3)
         AND(m.codalmoxarifado IN(9))
         AND(m.codalmoxarifado = a.codalmoxarifado)
         AND(a.codcusteio = 3)
         AND(m.datamov < to_date('01/08/2007',    'DD/MM/YYYY'))
         AND(m.datamov >=
          (SELECT to_date(nvl(s.ultimadata,    p.dataimplanta),    'DD/MM/YYYY')
           FROM paralmox p,
              (SELECT MAX(ultimadata) AS
             ultimadata
             FROM ultdatarepresa
             WHERE ultimadata < to_date('01/08/2007',    'DD/MM/YYYY')
             AND idpessoa = 3) s
             WHERE p.idpessoa = 3))
          GROUP BY m.codartigo,
             m.codalmoxarifado)
        md
         WHERE(md.datamov = m.datamov)
         AND(md.codartigo = m.codartigo)
         AND(md.codalmoxarifado = m.codalmoxarifado)
         AND(m.codalmoxarifado = a.codalmoxarifado)
         AND(a.codcusteio = 3)
         AND(m.datamov < to_date('01/08/2007',    'DD/MM/YYYY'))
         AND(m.datamov >=
          (SELECT to_date(nvl(s.ultimadata,    p.dataimplanta),    'DD/MM/YYYY')
           FROM paralmox p,
              (SELECT MAX(ultimadata) AS
             ultimadata
             FROM ultdatarepresa
             WHERE ultimadata < to_date('01/08/2007',    'DD/MM/YYYY')
             AND idpessoa = 3) s
             WHERE p.idpessoa = 3)
          )
        GROUP BY m.codartigo,
           m.codalmoxarifado
Attached you can see the tkprof file with explain plan.
Thank you.
Regards,

mson77
Previous Topic: TUNNING
Next Topic: CONTEXT index
Goto Forum:
  


Current Time: Fri Dec 13 06:07:21 CST 2024