Home » RDBMS Server » Performance Tuning » Is this the best performed query?
| Is this the best performed query? [message #203333] |
Tue, 14 November 2006 16:11  |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Hi there!
Please help to figure out whether the following query is best performed or still there are/is any chances to improve.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'ASH'
2 FOR
3 SELECT /*+ FIRST_ROWS(30) */ PSP.PatientNumber, PSP.IntakeID, U.OperationCenterCode OpCenterProcessed,
4 PSP.ServiceCode, PSP.UOMcode, PSP.StartDt, PSP.ProvID, PSP.ExpDt, NVL(PSP.Units, 0) Units,
5 PAS.Descript, PAS.ServiceCatID, PSP.CreatedBy AuthCreatedBy, PSP.CreatedDateTime AuthCreatedDateTime,
6 PSP.AuthorizationID, PSP.ExtracontractReasonCode, PAS.ServiceTypeCode,
7 NVL(PSP.ProvNotToExceedRate, 0) ProvOverrideRate,
8 prov.ShortName ProvShortName, PSP.OverrideReasonCode, PAS.ContractProdClassId
9 ,prov.ProvParentID ProvParentID, prov.ProvTypeCd ProvTypeCd
10 FROM tblPatServProv psp, tblProductsAndSvcs pas, tblProv prov, tblUser u, tblGlMonthlyClose GLMC
11 WHERE GLMC.AUTHORIZATIONID >= 239
12 AND GLMC.AUTHORIZATIONID < 11039696
13 AND PSP.AuthorizationID = GLMC.AUTHORIZATIONID
14 AND PSP.Authorizationid < 11039696
15 AND (PSP.ExpDt >= to_date('01/03/2000','MM/DD/YYYY') OR PSP.ExpDt IS NULL)
16 AND PSP.ServiceCode = PAS.ServiceCode(+)
17 AND prov.ProvID(+) = PSP.ProvID
18* AND U.UserId(+) = PSP.CreatedBy
19 /
Explained.
Elapsed: 00:00:00.46
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3602678330
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8503K| 3073M| 91 (2)| 00:00:02 |
|* 1 | HASH JOIN RIGHT OUTER | | 8503K| 3073M| 91 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL | TBLPRODUCTSANDSVCS | 4051 | 209K| 16 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 31 | 6200 | 75 (2)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 30 | 5820 | 45 (3)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER | | 30 | 4950 | 15 (7)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TBLUSER | 3444 | 58548 | 12 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | TBLPATSERVPROV | 8301K| 585M| 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| TBLPROV | 1 | 29 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_TBLPROV | 1 | | 0 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_W_GLMONTHLYCLOSE | 1 | 6 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PSP"."SERVICECODE"="PAS"."SERVICECODE"(+))
5 - access("U"."USERID"(+)="PSP"."CREATEDBY")
7 - filter(("PSP"."EXPDT">=TO_DATE('2000-01-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"PSP"."EXPDT" IS NULL) AND "PSP"."AUTHORIZATIONID">=239 AND "PSP"."AUTHORIZATIONID"<11039696)
9 - access("PROV"."PROVID"(+)="PSP"."PROVID")
10 - access("PSP"."AUTHORIZATIONID"="GLMC"."AUTHORIZATIONID")
filter("GLMC"."AUTHORIZATIONID">=239 AND "GLMC"."AUTHORIZATIONID"<11039696)
28 rows selected.
Elapsed: 00:00:00.42
|
|
|
|
| Re: Is this the best performed query? [message #203356 is a reply to message #203333] |
Tue, 14 November 2006 19:56  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Help us out a bit here:
- How many rows in each of the tables?
- How many rows in each table match the filter criteria in the query?
- How many rows do you expect the query to return?
- Do you plan to fetch every row returned, or just the first few and then discard the rest?
Ross Leishman
|
|
|
|
| Re: Is this the best performed query? [message #203365 is a reply to message #203356] |
Tue, 14 November 2006 22:38 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Hey Ross,
Really great to see your reply, Thanks, Ross.
Trying to answare your questions,
Please let me know if you need further info.
- How many rows in each of the tables?
ROWS
=====
SELECT count(*) FROM tblPatServProv psp ----> 10,609,068
SELECT count(*) FROM tblGlMonthlyClose GLMC ---> 9,372,970
SELECT count(*) FROM tblProv prov ---> 16,218
SELECT count(*) FROM tblProductsAndSvcs pas ---> 4,051
SELECT count(*) FROM tblUser u ---> 3,444
- How many rows in each table match the filter criteria in the query?
SELECT count(*) FROM tblGlMonthlyClose GLMC
WHERE GLMC.AUTHORIZATIONID >= 239
AND GLMC.AUTHORIZATIONID < 11039696
Count(*)
--------
9,372,969
SELECT count(*) FROM tblPatServProv psp,tblGlMonthlyClose GLMC
where PSP.AuthorizationID = GLMC.AUTHORIZATIONID
AND PSP.Authorizationid < 11039696
Count(*)
--------
9,369,205
SELECT count(*) FROM tblPatServProv psp,tblGlMonthlyClose GLMC
where PSP.AuthorizationID = GLMC.AUTHORIZATIONID
AND PSP.Authorizationid < 11039696
AND (PSP.ExpDt >= TO_DATE('01/03/2000','MM/DD/YYYY') OR PSP.ExpDt IS NULL)
Count(*)
--------
8,491,626
SELECT count(*) FROM tblPatServProv psp,tblProductsAndSvcs pas where PSP.ServiceCode = PAS.ServiceCode(+)
Count(*)
--------
10,609,068
SELECT count(*) FROm tblPatServProv psp,tblProv prov ,tblUser u where prov.ProvID(+) = PSP.ProvID
AND U.UserId(+) = PSP.CreatedBy;
Count(*)
--------
10,609,068
- How many rows do you expect the query to return?
query should return ----> 8,491,626
- Do you plan to fetch every row returned, or just the first few and then discard the rest?
fetch every row returned by the query and process logic.
|
|
|
|
|
|
| Re: Is this the best performed query? [message #203533 is a reply to message #203410] |
Wed, 15 November 2006 07:32 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Ross,thanks for looking in details.
Here is the plan withtout first_rows hint.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'ASH'
2 FOR
3 SELECT psp.patientnumber, psp.intakeid,
4 u.operationcentercode opcenterprocessed, psp.servicecode, psp.uomcode,
5 psp.startdt, psp.provid, psp.expdt, NVL (psp.units, 0) units,
6 pas.descript, pas.servicecatid, psp.createdby authcreatedby,
7 psp.createddatetime authcreateddatetime, psp.authorizationid,
8 psp.extracontractreasoncode, pas.servicetypecode,
9 NVL (psp.provnottoexceedrate, 0) provoverriderate,
10 prov.shortname provshortname, psp.overridereasoncode,
11 pas.contractprodclassid, prov.provparentid provparentid,
12 prov.provtypecd provtypecd
13 FROM tblpatservprov psp,
14 tblproductsandsvcs pas,
15 tblprov prov,
16 tbluser u,
17 tblglmonthlyclose glmc
18 WHERE glmc.authorizationid >= 239
19 AND glmc.authorizationid < 11039696
20 AND psp.authorizationid = glmc.authorizationid
21 AND psp.authorizationid < 11039696
22 AND (psp.expdt >= TO_DATE ('01/03/2000', 'MM/DD/YYYY') OR psp.expdt IS NULL
23 )
24 AND psp.servicecode = pas.servicecode(+)
25 AND prov.provid(+) = psp.provid
26 AND u.userid(+) = psp.createdby;
Explained.
Elapsed: 00:00:00.21
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 660171076
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8503K| 1451M| | 139K (2)| 00:27:51 |
|* 1 | HASH JOIN RIGHT OUTER | | 8503K| 1451M| | 139K (2)| 00:27:51 |
| 2 | TABLE ACCESS FULL | TBLPROV | 16750 | 474K| | 190 (2)| 00:00:03 |
|* 3 | HASH JOIN RIGHT OUTER | | 8503K| 1216M| | 138K (2)| 00:27:48 |
| 4 | TABLE ACCESS FULL | TBLPRODUCTSANDSVCS | 4051 | 209K| | 16 (0)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER | | 8503K| 786M| | 138K (2)| 00:27:47 |
| 6 | TABLE ACCESS FULL | TBLUSER | 3444 | 58548 | | 12 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 8503K| 648M| 160M| 138K (2)| 00:27:46 |
|* 8 | INDEX FAST FULL SCAN| PK_W_GLMONTHLYCLOSE | 9372K| 53M| | 5698 (4)| 00:01:09 |
|* 9 | TABLE ACCESS FULL | TBLPATSERVPROV | 8503K| 600M| | 83901 (2)| 00:16:47 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PROV"."PROVID"(+)="PSP"."PROVID")
3 - access("PSP"."SERVICECODE"="PAS"."SERVICECODE"(+))
5 - access("U"."USERID"(+)="PSP"."CREATEDBY")
7 - access("PSP"."AUTHORIZATIONID"="GLMC"."AUTHORIZATIONID")
8 - filter("GLMC"."AUTHORIZATIONID">=239 AND "GLMC"."AUTHORIZATIONID"<11039696)
9 - filter(("PSP"."EXPDT">=TO_DATE('2000-01-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"PSP"."EXPDT" IS NULL) AND "PSP"."AUTHORIZATIONID">=239 AND "PSP"."AUTHORIZATIONID"<11039696)
27 rows selected.
Elapsed: 00:00:00.31
|
|
|
|
| Re: Is this the best performed query? [message #203620 is a reply to message #203533] |
Wed, 15 November 2006 19:43 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
That plan is optimal for the sort of data volumes you are running.
You could improve it by creating an index on tblglmonthlyclose(authorizationid). The index PK_W_GLMONTHLYCLOSE contains authorizationid, but not as a leading column I think.
The problem at the moment is the HASH JOIN between the two big tables. Oracle cannot hash either of them into memory, so it has to partition them in TEMP space which then pages to disk.
Creating the index will permit Oracle to perform a Nested Loops join instead of a HASH join. Normally, a NL join would be suicide for such a high-volume SQL, but you are only using one column of the table, so it can satisfy the join using just the index - it does not have to lookup the table row - this is what kill high-volume nested-loops joins.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 02 01:35:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
