-
Pavb U r in my way.
I too tried to fine tune the subquery,but nothing helped me(i.e, not major change).So i posted here to get some guru's idea.
-
How about doing what hrishy suggested for subquery #2?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Why don't you try to force the indexes or you can choose to accept the full scan...but can help it along with a PARALLEL HINT. In some cases it's quite good. Full table scans are not necessarily a bad thing. I have seen situations where indexes have performed worse.
Looking for the greatest evil in the world? Look in the mirror.
-
Code:
SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'
37115 rows processed
Elapsed: 00:00:34.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2435 Card=382725 Bytes=8802675)
1 0 HASH JOIN (Cost=2435 Card=382725 Bytes=8802675)
2 1 HASH JOIN (Cost=1981 Card=69850 Bytes=1257300)
3 2 TABLE ACCESS (FULL) OF 'PERSON' (Cost=673 Card=33831 Bytes=338310)
4 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=1266 Card=158302 Bytes=1266416)
5 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)
Statistics
----------------------------------------------------------
35 recursive calls
0 db block gets
13929 consistent gets
0 physical reads
0 redo size
424699 bytes sent via SQL*Net to client
27709 bytes received via SQL*Net from client
2476 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37115 rows processed
37115 rows selected.
----------------------------------------------------------------------------------------------------------------------------------------
After creating composite index
SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'
Elapsed: 00:00:02.68
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1479 Card=397716 Bytes=7556604)
1 0 HASH JOIN (Cost=1479 Card=397716 Bytes=7556604)
2 1 HASH JOIN (Cost=1028 Card=72586 Bytes=1016204)
3 2 TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=278672)
4 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=164400 Bytes=986400)
5 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13918 consistent gets
0 physical reads
0 redo size
528694 bytes sent via SQL*Net to client
27713 bytes received via SQL*Net from client
2476 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37115 rows processed
Hrishy thanks a lot,Plan doesn't changes.but execution time remarkable.Another subquery has no change.
please suggest to change NOT LIKE predicate.
-
Originally Posted by ams-jamali
please suggest to change NOT LIKE predicate.
As far as I can see you have test data in your production database... bad mojo but, not the first time I see something like this.
You can either delete those test rows or filtering them out; since I do not know your environment I would filter them out after the query completes meaning, run your query as if all your data is valid and then filter it.
You can accomplish this by enveloping your query into an outer filter query, something like:
Code:
select pern,
regs,
regt
from
(
SELECT rp.person_nr "pern",
rp.regimen_sequence "regs",
rp.regimen_type_code "regt",
p.email "mail"
FROM REGIMEN_PRES rp,
PERSON p
WHERE regimen_type_code = 'CD'
AND prescription_status_code= 'FNSH'
AND rp.person_nr = p.person_nr
-- and p.email not like '%test%'
...
...
...
)
where email not like '%test%'
ORDER BY pern;
Note: Code was not tested, this is just a hint.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi
Even though the plans are same the cardinalities are different.
So i am wondering now how do you collect stats .
Can you post exact statements which you use to collect stats
regards
Hrishy
-
I gave only timing and trace output of that statement.
before creating comp.index i took timing with trace for that query(i ran that query twice same time returns,since it may vary due to buffer cache),then after creating index i ran that same query.I saw a big difference in timing.
-
Hi
Can you run the query again and post the query and the plan again using code tags
regards
Hrishy
-
here is the report,
Code:
SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG')
Elapsed: 00:00:02.47
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1448 Card=225193 Bytes=5179439)
1 0 HASH JOIN (Cost=1448 Card=225193 Bytes=5179439)
2 1 HASH JOIN (Cost=1007 Card=41099 Bytes=739782)
3 2 TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=278672)
4 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=41100 Bytes=411000)
5 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_QX28' (N
ON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13918 consistent gets
10271 physical reads
0 redo size
528694 bytes sent via SQL*Net to client
27713 bytes received via SQL*Net from client
2476 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37115 rows processed
So what u r expecting..?
To..,PAVB
There is a slight change in timing.(nearly 1sec).,thanks for your suggestion
-
Hi
I want to see EXACTLY what statements do you use to gather your stats.
Your cardinalities still don't match to what you posted earlier by the way
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|