Current Position:Home > SQL performance 9i and 10g

SQL performance 9i and 10g

Update:11-30Source: network consolidation
Advertisement
Hi guys,
I have question regarding sql fetching slow response issue in 10g compared to 9i. Have enabled the 10046 trace the actual CPU and Elapsed time of that query.
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
rows = number of rows processed by the fetch or execute call
SQL == Running on 9i
call count cpu elapsed disk query current rows
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13295 1.39 1.55 513 14742 0 13294
total 13297 1.40 1.56 513 14742 0 13294
SQL == Running on 10g
call count cpu elapsed disk query current rows
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13305 1.41 1.32 0 14621 0 13304
total 13307 1.42 1.32 0 14621 0 13304
They are running quite fast in 9i and 10g Database with the trace report. However, there has slow response issue on 10g.
E.g SQL> set time on
SQL> set timing on
---- fetching records on screen
---- truncated
13304 returned.
00:15:21
SQL>
In 9i, it only takes 2 mins to return the sqlplus prompt, but take at least 15 mins to return in 10g. Do you have any idea?
I have raised the issue with Oracle Support, the the OSS said.. your 10046 trace is good and take only 1-2 sec. And he didn't follow after that. Please help if you have idea. Thanks
Best Regards,
Vbeer.

The Best Answer

Advertisement
Hi Charles,
Oracle Version 10.2.0.3
NAME TYPE VALUE
statistics_level string TYPICAL
NAME TYPE VALUE
optimizer_dynamic_sampling integer 2
In 10g raw trace file of 10046:
<--QUERY-->
END OF STMT
PARSE #1:c=10000,e=4193,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=21234109349008
BINDS #1:
EXEC #1:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=21234109349329
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109349392
WAIT #1: nam='SQL*Net message from client' ela= 669 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109350166
WAIT #1: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109350353
WAIT #1: nam='SQL*Net more data to client' ela= 12 driver id=1650815232 #bytes=2161 p3=0 obj#=77206 tim=21234109350403
FETCH #1:c=0,e=224,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109350428
WAIT #1: nam='SQL*Net message from client' ela= 149 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109350613
WAIT #1: nam='SQL*Net more data from client' ela= 9 driver id=1650815232 #bytes=524 p3=0 obj#=77206 tim=21234109350665
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109350737
WAIT #0: nam='SQL*Net more data to client' ela= 9 driver id=1650815232 #bytes=2524 p3=0 obj#=77206 tim=21234109350774
WAIT #0: nam='SQL*Net message from client' ela= 5199 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109355996
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109356058
FETCH #1:c=0,e=48,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109356084
WAIT #1: nam='SQL*Net message from client' ela= 61 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109356177
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109356218
WAIT #0: nam='SQL*Net message from client' ela= 4931 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109361179
WAIT #1: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109361248
FETCH #1:c=0,e=43,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109361272
WAIT #1: nam='SQL*Net message from client' ela= 81 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109361388
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109361447
WAIT #0: nam='SQL*Net message from client' ela= 5057 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109366540
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109366602
FETCH #1:c=0,e=52,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109366629
WAIT #1: nam='SQL*Net message from client' ela= 75 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109366737
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109366778
WAIT #0: nam='SQL*Net message from client' ela= 4913 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109371739
WAIT #1: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109371791
FETCH #1:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109371815
WAIT #1: nam='SQL*Net message from client' ela= 64 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109371911
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109371947
WAIT #0: nam='SQL*Net message from client' ela= 95474 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109467455
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109467536
FETCH #1:c=0,e=58,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109467563
WAIT #1: nam='SQL*Net message from client' ela= 248 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109467848
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109467893
WAIT #0: nam='SQL*Net message from client' ela= 5526 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109473480
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109473716
FETCH #1:c=0,e=143,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109473761
WAIT #1: nam='SQL*Net message from client' ela= 148 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109473979
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109474068
WAIT #0: nam='SQL*Net message from client' ela= 5516 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109479623
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109479745
FETCH #1:c=0,e=84,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109479781
WAIT #1: nam='SQL*Net message from client' ela= 126 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109479950
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109479996
WAIT #0: nam='SQL*Net message from client' ela= 182929 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109662981
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109663141
FETCH #1:c=0,e=100,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109663177
WAIT #1: nam='SQL*Net message from client' ela= 139 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109663368
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109663434
WAIT #0: nam='SQL*Net message from client' ela= 5593 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109669069
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109669240
FETCH #1:c=0,e=110,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109669282
WAIT #1: nam='SQL*Net message from client' ela= 167 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109669506
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109669597
WAIT #0: nam='SQL*Net message from client' ela= 5505 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109675138
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109675213
FETCH #1:c=0,e=56,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=21234109675239
WAIT #1: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=77206 tim=21234109675351
Cheers,
Vbeer
  • SQL performance 9i and 10g Update:11-30

    Hi guys, I have question regarding sql fetching slow response issue in 10g compared to 9i. Have enabled the 10046 trace the actual CPU and Elapsed time of that query. cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing row

  • SQL performance slow -- oracle 10g to oracle 11g Update:11-30

    Hi, We are have two development server, here we can call server10 and server11. Server's having same hardware and OS, but different oracle version, server 10 and server 11 having oracle 10g and oracle 11g respectively. Problem, when you run sql query

  • Index to Improve SQL Performance Update:11-30

    Please provide a link to information about how to use an Index to enhance SQL performance with Oracle 10g database.user8973820 wrote: I would like information regarding the use of clustered versus non-clustered indexes.The documentation is your frien

  • Real Application Testing/Sql Performance Analyzer Docs in 10g.... Update:11-30

    I believed that both the tools mentioned in the subject are a part of 11g and so are in the 11g docs. http://download.oracle.com/docs/cd/B28359_01/server.111/e12253/toc.htm But I was just looking at 10g book listing and I saw the same book in 10g doc

  • How to improve my pls/sql performance tunning skills Update:11-30

    Hi All , I would like to learn more about pl/sql performance tunning , where or how can i get more knowledge in this area ? Is there any tutorials which can help me to understand the Explain plan, Dbms_Profiler, Dbms_Advisor more etc ........Thanks .

  • SQL Performance Analyzer Information Update:11-30

    Hi, i would use SQL Performance Analyzer to test changes between 10g and 11g database. I will use Parameter Change option in SQL Performance Analyzer Workflows and test the change of parameter OPTIMIZER_FEATURES_ENABLE. Now, if I find some query that

  • Help needed in SQL performance - Using CASE in SQL statement versus 2 query Update:10-11

    Hi, I have a requirement to find count from a bunch of tables. The SQL I have gives the count of all members. I have created 2 queries to find count of active and inactive members. The key difference is only the active dates. Each query takes 20 seco

  • SQL Performance issue: Using user defined function with group by Update:10-11

    Hi Everyone, im new here and I really could need some help on a weird performance issue. I hope this is the right topic for SQL performance issues. Well ok, i create a function for converting a date from timezone GMT to a specified timzeone. CREATE O

  • [sql performance] inline view , group by , max, join Update:10-11

    Hi. everyone. I have a question with regard to "group by" inline view , max value, join, and sql performance. I will give you simple table definitions in order for you to understand my intention. Table A (parent) C1 C2 C3 Table B (child) C1 C2 C

  • SQL*Plus with Oracle 10g Express Edition Update:10-11

    How Can I do to log in using SQL*Plus into Oracle 10g? My Schema is: Banco.Global My Password is: 111111 I need to write SET SERVEROUTPUT ON to get answer? I opened the Prompt, but i don't know to log in. Thanks and my english is not as well as you.I