Home » RDBMS Server » Performance Tuning » how to increase a performance of a table in oracle 10g (merged)
how to increase a performance of a table in oracle 10g (merged) [message #253900] Wed, 25 July 2007 05:01 Go to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
hello,

I have table which has 6 lac records.
when i execute this table its take more than 2 hours.
please, tell me hows to increase the performance.

thanks and regards,
Anamika
Re: how to increase a performance of a table in oracle 10g [message #253903 is a reply to message #253900] Wed, 25 July 2007 05:05 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
anamika_025 wrote on Wed, 25 July 2007 06:01

I have table which has 6 lac records.


How many is that?
anamika_025 wrote on Wed, 25 July 2007 06:01

when i execute this table its take more than 2 hours.


Find a more humane way to execute the table.

Seriously, more information is needed to help with a solution.
Re: how to increase a performance of a table in oracle 10g [message #253904 is a reply to message #253903] Wed, 25 July 2007 05:08 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
in this table i do not have any primary keys...

i executed simple query

select * from tablename;

if i executed this query then its take more than 2 hours..

Re: how to increase a performance of a table in oracle 10g [message #254022 is a reply to message #253900] Wed, 25 July 2007 09:42 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Anamika,

[1] How much time is taken by your query?

[2] Why you want to execute ?
select * from table;

[3] Why you dont have primary key ?

[4] Is this table is use for Dataware housing?

[5] Why your table is not normalize?

[6] As u are doing only select, i dont think any records are frequently updated in table and if so then table may be fragmented.

[7] If you don't want to change any thing in table designing then better to make partition table depending on some column value (i.e. on basis of year, month or some other column)


Note:--Your query is doing FTS (full table scan) so it will take time.

Regard
Sunilkumar

[Updated on: Wed, 25 July 2007 09:44]

Report message to a moderator

Re: how to increase a performance of a table in oracle 10g [message #254165 is a reply to message #254022] Thu, 26 July 2007 01:05 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
i created partition table by range..but there is not much difference...

how to use index in this table...

i will not change in this table..i will not do normalize this table because its too late..

how to do..please tell me...

thanks and regards.,
please help me [message #254184 is a reply to message #253900] Thu, 26 July 2007 02:00 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
m sending to you my table ..there is 6 lacs records...
RECEIPTD_KID VARCHAR2(7),
RECEIPTD_RECEIPTMID VARCHAR2(7),
RECEIPTD_TYPE VARCHAR2(7),
RECEIPTD_RECNO CHAR(11),
RECEIPTD_DATE CHAR(10),
RECEIPTD_TIME CHAR(11),
RECEIPTD_PTREGID VARCHAR2(7),
RECEIPTD_ADMDISCHID VARCHAR2(7),
RECEIPTD_SERVICEID VARCHAR2(7),
RECEIPTD_SERVGRPID VARCHAR2(7),
RECEIPTD_SUBSERVGRPID VARCHAR2(7),
RECEIPTD_TARIFFID VARCHAR2(7),
RECEIPTD_EMERYN CHAR(1),
RECEIPTD_PTCATID VARCHAR2(7),
RECEIPTD_DOCTORID VARCHAR2(7),
RECEIPTD_RATE NUMBER(8,2),
RECEIPTD_QTY NUMBER(3),
RECEIPTD_AMOUNT NUMBER(10,2),
RECEIPTD_SERVCHRG NUMBER(10,2),
RECEIPTD_STATUS CHAR(1),
RECEIPTD_CDATE CHAR(10),
RECEIPTD_CTIME CHAR(11),
RECEIPTD_CREMARK VARCHAR2(100),
RECEIPTD_TRANNO NUMBER(3))

i want to increase the performance of above table
what can i do? please help me..

i execute this query

Select * from tablename;

i want to improve the performance because it takes mow than 2 hours.
thanks and regards,
Anamika


Re: please help me [message #254188 is a reply to message #254184] Thu, 26 July 2007 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start a new SQL*Plus session and execute:
alter session set events '10046 trace name context forever, level 8';
Select * from tablename;
alter session set events '10046 trace name context off';
exit

Then use TKPROF on the generated trace file and post its ouput.

Regards
Michel
Re: please help me [message #254233 is a reply to message #254188] Thu, 26 July 2007 04:19 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
in above table which index is used or partition is use to increase performance....
Re: please help me [message #254238 is a reply to message #254233] Thu, 26 July 2007 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Index is useless for this statement.
Partition can be useful only if you can parallel the query.
Do what I asked.

Regards
Michel
Re: please help me [message #254244 is a reply to message #254238] Thu, 26 July 2007 05:04 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
actually i m doing

select * from tablename where receiptd_date='12/12/2006';

but the performance is not good...

its take 00:00:47.87

tota records are 1037
Re: please help me [message #254271 is a reply to message #254244] Thu, 26 July 2007 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do what I asked.

If you don't, you won't have help.

Regards
Michel
Re: please help me [message #254275 is a reply to message #254271] Thu, 26 July 2007 06:22 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
but how to do this

Then use TKPROF on the generated trace file and post its ouput.
Re: please help me [message #254279 is a reply to message #254275] Thu, 26 July 2007 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I posted EXACTLY what you have to do in SQL*Plus.
For TKPROF:
C:\>tkprof
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

Easy to have the syntax, doesn't it?
For your case, just give tracefile and outputfile.

Regards
Michel
Re: please help me [message #254281 is a reply to message #254279] Thu, 26 July 2007 06:29 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
sorry my questions are stupid but i m new in this field

tracefile and output file. where it is??
Re: please help me [message #254285 is a reply to message #254281] Thu, 26 July 2007 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trace file is what it is generated by the "alter session" statements above. You'll find it in the directory given by the parameter "user_dump_dest". Take the last one.

output file is the file generate by TKPROF after it analyses the trace file, you give the name you want then you post it.

Regards
Michel
Re: how to increase a performance of a table in oracle 10g [message #254326 is a reply to message #253903] Thu, 26 July 2007 07:46 Go to previous messageGo to next message
vipindiwan
Messages: 7
Registered: July 2007
Location: INDIA
Junior Member

hi,
u should make index on this table

Re: how to increase a performance of a table in oracle 10g [message #254494 is a reply to message #254326] Thu, 26 July 2007 23:24 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member

hi vipindiwan,

how to do this...

please help me...

[Updated on: Thu, 26 July 2007 23:25]

Report message to a moderator

Re: how to increase a performance of a table in oracle 10g (merged) [message #254495 is a reply to message #253900] Thu, 26 July 2007 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
vipindiwan,

Congratulations, this problem is now yours to solve.

You're On Your Own (YOYO)!
Re: how to increase a performance of a table in oracle 10g (merged) [message #254496 is a reply to message #254495] Thu, 26 July 2007 23:32 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
i m just asking which index i will use to here..

becasue i used different indexes but not yet succeed.

Re: how to increase a performance of a table in oracle 10g (merged) [message #254497 is a reply to message #253900] Thu, 26 July 2007 23:35 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
With Oracle characters between single quote marks are strings; such as 'this is NOT a date 2007-07-26'
When you want a DATE data type you should use TO_DATE.
Re: how to increase a performance of a table in oracle 10g (merged) [message #254498 is a reply to message #254497] Thu, 26 July 2007 23:38 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
ya i know..here date defined in char;

here is 6 lac records

how to do all this.

i just joined here.

i m new in this field ...so i have face problems
Re: how to increase a performance of a table in oracle 10g (merged) [message #254501 is a reply to message #253900] Thu, 26 July 2007 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You ignored what Michel suggested.
You reached out to "hi vipindiwan" so you can work out your problem with vipindiwan if he ever returns & can assist.

Your On Your Own (YOYO)!
Re: how to increase a performance of a table in oracle 10g (merged) [message #254502 is a reply to message #254501] Thu, 26 July 2007 23:54 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
What michel said right now i m doing that...

but here is i m working on client side oracle
Re: how to increase a performance of a table in oracle 10g (merged) [message #254529 is a reply to message #254502] Fri, 27 July 2007 01:13 Go to previous messageGo to next message
vipindiwan
Messages: 7
Registered: July 2007
Location: INDIA
Junior Member

hi anamika,

please increase the shared pool size OR CHECK TABLESPACE
Re: how to increase a performance of a table in oracle 10g [message #254532 is a reply to message #254326] Fri, 27 July 2007 01:18 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
shared_pool_size = 372M;

one tablespace size is 1500 or 2000

[Updated on: Fri, 27 July 2007 01:21]

Report message to a moderator

Re: how to increase a performance of a table in oracle 10g [message #254549 is a reply to message #254532] Fri, 27 July 2007 01:53 Go to previous messageGo to next message
vipindiwan
Messages: 7
Registered: July 2007
Location: INDIA
Junior Member

increase shared pool=1000m

if it work little bit then increase more
otherwise tell me
Re: how to increase a performance of a table in oracle 10g [message #254582 is a reply to message #254549] Fri, 27 July 2007 03:46 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
not too much difference..

which index i will use
Re: how to increase a performance of a table in oracle 10g [message #254595 is a reply to message #254582] Fri, 27 July 2007 04:23 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
hi Michel,

This is my trace file
Re: how to increase a performance of a table in oracle 10g [message #254603 is a reply to message #254595] Fri, 27 July 2007 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't read it and will not.
Use tkprof to analyze it and post tkprof result.

Regards
Michel
Re: how to increase a performance of a table in oracle 10g [message #254615 is a reply to message #254603] Fri, 27 July 2007 04:54 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
TKPROF rnd_ora_304.trc rnd_ora_304.txt explain=dev/dev@rnd table=sys.t_receiptd


this is right command or not

here ,,,error is coming

SP2-0734: unknown command beginning "TKPROF rnd..." - rest of line ignored.


this is the error..
Re: how to increase a performance of a table in oracle 10g [message #254617 is a reply to message #254615] Fri, 27 July 2007 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the explain..., just keep "TKPROF rnd_ora_304.trc rnd_ora_304.txt".
Check your ORACLE_HOME is set, check your PATH contains ORACLE_HOME/bin directory. If SQL*Plus work, tkprof should work unless you just have a basic client.
Use this on the server.

Regards
Michel
Re: how to increase a performance of a table in oracle 10g [message #254631 is a reply to message #254617] Fri, 27 July 2007 05:42 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member
again error show...

all things are in my system..it is not a client server machine
Re: how to increase a performance of a table in oracle 10g [message #254665 is a reply to message #254631] Fri, 27 July 2007 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TKPROF command is in lower case "tkprof" (without quotes).

Regards
Michel
Re: how to increase a performance of a table in oracle 10g [message #254667 is a reply to message #254615] Fri, 27 July 2007 07:14 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
tkprof is not a sql or sqlplus command. Go to a command line to run it (which you will notice, Michel did in his posted example)
Re: how to increase a performance of a table in oracle 10g [message #254670 is a reply to message #254667] Fri, 27 July 2007 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well said, I didn't notice "SP2-0734".

Regards
Michel

[Updated on: Fri, 27 July 2007 07:34]

Report message to a moderator

Re: how to increase a performance of a table in oracle 10g [message #254724 is a reply to message #254670] Fri, 27 July 2007 13:14 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
TKPROF
If you are working with Tkprof connect to a new session, oracle create trace for a session
SQL> Alter session set sql_trace = true;
SQL> Alter session set time_statistics = true;
After this write a query for which you have to see performance
SQL> select * from tablename where receiptd_date='12/12/2006';
SQL> Alter session set sql_trace = false;
Go to dos
D:\>tkprof c:\ora01780.trc c:\report.txt explain = sap/sap sys = yes
Then see report.txt

note i made trace file name ora01780.trc which i copied on C drive on server then i run tkprof utility as mention above.

In your case anamika explain = username/password sys = yes.

then send output to michel... Wink
Re: how to increase a performance of a table in oracle 10g [message #254726 is a reply to message #254724] Fri, 27 July 2007 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I want wait events, this is why I asked for "event 10046", depending on version sql_trace won't give wait events.
I don't want explain which requests a connection to the database and is useless as row source is already in the trace file.
So just tkprof without any optional parameter (just infile and outfile).

Regards
Michel
Re: how to increase a performance of a table in oracle 10g (merged) [message #254762 is a reply to message #254529] Sat, 28 July 2007 00:50 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

please increase the shared pool size OR CHECK TABLESPACE

How can you recommended increase shared pool size without knowling much details about query ?

There is way to troubleshoot performance related problem as micheal suggested. First we have to analyze trace file output then we will take any decision.

@anamika
if you don't know how to use tkprof utility please search on google and documentation ...

http://www.oracle.com/pls/db10g/search?remark=quick_search&word=tkprof

and you can use "tkprof" utility in Oracle Client.


Regards
Taj
Re: how to increase a performance of a table in oracle 10g (merged) [message #254879 is a reply to message #254762] Sun, 29 July 2007 23:35 Go to previous messageGo to next message
anamika_025
Messages: 81
Registered: July 2007
Location: Indore
Member


THIS IS THE RESULT OF OUTPUT FILE..............
C:\>tkprof E:\oracle\product\10.2.0\admin\RND\udump\rnd_ora_2468.trc d:\report.t
xt explain=dev/dev sys=yes


TKPROF: Release 10.2.0.1.0 - Production on Mon Jul 30 09:57:13 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: E:\oracle\product\10.2.0\admin\RND\udump\rnd_ora_2468.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

Alter session set time_statistics = true

Error encountered: ORA-02248
********************************************************************************

Alter session set sql_trace = true


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.04 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.04 0 0 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (DEV)
********************************************************************************

Alter session set timed_statistics = true


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (DEV)
********************************************************************************

select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 0 0 0
Fetch 1 0.00 0.02 1 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.05 1 3 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=1 pw=0 time=28556 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=1 pw=0 time=28524 us)(object id 37)

********************************************************************************

select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
ts.logicalread
from
tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 1 0.00 0.01 1 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.02 1 4 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS OUTER (cr=4 pr=1 pw=0 time=11043 us)
1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=1 pw=0 time=11002 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=1 pw=0 time=10933 us)(object id 3)
0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=23 us)
0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=11 us)(object id 709)

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 60 0.00 0.00 0 0 0 0
Fetch 60 0.01 0.02 2 179 0 59
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 121 0.01 0.02 2 179 0 59

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=66 us)
1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=35 us)(object id 257)

********************************************************************************

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=3 pr=0 pw=0 time=417 us)
0 HASH JOIN OUTER (cr=3 pr=0 pw=0 time=388 us)
0 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=79 us)
0 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=70 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=33 us)(object id 3)
0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=0 pr=0 pw=0 time=0 us)(object id 711)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_CDEF2 (cr=0 pr=0 pw=0 time=0 us)(object id 51)

********************************************************************************

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 25 0.00 0.00 0 3 0 24
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.00 0.00 0 3 0 24

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
24 SORT ORDER BY (cr=3 pr=0 pw=0 time=195 us)
24 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=168 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=26 us)(object id 3)

********************************************************************************

select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
NVL(scanhint,0)
from
seg$ where ts#=:1 and file#=:2 and block#=:3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=66 us)
1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=28 us)(object id 9)

********************************************************************************

select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY (cr=2 pr=0 pw=0 time=75 us)
0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=39 us)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=27 us)(object id 103)

********************************************************************************

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY (cr=2 pr=0 pw=0 time=85 us)
0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=45 us)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=32 us)(object id 103)

********************************************************************************

select con#,obj#,rcon#,enabled,nvl(defer,0)
from
cdef$ where robj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 1 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=1 pr=0 pw=0 time=43 us)
0 INDEX RANGE SCAN I_CDEF3 (cr=1 pr=0 pw=0 time=31 us)(object id 52)

********************************************************************************

select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
cdef$ where obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=36 us)
0 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=24 us)(object id 51)

********************************************************************************

select /*+ rule */ bucket, endpoint, col#, epvalue
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 41 0.00 0.00 0 0 0 0
Fetch 41 0.01 0.05 12 123 0 695
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 83 0.01 0.05 12 123 0 695

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
8 SORT ORDER BY (cr=3 pr=1 pw=0 time=13821 us)
8 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=1 pw=0 time=13766 us)
1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=28 us)(object id 252)

********************************************************************************

select *
from
t_receiptd where receiptd_date='12/12/2006'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 71 0.57 2.51 13317 13390 0 1037
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 73 0.60 2.56 13317 13390 0 1037

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (DEV)

Rows Row Source Operation
------- ---------------------------------------------------
1037 TABLE ACCESS FULL T_RECEIPTD (cr=13390 pr=13317 pw=0 time=1344006 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1037 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T_RECEIPTD' (TABLE)

********************************************************************************

Alter session set sql_trace = false


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (DEV)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.03 0.04 0 0 0 0
Execute 5 0.00 0.05 0 0 0 0
Fetch 71 0.57 2.51 13317 13390 0 1037
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80 0.60 2.62 13317 13390 0 1037

Misses in library cache during parse: 4
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 11 0.00 0.00 0 0 0 0
Execute 110 0.01 0.06 0 0 0 0
Fetch 134 0.03 0.11 16 325 0 781
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 255 0.04 0.18 16 325 0 781

Misses in library cache during parse: 11
Misses in library cache during execute: 11

5 user SQL statements in session.
110 internal SQL statements in session.
115 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: E:\oracle\product\10.2.0\admin\RND\udump\rnd_ora_2468.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
5 user SQL statements in trace file.
110 internal SQL statements in trace file.
115 SQL statements in trace file.
15 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
DEV.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
1079 lines in trace file.
330 elapsed seconds in trace file.


Re: how to increase a performance of a table in oracle 10g (merged) [message #254881 is a reply to message #253900] Sun, 29 July 2007 23:43 Go to previous messageGo to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
The TKPROF output was not formatted & slightly difficult to read.
I saw the elasped time was less than 3 seconds & no where near 2+ hours.

I guess I'll believe TKPROF over unsubstantiated reports.
Alternatively use:
SQL> SET TIME ON

Previous Topic: Help : Histograms Size
Next Topic: Need help - Tuning the SQL
Goto Forum:
  


Current Time: Mon Dec 05 20:58:34 CST 2016

Total time taken to generate the page: 0.06305 seconds