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 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #203410 is a reply to message #203365] Wed, 15 November 2006 01:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Get rid of the FIRST_ROWS hint and run the explain plan again.

Ross Leishman
Re: Is this the best performed query? [message #203533 is a reply to message #203410] Wed, 15 November 2006 07:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Is this the best performed query? [message #203878 is a reply to message #203620] Thu, 16 November 2006 15:43 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Ross, Thanks for diagnosis the problem.

I will try creating index.

What you do think about partitioning the large table ?
will it help?


Thanking you again.

Re: Is this the best performed query? [message #203900 is a reply to message #203878] Thu, 16 November 2006 21:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Partitioning would only help if you didn't create the index, and then only if you partitioned both of the big tables on the join-key.

Ross Leishman

Re: Is this the best performed query? [message #203926 is a reply to message #203900] Thu, 16 November 2006 23:54 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Thanks for you guidence.

I will try with partition option.
and will be back here if face any problem.




Previous Topic: Chained Fetch Ratio & Parse to Execute Ratio
Next Topic: Please Help me for Tune
Goto Forum:
  


Current Time: Sat Dec 03 10:21:33 CST 2016

Total time taken to generate the page: 0.07053 seconds