Partition range - iterator or single 1

[Feodorit]

I have a question about Oracle Database (9i).
There are two simple queries:

select * from t3_main where data <= trunc(sysdate-112) and data >= trunc(sysdate-112);
select * from t3_main where data = trunc(sysdate-112);

First of this queries parsed for nearly 1 second and executes for nearly 1 second
Second query, as you see? provide the same result, but parsed for nearly 13 seconds, and executes for nearly 1 second.
Yes, FIRST query need much less time for parsing, and the second query run after the first.
On more complex queries parsing time of second type query dramatically increased (in real system 1.3 second for first query and 30 minutes for parsing second query).

t3_main - medium size, partitioned by date on daily basis table.

Execution plans for queries respectively:

1)
SELECT STATEMENT Optimizer=CHOOSE (Cost=4746 Card=14480 Bytes=2896000)
FILTER
PARTITION RANGE (ITERATOR)
TABLE ACCESS (FULL) OF T3_MAIN (Cost=4746 Card=14480 Bytes=2896000)

2)
SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=5965 Bytes=512990)
PARTITION RANGE (SINGLE)
TABLE ACCESS (FULL) OF T3_MAIN (Cost=8 Card=5965 Bytes=512990)

Yes Cost optimization is impressible.

Question: &quot;is it bug, feature or...&quot; - why so hard parsing and how can I avoid it, because for hours parsing for complex join queries.


With respect, Arsen Kroptya.

 

[sem]

Are you really talking about seconds, not about milliseconds? If so, could you provide your hardware details? And how have you measured these intervals?
I can't imagine how such simple query may take more than 1 millisecond for parsing.

Regards, Dima

http://seminihin.narod.ru

 

[Feodorit]

sem> And how have you measured these intervals?
Execution time I get from front-end Oracle tools and parsing time intervals is my own based primary on &quot;Prepare statement...&quot; and tkprof and getting statement differences.


sem> Are you really talking about seconds, not about milliseconds?
I agree that first query may be parsing for less than second, but at the case of second query I can distinguish 13 seconds from milliseconds.

sem> If so, could you provide your hardware details?
dual-processor Xeon 2.4 Ghz, 2 Gb of RAM.

 

[sem]

Wow! Can you send snapshots of v$sgastat and v$waitstat?

Regards, Dima

http://seminihin.narod.ru

 

[Feodorit]

select * from v$sgastat;

fixed_sga 456092
buffer_cache 763363328
log_buffer 656384
shared pool errors 193316
shared pool subheap 50816
shared pool KGK heap 3756
shared pool KQR L PO 1024
shared pool KQR M PO 78188712
shared pool KQR S PO 2134296
shared pool KQR S SO 17172
shared pool KQR X PO 170252
shared pool sql area 79921208
shared pool KGLS heap 5891676
shared pool PX subheap 19392
shared pool parameters 465460
shared pool free memory 28845916
shared pool PL/SQL DIANA 2565080
shared pool PL/SQL MPCODE 7297084
shared pool PL/SQL PPCODE 717456
shared pool PL/SQL SOURCE 21192
shared pool library cache 24131928
shared pool miscellaneous 24006388
shared pool pl/sql source 7360
shared pool MTTR advisory 139428
shared pool XDB Schema Cac 4528732
shared pool joxlod: in ehe 497568
shared pool joxs heap init 4220
shared pool kgl simulator 6864628
shared pool partitioning d 7964904
shared pool sim memory hea 424024
shared pool table definiti 19176
shared pool trigger defini 8700
shared pool trigger inform 2024
shared pool trigger source 3056
shared pool type object de 102880
shared pool dictionary cache 1614976
shared pool fixed allocation callback 264
large pool free memory 192937984
java pool free memory 118169600
java pool memory in use 7659520

 

[Feodorit]

select * from v$waitstat;

CLASS COUNT TIME
data block 4798 415
sort block 0 0
save undo block 0 0
segment header 28 0
save undo header 0 0
free list 0 0
extent map 0 0
1st level bmb 0 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 1066 8
undo block 7321 276

 

[sem]

How about waitstat? Do you experince such problems with all partitioned tables? Or only with that specific one? BTW, how large is it? Appr. number of rows and partitions?

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Does tkprof show the same (13 seconds) time?

Regards, Dima

http://seminihin.narod.ru

 

[Feodorit]

sem> Do you experince such problems with all partitioned tables?
No, for 3 from 7 in this schema.

sem> BTW, how large is it? Appr. number of rows and partitions?
Medium size, I will take this table for test (Rows near 6 millions. Partitions 760).

 

[sem]

Measuring times on client side doesn't take into account client and network problems, so this may be an issue of that components, not database one.

Regards, Dima

http://seminihin.narod.ru

 

[sem]

I think that the number of partitions is really huge (doubt such design can improve performance), but not to produce 13 seconds parse time Can you send tkprof output?

Regards, Dima

http://seminihin.narod.ru

 

[Feodorit]

sem> Does tkprof show the same (13 seconds) time?

Maybe it is lack of my knowledge in Oracle, but I don't know where tkprof show time for parsing,
time for executed is 0.8 second, but if I send statement, and for a period time tkprof not show that session execute statement.
Adding some where statements it is easy to get a half an hour to ensure that tkprof not see statement and after all read tkprof log that statement take seconds.

 

[Feodorit]

sem> Measuring times on client side doesn't take into account client and network problems

Question is comparison two queries, in same conditions. Test repeated many times during 4 days.
Now I will try to compare explain plan statements for this queries.

 

[sem]

Find expected file name:

select u.value || '/' || i.value || '_ora_' || p.spid
|| nvl2(p.traceid, '_' || p.traceid, null ) || '.trc' Filename
from
v$parameter u,
v$parameter i,
v$process p,
v$session s
where
u.name = 'user_dump_dest' and
i.name = 'instance_name' and
s.audsid=sys_context('userenv','sessionid') and
p.addr = s.paddr


Enable tracing:
alter session set sql_trace=true;

Execute statements:

select * from t3_main where data <= trunc(sysdate-112) and data >= trunc(sysdate-112);
select * from t3_main where data = trunc(sysdate-112);

Disable tracing:
alter session set sql_trace=false;

Invoke tkprof (on server!):

tkprof <filename from the 1st query> <readable report name> SYS=NO


Regards, Dima

http://seminihin.narod.ru

 

[Feodorit]

Thank You, with your help we at last have precisely stated problem:

results from tkprof

EXPLAIN PLAN SET statement_id = 'firstquery' FOR select * from t2_main where
data = trunc(sysdate-156)


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

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92
********************************************************************************


EXPLAIN PLAN SET statement_id = 'secondquery' FOR select * from t7_main where
data >= trunc(sysdate-156) and data <= trunc(sysdate-156)


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

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92



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

 

[sem]

Can you send the whole file? Or publish it somewhere on the web? As I see, EXPLAIN statement takes this time, not real query. Do you use stored outlines intensively? Send also the output of

select (1-(sum(getmisses)/ sum(gets)))*100
&quot;Hit Ratio&quot; from v$rowcache


Regards, Dima

http://seminihin.narod.ru

 

[Feodorit]

TKPROF: Release 9.2.0.2.1

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: uzc1_ora_640.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:

insert into plan_table (statement_id, timestamp, operation, options,object_node,

Error encountered: ORA-00904
********************************************************************************

alter session set sql_trace=true


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 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 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 92
********************************************************************************

alter session set timed_statistics=true


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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92
********************************************************************************

EXPLAIN PLAN SET statement_id = 'firstquery' FOR select * from t2_main where
data = trunc(sysdate-156)


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

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92
********************************************************************************

select user#
from
sys.user$ where name = 'OUTLN'


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 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1

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

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 r=0 w=0 time=56 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 r=0 w=0 time=29 us)(object id 44)

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

insert into plan_table (statement_id, timestamp, operation, options,
object_node, object_owner, object_name, object_instance, object_type,
search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
bytes, other_tag, partition_start, partition_stop, partition_id,
distribution, cpu_cost, io_cost, temp_space )
values
1,SYSDATE,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,
:20,:21,:22,:23,:24,:25)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 7 0.01 0.04 4 2 8 7
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.01 0.05 4 2 8 7

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92 (recursive depth: 1)
********************************************************************************

select o.name, u.name
from
sys.obj$ o, sys.user$ u where obj# = :1 and owner# = user#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 0 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
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

EXPLAIN PLAN SET statement_id = 'firstquery' FOR select * from t7_main where
data >= trunc(sysdate-156) and data <= trunc(sysdate-156)


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

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 180.07 184.47 0 0 0 0
Execute 4 0.01 0.02 1 2 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 180.09 184.49 1 2 2 0

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


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 8 0.01 0.04 4 2 8 7
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.01 0.05 4 4 8 8

Misses in library cache during parse: 3

6 user SQL statements in session.
3 internal SQL statements in session.
9 SQL statements in session.
********************************************************************************
Trace file: uzc1_ora_640.trc
Trace file compatibility: 9.00.01
Sort options: default

15 sessions in tracefile.
6 user SQL statements in trace file.
3 internal SQL statements in trace file.
9 SQL statements in trace file.
7 unique SQL statements in trace file.
367 lines in trace file.

 

[Feodorit]

SQL> select (1-(sum(getmisses)/ sum(gets)))*100
2 &quot;Hit Ratio&quot; from v$rowcache;

Hit Ratio
----------
99,2230576

Thank You for spending your valuable time, but I need some time to thoroughly verify my data dictionary.

 

[sem]

Is it an old upgraded/imported database? ORA-00904 often occurs when executing EXPLAIN PLAN against an old version of PLAN_TABLE. Another possible reason of bad paring is wrong statistics gathered for SYS schema.
BTW this report still shows NO real SELECT statemens with large parse time

Regards, Dima

http://seminihin.narod.ru

 

[AOLEURO]

Hi Feodorit

Why do you want a partitioned table if only 6 millions rows and why 760 partitions?

Do you understand the basic concept for partition?

In generally - partition do not increase performance - the idea is to minimize contention between concurrent sessions and increase availability. To increase performance you really need to control all queries and DML on the table.

Then you test a query you may see that the query is slower than if the table wasn’t partitioned. It is because Oracle has to make some “startup”, before accession the indexes and table.

Is it possible you can list the create table and create indexes on the table – and count (group by) on each index so I can se the numbers of rows in each index.

It may not help you - but I can see your idea for partition.

Regards
Allan
Icq: 346225948